Voyager Queries

Holdings spreadsheets

LC conversion

Barcodes to holdings spreadsheet

Shipping list from barcodes

 

Tips and tricks

Unindexed fields

To get field by field number (edit fieldname and 3-digit field number).

  • Field: FIELDNAME: GetFieldRaw(GetBibBLOB([BIB_TEXT].[BIB_ID]),”050″,1)
  • Field: LC Call Number: GetField(GetBibBLOB([BIB_TEXT].[BIB_ID]),”050″,1)

must have in another column:

  • Field: BIB_ID
  • Table: BIB_TEXT

 

Last charge date

Add table: CIRC_TRANS_ARCHIVE; right-click to select Join Properties; ALL records from table linked to CIRC_TRANS_ARCHIVE

  • Field: CHARGE_DATE
  • Table: CIRC_TRANS_ARCHIVE
  • Total: Max

 

Government Documents

Add table: MARCBOOK_VW; Join Properties: ALL records from table linked to MARCBOOK_VW

  • Field: GOVERNMENTPUB
  • Table: MARCBOOK_VW
  • Criteria: “Is Not Null” or “f” for federal

 

Holdings reports

Excel

  1. Freeze Top Row
  2. make top row Bold
  3. Column width = 15
  4. adjust columns as follows:
NETWORK_NUMBER OCLC width: 20
BIB_FORMAT FORMAT width: 5
TITLE_BRIEF TITLE width: 50
LOCATION_CODE MFHD_LOCATION
DISPLAY_CALL_NO CALL_NO width: 30
PERM_LOCATION_CODE ITEM_LOCATION
PERM_ITEM_TYPE ITEM_TYPE width: 30
ITEM_STATUS_DESC ITEM_STATUS
ITEM_STATUS_DATE STATUS_DATE width: 10
and everything to its right
HISTORICAL_CHARGES CHARGES data: Convert to Number
SumOfRENEWAL_COUNT RENEWALS
MaxOfCHARGE_DATE LAST_CHARGE

 

If you add LAS info, delete the Item column.

 

Reduce to a single line for each item record

  1. name the original sheet: all results
  2. copy the sheet, and name it: items
  3. In the first cell of the first free column: results rows
  4. In the second cell of the first free column (edit L to ITEM_ID column, as needed):
    =COUNTIF(L:L, L2)
  5. Fill down
  6. Select everything in the column, then paste just the values in a new column, then delete the old column.
  7. Save
  8. Custom Sort:
    ITEM_ID
    BIB_ID
    ITEM_STATUS_DATE
  9. In the second cell of the first free column (edit L to ITEM_ID column, as needed):
    =IF(L2=L3, “Delete”, “Keep”)
  10. Fill down
  11. Select everything in the column, then paste just the values in a new column, then delete the old column.
  12. Save
  13. Filter for “Delete” and delete lines
  14. Remove filter, then delete the column
  15. Save

 

Criteria filters

You may want to copy the sheet, and filter on the copy.

  • hasn’t circulated in last N# years: filter LAST_CHARGE_DATE, Date Filters after <date>; delete remaining rows; un-filter
  • no charges: filter CHARGES, excluding “0”; delete remaining rows; un-filter
  • published before 2000: filter PUB_DATES_COMBINED, Text Filters: Begins With “20”; delete remaining rows; un-filter

 


 

LC conversion

Voyager queries database

ipm – LC conversion mfhd loc=
set location(s) and save (if multiple locations, include on one line w/OR)
G:\CMS\Voyager queries\<Library> LC conversion

 

  1. open query in Design View
  2. set locations or ranges as above, then save
  3. export to Excel spreadsheet
  4. file name from above
  5. click “OK”  (do NOT change file format or check any export options)
  6. when it finishes running, click “OK” (don’t save the export steps)

 

Excel

    1. name sheet ALL <library> – <# of records>
    2. Freeze Top Row
    3. make top row Bold
    4. add an extra column at the left: line #
      • use auto-fill (+ at lower-left corner of marked cells) to fill it with a sequence of numbers
    5. Column width = 15 (columns B through M)
    6. adjust columns as follows:
TITLE_BRIEF width:  50
DISPLAY_CALL_NO width:  30
  1. Clean up NETWORK_NUMBER:
    • change NETWORK to OCLC
    • use Ctrl-H to delete all (OCoLC)ocm and (OCoLC)ocn
    • filter column to display all but non-standard entries (at end of filter list); copy into Notepad

 

Notepad

  1. copy filtered OCLC_NUMBER column into Notepad
  2. delete the top row and any empty rows at the end
  3. save to Desktop as oclc.txt

 

Connexion

(you don’t need to log in)

  1. click CatalogingSearchLocalSaveFile (fourth icon or F3); click OK; right-click Select All; Ctrl-Alt-D; click Yes (…wait until clear)
  2. Batch menu –> Enter Bibliographic Search Keys… (Use default index: OCLC Number (no:))
  3. click Import; browse to oclc.txt on Desktop; click Open; click No; click Save then Close
  4. Batch menu –> Process Batch…; check: Online Searches (+ a path & Retain file selection); click OK (…wait)
  5. when the Batch Search Report displays, check for problems at top of report then close it (if problems, clean up in Excel, and repeat)
  6. click CatalogingSearchLocalSaveFile (fourth icon or F3); click OK
  7. right-click Select All then right-click Copy

 

Excel

  1. create an additional sheet, then paste the data from Connexion, starting in cell B1
  2. columns C-H: Column width = 15
  3. use auto-fill (+ at lower-left corner of marked cells) to make the first column a sequence of numbers
  4. column N: filter to display only Blanks; delete lines
  5. column B: Sort A to Z; move all non-number lines two columns to the right
  6. column E: sort Z to A; move all non-number lines one column to the left
  7. column A: Sort A to Z
  8. insert a blank row at the top of the sheet
  9. in the original sheet, filter column G to display all but non-standard entries (at end of filter list) (filter may still be active from earlier)
  10. in column J, put an x in the last row of data; copy that cell, then Ctrl-Shift-up-arrow, then paste
  11. turn off column G filter
  12. column J: Sort Z to A
  13. in the additional sheet, copy G column
  14. in the original sheet, paste in column J
  15. title column J: Call # from OCLC
  16. delete the additional sheet
  17. copy column J into column K
  18. title column K: LC Call Number; Column width = 30
  19. column K: Sort A to Z
  20. delete all non-LC call numbers in column K
  21. replace all empty cells with a .
  22. Sort column K:
    • LC Call Number: A to Z
    • 050 Field: Z to A
    • 090 Field: Z to A
    • (this will make the top rows have LC Call Number be empty, but have an 050 or 090)
  23. copy the 050 or 090 to column K
  24. manually delete everything before the call numbers in the 050 and 090 entries in column K
  25. column K: Sort A to Z

 


 

Barcodes to holdings spreadsheet

Create a file of barcodes with “Item BarCode” as the first row

Remove Duplicates, if necessary

 

Voyager queries database

Import as table: temp barcodes

Export to Excel Spreadsheet

  1. click on pretty from temp barcodes
  2. Export to Excel Spreadsheet
  3. browse and name as appropriate
  4. OK

 

Excel

  1. Freeze Top Row
  2. make top row Bold
  3. Column width = 15
  4. adjust columns as follows:
NETWORK_NUMBER width: 20
BIB_FORMAT_DISPLAY width: 20
TITLE_BRIEF width: 50
DISPLAY_CALL_NO width: 30
ITEM_TYPE_NAME width: 30
ITEM_STATUS_DATE width: 10
MODIFY_OPERATOR_ID
width: 10

 


 

Shipping list from barcodes

Excel

Jenny provides an excel spreadsheet with two columns:

  • Box
  • Barcode

Format cells: Number (0 decimal places)

 

Voyager queries database

Import Excel Spreadsheet

  1. Import Excel Spreadsheet or Import text file
  2. browse to file
  3. OK
  4. Next
  5. check “First Row…”; Next
  6. Field Options:
    • Field Name: Barcode
    • Data Type: Short Text
  7. Next
  8. Choose my own primary key: Barcode
  9. Next
  10. Import to table: cms temp – shipment
  11. Finish
  12. Yes
  13. OK
  14. Close

Export to Excel Spreadsheet

  1. click on cms – shipping list from barcodes
  2. Export to Excel Spreadsheet
  3. name as: G:\CMS\CIC SPR\Shipment Reports\CIC shipment #
  4. OK

 

Excel

  1. Freeze Top Row
  2. make top row Bold
  3. Column width = 15
  4. adjust columns as follows:
NETWORK_NUMBER width: 20
TITLE width: 50
CALL_NO width: 30