5 ms·
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 h
by onemorepassword 14y ago
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.
- jrajav 14y agoFrom 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.
- thomasz 14y ago"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.
- niggler 14y ago'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/WINDOWS/CP1252.TXT 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.
- bjhoops1 14y ago> I guess the Office team would rather work on the metro ribbon. Zing! Thank you for that.
- justincormack 14y agoWell CSV has no real specification, or way to store metadata eg about encoding. It is a terrible format.
- UnoriginalGuy 14y agohttp://tools.ietf.org/html/rfc4180 http://tools.ietf.org/html/rfc4180
- justincormack 14y agoAs 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...
- anonydsfsfs 14y agoThe HTTP 1.0 RFC says the same thing: http://tools.ietf.org/html/rfc1945#page-4 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?
- justincormack 14y agoAnd 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.
- merlincorey 14y agoMeet RFC 4180 - http://www.ietf.org/rfc/rfc4180.txt http://www.ietf.org/rfc/rfc4180.txt (Yes, it's not an internationally recognized standard, but neither is HTTP or IRC)
- npsimons 14y agoI 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.
- nickm12 14y agoAgreed, 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.
- niggler 14y ago'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 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/ http://niggler.github.io/js-xlsx/
- Someone 14y agoNice example: http://www.biomedcentral.com/1471-2105/5/80 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)
- shrikant 14y agoI'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..
- coldtea 14y agoAnd then you get sent the file: Statistics on 100000 UNIX shell one liners!
- kyllo 14y agoYes, 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.
- jleader 14y agoTabs 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.
- eksith 14y agoSame for me. CSVs would be far less likely to mess up the data if they were PSVs instead.
- qw 14y agoExcel 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.
- eli 14y agoYou 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"
- niggler 14y agoIf 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
- UnoriginalGuy 14y agoWhere 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
- niggler 14y agoIt 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`)
- vacri 14y agoIf you start using metacharacters, it's no longer a universal .csv, but an excel-specific one.
- eli 14y agoSure, 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.