We may occasionally run into the situation where we need to import data into the Webform submission tables. In our demonstrator specification, the video store owners have requested functionality that will enable them to upload some old data they have in a spreadsheet into the survey form database for future use. Another instance of such a situation is when we need to transfer submitted data from one Webform to another.
Standard Webform has no such upload facility, but the Webform Import project does.
Let's navigate to the Webform Import homepage at http://drupal.org/project/webform_import and download the latest 7.x version. The compressed folder needs to be extracted in sites/all/modules so that we have sites/all/modules/webform_import.
Now we navigate to our site's Modules page to enable the module by checking the box next to its name and clicking on Save configuration.
- Once the module has been enabled, we navigate to our Video Store Survey form, click on the Results tab, and select the new Upload subtab we find there.
- Click on the INSTRUCTIONS fieldset heading to expand it.
- Click on the DOWNLOAD TEMPLATE link next to FIELD FORM KEYS to download a template CSV (comma separated values) file.
- Open the downloaded template with a spreadsheet application, being sure to specify that fields are separated by commas.
- Now we can fill out information for a few test submissions to be uploaded to our form:
name
surname
preferred_username
email_address
acceptance
film_genres
SID
John
Doe
johnd
0
0,3,3,2,1
Jane
Doe
janed
0
2,2,2,3,1
Tom
Smith
tomtom
0
3,3,3,0,1
- Let's save the file to disk, taking care to ensure that it is saved as a CSV file, with comma as the field delimiter and double quotes as the text delimiter.
- Returning to the Upload page on our site, we scroll down to Delimited data file and click on the Browse... button to locate our data file.
- For File Delimiter we select the Comma (,) option.
- At Column header contains we select the Field Form Keys option and click on the Submit button.
- Scrolling back up the page, we click on the Submissions sub-tab and verify that the data has been received by viewing or editing our uploaded submissions.
Importing data to Webform using this module is essentially a three-step process. First, we download a template file, then we populate the template file with data, and finally we upload the data to our Webform. It's as easy as that.
Perhaps the main criteria to bear in mind to ensure a successful import is consistency of separators and delimiters. We absolutely must specify the same options on the Upload page as we used when we saved the populated data file. Failure to do so will not end well as we shall experience the frustration of an incomplete or corrupted data import.
Working with CSV files is not always quite as straightforward as it sounds, because not all CSV files are created equal. Adding to the potential for unexpected results, we also have idiosyncrasies in the way Webform deals with its components. Let's have a look at some of the issues.
At first glance there does not seem to be any pressing reason why one would select one type of field/column separator over another. Generally, that is true, but what happens when the separator we select is actually contained within one of the cells of our CSV spreadsheet? What happens when we specify double quotes as our text delimiter and one of the cells contains a double quote within the cell as part of the data content?
These seemingly innocuous considerations can cause a fair amount of trouble for an automated process trying to handle a CSV file. The two possibilities outlined previously would cause our CSV parser to create an extra cell for the pertinent row, because it has found what we specified to be a delimiter. Webform Import will not insert such a line into the database because it cannot successfully map CSV cells to Webform components.
It is a small thing and easy to overlook but it can cause the processes to fail.
Webform Import offers us the choice of effecting mapping between spreadsheet columns and components by utilizing either the component labels (field names), or the internal Webform field keys (form field keys). In our example we selected the form field keys option for only one reason: form field key names are unique, whereas many components may have the same display name (label).
As an illustration of the point, let's assume we had a form with three components with the display name (label or form field name) of Name. The first of these is for the user's name, the second is for the name of the user's cat, and the third is for the user's pet name for their computer.
Webform enforces the rule that each of these must have a unique field key, so we would potentially have user_name, cat_name, and computer_name as the respective field keys.
Should we attempt an import using Field Names as the column heading option, we would import submissions where two of the name fields would be blank. The import process would place the value in the first Name column into its own field for Name. This value would then be replaced by the value in the second Name column, because it would appear to be the same internal field. This second name would then be overwritten by the value in the third Name column.
Assuming the components are in the sequence described, such an import will result in submissions reflecting that all users are named after their computers, and their respective cats and computers have no names.
It is safer, by far, to utilize the form keys as our column headings to ensure that the mapping of data is accurate.
Webform Import will not import any spreadsheet rows (that is, submissions) where a mandatory field has a blank value in the CSV file. As we see in the instructions text, it will also fail if we do not properly format Time and Date data as required. The good news is that we will be shown error messages indicating the spreadsheet rows which were not successfully imported, along with a description of why the respective rows were ignored
We can also use Webform Import to bring about bulk updates of existing submissions. First, we download our template CSV, then we export our existing data.
We click on the Download subtab to specify our export options. We need to specify that Webform exports our Select options component data using the option keys instead of the option value that our users see on the form. This is achieved by expanding the SELECT LIST OPTIONS fieldset and selecting Short, raw options (keys) in Compact form.
Next we uncheck all the Submission information fields (with the exception of Submission number!) in the INCLUDED EXPORT COMPONENTS fieldset.
Now we click on the Download button so that we make any changes that we need to the data. When we're done editing we replace the default spreadsheet headings with the headings from our template file, checking to see that everything lines up correctly. After carefully saving the file, with due attention to separators and delimiters, we may upload the data.
We need not upload every row that was exported. If we have only made changes to a sub-set of the data, then we need only upload the affected rows.
Any row in the imported CSV file which does not have a value in the SID column will be inserted as a new submission. Rows that contain an SID value will cause an update of the corresponding submission.