The Import tool: create and update records

New records can be created and existing records updated with the Collections Import tool, which is accessed from the Main menu. Data, typically imported from a CSV file1, can be verified with a Test run option before the import is processed and records in your data sourcesClosed The management of a collection can involve a vast amount of information about objects / items / books, people and organizations, events, administration and more. This information is stored as records in data sources. Each data source stores a specific type of information: details about collection items, people, events, loans, and so on. are added or changed.

Before demonstrating how to use the Import tool, we describe how to construct a CSV import file. Note that building a CSV import file can require details about fields and the data they hold that is only available in Axiell DesignerClosed An application for administration and customization of Axiell Collections. Amongst other things, field labels, tooltips, values in drop lists, etc. are specified and translated in Designer..

How to build a CSV import file

Fields in Collections can have both:

  • A data storage format: the format in which data is stored in the database.

    -AND-

  • A presentation format: the format in which data displays in the User Interface (UI).

When we build an import file, it is important that we enter data in the file in the format it is stored in the database rather than the format in which it displays in the UI.

When specifying the import data it is necessary to know certain properties of the import fields, such as:

  • The field's data type.

    This information, and more, is available in the Field properties box:

    The Field properties box provides details about the properties of a field and the data it contains:

    Field Info tab

    To display the Field properties box:

    1. Right-click a field in Record details View or Result set View when viewing or editing a record.
    2. Select Properties in the context menu that displays to display the Field properties box:

      Properties

    The Field properties box is described here.

  • Dates: the format of a date in the target field (the field that will be updated).

    Each date field can have its own data and presentation formats. Typically, all dates are stored in the ISO date (DateISO) format (yyyy-mm-dd), and while they can have a different presentation format in the UI (dd/mm/yyyy for instance), they usually do not. However, it is necessary to check in the Field properties box which data format a date field uses when importing dates:

    Field info

    When building your import data file, enter dates in the format in which they are stored in the target date field. Details about each Field (data) type and the required format can be found here.

  • Decimal separator: the decimal separator (dot or comma) used in numerical fields.

    Just as with dates, the decimal separator in numerical fields can be different when data is saved to the database and when presented in the UI and it is necessary to specify import data using the data storage decimal separator.

    By default, numerical values are stored in the database with a dot as the decimal separator (e.g. 3.14159), but it is possible to specify a comma as the decimal separator (e.g. 3,14159). This can only be checked in Axiell DesignerClosed An application for administration and customization of Axiell Collections. Amongst other things, field labels, tooltips, values in drop lists, etc. are specified and translated in Designer..

    In Axiell Designer:

    1. Select the (target) data source in which records will be updated by the import, collect.inf in this example.
    2. Select the Advanced tab.

      The Decimal separator drop list indicates which separator is used:

      Advanced tab

      Details about decimal separators storage and presentation formats can be found in the Axiell Designer Help.

    When performing the import you specify which decimal separator you have used in numerical fields in the import data file (details below): it is important to use the same decimal separator consistently.

  • Enumerative fields are read-only drop lists, such as:

    Again, the value stored in the database can be different from the value displayed in the UI, and it is necessary to specify the former. This can be checked in the UI or in Axiell DesignerClosed An application for administration and customization of Axiell Collections. Amongst other things, field labels, tooltips, values in drop lists, etc. are specified and translated in Designer..

    In the UI it is necessary to check the values in an enumerative drop list one by one:

    1. With the value displaying in the field, right-click the field and select Properties in the context menu:

      The database storage value is shown on the Data tab2:

    In Axiell Designer:

    1. Locate the Enumerative field in the target data source, current_location.package_location (2B) in this example.
    2. Select the Enumeration values tab:

      Enumerative values

Data is imported into Collections in a plain text file with a .csv extension; this can be created in an application such as Notepad or MS Excel:

  1. The header (first) row in the file is a comma separated list of field tags or system field names found in the data sourceClosed The management of a collection can involve a vast amount of information about objects / items / books, people and organizations, events, administration and more. This information is stored as records in data sources. Each data source stores a specific type of information: details about collection items, people, events, loans, and so on. that will be updated (details about how to identify field tags and system field names can be found here).

    Note: You can use a semicolon instead of a comma to separate fields and values, just be consistent and use one or the other.

    Always use English system field names in the header row: if necessary, switch the Interface language to English before checking the system field name. Alternatively, use the field tag, which is the same in all languages.

    The header row is not itself imported: it tells Collections which fields to update.

  2. Subsequent rows contain the comma separated values for each field listed in the header row.

Notes

Import

Details
Checkbox

Here we see the Publish on web (publish_on_web (wp)) field on the Management details panel:

When a checkbox is not selected (it does not contain a tick), it is obviously empty and its value is NULL. When a checkbox is ticked, the value it holds is an x.

To add a tick to a field with a checkbox when importing data, the value you import into the field is an x.

Linked fields

When importing a new value into a Linked fieldClosed A type of field used to link one record to another. A Linked field is a drop list of values (records that the field can link to). When a link is made, the field stores a reference to the linked record (a linkref)., Creatorin the example above for instance, a record will be created in the targetClosed A link is made from a record in one data source (primary) to a record in another data source (target). A data source could be both the primary and target data source in a linking relationship if one of its records links to another of its records. data source and the appropriate domain will be applied too. In the example above, the following record was created and automatically added to the creatordomainClosed A subset of records in an Authority data source, grouping similar sorts of records. When a search or Linked field is associated with a domain, only records in that domain are available to that field. For example, records for authors can be assigned to the AUTHOR domain (using the Name Type / name.type (do) field). When linking a record for a book to a record for its author, the Author Linked field will point to the AUTHOR domain, a subset of records in Persons and institutions exclusively for authors. In the Thseaurus, a record is assigned to a domain using the Term Type / term.type (do) field. (Name type):

Merged-in fields

If you attempt to import data into a Merged-in field, you will receive an error similar to:

Field ‘institution.code’ is a merge field that cannot process data.

Data that displays in a Merged-in field is pulled dynamically from a linked record; it is important to understand that the data is not actually stored in the Merged-in field, it only displays in the field: the data is actually stored in the linked record. As such, data cannot be imported directly into a Merged-in field.

Full details about Merged-in fields, and an explanation for the error message here.

Multilingual fields

If you import data into a multilingual field, the data is imported in the current data language.

Non-unique location names

It is possible to import object / archive records with a new current or default locationClosed it is also possible to update the current location of existing object records but note that this will not update the object's location history; updating an existing object's current location is better achieved with the Change locations task.

Where location names are unique it is possible to link object / archive records accurately by specifying a location name in location.default.name or current_location.name in the import file. A problem arises however when location names are not unique, something like Shelf 1 for instance. In versions of Collections prior to version 1.11, it is not possible to import non-unique location names and expect the link between an object / archive record and a location record to be correct: if there is more than one location called Shelf 1, which is the correct Shelf 1? Collections version 1.11 onwards provides a solution for this issue:

Two conditions must be met to import locations with non-unique names:

  • An appropriate location context field must be available.

    Context fields are usually unlabelled and read-only. Locations context fields, which are typically placed beneath the Normal location and Current location fields in object and archive records, display the entire location hierarchy from the current location upwards. In this example, the current location of an object is Bay 1 (the Object record is linked to the Locations and containers record for Bay 1), and the context field shows the entire hierarchy from Bay 1 up to the highest level, National Museum:

    Current location

  • A record for every location you wish to import must already exist in Locations and containers, as well as its upper hierarchy.

    For example, if a new location hierarchy is Room 2/Upright 2/Shelf C, records for these three locations must already exist in Locations and containers and they must be hierarchically linked.

When constructing the .csv import file:

  • Use the location context field (location.default.context or current_location.context), not the linked location field itself (location.default.name or current_location.name).
  • As the import value, specify the full location context string of the target location up to and including the location or container name itself, e.g.:

    Room 2/Upright 2/Shelf C

    In this case, the Locations and containers record for Shelf C (the location that is linked hierarchically to location Upright 2, which in turn is linked to location Room 2) will be linked to the relevant location field (location.default.name or current_location.name) in the object record.

Note: The full location context string is not actually stored in the location context field as the contents of such fields is generated dynamically.

If an imported context string cannot be resolved because the relevant hierarchy does not exist in Locations and containers, an error will be generated and the record will not be imported / updated:

Import error

Occurrences

If a field is repeatable, multiple occurrences can be imported by repeating the field name / tag column for each occurrence.

For example:

object_number;object_name;object_name

FDJK001;bike;bicycle

Note: Be sure that the field is repeatable.

Field groups are respected during import, even if there is no value to import for an occurrence in the group. For example, this import data:

will result in the following record:

When you build an import file in MS Excel, be sure to save as:

CSV UTF-8 (Comma delimited) (*.csv)

Place each system field name or tag in a cell in the header row; values are placed in the corresponding cell in the subsequent rows, building a table with field names as column headers:

Excel file

It is necessary to confirm which field separator character Excel is using. By default, Excel uses the separator character set in the Windows Control Panel (Clock and Region > Change date, time or number formats > Additional settings > List separator). A quick way to confirm which character has been used is to open your import .csv file in a text editor. When you perform the import it is necessary to specify this character in Settings (details here).

Auto-correction

Excel may auto-correct date formats, changing an ISO date to a European date for example. To set the required date format:

  1. Right-click a column header and select Format Cells from the context menu.

  2. In the Format Cells dialogue, set the required format for dates in this column:

    Format Cells

To avoid Excel reformatting data in an existing .csv import file:

  1. Open a new blank workbook in Excel.
  2. On the Data tab of the Ribbon, select From Text/CSV and locate the .csv file and click Import.

    Excel opens the file in a preview window.

  3. Make sure that the Delimiter option is set to the field separator used in the .csv file and that Data Type Detection is set to Do not detect data types to prevent automatic data formatting:

    Import preview

  4. Click Load.

    The import data is loaded into Excel, and a header row is added with Column1 Column2, etc. We need to remove this;

    Excel

  5. On the Table Design tab, remove the tick from the Header Row checkbox.

    The row will be cleared but still present.

  6. Select the row and select Delete on the Home tab of the Ribbon.
  7. When you have finished making any other changes to the file, save as CSV UTF-8 (Comma delimited) (*.csv).
  8. Click OK if a message displays regarding support for workbooks containing multiple sheets.

When you build an import file in a text editor, be sure to save the file with a .csv extension.

You can use a semicolon or a comma to separate fields and values, just be consistent and use one or the other.

Tip: If you build the import file in Excel, field names and values are entered in cells and there is no need to key in the separator.

When you perform the import it is necessary to specify this character in Settings (details here).

It is important to type the separator character between field names / tags and between values, and to use double quotes where required:

Double quotes

Whenever the field separator character (comma or semicolon) appears in the import data, enclose the data in double quotes to prevent the character being interpreted as a separator. For example, if you are using a comma as the field separator, you would need to use double quotes here:

"Wood, Gerard"

Without the double quotes, the comma will be interpreted as a separator and Collections will attempt to split the value across two fields.

If any numerical values in your import data file include commas, and you use a comma as the field separator, you must enclose the entire numerical value in double quotes.

Tip: All values in the import file can be enclosed in double quotes whether the separator character is present or not.

If a value in your import data includes double quotes, these must be doubled and the entire value must also be enclosed in double quotes. For example, if you want to import the following value:

Annual reports energy management first "conceptual" draft

it would be formatted in the import file as:

"Annual reports energy management first ""conceptual"" draft"

In an import data file, this would appear as:

object_number,title,description_level,dating.date.start,creator

AK/OW/0211/b,"Annual reports energy management first ""conceptual"" draft",FILE,2011,St Johan District Board of Works

Tip: An advantage of building an import data file in MS Excel, is that Excel manages this in the background and you do not need to double up quotes.

Here we see an example .csv file containing five target fields and twelve records that will be imported into the Archives catalogue data sourceClosed The management of a collection can involve a vast amount of information about objects / items / books, people and organizations, events, administration and more. This information is stored as records in data sources. Each data source stores a specific type of information: details about collection items, people, events, loans, and so on.. In this case, a semicolon was used as the delimiter:

object_number;title;description_level;dating.date.start;creator

AK/OW/0201;Annual reports energy management;FILE;2001;St Johan District Board of Works

AK/OW/0202;Annual reports energy management;FILE;2002;St Johan District Board of Works

AK/OW/0203;Annual reports energy management;FILE;2003;St Johan District Board of Works

AK/OW/0204;Annual reports energy management;FILE;2004;St Johan District Board of Works

AK/OW/0205;Annual reports energy management;FILE;2005;St Johan District Board of Works

AK/OW/0206;Annual reports energy management;FILE;2006;St Johan District Board of Works

AK/OW/0207;Annual reports energy management;FILE;2007;St Johan District Board of Works

AK/OW/0208;Annual reports energy management;FILE;2008;St Johan District Board of Works

AK/OW/0209;Annual reports energy management;FILE;2009;St Johan District Board of Works

AK/OW/0210;Annual reports energy management;FILE;2010;St Johan District Board of Works

AK/OW/0211;Annual reports energy management;FILE;2011;St Johan District Board of Works

AK/OW/0211/b;"Annual reports energy management first ""conceptual"" draft";FILE;2011;St Johan District Board of Works

Next we describe how to import your data with the Import tool.