Importing Data from a CSV File

Documentation WorkBench Workflows
1

Importing Data from a CSV file

The WorkBench can easily import data from a CSV (comma separated value or comma delimited text) file into a new Data Set.

Notes:

  • Changes made to the data in the WorkBench will not be reflected in the original CSV file.
  • The WorkBench has a 4000 record maximum. Files that include more than 4000 rows will not import.

 

To begin, click on the Import Data action on the side bar and choose the import file in the file dialog.

The following information dialog will open:

CSV Import

Import from CSV

 

This dialog enables the WorkBench to properly import the file by allowing the user to set data import options.

The Data Preview window reads and displays a sample of the column headings and data from the import file. If the file does not contain column headings, the columns will be labelled in numerical order (Column 1, Column 2, etc.).

Incorrect import options will be visible in the Data Preview window and will also have a warning icon and message displayed.

Bad Import

Incorrect Import Options

 

To correct the information displayed, choose different selections in the 'Data Import Options' section of the dialog. When the 'Data Import Options' are correct, the 'Data Preview' window will display the CSV file correctly, the warning icon will disappear and the dialog will look like the 'Import from CSV' dialog above.

The next step in the import process is to 'map' the column headings from the import file to Specify fields.

 

File includes Column Headings

The Workbench includes an 'auto-mapping' feature which first identifies column headings and compares them to existing Data Sets. If the column headings in the import file match those in an existing Data Set the following dialog will open:

New Data Set Column Headings

 

To copy the 'map' from an existing Data Set select the 'Use an existing Data Set mapping' button and choose the Data Set from the list provided. The Data Set Properties dialog will then open, to name and describe the new Data Set.

To create a new map for the new Data Set select the 'Create new Data Set mapping' button and click 'OK'.

Note: Mappings can only be reused if the CSV file being imported contains the same or fewer fields than the mapping. In other words, if you split a CSV file into two smaller files for import, then import the first CSV file but do not map all the fields, the unmapped fields are discarded. When you then import the second CSV file you will NOT be given the option to reuse the mapping created for the first CSV file. To reuse the mapping you must first delete the unmapped/discarded fields from the CSV file before importing it into the WorkBench.

 

The 'auto-mapper' then compares the import column headings to the Specify fields and 'maps' the columns to matching fields.

automapped

Column Headings Automapped in the Data Set Mapping Editor

 

The column headings from the import file appear under 'Data Set Columns' on the right side of the dialog and are mapped to 'Specify Fields' on the left.

Column headings that map to Specify fields are identifed by a Linked (linked) icon.

Column headings that have not been auto-mapped will have 'To Be Discarded' displayed under 'Specify fields'. Unmapped column headings will be deleted if they are not mapped to a Specify field before clicking 'OK'.

The buttons in the dialog enable columns to be mapped, unmapped, added, deleted and reordered.

Icon Name Result

map

 Map Adds the field to the column list.

unmap

 Unmap Removes the field from the list of columns.

reorderup

 Reorder up Moves the selected field up.

reorderdown

 Reorder down Moves the selected field down.

 

To map an unmapped Data Set column to a Specify field:

  1. Click on the column in the right hand window to select it.
  2. Click on the Data Type to display the associated fields in the 'Specify Fields' window.
  3. Click on the required Specify field to select it.
  4. Click the map (map) button.

To unmap a column from a field:

  1. Click on the column in the right hand window to select it.
  2. Click the unmap (unmap) button.

To add a column

  1. Click on the Data Type to show the Specify fields in the 'Fields' window.
  2. Click on the Specify field.
  3. Click the map (map) button.

A link (link) icon confirms that the column and field are the same heading.

Note: A field may only map once. Specify fields that are mapped display a green check mark next to their name in the Specify field list confirming that they are in use.

Note: When importing Taxon information only use the Taxon Only Table in the mapping editor.

Note: The Agent table in the mapping editor may only be used for agent information. When uploading agent information along with collection object information use the Cataloger Title, First Name, Middle Name and Last Name fields in the Collection Object table.

 

When all the Columns are mapped and ordered correctly click the 'OK' button.

Complete the import process by naming and describing the new Data Set:

data set properties

Data Set Properties

 

File does NOT include Column Headings

Import files that do not contain column headings cannot be 'auto-mapped' to fields in Specify. Columns are labelled according to their numerical order and need to be manually mapped.

no names mapped

Map of Import File with No Column Headings

 

Columns without headings display 'To Be Discarded' rather than mapping to Specify fields. Unmapped column headings will be deleted if they are not mapped to a Specify field before clicking 'OK'.

 

The buttons in the dialog enable columns to be mapped, unmapped, added, deleted and reordered . The function of each button is described below:

Icon Name Result

map

 Map Adds the field to the column list.

unmap

 Unmap Removes the field from the list of columns.

reorderup

 Reorder up Moves the selected field up.

reorderdown

 Reorder down Moves the selected field down.

 

To map an unmapped Data Set column to a Specify field:

  1. Click on the column in the right hand window to select it.
  2. Click on the Data Type to display the associated fields in the 'Specify Fields' window.
  3. Click on the required Specify field to select it.
  4. Click the map (map) button.

To unmap a column from a field:

  1. Click on the column in the right hand window to select it.
  2. Click the unmap (unmap) button.

To add a column

  1. Click on the Data Type to show the Specify fields in the 'Fields' window.
  2. Click on the Specify field.
  3. Click the map (map) button.

A link (link) icon confirms that the column and field are the same heading.

Note: A field may only map once. Specify fields that are mapped display a green check mark next to their name in the Specify field list confirming that they are in use.

Note: When importing Taxon information only use the Taxon Only Table in the mapping editor.

Note: The Agent table in the mapping editor may only be used for agent information. When uploading agent information along with collection object information use the Cataloger Title, First Name, Middle Name and Last Name fields in the Collection Object table.

 

When all the columns are mapped and ordered correctly click the 'OK' button.

Complete the import process by naming and describing the new Data Set:

data set properties

Data Set Properties

 

To rename columns headings, see Form Editing.

Note: When the data set opens the validation for invalid fields will be turned on. The validation can be turned off by unchecking the Highlight Invalid Cells checkbox in the validation panel at the bottom of the work space.

validation_screen

Validation Panel

 

Bad Imports

CSV files that are corrupt or have not been saved correctly will not import correctly into the WorkBench. A bad file may appear in the Data Set Mapping Editor with partial or no column headings, or display a warning at the bottom of the WorkBench window:

 

Wrong File Warning

Bad File Warning