Tuesday, 10 September 2013

Excel-Friendly CSV Files

Xataface has supported CSV export since the beginning, but I have recently been made aware that the CSV files that Xataface produces haven't been "MS Excel" friendly when it comes to UTF-8 special characters.  You may have noticed that if you export a data set as CSV from Xataface where some records include special characters (like é and å), and try to open it with MS Excel, the characters become garbled.  These CSV files work fine with Open Office, Google Docs, and pretty much every other spreadsheet program out there.  It's just that MS Excel doesn't seem to support UTF-8 encoded CSV files.

(Note: You can still import them into Excel using its import features, where you can specify the encoding explicitly, but if you try to simply open the CSV file with Excel, it will assume that the encoding is Mac Roman or Windows 1252 depending on whether you're on Mac or Windows).

Many solutions were tried to try to make these CSV files work nicely with Excel, but it seems that Excel simply can't handle UTF-8!

Today I implemented an experimental feature in Xataface to be able to export data to CSV that Excel can handle.  It works by using UTF-16 encoding instead of UTF-8 as well as some other settings specifically designed to cater to Excel (e.g. tab delimiter instead of commas, and a different mimetype).

You can enable this feature by adding the following to your application's conf.ini file:


Then you can just use the export CSV action as normal (e.g. from list view click "Export" > "CSV" if using the g2 theme, or click the "Export CSV" icon in the upper right of the list view if using the default theme).

This change is currently in the master repository on GitHub and will be included in the 2.0.3 release.

1 comment: