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, you must first save the file locally. Then, open the file in a plain text editor such as TextEdit. Select “Save As” from the File menu, and for “Plain Text Encoding” choose “Unicode (UTF-16)”. Then open a blank document in Excel and use the data import wizard (Data menu->Get External Data). Locate the file you just saved, and finish the wizard.

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.

Workaround:

• Add a column, here in position J.
• Select the second cell (J2)
• Assuming E is the column we want to sort on, enter this formula: =IF(ISBLANK(E2),J1,E2)
• 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 on the column J.

See a 2mn screencast showing this issue and the workaround.