8 ms·
Question: why the hell do people use CSVs? Use tabs, goddammit. Documents are littered with commas. It's trivial to parse a TSV with awk - not so a CSV. There i
by astazangasta 11y ago
Question: why the hell do people use CSVs? Use tabs, goddammit. Documents are littered with commas. It's trivial to parse a TSV with awk - not so a CSV. There is literally no reason to prefer CSV to TSV, and yet it seems the former is the default for many people. Why? As a guy who parses text files all day, I never ever want to see a CSV again. End rant.
- minimaxir 11y agoPretty much every programming language in existence has a utility for parsing CSVs into tabular data while handling commas sanely. (using awk for processing is not a typical pipeline) More importantly, CSVs are handled natively by statistical programs such as Excel and R (you can change the expected delimiter while importing, but in my experience that leads to unexpected results)
- gruez 11y ago>More importantly, CSVs are handled natively by statistical programs such as Excel and R in excel or R? Because I've never encountered problems with tsv files with excel
- semi-extrinsic 11y agoR can also read both CSV and TSV just fine. Just use read.csv() or read.table() as you want. Same goes for Pandas.
- Pxtl 11y agoMicrosoft often fails with CSVs actually. I've found I can't copy/paste from Sql Server Management Studio into Excel if you have a mismatched double-quote in a cell - it fails silently and garbles your data. That was embarrassing. The CSV format is so messy that even big corporate implementations will have subtle bugs like that. Do not trust it.
- 0x0 11y agoMicrosoft excel has the worst CSV defaults. The separating character is even locale-specific! Many locales use semicolon ";" instead of "," as a separator. Great stuff when creating web app exporters.
- muddi900 11y agoExcel can handle TSV's if you name them .txt
- hiperlink 11y ago> using awk for processing is not a typical pipeline except it is. (g)awk is more often part of the pipe(d)line than other programming langueage(s) interpreter(s).
- dorfsmay 11y agoNetwork effect: Database upload/export, spreadsheets, etc... I used to hate them too until I realized how ubiquitous and well supported they are. comma vs. tab: It doesn't matter, in the end, you have to use a character to separate fields, and if you are going to use a "normal" character, there are chances that it might appear in your text, so you have to either escape that character, or quote your text, which csv does in a fairly standard way, so comma vs tab doesn't really matter.
- infecto 11y ago+1 I always found it best practice to quote text. Makes the debate a moot point.
- username223 11y ago> ... if you are going to use a "normal" character, ... It's a shame that the ASCII file, group, record, and unit separators that have been around since forever did not catch on.
- Rotten194 11y agoMostly because they aren't user-inputtable. Yes you can't realistically hand-edit a 3gb .csv either, but csvs don't usually start out that large.
- username223 11y agoI was going to add a sentence to my comment... They were easy to input via control-caret, etc., until various programs repurposed those combinations. I think a bigger problem is that there is no standard way to display them. (BTW, if inputtability mattered at all, Unicode wouldn't exist. ;-)
- e12e 11y agoI'm not sure what you mean by your last sentence. Sure, you might have to work a bit to be able to change your locale to Klingon, and input that -- but for something for which unicode is actually useful, like Japanese, there's been input solutions for a long time (predating unicode, as you need a wide character set for such languages). And unicode is of course easy to use with "less exotic" languages like Norwegian that can't be represented in basic 127-bit ascii -- via a simple keyboard map.
- andreasvc 11y agoI can think of at least one reason. Copy-pasting from a terminal fails to preserve tabs, while commas are fine. Tabs might be somewhat less frequent than commas in data, but you're still gonna have just as bad a time when they do turn up. And don't forget quoting and escaping. Without proper parsing you can neither read CSV nor TSV safely. My go to solution is pandas.read_table()
- danso 11y agoYeah, this is the deciding factor for me, too. The point of delimited text files is to find some compromise between machine-efficency and human readability. CSVs are generally much less readable than TSVs, but have less ambiguity, too. With TSVs, it's hard to tell if a tab space is actually a tab or just multiple spaces. And with developers (especially Python devs) setting their text editors to translate tabs-into-spaces, copy-pasting TSVs results in data corruption. Or even opening a TSV in your text editor with default settings.
- devit 11y agoGood terminal applications such as Gnome Terminal preserve tabs.
- spilk 11y agoWhy use a character like tab, which can also appear in common text, when there are control characters designed exactly for this purpose? ASCII 0x1C - 0x1F are there specifically for machine parsing of tabular data.
- TorKlingberg 11y agoIf you use control characters, it is not plain text any more. It's hard to edit with a text editor. Tabs have a similar problem: They look like spaces in a simple text editor like notepad.
- jsprogrammer 11y agoThis just seems like a failing of the text editor. But, if you can't input the character...you can't input the character.
- twic 11y agoBecause you immediately run into some madman who wants to separate the columns with four spaces instead.
- walterstucco 11y agoTab? You mean two spaces right? :)
- mbell 11y ago> Use tabs, goddammit. Use ASCII's `RECORD`, `GROUP` and `UNIT` separators, they were literally designed for it.
- nullwagon 11y agoawk -F"," Problem solved.
- z92 11y agoNot when the data is like this. "Richard, Martin", "23 NS, North Coast, NY" Comma frequently occurs inside texts, and then awk fails.
- ferrari8608 11y agoawk -F'", "' That's apostrophe, quote, comma, space, end quote, apostrophe.
- Rotten194 11y agoWhat if not all columns are quoted? A lot of the time only the columns that need quoting are quoted and the others are bare.
- z92 11y agoNice solution. Will not work were numeric fields are not quoted. But nice solution, nevertheless.
- Rangi42 11y agoWhich still fails if the values contain escaped quotes: Name, Age, Address "James aka ""Jim"", ""licensed"" attorney", 42, "New York" That's three values: James aka "Jim", "licensed" attorney 42 New York And there are other possible irregularities: zero or N spaces after the comma separators; unquoted values when they're not needed; backslash-escaped special characters; escaping newlines.
- dilap 11y agoi agree, but sadly programs like excel tend to do better w/ commas. it's sad.
- bane 11y agoUse tildes. Tilde Separated Values are relatively robust and nobody uses tildes by themselves for anything I've ever seen. Most tools that let you set a delimiter will accept tildes also.
- icebraining 11y agonobody uses tildes by themselves for anything I've ever seen Abbreviated Unix paths? ~/.bashrc Approximated values? ~30º
- MichaelBurge 11y agoThe company I work at uses the pipe character('|') everywhere. It's fairly painless, and you can edit the files using standard text editors. It's also pretty easy for clients to provide or consume pipes. CSVs are terrible - people always have trouble providing or ingesting them with the correct quoting: probably 50% of the time it's suggested there is strong resistance to the idea of using CSV quoting. TSVs are okay - you can copy-paste from Excel into your terminal so it's got that going for it. There's been one time where a client provided a couple hundred gigabytes of data where all the reasonable characters one might use were used in the fields themselves, including their own separator character(the comma). So I made up some rules for guessing which commas were delimiters and which were part of a field value, and stored it using the ASCII control character as a delimiter. The only other use of this character at my company are in jokes, like "We should ask the client to provide or ingest files separated using the ASCII control codes" which always gets a laugh.
- ycitera 11y agoWhy use TSV when you can use an sqlite3 file?
- Nicholas_C 11y agoI can't open a sqlite3 file easily in Excel for easy data manipulation, visualization, and sharing.
- stevepike 11y agoIf you haven't tried it, check out the csvkit tools: https://csvkit.readthedocs.org/en/0.9.1/ https://csvkit.readthedocs.org/en/0.9.1/. `csvlook` and `csvcut` are awesome. I'm also a huge fan of Ruby's `csv` package in the standard library, particularly http://ruby-doc.org/stdlib-1.9.2/libdoc/csv/rdoc/CSV.html#method-c-table http://ruby-doc.org/stdlib-1.9.2/libdoc/csv/rdoc/CSV.html#me.... All that only works for properly quoted CSV though... for companies who can't generate quoted csv files pipes or tabs are the unfortunate way to go.
- d0mine 11y agoTo get tabs instead of commas: #!/usr/bin/env python import csv import sys writer = csv.writer(sys.stdout, delimiter='\t') writer.writerows(csv.reader(sys.stdin))
- ised 11y ago"Documents are littered with commas." It's trivial to change all those commas to something else before making the CSV file and importing into the database. They can then be converted back to commas by processing the query result. This type of conversion before storage is routinely done with other characters, such as newlines, quotes, etc. For example, look at the JSON for the 10mHNComments data dump. Personally, from a readability standpoint, tabs drive me nuts. I am glad TSV is not the default.
- techbio 11y agoNO CONSISTENT DELINEATORS STOP SEND ESCAPE CHARACTERS FULL STOP
- InclinedPlane 11y agocsv is a garbage format but at least it's a standard, and one with a lot of tool support, and that can handle embedded commas in data if processed correctly. The idea that tab separated data is a superior format is, frankly, absurd. I could understand a call for, say, json or even xml, but an ad hoc standardless format which has the solitary advantage of being incrementally easier to parse with awk? No thanks.