Doorgaan naar de website
OCLC Support

Create an Excel file of barcodes

Learn how to create a list of barcodes in Amlib to be used for the Overdrive Card manager and other e-book platforms.

This report is used to create a list of barcodes to be used for the Overdrive Card manager and other e-book platforms that require a barcode list of the borrowers.

Add New Report to Amlib Reports

  1. Save the file &XBARCDPIN.qrp to your Amlib Reports folder, or your documents folder if being done on a single PC.
  2. Go to Main > Reports > RepBorrower.
  3. Click on Application > RepAddNew.
    clipboard_e4b48d0ab29d6d6067fe9bf79a0c85d01.png
  4. Click on F1 New.
    clipboard_e8d55949eea98895e535da5befd2d4abf.png
  5. Type in a Description – for example: Overdrive CSV Export
    clipboard_ee1297f07d1f489202de19a16e8cbb8b9.png
  6. Browse to the Amlib/Reports folder on your Amlib and locate the template (&XBARCDPIN.QRP) to be loaded (if the template has been loaded onto your PC –then navigate to the local folder). Select File Of type ‘All files’.
  7. Click the Open button.
  8. Select the appropriate Choose Type – Borrower (Default).
  9. You can leave Default Stats Code blank.
  10. Update Entity (Y/N) = N
  11. Click the F3 Update button.
  12. Close out of the Report Files window.
  13. The template is now loaded and available for use in a Report.

Preparing New Report for Use

  1. Go to Application > RepBorrower.
  2. Click on F1 New.
    clipboard_e70521682900ea9089be59228f67ecf24.png
  3. Find the report you just created, highlight it and click on Select.
    clipboard_e596d45525e2fd31d53543bce292a3bf1.png
  4. Type in a description then click on F3 Save.

Create your Barcode file

  1. Select the report you created (eg. Overdrive CSV Export)
    clipboard_e20acf16fc6bfb2fa92bffee6f8d8fa3d.png
  2. Click on F7 Where.
  3. Parameters should be something like:
    clipboard_e7d92320fb48f072ee36243d0cbb782e6.png
    These parameters will find all borrowers who have registered in the last 2 years, with no outstanding accounts, owing less than $4.00 but does not include Borrowers of the type Bulk loan, External Agency, ILL or Traveller. Choose parameters that fit you r library but they must include the BorStatus and Registration date.
    clipboard_e816dca79695c7a05ffe4036ba8f3c74c.png
  4. Select F3 Save.
  5. Click on F6 Print and select print to File then OK
    clipboard_e92355f6bb1193c6ebb935949536bc88b.png
  6. When the save box opens
    clipboard_e3b38f981b50ea0f3472f062e5dbbc1d2.png
    Name the file (eg. Borrower list overdrive) and save as a text document (.txt) or it will not open properly in Excel. Don’t save as Rich text. 
  7. Click on Application > RepStartSchedule.
    clipboard_eb69a821d4eab801ca1594837c4a9b96d.png
  8. Make sure that Save to File is selected and click on OK

To Open an Excel File of Barcodes

  1. Open Microsoft Excel.
  2. Select Data > From Text select your file you saved (eg. Borrower list overdrive.txt).
  3. Select Import.
    clipboard_e2e6c3178d46534bacdfefb476b3308ea.png
  4. The text import wizard will open.
  5. Click on Delimited then click on Next.
    clipboard_e2b07002cbd9d90395b8b854917ab4de8.png
  6. Select tab and other and type a Pipe | in the box (to make a pipe select the shift key and the backslash \ key that is normally above the Enter key on your keyboard)
    clipboard_e64bca58c76ca6c79e246b2f8a0d983e8.png
    clipboard_eea8336ba51e4832f6eb0c376914993de.png
  7. Click on Next and then Finish.
  8. The excel worksheet will have 2 columns: column A is the barcode, column B is the PIN.
    You may need to extend the width of Column A to read the file properly; it doesn’t matter to Library Card Manager. If you don’t use a PIN in Amlib you can create one here, just edit the file.
  9. You may need to have 4 columns. Column A is for the Barcode, Column B is the PIN number, column C is for Status (only those libraries using Status otherwise leave it blank), Column D is the library.
  10. If you need to add a PIN number you can use a generic one eg. Postcode - Type the PIN into cell B1, copy and then paste into the others in column B. The same techniques can be used for the library name in column D.
    clipboard_e0b386d65126d87b6f639e46bcde7a10e.png
  11. When the file is edited and complete it needs to be saved in .csv format to allow it to be uploaded using Library Card Manager
  12. Select Save As.
    clipboard_e6d9df98a88172397aeb8bbd88b3acffe.png
  13. In Save as Type select CSV (Comma delimited) and then Save.
  14. The .csv file can now be loaded to Library Card Manager. If there are any errors in the file the Library Card Manager will tell you.