Hacker News Reader

Comment by onemorepassword | Hacker News Reader

My preference is .csv, because it can be read by almost any program.

Except the one most used for it: Excel. Excel sucks at csv, especially if the recipients have various internationalized versions. It can't deal properly with multi-line strings and spaces, but especially the internationalization is hell (semi-colons instead of commas, decimal separators, date formats).

Many times have I dealt with CSV-exports that could be read in any software except client's Excel versions.

View on HN

You left out the absolute worst bit: Excel tries to "helpfully" guess the correct data format for each cell in a CSV file. This can lead to silent data loss. Long strings of numbers are converted to a Numeric type that only stores a certain number of significant figures. The string "1-5" is converted to a Date field with the value "5-Jan"

If you are generating the CSV yourself, save yourself some agony and just wrap the text in =""

    $ cat test.csv
    ="12.34567890124312341234123412341234",="1-5"
    $ open -a Microsoft\ Excel test.csv

Where did that = sign come from? That isn't in the standard... Quotes are, but wtf? An equals sign? Fucking Excel.

JUST TREAT THE NUMBERS LIKE TEXT YOU STUPID PROGRAM.

000001 === 000001 != 1

It uses another auto trick: if the leading character is '=', the result is treated as a formula.

Thus, you can actually write formulas in your CSV and excel will interpret appropriately:

    $ cat test.csv
    =1+1,="1-5"
    $ open -a Microsoft\ Excel test.csv

You should see the value '2' (with content `=1+1`)

If you start using metacharacters, it's no longer a universal .csv, but an excel-specific one.

Sure, it's a nice trick, but now it's not really a CSV file any more. If I have to create a file that can only be read by Excel it might as well be XLSX.

There's a slight difference between having to strip "=" from the column values for other applications to handle it, and require those other applications to be able to handle XLSX..

At least XLSX is an open and documented format. Are the rules for Excel CSV parsing written down anywhere?

"At least XLSX is an open and documented format."

The documentation diverges from the excel implementation in so many places that there's a special document (which itself is incomplete): http://msdn.microsoft.com/en-us/library/dd922181(v=office.12...

"Are the rules for Excel CSV parsing written down anywhere?"

They are loosely described in ECMA-376 section 18.8.30 under the "General" format code. But like with most details surrounding XLS and XLSX, the documentation is barely coherent and incorrect in some places.

You can specify the field types when opening the file.

Except that when users click on or download a csv-file, it Excel opens it automatically, bypassing any options and not even bothering to check if the contents of the csv file matches its defaults.

This is 90% of use cases with regular users.

For the other 10%, the interface for specifying the field types sucks, doesn't cover certain options and you can't set your preferences.

Imagine being a user and having to do that every day in order to import your daily whatever report, and having to start all over if you make one little mistake.

And that's just for the options Excel supports. Don't even get me started on all the other quirks. Ever tried to read a csv file who's first line starts with "ID"?

… and precisely how many users do you think know to do this? They might if it's happened before, been correctly diagnosed and explained before and it's obvious as soon as you open the file. It's more common, however, that the Excel bug only causes data loss or invalid results in a percentage of records and, when people notice it, it's corrected by hand.

If you need to do this a lot, generate xlsx - it's not that hard and there are decent libraries for most languages. Relying on users correctly setting column types every time is like making your daily commute depend on having airbags.

Any numbers with leading zeroes, i.e. 0000123, excel will interpret as the integer 123 even if you change the column format to "text". It's infuriating.

The only way I found to get around this was to open a new workbook, change the column type to "text" and then paste the data in. I believe this was excel 2010 on windows.

You can add an apostrophe before the leading zeros, and the cell will keep the zeros and still be a number type.

Indeed. I don't know what version of Excel the parent is using, but Excel for Mac 2010 handles csv files just fine, even ones that have weird delimiters. I'd love to see an example of a non-readable csv file.

Edit: I haven't tried different Unicodes yet, so granted that may not work.

'It can't deal properly with multi-line strings and spaces'

Works fine in excel 2011 for mac:

    $ cat test.csv
    abc,"def
    
    ghi",jkl
    mno,pqr,stu
    $ open -a Microsoft\ Excel test.csv

http://imgur.com/CRUJZ57

This is an example of a poorly laid-out menu structure. To force it to do the right thing, you have to wrap text (right click -> format cells -> alignment -> "Wrap text" on).

Basically, the default rendering is supposed to be a single space for any whitespace (tab, newline, space) and "wrap text" forces it to do the right thing.

Shameless plug: I'm actually working on an XLSX parser: http://niggler.github.io/js-xlsx/

From someone currently dealing with Excel's CSV writing for a business application, I heartily concur. There is a decade old bug (at least) where the CSV save as format does not properly handle Unicode, and there's no simple way to handle it down the pipeline. I guess the Office team would rather work on the metro ribbon.

"Save as CSV" produces text encoded in the users ANSI codepage which is atrocious, Excel 2007+ at least has "Save as Unicode Text" which produces \t separated UTF16 output.

Well CSV has no real specification, or way to store metadata eg about encoding. It is a terrible format.

http://tools.ietf.org/html/rfc4180

As I said, no real specification. It reads "This section documents the format that seems to be followed by most implementations". Another part reads "Each field may or may not be enclosed in double quotes (however some programs, such as Microsoft Excel, do not use double quotes at all)."

The RFC is a useful collection of anecdotes...

The HTTP 1.0 RFC says the same thing:

http://tools.ietf.org/html/rfc1945#page-4

"This specification describes the features that seem to be consistently implemented in most HTTP/1.0 clients and servers."

Is HTTP not a real specification either?

And then laid down which ones would be used and exactly how, and how to specify that you conform. Http is just describing process of how the spec came about. Because there is no metadata in csv you cannot even say how or if you conform.

Meet RFC 4180 - http://www.ietf.org/rfc/rfc4180.txt

(Yes, it's not an internationally recognized standard, but neither is HTTP or IRC)

I guess the Office team would rather work on the metro ribbon.

Of course they would! They've already achieved their goal with Excel: make it a lock-in product. In this case, they did it by breaking CSV compatibility, thereby forcing everyone to use XLSX.

'CSV save as format does not properly handle Unicode'

[MS-XLS] ends up using special codepages (e.g. http://www.unicode.org/Public/MAPPINGS/VENDORS/MICSFT/WINDOW...) for representing characters, so you need to make sure the same codepage is used through all of your applications.

Example: For those that have access to windows and macintosh excel, CHAR(240) is the apple logo on mac but something different on windows.

> I guess the Office team would rather work on the metro ribbon.

Zing! Thank you for that.

Nice example: http://www.biomedcentral.com/1471-2105/5/80 "Mistaken Identifiers: Gene name errors can be introduced inadvertently when using Excel in bioinformatics"

I am fairly sure that other spreadsheets will make such errors, too, though. I recently saw one in Numbers that depended on the amount of data pasted (pasting hundreds of rows seemed to disable the smartness)

I've always found the C in CSVs to be needlessly limiting. I'm not aware of any tool that contextualise commas in addresses and other "free text" fields that might form part of the data dump/extract.

For this reason, I am strongly in favour of pipe-separated values in such files. The probability that a data extract has a pipe symbol in any data field is quite small (in my experience, it's been 0 so far).

The first thing I do in any Windows system I have to use on a regular basis is to change the the system separator to '|' in the Regional Settings..

Then you run into ASCII art some end user made, or pre-Unicode text from Scandinavia where 0x7C was the code point of a letter. Commas make it pretty obvious which tools are unusably broken, where very rare characters let these bugs go undetected far too long.

And then you get sent the file:

Statistics on 100000 UNIX shell one liners!

Yes, pipe-delimited format is great, and much easier to parse than comma-delimited.

Tab-delimited is also better than comma-delimited, I think.

Commas just have far too high a probability of appearing within the data itself, and when you hit a comma that was supposed to be escaped, it adds an extra column to the row, screwing everything up.

Tabs are great, until you try to explain to a non-programmer why your tool doesn't parse their file correctly, even though in the word-processor they're using as a text editor it looks identical to another file that parses correctly.

Tab-seperated is also very common, and I find better - how often are you storing text-formatting within cell for a data interchange? Similar to pipes, tabs are much less common than commas.

I also find the C in CSVs to be needlessly limiting. Especially since ASCII already comes defined with characters for record separator (30), unit separator(31), and group separator(29).

Excel and other tools can handle commas if you use quoted fields. Unfortunately this generates a new problem when you need to escape quotes inside a quoted field. There is no universal escape method.

Same for me. CSVs would be far less likely to mess up the data if they were PSVs instead.

Agreed, CSV sucks if you data contains punctuation. UTF-8 encoded tab-delimited tables are preferred. These are kind of a pain to import into Excel (on Mac at least) because you have to specify exactly what format they are in, but it can be done and there is no worry about quoting conventions and so forth unless your data contains literal tab characters that are important.

Reply on news.ycombinator.com