Importing Records via .CSV

Importing records allows you to add or update multiple Records in an App using a CSV file. This can be used to transfer data from another system, make changes to exported Softools data offline, or create and update Records in bulk.

• How to Prepare the .CSV File

• Using the [ID] and [Hierarchy] Columns

  • Importing from Within a Parent Record

  • Importing Directly into the Child App

  • Creating Child Records with Parent-Based Default Values

• Uploading the Import File

• Field Validation on the Import File

• Checking the Progress of your Import

• Updating a Record via a User Identifier Field

How to Prepare the .CSV File

It is important to structure the CSV file correctly for the required Record additions and updates.

First, create a CSV file using Excel or similar software. Add the Softools [ID] as the first column heading in cell A1. The identifiers of the Fields that you want to import data into can then be added as the remaining column headings.

Note: Field identifiers can be found by exporting the App data first and then removing any columns that you do not want to import.

This creates a template to which you can add the required data. Each row in the import file represents a Record, with the value in each cell being imported into the Field identified by that column heading.

• Record update: To update an existing Record, enter its Softools Record ID in the [ID] column. Add the new Field values to the remaining columns. Each value must be valid for the relevant Field Type. We recommend starting with an export from Softools so that the correct Record IDs and Field identifiers are used.
• Record creation: To create a new Record, leave [ID] blank for that row. Softools will create a new Record instead of looking for an existing Record to update. If the import contains only new Records, the [ID] column can be excluded.

Important: A blank cell is imported as an empty value. When updating an existing Record, this can remove the current value from the corresponding Field.

An import file can contain a mixture of rows that update existing Records and rows that create new Records.

Using the [ID] and [Hierarchy] Columns

When Records are exported from Softools, the CSV file includes [ID] as the first column and [Hierarchy] as the second column. For more information about the exported file structure, see Export to CSV.

The [ID] column determines whether Softools creates a new Record or updates an existing one:

• If [ID] contains a value matching an existing Record, Softools updates that Record.
• If [ID] is blank, Softools creates a new Record.

The [Hierarchy] column identifies the parent of a child Record. Its value uses the following format:

ParentAppID|ParentRecordID

For example:

Risk|5f2aeebddc462c580cbba2ca

In this example, Risk is the Parent App ID and 5f2aeebddc462c580cbba2ca is the Softools Record ID of the parent Record.

The effect of [Hierarchy] depends on whether the import is started from within a parent Record or directly from the child App.

Importing from Within a Parent Record

When an import is started from a child Report within a parent Record:

• The value in [Hierarchy] is ignored, whether it is populated or blank.
• Every imported Record is linked to the parent Record from which the import was started.
• If an existing Record is linked to a different parent, it is moved from that parent and linked to the current parent.

Important: Importing an existing child Record from within another parent will replace its current parent relationship.

Importing Directly into the Child App

When an import is started directly from a Report in the child App:

• A valid [Hierarchy] value links the new or existing Record to the specified parent.
• If [Hierarchy] is blank for a new Record, the Record is created without a parent.
• If [Hierarchy] is blank for an existing Record, its current parent relationship is removed and the Record becomes unlinked.
• An invalid hierarchy format or invalid Parent App ID will not create a parent relationship. The invalid value is cleared and will appear as blank in later exports.

Important: When updating existing Records directly in a child App, do not leave [Hierarchy] blank unless you intend to remove their current parent relationships.

Creating Child Records with Parent-Based Default Values

To create a new child Record and allow parent-based default value expressions to populate:

1. Leave [ID] blank so that Softools creates a new Record.
2. Enter a valid parent reference in [Hierarchy] using the format ParentAppID|ParentRecordID.
3. Include the other Field values required for the new Record.

Softools can then establish the parent relationship during creation. This allows default values that reference the parent, such as [Parent.ProjectName], to retrieve values from the specified parent Record.

If the new Record is created without a valid parent relationship, a parent-based default expression cannot retrieve a value from the parent.

For more information, see Child & Parent Expressions.

Note: For CSV files created in different locales, where the comma separator or delimiter may be different, see Importing via CSV from Different Locales.

Uploading the Import File

Once the import file has been prepared, navigate to a Table Report, open the Report features menu and select Import.

This opens the Import pane, where you can upload the file by dragging and dropping it or selecting it from your device. The language and Field separator options ensure that Softools interprets the file correctly for your region.

• Drag & Drop: Find the CSV file on your device and drag it into the box at the top of the Import pane. The file will begin uploading and a progress bar will appear. Once uploaded, the import will run as a background operation.

• Choose Import File: Select Choose File to open your device’s file explorer. Find and select the required CSV file. Once uploaded, the import will run as a background operation.
• Show Import Options: Enable this option to set the Country (Language) and Field separator character for the import file.
• Country: Select the appropriate country to ensure that the correct character encoding and regional data formats are used. This is particularly important for characters such as accented letters.
• Field Separator Character: The default separator is based on the selected country, but it can be changed. Common separators include commas, semicolons, tabs and pipes. If the wrong separator is selected, the data may be interpreted as a single column instead of separate Fields.
• Run Workflows: Select this option if applicable Workflows should run during the import. Clear the option if the import should update the Records without triggering those Workflows.

Note: Import file names can contain letters, numbers, underscores and ampersands. Files containing other special characters in their names will not be accepted.

Field Validation on the Import File

Field values must use the correct format and be placed under the appropriate Field identifier. The following guidance applies to commonly imported Field Types.

• Selection Fields: A single-value Selection Field will have a column for the Field value and a second _Text backing Field containing the friendly text shown to Users. Import the value into the original Field, not the _Text backing Field. Multi-value Selection Fields can be imported as an array into the original Field.
• Image List Fields: Image List Fields have a _Text backing Field and an _ImageListAssetUri backing Field. Import the value into the original Field rather than either backing Field.
• Date Fields: Date and Date Time values use the format yyyy-MM-ddTHH:mm:ssZ. For example, to set a value to 12:45 on 5 January 2017, use 2017-01-05T12:45:00Z. Date Fields without a time are stored in the same format with the time set to 00:00:00. For more information, see Importing Date Fields.
• List Fields: A List Field is structured like an import within the main import. Each Field in the List is represented within one CSV cell. We recommend creating a Record containing representative List data and exporting it to see the required structure. A new List row uses a structure such as:

{"added": [{"TextField":"Text Data"}]}

• Formatted Fields: Adding a Format string to a Field creates a _Formatted backing Field. Import the value into the original Field rather than the _Formatted backing Field.
• Required Fields: Each new Record must contain valid values for all required Fields. The import also respects other Field validation, including minimum and maximum numeric values and character-length restrictions.

Checking the Progress of your Import

Import progress is shown in the notification area. Select the notification bell at the top right of the Workspace to view it. If desktop notifications are enabled, progress notifications may also appear in your browser.

For more information about the import, including any rows that produced errors, open Import Summaries in Settings. See Import Summaries for more information.

Updating a Record via a User Identifier Field

The User Identifier feature allows a Field in the App to be used instead of the Softools [ID] to identify a Record for updating.

For example, an App could use a Field called RAID ID containing values such as RAID-0012, RAID-0013 and RAID-0014.

When this Field is included in the import file:

• If its value matches an existing Record, Softools updates that Record.
• If its value does not match an existing Record, Softools creates a new Record using that identifier.

Important: Ensure that every User Identifier value is unique across the App. If more than one Record contains the same value, only the first matching Record will be updated. Archived Records are also included when Softools looks for a matching identifier.

Fields that take their values from expressions are excluded from an import. If the App’s User Identifier takes its value from an expression, use [UserIdentifier] as the column heading in the import file instead of the Field identifier.

Note: We recommend including only one of [ID], [UserIdentifier] or the User Identifier Field in an import file. If more than one is present, Softools applies them in the following order of priority: [UserIdentifier], the User Identifier Field and then [ID].

When working in Excel with data containing characters outside the ASCII character set, save the CSV file using UTF-8 encoding.