Tips & Tricks

Making CSV Data Exports with UTF-8 More Excel Friendly

1. •  
     CommentAuthorcameron
   • CommentTimeOct 15th 2008

    

   By design, Intervals handles all data, input or output, import or export, as UTF-8. UTF-8, a subset of Unicode, is a method of encoding data that essentially treats the data as "region-neutral." This gives Intervals truly international support.

   However, some Intervals users recently ran into trouble when attempting to export data into Microsoft products. Specifically, MS Excel has trouble opening CSV files encoded in UTF8. Characters outside the range of ASCII characters get garbled into nonsense. Excel does have support for Unicode, it's spotty. Here are some of the issues we encountered when trying to make CSV output more friendly to MS Excel:

   1. To get Unicode to display properly in Office (including Excel), UTF-8 has to be converted to UTF-16LE (little endian).
   
   
   2. Once the output is converted to UTF-16LE, Excel doesn't pay attention to the column definitions. To get around this, you have to use the tab ("\t") as the value separator rather than a comma, effectively changing the CSV file into a TSV file.
   
   
   3. Using TSV format, the columns are lined up correctly, but multi-line content within a single cell isn't regarded, and the content flows across multiple rows in Excel. There are many places within Intervals where the CSV output displays multi-line content within a single cell (an example of this is the Task List, where task history is dislayed within the row for each task). There doesn't seem to be a solution for this.

   Internally, we've discussed a few options for dealing with this issue, though none of them appear to be ideal. On one hand, we'd like to avoid changing the integrity of our CSV output just because Excel is unable to support UTF-8; on the other hand, we realize that most people who export data do so with the intention of viewing the data within Excel. I invite you to discuss the following options and let us know which is most appealing to you and your company:

   1. Handle data exports the way Google does: provide both a UTF8 and UTF16LE (Google calls it "CSV" and "CSV for Excel") download option.
   
   
   2. Modify our CSV output to be more compatible with MS Excel. In otherwords, change the encoding on our CSV output to UTF-16LE, change the CSV to TSV, and change the exports themselves so that all multi-line content is either made inline or is eliminated altogether.
   
   
   3. Forgo Excel and preserve the integrity of the data.
   
   
   4. Upgrade to a third-party Excel library and start outputting data as XLS files (rather than CSV files).

   In the interim, if your data contains multi-byte characters and you're having trouble exporting it into Excel as CSV, we recommend trying a spreadsheet application with full Unicode support like Calc from OpenOffice. OpenOffice is a free office suite with solid reliability, an excellent reputation, and a feature set to strongly rival MS Office.