Post processing survey responses

This is just one method, but it works well and allows the use of easily available tools to convert a lot of respones into a spreadsheet for further analysis.

The first step is to craft the form responses to return a format that helps the process. If you are using cgi-mailer, then this is done by formatting the .data file. The idea is to put some kind of unique character or string of characters as a field delimiter so that you can easily search and replace without the risk of false positives, for example:

[:::] 
[|:|] $q0
[|:|] $q1
[|:|] $q2

This clearly defines the start of each record and the start of each field with unique codes that a user is most unlikely to have typed into their response.

Assuming you’ve filtered all your responses into a single mailbox, the next step is to gather them all into one file.

The simplest method so far discovered is to use Apple Mail to connect via IMAP, select the contents of the mail box and ‘Save as...’, which results in a clean, single file of all respones. Outlook will let you export a mailbox to tab separated text, which works well too. There are probably many other similar methods.

The final step is to use a text editor (with grep capability) to ‘search and replace’ the text file to clean out all the text that is not required such as the headers of the email. This example uses BBEdit, which makes the task very simple, but experienced users of other tools should find a similar process which works for them.

The main problem is that text inputs and in particular textarea elements in forms allow respondents to enter text that creates new lines and this needs to be filtered out or it will break any import to a spreadsheet.

• remove lines containing “^From:|^To:|^Subject:|^Date:” gets rid of the headers. This will depend on what tool you’ve used to download the responses - there may be more or less headers to deal with.
• replace “\r|\t” with " “ - replaces all carriage returns and tabs in the file with spaces.
• replace " " with " “ - replace double spaces with single spaces, repeat until there are none.
• replace “[:::]” with “\r” - puts each separate response on a single line.
• replace “[|:|]” with “\t” - puts tabs between each field in a response

This should only take a couple of minutes total to reach the point where you can just copy and paste straight into a spreadsheet, the correctly formed text.