Importing records allows you to perform multiple updates across records as well as add new records on mass to an App. It provides a means of integrating with other systems by exporting the data from that source and then importing it into a Softools App. There is also the benefit of being able to download to a .CSV file so that you can work offline and then import your work back in after.
How to Prepare the .CSV File
It is important to structure the .CSV file correctly to perform the Record add and updates that are required. First create a .CSV file using Excel or other similar software. You need to have the Softools [ID] as the first column Heading in cell A1. Then all of the Fields that you want to import data to can be add in the first row of the file as column headings.
Note: The ID for the Field can be found by taking an Export of the data first and then removing the columns that you do not want to import the data for.
This then creates the template that you will be able to add rows to perform Record adds and updates. Each row in the import file is a Record with the data in each column being the value for the Field that is in the column heading.
- Record Update: To perform a Record update the Softools Record ID [ID] will need to be entered in the first column of the row. This will then be followed by the values that you want the Fields to take in each of the columns under the column headings. Each of the Fields must contain a valid value for the Field Type. To get the Record Ids we recommend working with a file that you have generated via an export first.
- Record Add: To perform a Record add the value in the Softools Record ID [ID] for that row needs to remain blank. This will add a Record instead of looking for a Record with a matching ID to perform an update. Then enter the values for the Fields that you want to add data to below each of the column headings for the Field. If the import file is only Record adds then you can exclude the [ID] column from the import.
Note: If a cell is left blank then this counts as having a value of empty string and so would delete the value for that Field if imported.
The import file can contain a mixture of rows that will update Records and rows that will add new Records
When you export records from Softools, you receive a CSV file with the [ID] of the record as the first column, and the [Hierarchy] as the second column. (see https://support.softools.net/hc/en-gb/articles/115003117786-Sharing-Data-via-Export-to-CSV)
When you import csv files back into Softools,
-
If the [ID] column has a value the system will look for an existing record to update
-
If the [ID] column has no value then the system will create a new record
-
If the [Hierarchy] column has no value, then the system will only add a hierarchy if the record is being imported from within a parent record, otherwise the record will be created with no hierarchy, relationship or parent record
-
If the [Hierarchy column has a value (such as Risk|5f2aeebddc462c580cbba2ca ) then the system will create or update the record from the CSV and make sure it is a child record of the one specified in the column
NOTE : for files created in different locales where the comma separator or delimiter is different in the CSV, see this article : Importing via CSV from different locales
Uploading the Import File
Once you have prepared your import file ready to perform Record adds and updates, you need to upload the file to trigger the start of the import. From a List Report click on the Report features menu and select 'Import'.
This will pop up a module where you can upload your import file by either drag and drop or choosing a file. Setting the language and appropriate field delimiters will ensure the expected result for your region.
- Drag & Drop: Find the file that you would like to upload from your device. Then drag the file into the drag and drop box at the top of the Import pane. This will accept the file and start uploading the document showing you a progress bar. Once complete the import will run as a background operation.
- Choose Import File: Clicking on Choose File will open your devices File explorer. From here you can then search for the Import File that you would like to upload and this will then begin the upload of the file. Once complete the import will run as a background operation.
- Show Import Options: Enabling this toggle will allow you to set the Country (Language) of the Import file along with the appropriate Field separator character.
- Country: Selecting the correct language for the import is important for a few reasons. Different languages use different character sets. Whilst most languages use the UTF-8 character set, others such as some European countries use ISO-8859-1. Selecting country will also make sure that characters such as accented characters are correctly encoded.
- Field Separator Character: By default, the Field separator character will be based on the country selected however you can override this to the desired delimiter. If the wrong delimiter is selected then the file could be read as all data appearing to be in one column as opposed to being separated into Field headed columns of data. The most common two separators are comma which is standard in most English speaking countries whereas a semicolon is common in European countries where commas are used as decimal points. Tab and Pipe separated files are often used when there are many commas or semicolons within the data file.
- Run Workflows: Ticking this option ensures that workflows, if applicable will trigger on importation. Equally you may want to uncheck this to give flexibility with certain field values that would otherwise be determined by workflow.
Note: There is validation on the file name for an import file. It can contain letters, numbers, underscores and ampersand but the import will not accept the file if it contains other special characters.
Field Validation on the Import File
Field values will need to be in the correct Format and against the right column in order to add the value for a Record. Here are some key tips that will help you to update the values in different Field Types.
- Selection Fields: If the Field is a single value Selection Field then there will be a data point for the Field and the Field_Text. Field_Text is the backing Field that holds the friendly name for the selection value. To update the value it will need the value imported to the Field and not the Field_Text. Muti-Value selection Fields can have their data imported in an array again to the original Field.
- ImageListField: As with Selection Fields, Image List Fields will have a _Text backing Field and also a _ImageListAssetUri backing Field holding the ID for the image displayed to the User. To update the value, add the value to the column that has the value for the Field and not the _Text or _ImagListAssetUri backing Fields.
- Date Fields: The format for data that is being imported to date or date time Fields is yyyy-mm-ddThh:mm:ssZ. (Technically this is called UTC date format) For example, to set a value to 12:45 on 5th January 2017 you would add the value as 2017-01-05:T12:45:00Z. Date fields (without time) are also stored in this format, with a 00:00 as the time) - More here
- List Fields: This field type is structured like a mini import into an import. each column in the list is a separate field to import into but is structured in one cell. We suggest creating a record where the list field has data and exporting that. You can then see how the field is structured for imports and copy and paste that into your new import file to edit. New rows in the list field are structured as such - {"added": [{"TextField":"Text Data"}]}
- Formatted Fields: When adding a Format string to a Field, this creates a backing Field_Formatted Field that contains the Format String. When importing data you will need to import the value to the original Field and not the _Formatted backing Field.
- Required Fields: Another validation check that is performed on the data is to check that each Record has a value for all required Fields before creating the Record. It will also respect all validation for string character lengths, min and max numeric values and any others as it would if you were creating the Record on screen.
Checking the Progress of your Import
The progress of the import will be present in the notification areas. You can view this in the notification bell at the top right of the screen and if you have desktop notifications enabled then these will also appear in browser notifications flashing up at the bottom of the screen.
For more detail on the import, including any rows that have given errors, use the Import Summaries section in Settings. For details on how this feature works look at are article on Import Summaries.
Updating a Record via a User Identifier Field
The User Identifier feature allows you to use a Field in the App instead of the Softools [ID] to identify a Record to update. An example would be to use a User added Field called RAID ID instead of the Softools ID. This can then be set up to give a consistent format to the ID (RAID-0012, RAID-0013, RAID-0014, ...).
By having the Field in the import File it will then take the same role that the [ID] was performing. If a value is in the import Row for the User Identifier and it matches an existing Record then it will perform a Record Update. If the value is not found in the Record collection then it will add a new Record with that ID.
Note: When using a User Identifier ensure that every value is unique across Records. Otherwise it will only update the first Record that it finds. Remember that Archived records also count, so remove archive records as well as they will be updated if the Identifier is found in an archived record
Fields that take their value via expression are excluded from an import. If the User Identifier set for an App takes it's value via expression then you will need to replace the Field ID with [UserIdentifier] as the column header in the import file.
Note: We advise only having one of [ID], [UserIdentifier] or UserIdentifierField in the import file. If more than one is present then they will be respected in priority order [UserIdentifier], then UserIdentifierField and then [ID].
- When using data that contains characters outside the ASCII SET in Excel. It is advised you save the CSV as UTF-8
Comments
0 comments
Please sign in to leave a comment.