Formatting and Validation

In the Upload tabs, click Formatting and Validation to access a number of properties that you can select for the upload file.

The properties are as follows

• Delimiter - a sequence of one or more characters used to specify the boundary between separate, independent regions in plain text or other data stream. An example of a delimiter is the comma character in a sequence of comma-separated values. Select the delimiter that has been used in the file you are importing. The options are:
• Tab separated - the columns in the file are separated by tabs. Note that tab-separated fields must not be in quotes (“..”).
• Comma separated - the columns in the file are separated by commas.
• Custom - a different character has been used as the separator. Type the character that has been used as the delimiter into the Custom Delimiter field.

• Text qualifier - this setting appears when the Delimiter selected is Comma Separated or Custom (see above). If the respondent values may contain the delimiter character that is being used as the separator (for example, text fields containing commas as punctuation when Delimiter is set to Comma Separated), then you can use “With quotes” to avoid the file format being corrupted. Note that all fields in the respondent table are of type Text, so if you use “With quotes” for the Text Qualifier then all values (not column headers) must be enclosed with quotes.

• Date format - use this option if the respondent file contains a column which will be uploaded to a background variable of type Date, and the date value in the file differs from a format that is supported by the system (yyyy-mm-dd hh:mm:ss or yyyy-mm-dd). You can either select the appropriate date format from the drop-down list, or select Custom date format and then manually type the format into the custom field using the standard format codes: “y” for year, “M” for month, “d” for day, “m” for minutes, “s” for seconds, and "tt" the AM/PM designator.
• File Encoding - choose the encoding method used by the respondent list.

• Check for background variables

  All background variables required – the upload file must contain every background variable defined in the survey. If any background variables are missing, the file will not be uploaded and a warning will be displayed. Default.

  Only selected background variables required – only the background variables you select are validated. Click SELECT VARIABLES to open the Select Background Variables dialog, then add the variables you want to validate to the Selected variables pane and click SELECT to save your changes. Once a selection has been made, the button changes to MODIFY SELECTION, and the selected variables are displayed beneath the option. If any of the selected background variables are missing from the upload file, the file will not be uploaded and a warning will be displayed.

  No background variables required – no validation is performed against background variables. The upload file can be uploaded even if it does not contain any of the survey's background variables.

• Validate Emails - when selected, the email addresses will be checked and rows containing invalid email addresses will not be uploaded. On completion of the upload, an email listing the rows that have not been uploaded will be sent to the user. Note that the system does not check if the addresses actually exist; only that the format is correct.
• Validate answers and codes for Single questions - when selected, the codes and answers for single questions in the respondent file are validated. The validation process will first check if the value specified in the file exists as a code. If it does, the record will be uploaded. If it does not exist, the validation process will check if the value is specified as an answer label in the Single question. If so, the label will be converted to the code specified in the Single question and stored as such. If this validation process fails, the record will be rejected and a notification email with the rejected records will be sent to the user.
• Check for duplicates - when selected, the uploaded file will be checked for duplicates. If a record already exists in the respondent database then subsequent records will be rejected from the upload task (the task owner will be emailed notification with a list of rejected records). Respondent records are uploaded in the order they occur in the upload file, therefore in a file containing two duplicate records, the first record will be uploaded to the Respondent table and the second record will be rejected. When the box is checked, two option fields become available:
• Columns to check for duplicates - allows you to specify which columns in the uploaded file are to be checked for duplicate rows. By default the "email" column will be added to the field. If the uploaded file then contains rows with email addresses that already exist in the database or which have previously been uploaded, then those rows will not be uploaded. On completion of the upload, an output file listing any rows that have not been uploaded will be sent to the user. You can overwrite the "email" default with any column which exists in the uploaded file. It is also possible to check more than one column, by adding multiple column names in the text field. In this case the column names must be separated by semi-colon as for example email;phone. If more than one column is specified, a row will be treated as a duplicate if it has the same values for all the specified columns as a row that already exists.
• Duplicate Interval (days) - if a value is specified, only data for respondents created N days prior to the task scheduling will be used for de-duping. Records with NULL in Created Date will also be included, unless updated through Respondent > Upload or Respondent > Edit.