Using Microsoft Word and mail merge to output and view data
By default, SurveyCTO Desktop exports data as .csv or comma-separated-values format. This is ideal for opening the data in Excel, Stata, or any other program that treats data as columns and rows. Unfortunately, this format may not be ideal for users who wish to review the data. For example, you may have a data scrutiny process that partly involves people who manually look over incoming data (to catch mistakes that can't be easily caught by automated computer review). For human review, .csv format is not ideal.
However, you may still want to build your own system for review, outside of SurveyCTO. One approach is to use Microsoft Word's "mail merge" feature to merge the computer-readable data with a more user-friendly template. If you choose this approach, SurveyCTO assists in two ways:
- You can download a starter template for any existing form. On the Design tab of your server console, select Download and then Mail merge template for any form in the Your forms and datasets section, then specify how you want to label your fields and whether you have SurveyCTO Desktop configured to include group names in .csv file headers. You can then customize this template or use it as-is.
- Desktop will auto-merge exported data for you. Go into Workspace settings, click the Export options tab, and check the Auto-run mail merge after export checkbox. Then, copy the mail-merge template you downloaded from the server into the destination directory into which you export your .csv files, and name it formid_template.docx (where formid is the unique ID of the form, like "sampleform"). Whenever a new .csv file is exported for that form, a formid_merged.docx file will also be output; that file will contain the combination of the latest .csv data with the mail merge template.
Note that fields within repeat groups (i.e., fields enclosed by begin repeat and end repeat rows) are not included by default in the mail-merge output. This is because such fields are exported into separate .csv files, and the mail-merge process only works with a single, primary .csv file. To manually add repeat-group data to a mail merge template, you would need to do the following:
- Enable the Desktop export setting to export repeated fields in "wide" format.
- Open the template and manually merge it with the (title)_WIDE.csv file now exported by Desktop.
- Add any of the repeated-field columns from the (title)_WIDE.csv file into your template to merge the data from those columns.
- Save your updated template.