1 of 27

Exploration of Using MarcEdit Delimited Text Translator to Convert Excel Data From the UW Gaihozu Inventory Into MARC Records

University of Washington Libraries

Keiko Hill

Japanese Cataloging Librarian/EA Serials and E-Resources Cataloging Librarian

Jian Ping Lee

Chinese Cataloging & Metadata Librarian

2 of 27

Agenda

Motivation
Steps of converting/translating Excel data into MARC records

Part 1: Preparing the spreadsheet to be translated
Part 2: Using MarcEdit to translate the spreadsheet
Part 3: Importing MARC records into Connexion

Challenges and findings
Demo

3 of 27

Motivation

UW Libraries hold a large collection of Gaihozu maps

Current status of accessing to the Gaihozu maps: inventory sheet and collection level bib record

Explore method to improve access to this invaluable collection
Desire to learn batch processing MARC records

4 of 27

Part 1. Working with Spreadsheet

Modified the existing inventory sheet

Select 10 titles published on the same year
Add corresponding MARC headings for each fields

For Latin fields: 034, 110, 245, 264, 490/830, 500
For non-Latin fields: 880s

5 of 27

外邦図 Gaihozu Inventory (original spreadsheet)

Excerpt as sample from The list of UW’s Gaihozu holdings in Research Guides

https://guides.lib.uw.edu/research/gaihozu/uw_gaihozu

6 of 27

Modified spreadsheet

Add corresponding MARC headings for each fields
Add romanization: 110, 245, 264, 490/830, 500

7 of 27

Non-Latin and transliteration columns

Use 880 for non-Latin fields.
To link the non-Latin fields with the corresponding transliteration fields, $6 is used to pair the parallel fields.

8 of 27

Punctuations in MARC fields

Use “$” for subfield delimiter.
Add indicator to MARC tag. Use “\” for no indicator value.
Add punctuation to each subfield as needed.

9 of 27

Asterisks for auto generate in the heading fields

To use “auto generate” function in MarcEdit Delimited Text Translator, add asterisks (*) to join the arguments in MARC heading fields.

10 of 27

Part 2. Working with MarcEdit

MarcEdit features

Export Tab Delimited Text

MarcEdit Delimited Text Translator

MarcEditor

11 of 27

Step 0. Encoding Setting

Go to the MarcEditor Settings in MarcEdit Preferences
Set default encoding to UTF8
Select compile records as UTF8

12 of 27

Step 1. Delimited Text Translator: Configure Data

Open up the Excel file in Source File
Choose location and create a file name for Output File
Select the correct Excel sheet if there are multiple sheets in one Excel file
Under Options, check UTF-8 Encoded
Click Import File

13 of 27

Step 2. Delimited Text Translator: Define Translation

Click Auto Generate to add arguments
Click Finish

14 of 27

Step 3. MarcEditor: Convert Output File to .mrc File

Open the MarcEdit mnemonic (.mrk) output file in MarcEditor
Go to File, select Compile File into MARC
Save as binary MARC file (.mrc) file

15 of 27

Part 3: Connexion

Import record to the local save file
Use of constant data for fixed fields and variable fields that apply to all records.

16 of 27

Importing Marc File to Connexion

Go to File and select Import Records
Choose file to be imported
Choose Import to Local Save File for destination
Click Options and select Apply Default Constant Data, click OK
Click Record Characteristics and select UTF-8 unicode, click OK
Click Ok on the Import Records pop-up window

17 of 27

Constant Data

18 of 27

MARC Record Example (1/2)

19 of 27

MARC Record Example (2/2)

20 of 27

Challenges

Learning new tools

Lacking of comprehensive documentation or tutorial

Formatting and organizing the spreadsheet for maps with many unique elements and values is difficult

Some punctuations in a MARC field are defined in MARC heading row, some are recorded with MARC values
Some subfields are recorded under one column, some are in separate columns

21 of 27

Challenges

Working with non-Latin materials involves additional columns which require more time and demand greater attention to detail
Handling individual maps from different map series with various publication dates

Unable to apply constant data for the LDR/008 consistently
Need to process maps with same publication dates from the same series as a batch to apply the tailored constant data

22 of 27

Record Example in Early Testing

23 of 27

Conclusion

Batch process title level records to Connxion is not an ideal solution for the Gaihozu map collection

Potential of creating duplicate records in Connexion
Complexity of the collection

Possible option is to add records to Alma

Import Excel data to Alma IZ with the Alma Profiles feature

24 of 27

Conclusion

MarcEdit Delimited Text Translator could be useful for working on large sets of materials that are similar
Working in a spreadsheet might be helpful for student workers who may not have a lot of or any cataloging experience

25 of 27

Resources

Kiser, Tim, and Nicole Smeltekop. “A Method for Creating Scanned Map Metadata for Geoportals, Library Catalogs, and Digital Repositories: Reworking Existing MARC Records of Paper Maps to Create New Records for Their Scanned Counterparts.” Journal of Map & Geography Libraries 14, no. 2–3 (2018): 109–31. doi:10.1080/15420353.2019.1640166.

Reese, Terry. “MarcEdit Delimited Text Translator.” YouTube. Accessed June 30, 2025. https://www.youtube.com/watch?v=Kp_N3ncjS7Q&t=21s

Reese, Terry. “MarcEdit Delimited Text Translator: Auto Generate Translation Arguments.” YouTube. Accessed June 30, 2025. https://www.youtube.com/watch?v=SPyIF4MfC30&t=11s

Tanaka, Azusa. “外邦図 Inventory: The list of Gaihozu held at University of Washington Libraries.” Accessed July 22, 2025.

https://docs.google.com/spreadsheets/d/1WjMSrj42744OsCkOnKAe-_58-StJJedwTuCx-VFfS14/edit#gid=1162903304

26 of 27

LIVE DEMO

27 of 27

Thank you

Question?

UW Libraries

Keiko Hill khill5@uw.edu

Jian Ping Lee jlee37@uw.edu