Import CSV Collection

My Collections (from version 3.1) allows subscribers to import a collection from a CSV file. This can be especially useful if you need to jump start your Collection from an existing list or spreadsheet.

What's a CSV File?

CSV is an acronym of Comma-Separated Values and the CSV file is a file that contains records (items) with fields separated by a comma. If your data is held in a spreadsheet, e.g. an Excel file, then most spreadsheet applications will allow it to be exported as a CSV file.

Features

• Read standard CSV files that follow the RFC 4180 standard

• Map fields to existing Collection fields, custom fields and tags

• Dynamically create custom fields and tags if they do not exist

• Map multiple fields from the CSV file to singular fields in the Collection 

Requirements

The CSV file has a number of basic requirements:

• The first line of the file is assumed to be a header and the header names are used to map to fields in the Collection

• Fields in the CSV are separated with commas

• Fields that contain commas must be wrapped in double quotes.

• CR or CRLF indicates the end of a record 

• Quoted text can contain CR or CRLF 

Important

Test the CSV import in a new collection.  This is critical, as there are many things that could affect the import, e.g. poor mapping, bad CSV data, hidden bugs in the code. Once the import has run, you cannot automatically undo the import other than delete all the imported items one by one. Any changes to the collection will have to be manually undone so it's important that you understand how the data will be imported.

If you want to test the import against an existing collection, then make a copy of your collection either by restoring it from a backup or by merging it into a new collection (you can create an empty collection to use as the second merge target).

Once you are happy with the import and understand the mappings, you can delete the test collection and import into the actual collection.

CSV Import Process

The process is a three step process:

1. Select the CSV file. The file needs to be accessible to the device you are merging one and be able to be found by the device's native File Picker tools. Android devices can usually read files from Google Drive. 

2. Map the CSV header fields to fields in the Collection

3. Review the mappings and start the import

The import can take a minute or so to complete, depending upon how many items are being created.

Mapping Fields

Thee are three types of fields in a collection:

• Standard collection fields 

• Custom collection fields

• Tags

Some fields support multiple mappings. This is where the more than one field, or the field contents contain many values. For example, consider Images that are represented by URLs. The field can hold multiple URLs (all comma separated) or there could be many fields with one URL in each. The import process will read all the image field mappings and ensure that all URLs are included in the Item.

Mandatory fields like Item name and any mandatory custom fields are shown with an asterisk prefix. These fields must be mapped for the process to continue. If you need to import a file but cannot map to a mandatory custom field, the consider editing the collection and temporarily making the custom field non mandatory.

Standard Collection Fields

The standard collection fields are all prefixed with "Item" :

• Item name. This is mandatory and must be mapped to a field from the CSV file

• Item notes

• Item quantity. The CSV field will be parsed as an integer and will be ignored if the field fails parsing

• Item expiry. This is expected to be a date string in the ISO8601 format (e.g. YYYY-MM-DDTHH:MM:SS)

• Item images. This should be a URL using the "https://" schema. Multiple images can be specified in a single field, comma separated, or in multiple fields.

Custom Collection Fields

Existing custom fields are shown in the collection mapping. If the custom field is mandatory, then it will be shown with an asterisk prefix. The contents of the CSV field is used as the custom field value. Note that the import process does not validate the fields which may lead to display issues if you import text data into a date or number field. To ensure date fields are correctly mapped there should be an existing Date Custom Field and the CSV date should be in the ISO8601 format or an accepted format that Dart can parse.

If you want to create a new custom field for the CSV field, then choose the "Create new field" mapping. This will create a new "text" custom field.

Tag Fields

Existing tags are shown in the collection mapping.  When mapping directly to existing tags, the contents of the CSV field are ignored and as long as the field is not empty, the Item will be created with the tag.

If you want to create a new tag for the CSV field, then choose the "Create new tag" mapping. This will create a new tag using the CSV header name. Again, the contents of the field will be ignored and the presence or absence of the CSV field value will determine if the Item is created with the tag or not.

Another mapping for tags is the "Match existing tags" mapping. This mapping uses the contents of the field to match existing tags. If there is no match, a new tag will be created. This mapping supports multiple mappings and each field can have many comma separated tag names. Note that tag name matching is case insensitive.