Importing an Excel File

1. If you are importing an Excel file, when you click Next in the first stage of the procedure, the Import Properties dialog box opens as shown - .



Figure 1 - The second stage of the Import dialog, for an Excel import

The options available are as follows:

• Locale - determines the formatting of elements such as numbers (decimal point) and dates/times. Select the locale appropriate to the formatting used in the file.
• Execution mode - select how the data is to be added to the target database.

You can choose between three modes for importing the data, listed below. In all the modes the user will be asked to provide a key (see Key field below). The keys available are respid, responseid, and any field in the survey database that has an index (go to General for more information). To create an index on a field in a survey database, go to the question properties of the field and set the "Indexed" property (available on single and open text questions with a field width set), and then re-launch the survey. By employing this setting, you can then use for example email address, membership number etc. as a key. The rule executer reads the value of the selected key field in the incoming data file and checks to see if a row with the same key already exists in the target.

• Merge data - all the data from the source will be added to the target survey database. Any records in the source that already exist in the target survey database will be updated, and any records in the source that do not currently exist in the target survey database will be added to the target survey database.
• Append data - any records in the source that do not exist in the target survey database will be added to the target survey database. Any records in the source that already exist in the target survey database will be ignored.
• Update data - any records in the source that already exist in the target survey database will be updated with values from the source. Any records in the source that do not already exist in the target survey database will be ignored.

In these scenarios, when new records are added, the new records are inserted both in the response data and in the respondent table (respondent list).

• Key field - the key field is used as a unique identifier to indicate which records are to be updated and which are to be appended. The key field is also used to map the responses in the top-level file with the responses in loop response files. The key field must exist in both the import file(s) and the database.

System variables respid and responseid and all survey variables with the "Indexed" property set, will be displayed in the "Key field" drop-down. You may choose any survey variable that has a unique value for each respondent as key field. The key field should either be one of the system-provided ids (respid or responseid), or an open text question with a defined field width. Examples of key fields could be membership number, customer id or email address.

Important: The fields respid and responseid are system-provided ids in Authoring. When appending records (inserting records that are not already present) through data import with one of these fields as key field, the system will replace the ids you import with new system-generated ids. 

2. Make the appropriate selections, then click Next .