As powerful as the Webform module undoubtedly is, it cannot hope to solve all of our varied and individual data usage needs. When we have reached the limit of what Webform offers, we download the data in spreadsheet format to continue.
Once we have downloaded the submitted data we may use the resulting spreadsheet with our preferred spreadsheet application to achieve other ends, such as creating a mail-merge document to print conference name badges or producing graphs, for example.
Importing the submitted data into our preferred spreadsheet application is as easy as clicking on the Download sub-tab on our Results page. Of course, we do need to be logged in as a user with Access all webform results permissions enabled in order to have these options visible.
There are several options we may use to define our download request parameters, but for the moment we will accept the default settings:
- Let's click on the Download button.
- Most Internet browser applications will now show a pop-up which allows us to either open the downloaded file directly, or to first save the file to our computer, before we open it with our spreadsheet application software. Let us select the option to open the file.
- While most spreadsheet applications can import data in a variety of formats, they do tend to expect the file to be in a special binary format. Webform exports data as a flat text file, using special characters to separate columns (that is, fields), with each spreadsheet row on its own line. Our spreadsheet application will now ask us to confirm that it has correctly identified the column separators and we click on OK to proceed with the import.
- To ensure that we get full value from our spreadsheet application, let's now save the spreadsheet to our computer in the standard format for our software, for example, as
.xlsxfor Microsoft Office Excel or.odsfor Calc (OpenOffice.organdLibreOffice).
By accepting the defaults on the Download page, we now have a spreadsheet listing all of the submissions made on our form. The data is sorted in submission ID sequence and we see our component names, or labels, as column headings.
The first few columns show us the familiar metadata for each submission, after which we see the submitted data for our form components in the same sequence that they render on the form, although now expressed from left to right in the same row.
Let's take a quick look at the various options on the Download page and learn how they influence the downloaded data file.
As the submitted data is exported in text file format, we need to tell Webform how our spreadsheet application knows where one field's data ends and the next one starts. We achieve this through defining the limits of each cell by means of a specified character known as the delimiter.
It is important to note that we must select our delimiter with some care, as otherwise our spreadsheet import could give us unexpected results. Selecting a comma as the delimiter would work only if we are assured that there can be no commas in the submitted user input. If commas are present in the submitted data, we will find that our spreadsheet software will split one field's data across two or more columns, depending on the number of embedded commas in the data.
In the general case, therefore, it is safest to use the default tab delimiter. The pipe (a vertical line) is also a fairly safe option to use.
When we define a Select Options component, such as our Title field from the form built in Chapter 2, Trying Out Webform, we specify two values per option on the component settings page—the internal key and the human-readable screen value. In this option set, we may specify which of these two values is preferred for the data download.
Additionally, we may specify whether the various selections per component should be listed each in their own column or concatenated together in a single spreadsheet column.
It is not always required for us to download all of the submitted data. On those occasions where only a few of the form fields are required, we may uncheck the fields we do not need.
Should we decide, for example, that we require a list containing only the registered speakers' first and last names, we could uncheck the INCLUDED ALL COMPONENTS option and check the boxes adjacent to First name and Last name.
To exclude only the submission metadata section from our download file, we would, intuitively, uncheck the box at Submission information.
Just as we do not always require all fields to be included in a download, we may also wish to download only a specific set of submissions. By default we will download all submissions, but we may reduce the downloaded data set to only those submissions that have come in since our last download.
The other options are to download only the most recent number of submissions or submissions where the submission ID falls within a specified range.