This is a brief documentation of how I used Marcedit to import correct URLs from an Excel spreadsheet into a large file of MARC records. The name of the ebook supplier has been changed to protect the innocent. The values below worked for me on the Excel spreadsheet I used.
Problem. Ebook supplier (EBS) supplies MARC records of generally good quality for a package of 600 ebooks. However, the URLs are inconsistent: there are between one and four in each record; several ebook supplies are represented, not just EBS; many of the DOIs for EBS- the only URLs that are consistent- do not work. We do have an Excel spreadsheet listing OCLC numbers and valid URLs for all titles.
General plan. To delete all the 856 fields in the MARC file and replace them with those from the spreadsheet. To do this, convert the relevant bits of the spreadsheet to a simple MARC file and merge the two using Marcedit.
Delete the URLs from the original file
Load/convert the original file as an .mrk file. Use the Tools>Add/Delete Field option to delete all the 856 fields in the original file.
Convert the spreadsheet to MARC.
- In Marcedit (version 6), select Export Tab Delimited Text.
- Choose the spreadsheet for the Source File
- Choose a filename for the Marc text (.mrk ) file to be created
- Specify the name of the sheet for an Excel file (e.g. in my case EBS)
- Choose the delimiter that separates the data (in my case I left this alone as Tab. It worked)
- Choose options (I left the LDR/008 and character encoding alone as I don’t think they mattered)
- Next. The data snapshot shows the columns numbered Fields 0 to whatever. I needed columns A (OCLC number) and P (URL), so this meant Fields 0 and 15. The fields to select and how they work is done by using the Settings section to create Arguments. For this, I needed two arguments, one for each field:
- First Argument (OCLC control number to go into the 001 field): Select = ”Field 0”; Map to = “001” ; Indicators = “\\” ; Term. punctuation = “” ; Constant Data & Repeatable Subfield = “”
- Add Argument when done
- Second Argument (URL to go into the 856 field): Select = “Field 15”; Map to = “856$u” ; Indicator =”40” ; Term. punctuation = “” ; Constant Data & Repeatable Subfield = “”
- Add Argument when done
- Finish. This disconcertingly takes you back to the previous screen but if you open up the .mrk file in the MarcEditor it should be all done. Each record will look something like this:
=LDR 00000nam 2200000Ia 45e0
Edit the new .mrk
As the OCLC numbers in the original MARC records were in the form “ocn123456789” (rather than simply “123456789”), I needed to do a find for “=001 “ and replace it with “=001 ocn” on the new file, then save it.
- From the Tools menu of Marcedit, select Merge Records
- Choose the .mrk of the original MARC records as the Source File (I don’t know if the .mrc would work too)
- Choose the newly created .mrk file as the Merge File
- Choose a filename for the newly merged file to be created
- Leave Record identifier as 001. If you were searching on the ISBN, presumably the 020 would work but haven’t tried it. Other options are 010, 020, 022, and 035, and MARC21 (?)
- Select the Merge Selected Field option
- Specify the 856 and move it to the Merge Fields box
- “Merge Completed”