Import Data

 

Why Import Data?

Importing data allows you to automate the completion of multiple assessments at one time by simply uploading a CSV file.

A collation of selected responses from these Assessments can then be downloaded.

This feature can be used for many use cases. Some include:

  • Automating the creation of hundreds of personalised reports or contracts which can be automatically emailed out with e-signature requests via DocuSign.
  • Pre-filling of certain sections of an App with set information. There is then the option to notify clients to continue the Assessment.

 

Field Formatting in CSV File

Field/Block Format Requirements Example Field Example Input
List Page (LIST) The list is wrapped in curly braces. Each list item begins and ends with double quotations (") and items within the list are comma-separated LIST6 {"Data Entry 1","Data Entry 2"}

User Picker
(USER)

Should be the the user email  USER6  jane.smith@checkbox.ai
Text Field (TXT) Must be plain text** TXT6 Anna Jones
Number Field (NUM) In Excel, ensure that the data has no formatting NUM6 300
Date (DATE) DD/MM/YYYY DATE6 24/05/2019
Radio (RAD) Should match the text value of that radio button RAD6 Under 30
Dropdown (SEL) Should match the text value of that dropdown SEL6 Ms
Checkbox (CBX) Should either be TRUE or FALSE CBX6 FALSE
Yes/No Page (ACT) Should either be YES or NO ACT6 YES

  ** Text Field (TXT) - please be aware that copying and pasting text into Microsoft Excel cells from external sources (e.g. Google Forms) may cause errors in the .csv file. This is because Microsoft Excel converts text that is externally sourced into UTF-8 characters which are not are not recognised by the Checkbox system. To fix this, you must manually re-write the characters.

 

How to Import Data for Bulk Assessment

Before starting, ensure that your App is working with the correct logic pathways.

  1. Construct a CSV file where:

    Note: A sample Import File is attached at the bottom of this article

    • The format of the CSV file is"CSV MS-DOS"

    MS_DOS.png

    • Each column header represents a variable name (e.g. TXT#). This has to be the exact Input Variable Name contained in your App

      Note: Each variable should be a column header, regardless of whether it is conditional variable or not

    • Each row represents one Assessment


    Things to consider:

    Not all variables need to be provided inside the CSV if the particular path does not require it.

    For example, refer to the screenshot below:

    Import_Data_-_Google_Docs_2020-08-28_15-10-28.png

    You have two separate paths in your App that the end user can follow - “Yes” and “No” paths.

    Case 1: If you only require a bulk assessment for the “Yes” path, only the variable TXT13 needs to be provided in the CSV.

    Import_Data_-_Google_Docs_2020-08-28_15-11-25.png

    Case 2: If you only require a bulk assessment for the “No” path, only the variable NUM15 needs to be provided in the CSV.

    Import_Data_-_Google_Docs_2020-08-28_15-11-54.png

    Case 3: If your bulk assessment requires both “Yes” and “No” paths, both TXT13 and NUM15 variables must be provided in the CSV.

    Import_Data_-_Google_Docs_2020-08-28_15-12-26.png

    Notes:

    • Conditional variables must still be provided in the CSV but the value can be left blank if the condition is not met (provided this variable is within the required path see first point)
    • Variables that are set as “not required” must still be included in the CSV but the value can be left blank (provided this variable is within the required path see first point)

     

  2. Once the App is published, click on “Import and Export” on the left panel of the App in the Project Team and click “+ IMPORT DATA” and upload your CSV file

 Import_Data_-_Google_Docs_2020-08-28_15-16-09.png

 

What if there is an error in my CSV file?

Errors in the CSV file will be detected by Checkbox and the user can see the diagnosis by downloading the uploaded file with error(s). See the following instructions below:

  1. Go to click on “Import and Export” on the left panel of the App in the Project Team

    Import_Data_-_Google_Docs_2020-08-28_15-17-47.png

  2. If the import status says “Error”, click onto the dotted icon under the “Actions” column, and click “Download” to download the CSV file with errors

    Import_Data_-_Google_Docs_2020-08-28_15-19-26.png

  3. Open the downloaded CSV file and click onto the error message in the first column named “error”

    In this case, the error indicates that “Ms” is a field value that has not been provided as an option in the dropdown variable with the variable name “Title”

    Note: The error message will be generated in the row that has the error. In this case, the error message is displayed on the first row after the header row because the error occurs from the “Ms” value in the “Title” column in the first row.

    Error_message_in_Excel.PNG


Another common error is when text has been copied and pasted from an external source causing the Checkbox system to not recognise the characters.


Please be aware that copying and pasting text into Microsoft Excel cells from external sources (e.g. Google Forms) may cause errors in the .csv file. This is because Microsoft Excel converts text that is externally sourced into UTF-8 characters which are not recognised by the Checkbox system. To fix this, you must manually re-write the characters.

 

 

How to Assign Pre-Filled Assessments to another user with User Picker

For use cases such as conducting audits, there may be times where the data is already available, and questions need to be assigned to and answered by an individual to approve or continue an Assessment. You can do this by adding a user picker variable in your CSV file for when you are importing data to Checkbox.

Note: User picker inputs in CSV files must be valid e-mail addresses that correspond to a Checkbox user who has been added to the Project Team where the bulk Assessment is being completed.

For example, header row: USER6 and row 2: jane.smith@checkbox.ai.

Import_Data_-_Google_Docs_2020-08-28_15-21-41.png

Watch the video below for a demonstration on how the user picker function can be used to create multiple assessments, with pre-filled and available data.


 

 

How to Name Assessments for Bulk Assessments from the CSV File

The individual Assessment names can be named and distinguished in the CSV file prior to importing data into Checkbox for bulk Assessments.

  1. In the CSV file, create a new column.

bulkupload1.png


2. In the first cell of the column, type the meta variable “[[assessment_name]]”

bulkupload2.png



3. In the cells below, write the name you would like each assessment to be known by. 

Note: Multiple Assessments can share the same name if that is desired.

bulkupload3.png



4. When you export the data once it has been imported, the CSV file should exported data should look like the following:

bulkupload6.png

 Learn more on how you can export data. 

 

Notes and Limitations

 

Bulk Assessment will stop if one of the following occurs:

  1. All values provided in the CSV file are used
  2. The Report page is reached
  3. The Workflow page is reached and further action is needed (e.g. Approval Workflows or Multi-User Assessments)
  4. Dynamic field column name in the CSV file is not added even if condition is false

 

Bulk Assessment supports:

  1. E-sign via DocuSign or Adobe Sign Integration
  2. Routing Logic 
  • If using bulk Assessment with a Y/N Path based Route Logic, not all variables of the App are required to be added in the CSV file
  • If using bulk assessment with a TXT, SEL, NUM etc based Route Logic, all variables of the App must be added in the CSV file. If they are not part of the selected route, variables still must be added but the field can be left blank in the CSV file

 

Bulk Assessment does NOT support and will cause errors in the file for:

  1. LOOP pages
  2. Parallel Workflow
  3. Javascript execution in pages
  4. Special characters in the Import CSV file (e.g. ~!@#$&? etc)
  5. Tables (But COMP Tables are supported)
  6. File Upload Fields
  7. Public Project Teams 
  8. [[initial_owner]] metavariable tag

*Attached below is a sample Import File 


Related Article: