User Manual: CSV Importer: Formatting Data

Data must be properly formatted before using the CSV Importer. The tool will provide error messages for improperly formatted data, but it’s best to do as much cleanup as possible before getting to the tool. In the templates generated from GitHub, the header rows hold the information needed to determine what should/can go in each field.

The data type of your field is noted in Row 3 of the GitHub-generated import template.

  • Strings are any combination of alphanumeric characters. 

    • Example: a, b, c, 1, 2, 3

  • Integers are whole numbers

    • Example: 1, 2, 3 

  • Floats are numbers that may include decimal points

    • Example: 1.2, 3.456

    • CollectionSpace does not currently support fractions in float fields (e.g. 1 ½).

  • Booleans are boxes that may be checked or unchecked

    • If you would like the box to be checked, enter TRUE into the field

    • If you would like the box to be unchecked, you may enter FALSE or leave the field blank

  • Dates are single calendar dates

    • For example: 01/02/2020

  • Structured dates are groups of fields in CollectionSpace that provide for fuzzy, ranged, and other non-single dates. You can import the whole date value as a string. If possible, it will be parsed into the more specific date elements (dateEarliestYear, dateEarliestMonth, datePeriod, dateLatestQualifierValue, etc.) that are viewable in the box that pops up when you click on a structured date in the record.

    • If you have more granular date data (separate day, month, year values), you cannot import them directly into the more specific structured date fields. You will need to combine them into a full date for import. 

    • Regardless of whether your date string can be parsed into the more specific structured date elements, the string you import will remain in the “dateDisplayDate” and will be visible in the record.

    • Example: 01/02/2020, ca. 1920, 1950-1980, early 20th century

 

The Value Source Type and Value Source for your field are noted in Rows 6 & 7 of the GitHub-generated import template. If the Value Source Type and Value Source fields have values in them other than N/A, the data in the field comes from a controlled source such as a pick list or authority.

  • Option lists are static pick lists in CollectionSpace - those that cannot be edited via the UI. For fields populated by an option list, you must use the values exactly as they appear in Row 7 of the GitHub-generated template, Value Source. For example, in Object Cataloging - Common, the collection value “permanent-collection” will import as expected. The following would not: “permanent collection” or “Permanent-collection”.

  • Vocabularies are dynamic pick lists in Collection - those that can be edited via the UI. For fields populated by a vocabulary, you must use the values exactly as they appear in the “Name” field of the vocabulary. This includes capitalization, spacing, and any included punctuation. (Variations will be imported and will display, but they will not be matched up with/reconciled against the vocabulary, introducing inconsistency in your data).

    • Tip: As every museum may have different terms in dynamic lists, these values are not included in the templates on GitHub. You may wish to add these values to your locally saved version of the template.

    • Critical Tip: You must create new terms in a given vocabulary before importing other records that use those terms. If you use a vocabulary term in a record, and that vocabulary term does not exist in the vocabulary, the CSV Importer will not import the record that uses the missing term. The Processing step will prepare a report of all unique missing terms in a given batch. This report includes the term and what authority or vocabulary the term must be added to.

  • Authority fields pull data from one or more authorities. For fields populated by an authority , the CSV value should exactly match the Display value field of the term in the appropriate authority. If there is a mismatch, the term used in your record will not be connected to the authority as expected and a new term will be created.

    • Tip: If a field is linked to more than one authority, the GitHub-generated template will include multiple columns for that field; for example, acquisitionSourcePerson and acquisitionSourceOrganization.

    • Critical Tip: You must create new terms in a given authority before importing other records that use those terms. If you use an authority term in a record, and that authority term does not exist in the authority, the CSV Importer will not import the record that uses the missing term. The Processing step will prepare a report of all unique missing terms in a given batch. This report includes the term and what authority or vocabulary the term must be added to.

  • Fields with N/A in the value source type can be formatted according to your local practice.

Whether or not a field is repeatable is noted in Row 4 of the GitHub-generated import template.

  • Values for multi-valued fields should be separated with a pipe: |

    • To enter the pipe | character, select shift and the key directly under your delete/backspace key

    • Example: Eleanor Brown|Daphne Brown

  • For multi-valued fields with multi-valued sub-fields, such as the Title block in Object Cataloging, non-repeating sub fields should be separated with a |, repeating sub-fields should be separated with a | for each top-level instance and ^^ for each repeating value of the subfield (sample below). The ^ is above the 6 key on your keyboard.

    • Tip: Still not sure how to format? Create a dummy record with data in the fields you’d like to import in your CSpace instance or on one of our demo sites and Export those fields to see the correct formatting.

  • Critical tip: If the data you’d like to update includes multi-valued fields, all existing values will be replaced by the data you import. To make sure you don’t delete existing values by mistake, we suggest first exporting the records you’d like to update via the Search Results Export feature, and then editing that CSV vs. creating one from scratch.

    • For example, if your existing record has colors Red and Blue, and in your spreadsheet you enter the value Green into the cell, after updating, your Color field will only include Green. If you want to keep Red and Blue and add Green, your Color cell needs to read: Red|Blue|Green.

title

A Man | A Woman

title

A Man | A Woman

titleLanguage

English | English

titleType

assigned-by-artist | popular

titleTranslation

Un Homme^^Un Hombre | Une Femme^^Eine Frau

titleTranslationLanguage

French^^Spanish | French^^German