User Tools

Converting Excel to MARC XML

Publishers are often not able to supply us with an ONIX or MARC 21 record set, but they're able to provide us with metadata in an excel spreadsheet. NNELS provides a standard spreadsheet for their use.

Using MarcEdit, you can map the data in this spreadsheet to their corresponding MARC 21 fields, and generate a MARC 21 record set. You can then save the resulting MARC 21 file as a MARC XML file.

  1. Open the excel spreadsheet
  2. Check the data is formatted properly (i.e. no HTML code in the fields, Runtime in HHMMSS). If so, click Save As and save the file in Tab Delimited Text format (.txt)
  3. Open MarcEdit
  4. In the top menu bar, click on Tools and select Delimited Text Translator
  5. In the Source File field, upload the excel sheet
  6. In the Output File field, select where you would like to save the resulting .mrk file and give it a name. The name should contain the name of the publisher/supplier, and the current month and year, i.e. Heritage-Group-Jan-2018.mrk
  7. In the Delimited field, select Tab
  8. Click on Import Data. You'll be taken to a new Map Data window where you can map the fields in the excel sheet to their corresponding MARC 21 fields.
  9. In the Select drop-down field, select each of the excel fields one-by-one and map them to their MARC 21 field. The excel fields and their corresponding MARC 21 fields are given below.
  10. For example, select Identifier in the Select drop-down field
  11. In the Map To: field, enter the field and subfield separated by a dollar sign ($), i.e. 035$a
  12. Then click on Add Argument
  13. Repeat this process until all of the below fields are added
  14. Select both 264$b and 264$c and click Join Items (this will ensure that subfields $b and $c are both included in field 264)
  15. Check Ignore Header Row
  16. Click on Process Data
  17. Open up the resulting .mrk file and check that all the fields are there and have been mapped
  18. Remove the quotations that surround the subjects in 650$a by clicking on Tools then Edit Subfield Data. Enter the following data:
    1. In Field, enter 650
    2. In Subfield, enter a
    3. In Field Data, enter a single quotation mark "
    4. Click Remove Text. The quotations should now be gone.
  19. When everything looks good, save the file as a MARC XML record set by going to File > Save As and saving the file with a .xml extension

Now you have a MARC XML record set that can be uploaded to NNELS. Move on to Uploading a MARC XML record set.

Mapping the fields in the NNELS excel sheet to MARC 21:

  • Identifier - 035$a
  • Title and subtitle - 245$a
  • Author - 100$a
  • Name of publisher - 264$b
  • Publication date - 264$c
  • File format - 300$a
  • Narrator - 511$a
  • Runtime - 306$a
  • Abstract - 520$a
  • Subjects/keywords - 650$a
You can save these mappings as a template so that you can easily reuse these mappings in future conversions. After you've entered the mappings, check the Save Template box. The template will be saved as a .md file which you can then load next time.
public/nnels/publishing/excel-marcxml.txt · Last modified: 2018/08/16 22:06 (external edit)