Excel Displays a "File Not Loaded Completely" Error When Opening the CSV Export
This is a limitation of Excel 2003 (and older). If the form contains more than 256 questions, the exported file will contains more than 256 columns and it won't open directly in Excel.
For a workaround, visit: http://office.microsoft.com/en-us/excel/HA010548191033.aspx.
The latest version of Excel does not have this limitation.
Accented Characters are Garbled When Opening the Exported Data in Excel
The CSV file generated by the export uses the "UTF-8" character encoding, which is not correctly detected by some versions of Excel for Mac.
To open these files in Excel for Mac:
- Save the exported file as a .csv
- Open Excel
- Import the data using Data → Import External Data → Import Data
- Select the file type of csv and browse to your file
- In the import wizard change the File_Origin to "65001 UTF" (or choose the correct language character identifier
- Change the Delimiter to comma
- Select where to import to and select Finish
Repeated Sections Cannot Be Sorted in Excel
Data from repeated sections is displayed on separate lines in Excel. While this makes it easier to read, it's not possible to sort this data without losing the relationship between repeated rows and "master" rows.
Data from a form collecting parent and children information
To work around this problem, add a new column in your Excel spreadsheet and use a formula to fill the column with the data you need to sort on.
- Add a column. In the example here, we've added column J.
- Select the second cell. Here, it's J2.
- Assuming E is the column we want to sort on, enter this formula:
- Apply the formula to every cell in the column J. The column should now have data on every single row.
- You can now sort or filter the column J.
Some Data is Missing After a CSV Export of Responses
This happens when fields have changed on the form over the course of form revisions. When doing a CSV export you will only see data that has been collected with the latest version of a field. You will not be able to see response data from earlier versions of the same field.
In order to get response data from earlier revisions of your form you will need to revert back to an older version and then run your export. You can this by following these steps:
- Go to the Revisions tab.
- Make a comment in the revision notes along the lines of "last save before reverting to older versions in order to collect CSV export data."
- Revert the form to the previous revision
- Once the form has been reverted, download the CSV export data again.
- Open the export data and see if you have all of the data you need from the particular field(s).
- If you are still missing data, repeat steps 1-5 as needed, each time reverting to an older version of the form.
- Once you have found all your data, merge it all into a single CSV data sheet and restore the original version of the form that you started with and left a comment on.