Learn how to use a spreadsheet template to import contacts

1. Add data to the contact template spreadsheet

  • Each row in the spreadsheet template represents one contact. Complete one column or many.
  • For any of the add-to columns, multiple rows can be created for the same contact (i.e. the same contact ID with different work IDs on different rows will add multiple works to the same contact)
  • Four of the column names must be present in the top row to identify the spreadsheet as an import of contacts: Key names, Single name, Organisation name, and Contact ID. Do not delete these columns.
  • Each row must have data in one of those required columns.

New or update?

By default, a new contact is created for each row in your spreadsheet.

To update a contact already in the system, complete the Contact ID column with a valid contact ID from Consonance. Use a custom report to get contact IDs in bulk, or find the contact id in the url of the individual contact’s page. It is the number before /edit.

alt_text

After uploading the completed spreadsheet, the draft row displays if it is NEW or an UPDATE in the status column.

Even if all data matches exactly with an entry in the system, unless a system contact ID is in place, a new item will be created.

You are able to mix new and updating contacts in the same spreadsheet.

Updating an existing entry overwrites some fields and adds to others.

When you are updating an existing contact in your system, some data fields will be overwritten, some will be added to. If it is a field that accepts more than one value, such as phone numbers, the new phone number is added to any existing ones. However, if it is a single value field, such as key names or ORCID ID, the value is overwritten. The column listing below specifies if the column will be over-written or added-to.

Add contact to an existing work

To associate a contact with a work that already exists in the system, complete the Work ID column with a valid work ID from Consonance and a valid contributor role. You can see the work ID in the URL of the individual work’s page.

Consonance provides tools to help you export IDs in bulk.

2. Columns and validations

Contact name group

  • OVERWRITE: Data in any name field will overwrite existing data when updating a contact.
  • Person contacts use the multi or single name fields; company contacts use organisation columns.
  • In each row, one of the following columns must have data: key names, single name or organisation – unless there is a contact ID.
  • In each row, only one of the following can have data: key names, single name or organisation name.
  • Multi-named person contact columns should be used together (i.e. Rt Hon Iain Duncan Smith Jr, MP):
    • Titles before key names: Rt Hon
    • Names before key names: Iain
    • Key names: Duncan Smith
    • Suffix to key names: Jr
    • Qualification: MP
  • Single name should be used for a person without a first name/last name structure (i.e. Cher, An unknown solider, Anonymous).
  • Company contact columns can be used together (i.e. New York University, NYU).
    • Organisation name: New York University
    • Organisation acronym: NYU

Mailing address group

  • ADD TO: A new address will be added when updating a contact.
  • The country must validate with the postal/zip code. We use this open source automatic validation software.
  • In each row, if other address fields have data, there must be a valid country name/code.
  • The country name or code must be valid within the ONIX standard. View ONIX country codes.
  • If adding to an existing contact, addresses that are an exact duplicate are ignored.
  • Country codes are stored as ISO country codes. Valid country names are converted.
  • Address columns should be used together:
    • Address: Organisation
    • Address: Department
    • Address: Sub-building
    • Address: Building
    • Address: Care of
    • Address: Post Office Box
    • Address: Line 1
    • Address: Line 2
    • Address: Line 3
    • Address: Postal/zip code
    • Address: Country code or name

Professional affiliation group

  • ADD TO: A new professional affiliation will be added when updating a contact.
  • If no organisation existing in the system matches the exact spelling, a new contact organisation is created.
  • If an exact spelling match is found, the association will be with that existing contact organisation.
  • If adding to an existing contact, duplicates are ignored
  • Professional affiliation columns should be used together (Professor of Computer Science and Technology Evangelist, Durham University):
    • Professional affiliation position: Professor of Computer Science and Technology Evangelist
    • Professional affiliation organisation: Durham University

Contact ID

  • Required only if updating an existing contact.
  • Must be a valid Consonance contact id.

Address as

  • OVERWRITE: Data in this field will overwrite existing data when updating a contact.
  • Used for a contact that should be called or Addressed as a name different than the official name specified in the contact. Perhaps a contact has a name for the contract but prefers to be referred to by a different name.

Biographical note

  • OVERWRITE: Data in this field will overwrite existing data when updating a contact.
  • Basic HTML is permitted and converted to valid HTML.

ISNI number

  • Like an ISBN, but for a person. Highly recommended. More details at the International Standard Name Identifier website.
  • OVERWRITE: Data in this field will overwrite existing data when updating a contact.
  • The data will be validated for the correct number of characters and digits, and the standard ISNI checksum.
  • A valid ISNI is converted to standard ISNI format.

ORCID

  • Like an ISBN, but for a researcher. More details at the ORCiD website.
  • OVERWRITE: Data in this field will overwrite existing data when updating a contact.
  • The data will be validated for a correct ORCID range and pattern

In-house customer ID

  • OVERWRITE: Data in this field will overwrite existing data when updating a contact.

In-house supplier ID

  • OVERWRITE: Data in this field will overwrite existing data when updating a contact.

Phone/fax number group

  • ADD TO: A new phone or fax number will be added when updating a contact.
  • Must be in a valid phone/fax number format.
  • If adding to an existing contact, exact duplicates are ignored.

Email address

  • ADD TO: A new email address will be added when updating a contact.
  • Must be a valid email address format.
  • If adding to an existing contact, exact duplicates are ignored.

Gender

  • OVERWRITE: Data in this field will overwrite existing data when updating a contact.
  • The system can handle three options: male, female, and other.
  • If desired, ‘other’ can be explained within the contact, once imported.

Adding to an existing work group

  • Contributor role
    • ADD TO: A new contributor role will be added when updating a contact.
    • The code or exact description must exist in ONIX, or be one of our keywords: author, editor, illustrator.
    • Contributor roles are stored as ONIX codes.
    • If adding to an existing contact, duplicates are ignored.
    • View ONIX contributor role codes in a new window.
  • Work ID
    • ADD TO: A link between the contact and the work will be added when updating a contact.
    • Must be a valid Consonance work id.

All docs about Import & export