You can use an Excel spreadsheet to update existing records or to add new records to your project. The first step in this process is to generate the Excel template so that your values match the project fields. For records that utilize List field types or Linked field types, there are certain methods to formatting your data so that the terms are added or edited successfully. We recommend using .xls filetype for Excel.
Excel and Linked Fields
When cataloging values in a Linked field, integrated Getty vocabularies are available for direct linking via an Excel spreadsheet using specific formatting practices. Note that linking will only occur for records which are NEW, as the system will not link authorities in previously existing records. When formatting the linked data, enter the value as you would like it to display, followed by the source authority’s initials and the reference ID in double brackets. To find the reference ID for vocabularies, search in the Cataloging Tools for local names or Getty’s website to find authoritative vocabularies. External resources cannot receive imported reference IDs, only terms.
- Shared Shelf Names, or SSN
- Puryear, Martin [[SSN 500009936]]
- Thesaurus of Geographic Names, or TGN
- Seattle, Washington [[TGN 7014494]]
- Art and Architecture Thesaurus terms, or AAT
- sculpture; public art; plazas [[AAT 300047090][AAT 300056501][AAT 300008214]]
Excel and List Fields
For List fields, you can import terms both when importing new records and when updating existing records with your Excel spreadsheet. There are two available options for importing list terms, the first has been simplified for strictly List fields that link to one built list in Forum. The second is for Linked fields that link to a built list in Forum; it requires additional formatting and can only be performed for NEW records.
To import terms to a List field:
- Enter a single term in the cell as you normally would.
- For multiple terms, separate with a vertical bar or pipe | (but do not enter a space).
To import terms to a Linked field for a List:
For linked field types, you can only link to list terms when you are creating NEW records. This process applies the same formatting as Linked fields, enter the display information followed by double brackets containing “LIST” (not in quotes) and the term ID. The term ID can be found in the Controlled List tab of the Cataloging Tools, where you can select the list and search or browse for the term in the term panel.
For example, tomato’s term ID is 100 and banana’s is 101:
- tomato|banana [[LIST 100][LIST 101]]
Since a Linked field can be connected to controlled lists and the integrated Getty authorities, the chart below presents different scenarios and how they would be formatted. For this scenario your list has two terms:
|If you’re importing…||Format your Excel data like this||Text shown in display record||Link bubble in display record will show|
|LIST term(s), List ID and AAT ID||tomato|banana [[LIST 200][LIST 201][AAT 123]||tomato|banana||[tomato][banana][AAT authority]|
|LIST ID and AAT ID||[[LIST 200][LIST 201][AAT 123]]||none||[tomato][banana][AAT authority]|
If your project has a value that exists in two different lists, similar to the above ‘tomato’ example, you must use the LIST ID, instead of the term ID, so the value is linked to one of the available lists. Otherwise, the import will fail.
- You will be unable to add new list values when importing your records. If you attempt to import new values, you will see an error message.
- If you attempt the Excel import with values that are mixed valid and invalid values, the Excel import will fail.
Excel and Boolean Checkbox Fields
Excel import requires the values for Boolean fields to be all caps, (i.e. TRUE and FALSE). When editing a spreadsheet exported from the Cataloging Tools to Excel, the column formatting will default to capitalized text for this field.
Excel and Linked Work Fields
Excel import supports linking new Item records with existing Works, although only the Work can be linked and none of the data will propagate from Work to Item during import. Several steps are required when establishing a workflow that uses Excel to create linked relationships between Works and Item records, and ensuring that the data propagates into the Item record successfully.
To import new item records and link to a work record:
For linked work field types, you can only link to existing works when you are creating NEW records. This process applies similar formatting as Linked fields, enter double brackets identifying SSW and the work ID. The work ID can be found in the Work form of the Vocabulary Warehouse, where you can search or browse for the work.
- Shared Shelf Works, or SSW
- [[SSW 8000000004]]
After the formatted Excel spreadsheet has been imported, open the newly created Item records in bulk if they link to the same Work, or one at a time if they each link to multiple works. In the cataloging screen, click the View/Edit Work Record button to open the Work in the Vocabulary Warehouse. Saving the Work, or applying a minor change and then saving the Work, will prompt the system to propagate the Work’s data to the linked Items.
- Be sure the Linked Work field is linked to access the Shared Shelf Works source.
- If you are linking to a Work, but have entered information in fields that are mapped to receive propagated data from the Work to the Item, remember that any data entered in the Item record for those mapped fields will be overwritten by the Work data upon propagation.
Contact email@example.com if you are interested in setting up a Works project and would like to apply the above workflow.
Importing Values with Decimals
Use a Text Field or Text Area type if you need to input any values with decimals such as geographic coordinates. In order for the system to recognize the decimal point upon import, you need to add an apostrophe before the value in Excel. For example, if your geographic coordinate is 35.9940, then the Excel value would display as ‘35.9940. If you do not add an apostrophe, then anything after the decimal will be truncated after importing into Forum. Use .xls as the filetype when creating your import spreadsheet.