For future reference: [CSV][csv] is a terrible format for spreadsheet data.
Any by spreadsheet data I mean CSV is a terrible format for text data that will be opened in [Microsoft Excel][excel] (unless you only speak English).
My objection to CSV as a spreadsheet format is it has no way to indicate the text encoding, and Excel (at least Excel 2008 for Mac and Excel 2007 for Windows) has no way of choosing the encoding when opening a CSV file. When your data is text from any number of non-English languages you will likely be dealing with characters outside the standard ‘latin-1’ character set.
Even if Excel did have a little pop-up menu for choosing the encoding when opening a CSV file, it would mean instructing your Excel-happy friends to “open as UTF-8” whenever they wanted to open your CSV data.
Wot you mean you don’t have Excel-happy friends?
[csv]: http://en.wikipedia.org/wiki/Comma-separated_values
[excel]: http://office.microsoft.com/en-us/excel/
LibreOffice Calc and OpenOffice Calc always asks for “Character set” and “Separator” when opening CSV file.
I agree CSV sucks. Who the hell invented this thing with the intension of data exchange between applications? They didn’t think more than a few hour to make the very first version of the CSV file format. Every CSV file must have a “protocol” for how to interpi(r)ate the data. Date’s yyyy-MM-dd? or dd-MM-yyyy. Delimiters, Qualifiers, Decimal point. and encoding. Use OpenXML (Excel xlsx format when u have under 1 mill. records pls) :-) OpenXML (Excel +2007 files are XML ZIP files and “easy” to work with)
I strongly disagree with you guys. Its not csv that sucks, its Excel!!!
I mostly work with data – and dont get me wrong, there are many things I love about excel – but
Excel tells me that I will lose my formating everytime I save a csv –Duh!!! if cared about formatting I would’nt be saving as a csv. I also understand that there may be people that don’t know this. Fine, keep the existing behaviour as a default, but let users change it.
And, why cant I save multiple worksheets as csv at one time? (this would save me SO much time.
Oh, and why must it delete the leading 0’s in “numbers” in csv files (example – 067533) For us, these are identifiers. And yes, I DO agree that numeric ID’s that start with 0 are a bad idea for a number of reasons, but in my case theese numbers come from the state of Michigan. I can’t change their format, even though I “agree with Excel” that its a bad idea.
@scott, no CSV just sucks. It’s a terrible document format.
CSV isn’t a document format, its a data format. It’s perfectly fine for that. Excel just sucks at reading it. It’s easy to deduce the character encoding of a UTF-8 file, especially if it has a BOM, but Microsoft doesn’t want to encourage its use so is DELIBERATELY not making them easy to work with.
ITS 2017. EVERYTHING SHOULD BE IN UTF-8. If you’re using something else for your default, you’re doing wrong!