Excel CSV: watch and set your delimiters (and the #NSBusinessCard – Digitale specificatie #fail)
Posted by jpluimers on 2011/10/19
A while ago, a friend asked my why Excel sometimes doesn’t import CSV files correctly. Recently, I was reminded because NB Business Card sends their invoice as Excel CSV. And expected CSV to be universal. Which it isn’t. Besides pople doing all sorts of crazy things with CSV (like What would happen if you defined your system CSV delimiter being a Quotation), Excel CSV isn’t universal either, as it depends on a crucial entry your Windows regional settings: the “List Delimiter”. Two series of screeen shots show this: The first for Windows XP and systems like it:
And for Windows 7 and similar systems:
A few tips: When you export CSV, be sure to use this setting from the registry. Or at least allow your users to specify it in your application, because: when you are not using Windows, you might be out of luck as there is no operating system neutral way of querying this value. And when you get some CSV and cannot import it in Excel, make sure you change your list delimiter to match it. And don’t forget to restore it to the original setting when you are done: it is a user session wide setting, so it applies to all applications (including the ones that do watch it). –jeroen
Неожиданная сложность CSV-формата « Роман.Янковский.me said
[...] что это список значений, разделённых запятой. Но я выяснил, что на самом деле это не совсем [...]
Roman Yankovsky said
This RFC documents the format used for Comma-Separated Values (CSV) files
http://tools.ietf.org/html/rfc4180
jpluimers said
Thanks!
–jeroen