SCRIPTS for extraction of BID numbers for MARC export (FOR PHYSICAL ITEMS ONLY with barcodes attached). This script will limit all downloads of MARC records from Voyager that do not have physical barcodes attached AND that are within the specified MFHD locations you want to migrate.

  • Step one:
  • Make a table of all BID numbers associated with physical items in the library catalog. Substitute your own Location IDsfrom your Data Migration Translation Table in the Location_ID field of the MFHD Master and your own item barcode preface on the last line. In this example the new table has been named "WMS BIDs linked to physical items - Sample Script 1":

SELECT BIB_TEXT.BIB_ID, MFHD_MASTER.MFHD_ID, ITEM_BARCODE.ITEM_BARCODE INTO [WMS BIDs linked to physical items - Sample Script 1] FROM ITEM_BARCODE INNER JOIN ((MFHD_MASTER INNER JOIN (BIB_TEXT INNER JOIN BIB_MFHD ON BIB_TEXT.BIB_ID = BIB_MFHD.BIB_ID) ON MFHD_MASTER.MFHD_ID = BIB_MFHD.MFHD_ID) INNER JOIN MFHD_ITEM ON MFHD_MASTER.MFHD_ID = MFHD_ITEM.MFHD_ID) ON ITEM_BARCODE.ITEM_ID =MFHD_ITEM.ITEM_ID WHERE (((MFHD_MASTER.LOCATION_ID)="74" Or (MFHD_MASTER.LOCATION_ID)="77" Or (MFHD_MASTER.LOCATION_ID)="78" Or (MFHD_MASTER.LOCATION_ID)="80" Or (MFHD_MASTER.LOCATION_ID) ="82" Or (MFHD_MASTER.LOCATION_ID)="88" Or (MFHD_MASTER.LOCATION_ID)="113" Or
(MFHD_MASTER.LOCATION_ID)="174" Or (MFHD_MASTER.LOCATION_ID)="271" Or

(MFHD_MASTER.LOCATION_ID)="283" Or (MFHD_MASTER.LOCATION_ID)="290" Or

(MFHD_MASTER.LOCATION_ID)="306" Or (MFHD_MASTER.LOCATION_ID)="309") AND

((ITEM_BARCODE.ITEM_BARCODE) Like "30852*"));

  • Step two:
  • Use the table you created to dedupe your records:

SELECT [WMS BIDs linked to physical items - Sample Script 1].BIB_ID INTO [WMS BID records from

FROM [WMS BIDs linked to physical items - Sample Script 1]

GROUP BY [WMS BIDs linked to physical items - Sample Script 1].BIB_ID;

  • Step three:
  • Export your new table to Excel; delete the top label row; break your file into groups of 40,000 (if using PSW) or 75,000 (for EDX transfer) save each as file1.txt, file2.txt, etc.
  • Save each file as a .txt (tab delimited) file.
  • Step four:
  • Follow the instructions for MARCexport of each file:
  1. Put the file in the /m1/voyager/santdb/rpt/ subdirectory
  2. Execute the command for each file: Pmarcexport -rB -mM -t/m1/voyager/santdb/rpt/file.txt -i
  3. Take and download the files from the /m1/voyager/xxxxdb/rpt directory
  4. Carefully Rename them and created the accompanying LABEL files according to the
  5. specifications outlined in the OCLC document for sending MARC Records for Batchload using FTP:
  6. Upload them.

But what about MARC records you DO want to migrate that have no item barcodes attached to them? An example might be a gov’t doc that exists in digital format with a hyperlink in the MFHD but that has no barcoded physical item associated with it. In this case, there would be a two-step process for identifying the MARC BIDs that need to be exported.

  • Step one:
  • Identify the BIDs with MFHDs from the particular location you want to migrate (e.g. gov’t docs) AND that have no barcodes attached to them:

SELECT BIB_TEXT.BIB_ID, MFHD_MASTER.MFHD_ID, MFHD_MASTER.LOCATION_ID, ITEM_VW.BARCODE INTO [WMS BIDs from Specific Locations With No Barcodes] FROM ((BIB_TEXT INNER JOIN BIB_MFHD ON BIB_TEXT.BIB_ID = BIB_MFHD.BIB_ID) INNER JOIN MFHD_MASTER ON BIB_MFHD.MFHD_ID = MFHD_MASTER.MFHD_ID) LEFT JOIN ITEM_VW ON MFHD_MASTER.MFHD_ID = ITEM_VW.MFHD_ID WHERE (((MFHD_MASTER.LOCATION_ID)="156") AND ((ITEM_VW.BARCODE) Is Null));

OR you can run a similar script that creates a table of BIB IDs for records from a particular location that have no ITEMs attached depending on the procedures that had been followed in your library.

SELECT BIB_TEXT.BIB_ID, MFHD_MASTER.MFHD_ID, MFHD_MASTER.LOCATION_ID, ITEM_VW.ITEM_ID INTO [WMS BIDs from Specific Locations With No Items] FROM ((BIB_TEXT INNER JOIN BIB_MFHD ON BIB_TEXT.BIB_ID = BIB_MFHD.BIB_ID) INNER JOIN MFHD_MASTER ON BIB_MFHD.MFHD_ID = MFHD_MASTER.MFHD_ID) LEFT JOIN ITEM_VW ON MFHD_MASTER.MFHD_ID = ITEM_VW.MFHD_ID WHERE (((MFHD_MASTER.LOCATION_ID)="156") AND ((ITEM_VW.ITEM_ID) Is Null));

  • Step two:Deduping:
  • For BIDs with no barcodes:

SELECT [WMS BIDs from Specific Locations With No Barcodes].BIB_ID INTO [WMS BIDs from specific locations with no barcodes DEDUPED] FROM [WMS BIDs from Specific Locations With No Barcodes] GROUP BY [WMS BIDs from Specific Locations With No Barcodes].BIB_ID;

  • Again, for BIDs with no items:

SELECT [WMS BIDs from Specific Locations With No Items].BIB_ID INTO [WMS BIDs from specific locations with no items DEDUPED]FROM [WMS BIDs from Specific Locations With No Items]GROUP BY [WMS BIDs from Specific Locations With No Items].BIB_ID;

  • Step three:
  • Export your new table to Excel; delete the top label row; break your file into groups of 40,000 (if using PSW) or 75,000 (for EDX transfer) save each as file1.txt, file2.txt, etc.
  • Save each file as a .txt (tab delimited) file.
  • Step four:
  • Follow the instructions for MARCexport of each file:
  • Put the file in the /m1/voyager/santdb/rpt/ subdirectory
  • Execute the command for each file: Pmarcexport -rB -mM -t/m1/voyager/santdb/rpt/file.txt -i
  • Take and download the files from the /m1/voyager/xxxxdb/rpt directory
  • Carefully Rename them and created the accompanying LABEL files according to the
  • specifications outlined in the OCLC document for sending MARC Records for Batchload using FTP:
  • Upload them.