[HN Gopher] A love letter to the CSV format
___________________________________________________________________
A love letter to the CSV format
Author : Yomguithereal
Score : 254 points
Date : 2025-03-26 17:08 UTC (5 hours ago)
(HTM) web link (github.com)
(TXT) w3m dump (github.com)
| Qem wrote:
| 9. Excel hates CSV It clearly means CSV must be doing
| something right.
|
| This is one area where LibreOffice Calc shines in comparison to
| Excel. Importing CSVs is much more convenient.
| trzeci wrote:
| I don't get it - why the world, Excel can't just open the CSV,
| assume from the extension it's COMMA separated value and do the
| rest. It does work slightly better when importing, just a
| little.
| TuringTest wrote:
| It could, but it doesn't want to. The whole MS Office
| dominance came into being by making sure other tools can't
| properly open documents created by MS tools; plus being able
| to open standard formats but creating small incompatibilities
| all around, so that you share the document in MS format
| instead.
| Qem wrote:
| Probably Microsoft treats a pure-text, simply specified,
| human-readable and editable spreadsheet format that fosters
| interoperability with competing software as an existential
| threat.
| boricj wrote:
| Your comma isn't my comma. French systems use the comma as a
| decimal point for numbers and we use semicolons to separate
| fields in CSV files.
| mort96 wrote:
| No, french systems also use comma to separate fields in CSV
| files. Excel uses semicolon to separate fields in France,
| meaning it generates semicolon-separated files rather than
| comma-separated files.
|
| It's not the fault of CSV that Excel changes which file
| format it uses based on locale.
| boricj wrote:
| It's even worse than that. Office on my work computer is
| set to the English language, but my locale is French and
| so is my Windows language. It's saving semicolon-
| separated CSV files with the comma as a decimal point.
|
| I need to uncheck _File > Option Advanced > Use system
| separators_ and set the decimal separator to a dot to get
| Excel to generate English-style CSV files with semicolon-
| separated values. I can't be bothered to find out where
| Microsoft moved the CSV export dialog again in the latest
| version of Office to get it to spit out comma-separated
| fields.
|
| Point is, CSV is a term for a bunch of loosely-related
| formats that depends among other things on the locale. In
| other words, it's a mess. Any sane file format either
| mandates a canonical textual representation for numbers
| independent of locale (like JSON) or uses binary (like
| BSON).
| mort96 wrote:
| > It's saving semicolon-separated CSV files with the
| comma as a decimal point.
|
| It's not though, is what I'm saying. It's saving
| semicolon-separated files, not CSV files. CSV files have
| commas separating the values. Saying that Excel saves
| "semicolon-separated CSV files" is nonsensical.
|
| I can save binary data in a .txt file, that doesn't make
| it a "text file with binary data"; it's a binary file
| with a stupid name.
| oezi wrote:
| Sorry, but what Excel does is save to a file with a CSV
| extension. This format is well defined and includes ways
| to specify encoding and separator to be readable under
| different locales.
|
| This format is not comma separated values. But Excel
| calls it CSV.
|
| The headaches comes if people assume that a csv file must
| be comma separated.
| mort96 wrote:
| I don't care what Excel calls it. As I said, if I name a
| file .txt but stuff it with binary data, it's not a text
| file.
| orwin wrote:
| That, bad specs, weird management/ timezone/ governance/
| communications and random \n\r issues transformed a 2 day
| fun little project into a 4 week hell. I will never work
| with CSV in France ever again. Mostly because of Excel,
| normal CSV nice.
| criddell wrote:
| Most of the people most of the time aren't importing data
| from a different locale. A good assumption for defaults
| could be that the CSV file honors the current Windows
| regional settings.
| anilakar wrote:
| If it only was that easy. Experience has shown that the
| only reliable way is to run heuristics against the first
| few lines of the file.
|
| There are office programs that save CSV with the proper
| comma delimiter regardless of the locale.
|
| There are people who run non-local locales for various
| good reasons.
|
| There are technically savvy people who have to deal with
| CSV shenanigans and can and will send it with the proper
| comma delimiter.
| gibibit wrote:
| Excel won't import ISO 8601 timestamps either, which is crazy
| these days where it's the universal standard, and there's no
| excuse to use anything else.
|
| You have to replace the "T" separator with a space and also any
| trailing "Z" UTC suffix (and I think any other timezone/offset
| as well?) for Excel to be able to parse as a time/date.
| macintux wrote:
| Honestly I'm happier when Excel doesn't try to convert
| anything. too much bugginess.
| inglor_cz wrote:
| Especially gene names. It was so bad that the scientific
| community renamed the genes in question rather than
| suffering from the same horror endlessly.
|
| [0] https://www.theverge.com/2020/8/6/21355674/human-genes-
| renam...
| gibibit wrote:
| Just wrong!!
| TrackerFF wrote:
| Have you tried using the "from text/csv" importer under the
| data tab? Where it will import your data into a table.
| Because that one will import ISO 8601 timestamps just fine.
| Suppafly wrote:
| This, it's dumb but Excel handles csv way better if you
| 'import' it vs just opening it. I use excel to quickly
| preview csv files, but never to edit them unless I'm OK
| only ever using it in Excel afterwards.
| calfuris wrote:
| Even in that case I'd be hesitant to open a CSV file in
| excel. The problem is that it will automatically apply
| whatever transformation it thinks is appropriate the
| moment you open the file. Have a digit string that isn't
| semantically a number? Too bad, it's a number now, and
| we're gonna go ahead and round it. You didn't really need
| _all_ of the digits of that insurance policy number, did
| you?
|
| They did finally add options to turn off the common
| offenders, but I have a deeply ingrained distrust at this
| point.
| jandrese wrote:
| I usually have the opposite problem. There's even a joke
| about it:
|
| How is Excel like an Incel? Both of them think everything is
| a date.
| Night_Thastus wrote:
| I just wish Excel was a little less bad about copy-pasting CSVs
| as well. Every single time, without fail, it dumps them into a
| single column. Every single time I use "text to columns" it has
| insane defaults where it's fixed-width instead of delimited by,
| you know, commas. So I change that and finally it's fixed.
|
| Then I go do it somewhere else and have to set it up all over
| again. Drives me nuts. How the _default_ behavior isn 't to
| just put them in the way you'd expect is mind-boggling.
| nly wrote:
| Search and replace + text to columns after the fact works fine.
| Der_Einzige wrote:
| I'm in on the "shit on microsoft for hard to use formats train"
| but as someone who did a LOT of .docx parsing - it turned into
| zen when I realized that I can just convert my docs into the
| easily parsed .html5 using something like pandoc.
|
| This is a good blog post and Xan is a really neat terminal tool.
| kbouck wrote:
| Xan looks great. Miller is another great cli tool for
| transforming data among csv, tsv, json and other formats.
|
| https://miller.readthedocs/
| emmelaich wrote:
| https://miller.readthedocs.io/
| kbouck wrote:
| trimmed the version path off and went a bit too far!
| thanks.
| tengwar2 wrote:
| I'm not really sure why "Excel hates CSV". I import into Excel
| all the time. I'm sure the functionality could be expanded, but
| it seems to work fine. The bit of the process I would like
| improved is nothing to do with CSV - it's that the exporting
| programs sometimes rearrange the order of fields, and you have to
| accommodate that in Excel after the import. But since you can
| have named columns in Excel (make the data in to a table), it's
| not a big deal.
| recursive wrote:
| It used to silently transform data on import. It used to
| silently drop columns.
|
| That's it, but it's really bad.
| Suppafly wrote:
| It's really bad if your header row has less columns than the
| data rows. You really need to do the import vs just opening
| the file because it's not even obvious that it's dropping
| data unless you know what to expect from your file.
| roelschroeven wrote:
| One problem is that Excel uses locale settings for parsing CSV
| files (and, to be fair, other text files). So if you're in e.g.
| Europe and you've configured Excel to use commas as decimal
| separators, Excel imports numbers with decimals (with points as
| decimal separator) as text. Or it thinks the point is a
| thousands separator. I forgot exactly which one of those
| incorrect options it chooses.
|
| I don't know what they were thinking, using a UI setting for
| parsing an interchange format.
|
| There's a way around, IIRC, with the "From text / csv" command,
| but that looses a lot of the convenience of double-clicking a
| CSV file in Explorer or whatever to open it in Excel.
| Suppafly wrote:
| Excel is halfway decent if you do the 'import' but not if you
| just doubleclick on them. It seems to have been programmed to
| intentionally do stupid stuff with them if you just doubleclick
| on them.
| tfvlrue wrote:
| In the past I remember that Excel not properly handling UTF-8
| encoded text in a CSV. It would treat it as raw ASCII (or
| possibly code page 1252). So if you opened and saved a CSV, it
| would corrupt any Unicode text in the file. It's possible this
| has been fixed in newer versions, I haven't tried in a while.
| qw wrote:
| It's related to how older versions of Windows/Office handled
| Unicode in general.
|
| From what I have heard, it's still an issue with Excel,
| although I assume that Windows may handle plain text better
| these days (I haven't used it in a while)
|
| You need to write an UTF-8 BOM at the beginning (0xEF, 0xBB,
| 0xBF), if you want to make sure it's recognized as UTF-8.
| nayuki wrote:
| I greatly prefer TSV over CSV. https://en.wikipedia.org/wiki/Tab-
| separated_values
| recursive wrote:
| Thanks for your input.
| adzm wrote:
| Agreed, much easier to work with, especially if you can
| guarantee no embedded tabs or newlines. Otherwise you end up
| with backslash escaping, but that's still usually easier than
| quotes.
| recursive wrote:
| It's not really easier than CSV if you can guarantee no
| commas or newlines.
| jefftk wrote:
| Much easier to require fields not have tabs than not have
| commas, though.
| solidsnack9000 wrote:
| Parsing escapes is easier than parsing quoted text with
| field and record separators embedded in it. Every literal
| newline or literal tab is a separator. One can jump to the
| thousandth record, for example, just by skipping lines,
| without looking into the contents.
| alkh wrote:
| The problem with using TSV is different user configuration. For
| ex. if I use vim then Tab might indeed be a '\t' character but
| in TextEdit on Mac it might be something different, so editing
| the file in different programs can yield different formatting.
| While ',' is a universal char present on all keyboards and
| formatted in a single way
| tgtweak wrote:
| Csv suffers from similar encoding and platform specific
| gotchas surrounding newlines and delimiter escaping.
| caspper69 wrote:
| Well, back in my day, we used tabs for tabs.
|
| But then some folks came along about 15 years ago screaming
| about spaces, and they won, so now tabs are 2 or 4 spaces.
|
| The law of unintended consequences strikes again!
|
| Note: not meant to denigrate you space supporters out there.
| marcellus23 wrote:
| The only situation I can think of where a tab is not a tab,
| is in a code editor that's been configured (possibly by
| default) to use spaces instead. But that's an easy enough
| configuration to change. And certainly wouldn't be a problem
| for something like TextEdit.
| Night_Thastus wrote:
| Functionally the same. I'd prefer CSV if my content was likely
| to have whitespace in it and didn't want billion quotes. I'd
| prefer TSV if my content was unlikely to have whitespace, and
| more likely to contain commas.
| bee_rider wrote:
| CSV is a pseudo-standard anyway, IMO the delimiter should be a
| configurable option (like it is in Unix cut and those kinds of
| tools).
| Suppafly wrote:
| > IMO the delimiter should be a configurable option
|
| It is. CSV has been character separated vs comma separated
| for probably decades now. Most tools you'd use to mess with
| them allow you to specify which separator character is being
| used.
| bee_rider wrote:
| I agree although it seems to render the distinction made by
| GP moot, right?
| mort96 wrote:
| TSV looks incredibly ugly when opened in a text editor unless
| all values are 7 characters or less.
| ciupicri wrote:
| Set a bigger tab size in the editor, e.g. `:set ts=32` in
| vim.
| mort96 wrote:
| Aaaand now I can fit like 3 fields next to each other
| before they wrap and the field with arbitrary length text
| still misaligns the field after it
| bee_rider wrote:
| There is at least a chance that the text fields will fit
| in a tab. If not, a tool like "column" on Linux can be
| used. There is no chance that a text field will fit
| inside the width of a comma.
| ciupicri wrote:
| I was about to mention that column [1] is part of util-
| linux, so perhaps it's Linux specific, but then I noticed
| this in the FreeBSD man page [2]:
|
| > The column command appeared in 4.3BSD-Reno.
|
| [1]: https://man7.org/linux/man-pages/man1/column.1.html
|
| [2]: https://man.freebsd.org/cgi/man.cgi?query=column&sek
| tion=1
| nayuki wrote:
| There are some specialized text editors for editing files
| with tabs.
| https://en.wikipedia.org/wiki/Tab_stop#Dynamic_tab_stops ,
| https://nick-gravgaard.com/elastic-tabstops/ ,
| https://tibleiz.net/code-browser/
| mort96 wrote:
| If have to use a dedicated tabular data editing program, I
| may as well use a spreadsheet application. What do the
| options you propose do better than libreoffice calc?
| bee_rider wrote:
| column -t | less -S
|
| is pretty nice because you can inspect a file or dataset
| on a server (no X) before downloading, to see if you even
| want to bother. Or you can pass it along through a series
| of pipes to just get the rows you want.
| mort96 wrote:
| What part of "the value of CSV is to have something that
| can be easily viewed and modified in any text editor" do
| you not understand
| bee_rider wrote:
| I'm not sure where that quote is from but it is
| incorrect, CSVs aren't easily viewed or modified in text
| editors in general (at least not in any way that takes
| advantage of their tabular nature).
|
| There's at least a slight chance that tab separated
| values will look ok in a text editor (although in
| general, nope).
| solidsnack9000 wrote:
| How does CSV look when the fields are all of different
| widths?
| pstuart wrote:
| meh. The whitespace makes it easier to eyeball IMHO.
| putlake wrote:
| 100% agree. TSV is under-rated. Tabs don't naturally occur in
| data nearly as often as commas so tabs are a great delimiter.
| Copy paste into Excel also works much better with tabs.
|
| Code editors may convert tabs to spaces but are you really
| editing and saving TSV data files in your code editor?
| ciupicri wrote:
| The problem with TSV is what are you going to do about quotes.
| Some fields might contain them [1] or they might be needed to
| store fields with tabs inside them.
|
| Because of this in order to read a plain simple TSV (fields
| separated by tabs, nothing more) with the Python csv module [2]
| you need to set the quote character to an improbable value, say
| EUR (using the euro sign because HN won't let me use U+1F40D),
| or just parse it by hand, e.g. _row.split( '\t')_.
|
| [1]: https://github.com/wireservice/csvkit/issues/1194
|
| [2]: https://docs.python.org/3/library/csv.html
| makeworld wrote:
| Quotes are just characters in TSV. Actual tab characters are
| banned so this should be simple. (Of course poor
| implementations may behave differently.)
| solidsnack9000 wrote:
| TSV's big advantage is that, as commonly implemented, the
| separators are escaped, not quoted. This means that a literal
| newline (ASCII 0x0A) is always a record separator and a literal
| tab (ASCII 0x09) is always a field separator. This is the
| format many databases -- including Postgres -- use for text
| export by default.
|
| There are some notes I put together about TSV a few years ago
| that expand on these points:
| https://github.com/solidsnack/tsv?tab=readme-ov-file#motivat...
| mjw_byrne wrote:
| CSV is ever so elegant but it has one fatal flaw - quoting has
| "non-local" effects, i.e. an extra or missing quote at byte 1 can
| change the meaning of a comma at byte 1000000. This has (at
| least) two annoying consequences:
|
| 1. It's tricky to parallelise processing of CSV. 2. A small
| amount of data corruption can have a big impact on the
| readability of a file (one missing or extra quote can bugger the
| whole thing up).
|
| So these days for serialisation of simple tabular data I prefer
| plain escaping, e.g. comma, newline and \ are all \\-escaped.
| It's as easy to serialise and deserialise as CSV but without the
| above drawbacks.
| andrepd wrote:
| That would be solved by using the ASCII control chars Record
| Separator / Unit Separator! I don't get how this is not widely
| used as standard.
| mjevans wrote:
| The _entire_ point of a CSV file is that it's fully human
| readable and write-able.
|
| The characters you mention could be used in a custom
| delimiter variant of the format, but at that point it's back
| to a binary machine format.
| kevmo314 wrote:
| What do you mean? I just push the Record Separator key on
| my keyboard.
|
| /s in case :)
| mbreese wrote:
| I'm pretty sure those used to exist.
|
| But when looking for a picture to back up my (likely
| flawed) memory, Google helpfully told me that you can get
| a record separator character by hitting Ctrl-^ (caret).
| Who knew?
| corysama wrote:
| The entire argument against ASCII Delimited Text boils
| down to "No one bothered to support it in popular editors
| back in 1984. Because I grew up without it, it is
| impossible to imagine supporting it today."
|
| You need 4 new keyboard shortcuts. Use ctrl+, ctrl+.
| ctrl+[ ctrl+] You need 4 new character symbols. You need
| a bit of new formatting rules. Pretty much page breaks
| decorated with the new symbols. It's really not that
| hard.
|
| But, like many problems in tech, the popular advice is
| "Everyone recognizes the problem and the solution. But,
| the problematic way is already widely used and the
| solution is not. Therefore everyone doing anything new
| should invest in continuing to support the problem
| forever."
| zzo38computer wrote:
| The keys would be something other than those, though.
| They would be: CTRL+\ for file separator, CTRL+] for
| group separator, CTRL+^ for record separator, CTRL+_ for
| unit separator. Other than that, it would work like you
| described, I think.
|
| > But, like many problems in tech, the popular advice is
| "Everyone recognizes the problem and the solution. But,
| the problematic way is already widely used and the
| solution is not
|
| This is unfortunately common. However, what else happens
| too, is disagreement about what is the problem and the
| solution.
| WorldMaker wrote:
| In Windows (and DOS EDIT.COM and a few other similarly
| ancient tools) there have existed Alt+028, Alt+029,
| Alt+030, and Alt+031 for a long time. I vaguely recall
| some file format I was working with in QBASIC used some
| or all of them and I was editing those files for some
| reason. That was not quite as far back as 1984, but
| sometime in the early 1990s for sure. I believe EDIT.COM
| had basic glyphs for them too, but I don't recall what
| they were, might have been random Wingdings like the
| playing card suits.
|
| Having keyboard shortcuts doesn't necessarily solve why
| people don't want to use that format, either.
| zzo38computer wrote:
| > I believe EDIT.COM had basic glyphs for them too, but I
| don't recall what they were, might have been random
| Wingdings like the playing card suits.
|
| That is not specific to EDIT.COM; they are the PC
| characters with the same codes as the corresponding
| control characters, so they appear as graphic characters.
| (They can be used in any program that can use PC
| character set.)
|
| However, in EDIT.COM and QBASIC you can also prefix a
| control character with CTRL+P in order to enter it
| directly into the file (and they appear as graphic
| characters, since I think the only control characters
| they will handle as control characters are tabs and line
| breaks).
|
| Suits are PC characters 3 to 6; these are PC characters
| 28 to 31 which are other shapes.
| LegionMammal978 wrote:
| > The entire argument against ASCII Delimited Text boils
| down to "No one bothered to support it in popular editors
| back in 1984. Because I grew up without it, it is
| impossible to imagine supporting it today."
|
| There's also the argument of "Now you have two byte
| values that cannot be allowed to appear in a record under
| any circumstances. (E.g., incoming data from uncontrolled
| sources MUST be sanitized to reject or replace those
| bytes.)" Unless you add an escaping mechanism, in which
| case the argument shifts to "Why switch from CSV/TSV if
| the alternative still needs an escaping mechanism?"
| zzo38computer wrote:
| One benefit of binary formats is not needing the
| escaping.
| wat10000 wrote:
| It's more like, "because the industry grew up without it,
| other approaches gained critical mass."
|
| Path dependence is a thing. Things that experience
| network effects don't get changed unless the alternative
| is far superior, and ASCII Delimited Text is not that
| superior.
|
| Ignoring that and pushing for it anyway will at most
| achieve an xkcd 927.
| mikepurvis wrote:
| As soon as you give those characters magic meanings then
| suddenly people will have reason to want to use them--
| it'll be a CSV containing localization strings for tooltips
| that contain that character and bam, we'll be back to
| escaping.
|
| Except the usages of that character will be rare and so
| potentially way more scary. At least with quotes and
| commas, the breakages are _everywhere_ so you confront them
| sooner rather than later.
| EGreg wrote:
| Can't there be some magic sequence (like two unescaped
| newlines) to start a new record?
| macintux wrote:
| You'll still find that sequence in data; it'll just be
| rare enough that it won't rear its ugly head until your
| solution has been in production for a while.
| mbreese wrote:
| And that's why I tend to use tab delimited files more...
| when viewed with invisible characters shown, it's pretty
| clear to read/write separate fields and have an easier to
| parse format.
|
| This, of course, assumes that your input doesn't include
| tabs or newlines... because then you're still stuck with
| the same problem, just with a different delimiter.
| zoover2020 wrote:
| Perhaps the popularity,or lack thereof? More often than not,
| the bad standard wins the long term market
| andrewflnr wrote:
| Can't type them on a keyboard I guess, or generally work with
| them in the usual text-oriented tools? Part of the appeal of
| CSV is you can just open it up in Notepad or something if you
| need to. Maybe that's more a critique of text tools than it
| is of ASCII record separator characters.
| jandrese wrote:
| If there were visible well known characters that could be
| printed for those and keys on a keyboard for inputting them
| we would probably have RSV files. Because they are buried
| down in the nonprintable section of the ASCII chart they are
| a pain for people to deal with. All it would have taken is
| one more key on the keyboard, maybe splitting the tab key in
| half.
| thesuitonym wrote:
| > If there were visible well known characters that could be
| printed...
|
| ...There would be datasets that include those characters,
| and so they wouldn't be as useful for record separators.
| Look into your heart and know it to be true.
| jandrese wrote:
| I wouldn't feel too bad about blindly scrubbing those
| characters out of inputs unlike commas, tabs, and quotes.
| sundarurfriend wrote:
| I remembered seeing a comment like this before, and...
|
| comment: https://news.ycombinator.com/item?id=26305052
|
| comment: https://news.ycombinator.com/item?id=39679662
|
| "ASCII Delimited Text - Not CSV or Tab Delimited Text" post
| [2014]: https://news.ycombinator.com/item?id=7474600
|
| same post [2024]:
| https://news.ycombinator.com/item?id=42100499
|
| comment: https://news.ycombinator.com/item?id=15440801
|
| (...and many more.) "This comes up every single time someone
| mentions CSV. Without fail." - top reply from burntsushi in
| that last link, and it remains as true today as in 2017 :D
|
| You're not wrong though, we just need some major text editor
| to get the ball rolling and start making some attempts to
| understand these characters, and the rest will follow suit.
| We're kinda stuck at a local optimum which is clearly not
| ideal but also not troublesome enough to easily drum up wide
| support for ADSV (ASCII Delimiter Separated Values).
| ryandrake wrote:
| Hahah, I came here to make the comment about ASCII's
| control characters, so I'm glad someone else beat me to it,
| and also that someone further pointed out that this topic
| comes up every time someone mentions CSV!
| scythe wrote:
| >we just need some major text editor to get the ball
| rolling and start making some attempts to understand these
| characters
|
| Many text editors offer extensions APIs, including Vim,
| Emacs, Notepad++. But the ideal behavior would be to auto-
| align record separators and treat unit separators as a
| special kind of newline. That would allow the file to
| actually look like a table within the text editor. Input
| record separator as shift+space and unit separator as
| shift+enter.
| hermitcrab wrote:
| I think it would be enough for:
|
| 1. the field separator to be shown as a special character
|
| 2. the row separator to be (optionally) be interpreted as
| a linefeed
|
| IIRC 1) is true for Notepad++, but not 2).
| orthoxerox wrote:
| But what if one of your columns contains arbitrary binary
| data?
| ndsipa_pomu wrote:
| You'd likely need to uuencode it or similar as CSV isn't
| designed for binary data.
| masklinn wrote:
| > I don't get how this is not widely used as standard.
|
| It requires bespoke tools for edition, and while CSV is
| absolute garbage it can be ingested and produced by most
| spreadsheet software, as well as databases.
| da_chicken wrote:
| Eh, all you're really saying is "I'm not using CSV. Instead I'm
| using my CSV." Except that's all that anybody does.
|
| CSV can just as easily support escaping as any other format,
| but there is no agreement for a CSV format.
|
| After all, a missed escape can just as easily destroy a JSON or
| XML structure. And parallel processing of _text_ is already a
| little sketchy simply because UTF-8 exists.
| fragmede wrote:
| I want to push Sqlite as a data interchange format! it has the
| benefit of being well defined, and can store binary data, like
| images for product pictures inside the database. not a good
| idea if you're trying to serve users behind a web app, but as
| interchange, better than a zip file with filenames that have to
| be "relinked".
| 0cf8612b2e1e wrote:
| One very minor problem is that you max out storing blobs of
| 2GB(? I think, maybe 4GB). Granted few will hit this, but
| this limit did kill one previous data transfer idea of mine.
| Someone1234 wrote:
| For context: I have a LOT of experience of interchange
| formats, like "full time job, every day, all day, hundreds of
| formats, for 20-years" experience.
|
| Based on that experience I have come to one key, but maybe,
| counter-intuitive truth about interchange formats:
|
| - Too much freedom is _bad_.
|
| Why? Generating interchange data is cheaper than consuming
| it, because the creator only needs to consider the stuff they
| want to include, whereas the consumer needs to consider every
| single possible edge case and or scenario the format itself
| can support.
|
| This is why XML is WAY more costly to ingest than CSV,
| because in XML someone is going to use: attributes, CDATA,
| namespaces, comments, different declaration, includes, et al.
| In CVS they're going to use rows, a format separator, and
| quotes (with or without escaping). That's it. That's all it
| supports.
|
| Sqlite as an interchange format is a HORRIFYING suggestion,
| because every single feature Sqlite supports may need to be
| supported by consumers. Even if you curtailed Sqlite's vast
| feature set, you've still created something vastly more
| expensive to consume than XML, which itself is obnoxious.
|
| My favorite interchange formats are, in order:
|
| - CVS, JSON (inc. NDJSON), YAML, XML, BSON (due to type
| system), MessagePack, Protobuf, [Giant Gap] Sqlite, Excel
| (xlsx, et al)
|
| More features mean more cost, more edge cases, more failures,
| more complex consumers. Keep in mind, this is ONLY about
| interchange formats between two parties, I have wildly
| different opinions about what I would use for my own
| application where I am only ever the creator/consumer, I
| actually love Sqlite for THAT.
| fragmede wrote:
| Interesting! I've dealt with file interchange between
| closed source (and a couple open source) programs, but that
| was a while ago. I've also had to deal with csvs and xslts
| between SaaS vendors for import export of customer's data.
| I've done a bunch of reverse engineering of proprietary
| formats so we could import the vendor's files, which had
| more information than they were willing to export in an
| interchange format. Sometimes they're encrypted and you
| have to break it.
|
| What you say is fair. Csv is underspecified though, there's
| no company called csv that's gonna sue for trademark
| enforcement, there's no official csv standard library that
| everyone uses. (They exist are some but there are so many
| naive importations because from first principles, because
| how hard could it be? output records and use a comma and
| newline (of which there are three possible options)).
|
| How often do you deal with multiple Csv files to represent
| multiple tables that are actually what's used by vendors
| internally, vs one giant flattened Csv with hundreds of
| columns and lots of empty cells? I don't have your level of
| experience with csvs, but I've dealt with a them being a
| mess, where the other side implement whatever they think is
| reasonable given the name "comma separated values".
|
| With sqlite, we're in the Internet age and so I presume
| this hypothetical developer would use the sqlite library
| and not implement their own library from scratch for
| funsies. This then leads to types, database normalization,
| multiple tables. I hear you that too many choices can bad,
| and xml is a great example of this, but sqlite isn't xml
| and isn't Csv.
|
| It's hard to have this discussion in the abstract so I'll
| be forthcoming about where I'm coming from, which is Csv
| import export between vendors for stores, think like
| Doordash to UberEATS. the biggest problem we have is images
| of the items, and how to deal with that. It's an ongoing
| issue how to get them, but the failure mode, which does
| happen, is that when moving vendor, they just have to redo
| a lot of work that they shouldn't have to.
|
| Ultimately the North Star I want to push towards is moving
| beyond csvs, because it'll let a people who currently have
| to hand edit the Csv so every row imports properly, not
| have to do that. They'd still exist, but instead have to
| deal with, well, what you see with XML files. which has its
| shortcomings, as you mention, but at least once how a
| vendor is using it is understood, individual records are
| generally understandable.
|
| I was moved so I don't deal with import export currently,
| but it's _because_ sqlite is so nice to work with on
| personal projects where it 's appropriate that I want to
| push the notion of moving to sqlite over csvs.
| nomel wrote:
| Oh, this is interesting. Are you tying different systems
| together? If so, do you use some preferred intermediate
| format? Do you have a giant library of * -> intermediate ->
| * converters that you sprinkle between everything? Or maybe
| the intermediate format is in memory?
|
| What about Parquet and the like?
| gopher_space wrote:
| Not the person you were replying to, but from my
| experience CSV is a good place to define data types
| coming into a system and a safe way to dump data _as long
| as I write everything down_.
|
| So I might do things like have every step in a pipeline
| begin development by reading from and writing to CSV.
| This helps with parallel dev work and debugging, and is
| easy to load into any intermediate format.
|
| > do you use some preferred intermediate format?
|
| This is usually dictated by speed vs money calculations,
| weird context issues, and familiarity. I think it's
| useful to look at both "why isn't this a file" and "why
| isn't this all in memory" perspectives.
| theLiminator wrote:
| For tabular/time-series greater than 100k rows I
| personally feel like parquet cannot be beat. It's self-
| describing, strongly-typed, relatively compact, supports
| a bunch of io/decode skipping, and is quite fast.
|
| Orc also looks good, but isn't well supported. I think
| parquet is optimal for now for most analytical use-cases
| that don't require human readability.
| Someone1234 wrote:
| It is an interchange format, so it is inter-system by
| virtue of that. If I am a self-creator/consumer the
| format I use can be literally anything even binary memory
| dumps.
| sadcodemonkey wrote:
| I love the wisdom in this comment!
| koolba wrote:
| JSON serialized without extra white space with one line per
| record is superior to CSV.
|
| If you want CSV-ish, enforce an array of strings for each
| record. Or go further with actual objects and non-string types.
|
| You can even jump to an arbitrary point and then seek till you
| see an actual new line as it's always a record boundary.
|
| It's not that CSV is an invalid format. It's that libraries and
| tools to parse CSV tend to suck. Whereas JSON is the lingua
| franca of data.
| juliansimioni wrote:
| What happens when you need to encode the newline character in
| your data? That makes splitting _either_ CSV or LDJSON files
| difficult.
| mananaysiempre wrote:
| When you need to encode the newline character in your data,
| you say \n in the JSON. Unlike (the RFC dialect of) CSV,
| JSON has an escape sequence denoting a newline and in fact
| requires its use. The only reason to introduce newlines
| into JSON data is prettyprinting.
| koolba wrote:
| The new line character in a JSON string would always be \n.
| The new line in the record itself as whitespace would not
| be acceptable as that breaks the one line record contract.
|
| Remember that this does not allow arbitrary representation
| of serialized JSON data. But it allows for any and all JSON
| data as you can always roundtrip valid JSON to a compact
| one line representation without extra whitespace.
| benwilber0 wrote:
| JSON is a textual encoding no different than CSV.
|
| It's just that people tend to use specialized tools for
| encoding and decoding it instead of like ",".join(row) and
| row.split(",")
|
| I have seen people try to build up JSON strings like that
| too, and then you have all the same problems.
|
| So there is no problem with CSV except that maybe it's too
| deceptively simple. We also see people trying to build things
| like URLs and query strings without using a proper library.
| int_19h wrote:
| The problem with CSV is that there's no clear standard, so
| even if you do reach for a library to parse it, that
| doesn't ensure compatibility.
| rcbdev wrote:
| Same for JSON though. What Python considers a valid JSON
| might not be that if you ask a Java library.
| hajile wrote:
| JSON has a clearly-defined standards: ISO/IEC 21778:2017,
| IETF RFC 7159, and ECMA-404. Additionally, Crockford has
| had a spec available on json.org since it's creation in
| 2001.
|
| Do you have any examples of Python, Java, or any of the
| other Tiobe top 40 languages breaking the JSON spec in
| their standard library?
|
| In contrast, for the few of those that have CSV
| libraries, how many of those libraries will simply fail
| to parse a large number of the .csv variations out there?
| whizzter wrote:
| Not to mention that stuff like Excel loves to export CSV
| files in "locale specific ways".
|
| Sometimes commas to delimiter, sometimes semicolons,
| floating point values might have dots or commas to
| separate fraction digits.
|
| Not to mention text encodings, Ascii, western european
| character sets, or maybe utf-8 or whatever...
|
| It's a bloody mess.
| dwattttt wrote:
| You need more than a standard; that standard has to be
| complete and unambiguous. What you're looking for is
| https://github.com/nst/JSONTestSuite
|
| EDIT: The readme's results are from 2016, but there's
| more recent results (last updated 5 years ago). Of the 54
| parsers /versions tested, 7 gave always the expected
| result per the spec (disregarding cases where the spec
| does not define a result).
| noitpmeder wrote:
| It falls down under very large integers -- think large
| valid uint64_t values.
| gopher_space wrote:
| There _is_ a clear standard and it 's usually written on
| an old Word '97 doc in a local file server. Using CSV
| means that _you_ are the compatibility layer, and this is
| useful if you need firm control or understanding of your
| data.
|
| If that sounds like a lot of edge-case work keep in mind
| that people have been doing this for more than half a
| century. Lots of examples and notes you can steal.
| gthompson512 wrote:
| > It's just that people tend to use specialized tools for
| encoding and decoding it instead of like ",".join(row) and
| row.split(",")
|
| You really super can't just split on commas for csv. You
| need to handle the string encodings since records can have
| commas occur in a string, and you need to handle quoting
| since you need to know when a string ends and that string
| may have internal quote characters. For either format
| unless you know your data super well you need to use a
| library.
| benwilber0 wrote:
| Right, obviously.
| packetlost wrote:
| Eh, it really isn't. The format does not lend itself to
| tabular data, instead the most natural way of representing
| data involves duplicating the keys N times for each record.
| koolba wrote:
| You can easily represent it as an array:
| ["foo","bar",123]
|
| That's as tabular as CSV but you now have optional types.
| You can even have lists of lists. Lists of objects. Lists
| of lists of objects...
| packetlost wrote:
| You're missing my point: basically nothing spits out data
| in that format because it's not ergonomic to do so. JSON
| is designed to represent object hierarchies, not tabular
| data.
| koolba wrote:
| JSON was designed to represent any data. There's plenty
| of systems that spit out data in exact that format
| because it's the natural way to represent tabular data
| using JSON serialization. And clearly if you're the one
| building the system you can choose to use it.
| hajile wrote:
| CSV is lists of lists of fixed length.
|
| JSON is lists of lists of any length and groups of
| key/value pairs (basically lisp S-expressions with lots
| of unnecessary syntax). This makes it a superset of CSV's
| capabilities.
|
| JSON fundamentally IS made to represent tabular data, but
| it's made to represent key-value groups too.
|
| Why make it able to represent tabular data if that's not
| an intended use?
| meepmorp wrote:
| > CSV is lists of lists of fixed length.
|
| I'd definitely put that in my list of falsehoods
| programmers believe about CSV files.
| timacles wrote:
| CSV is a text file that might have commas in it
| freehorse wrote:
| The fact that json can represent a superset of tabular
| data structures that csv is specifically designed to
| represent can be rephrased into that csv is more
| specialised than json in representing tabular data. The
| fact that json can also represent tabular data does not
| mean it is a better or more efficient way to represent
| that data instead of a format like csv.
|
| In the same way, there are hierarchically structured
| datasets that can be represented by both json in
| hierarchical form and csv in tabular form by repeating
| certain variables, but if using csv would require
| repeating them too many times, it would be a bad idea to
| choose that instead of json. The fact that you can do sth
| does not always make it a good idea to do it. The
| question imo is about which way would be more natural,
| easy or efficient.
| kccqzy wrote:
| JSON is designed to represent JavaScript objects with
| literal notation. Guess what, an array of strings or an
| array of numbers or even an array of mixed strings and
| numbers is a commonly encountered format in JavaScript.
| simonw wrote:
| Right - the JSON-newline equivalent of CSV can look like
| this: ["id", "species", "nickname"]
| [1, "Chicken", "Chunky cheesecakes"] [2, "Dog",
| "Wagging wonders"] [3, "Bunny", "Hopping heroes"]
| [4, "Bat", "Soaring shadows"]
| nomel wrote:
| Remove the [] characters and you've invented CSV with
| excel style quoting.
| freehorse wrote:
| This misses the point of standardization imo because it's
| not possible to know a priori that the first line
| represents the variable names, that all the rows are
| supposed to have the same number of elements and in
| general that this is supposed to represent a table. An
| arbitrary parser or person wouldn't know to guess since
| it's not standard or expected. Of course it would be
| parsed fine but the default result would be a kind of
| structure or multi-array rather than tabular.
| lgas wrote:
| Typing isn't optional in JSON, every value has a concrete
| type, always.
| gghffguhvc wrote:
| [ ["header1","header2"], ["1.1", ""],
| [7.4, "2022-01-04"] ]
| derriz wrote:
| > It's that libraries and tools to parse CSV tend to suck.
| Whereas JSON is the lingua franca of data.
|
| This isn't the case. An incredible amount of effort and
| ingenuity has gone into CSV parsing because of its ubiquity.
| Despite the lack of any sort of specification, it's easily
| the most widely supported data format in existence in terms
| of tools and language support.
| hajile wrote:
| Can you point me to a language with any significant number
| of users that does NOT have a JSON library?
|
| I went looking at some of the more niche languages like
| Prolog, COBOL, RPG, APL, Eiffel, Maple, MATLAB, tcl, and a
| few others. All of these and more had JSON libraries (most
| had one baked into the standard library).
|
| The exceptions I found (though I didn't look too far) were:
| Bash (use jq with it), J (an APL variant), Scratch (not
| exposed to users, but scratch code itself is encoded in
| JSON), and Forth (I could find implementations, but it's
| very hard to pin down forth dialects).
| derriz wrote:
| I made no claim about JSON libraries. I contested the
| claim that "CSV libraries and tools suck". They do not.
| jstanley wrote:
| You claimed that CSV is "easily the most widely supported
| data format in existence in terms of tools and language
| support", which is a claim that CSV is better supported
| than JSON, which is a claim that JSON support is lacking.
| shawabawa3 wrote:
| Can you import .jsonl files into Google sheets or excel
| natively?
| freehorse wrote:
| Importing csvs in excel can be a huge pain due to how
| excel handles localisation. It can basically alter your
| data if you are not mindful about that, and I have seen
| it happening too many times.
| FridgeSeal wrote:
| CSV tooling has had to invest enormous amounts of effort
| to make a fragile, under-specified format half-useful. I
| would call it ubiquitous, I would call the tooling that
| we've built around it "impressive" but I would by no
| means call any of it "good".
|
| I do not miss dealing with csv files in the slightest.
| dylan604 wrote:
| Microsoft Windows has had to invest enormous amounts...
|
| Apple macOS has had to invest enormous amounts...
|
| Pick your distro of Linux has had to invest enormous
| amounts...
|
| None of them a perfect and any number of valid complaints
| can be said about any of them. None of the complaints
| make any of the things useless. Everyone has workarounds.
|
| Hell, JSON has had to invest enormous amounts of
| effort...
| otikik wrote:
| Meanwhile, Excel exports to CSV as "semicolon separated
| values" depending on your OS locale
| matthewmacleod wrote:
| Albeit for fairly justifiable reasons
| notpushkin wrote:
| Justifiable how?
| LoganDark wrote:
| Decimal separators being commas in some locales?
| ideamotor wrote:
| Commas are commonly used in text, too.
| otikik wrote:
| They could have just ignored the locale altogether
| though. Put dots on the numbers when using csv, and
| assume it has dots when importing
| kentm wrote:
| I've found that the number of parsers that don't handle
| multiline records is pretty high though.
| klysm wrote:
| You serialize the keys on every row which is a bit
| inefficient but it's a text format anyway
| taeric wrote:
| Reminds me of a fatal flaw of yaml. Turns out truncating a yaml
| file doesn't make it invalid. Which can lead to some rather
| non-obvious failures.
| nextts wrote:
| What is the failure mode where a yaml file gets truncated?
| They are normally config files in Git. Or uploaded to S3 or
| Kubernetes etc.
|
| CSV has the same failure mode. As does HTML. (But not XML)
| LPisGood wrote:
| I always treat CSVs as comma separated values with new line
| delimiters. If it's a new line, it's a new row.
| criddell wrote:
| Do you ever have CSV data that has newlines within a string?
| thesuitonym wrote:
| I don't. If I ever have a dataset that requires newlines in
| a string, I use another method to store it.
|
| I don't know why so many people think every solution needs
| to to be a perfect fit for every problem in order to be
| viable. CSV is good _at certain things_ , so use it for
| those things! And for anything it's not good at, use
| something else!
| criddell wrote:
| > use something else
|
| You don't always get to pick the format in which data is
| provided to you.
| thesuitonym wrote:
| True, but in that case I'm not the one choosing how to
| store it, until I ingest the data, and then I will store
| it in whatever format makes sense to me.
| kittoes wrote:
| I don't think we do? It's more that a bunch of companies
| already have their data in CSV format and aren't willing
| to invest any effort in moving to a new format. Doesn't
| matter how much one extolls all the benefits, they know
| right? They're paying someone else to deal with it.
| LPisGood wrote:
| No - that's what I'm trying to say. If I have newlines I
| use something else.
| 0x073 wrote:
| Wouldn't work if csv is used as a exchange format with
| external companies.
| LPisGood wrote:
| Of course. I'm not saying I roll my own parser for every
| project that uses a CSV file, I'm just describing my
| criteria for using CSV vs some other format when I have
| the option.
| 1vuio0pswjnm7 wrote:
| https://www.ietf.org/rfc/rfc4180.txt
| solidsnack9000 wrote:
| Tab-Separated Value, as implemented by many databases, solves
| these problems, because tab, newline and other control
| characters are escaped. For example, the default text
| serialization format of Postgres (`COPY <table> TO '<file>'`
| without any options) is this way.
| Yomguithereal wrote:
| A good way to parallelize CSV processing is to split datasets
| into multiple files, kinda like manual sharding. xan has a
| parallel command able to perform a wide variety of map-reduce
| tasks on splitted files.
|
| https://github.com/medialab/xan
| dietr1ch wrote:
| I don't understand why CSV became a thing when TSV, or a format
| using the nowadays weird ASCII control characters like
| start/end of text, start of heading, horizontal/vertical tab,
| file/group/record/unit separator.
|
| It seems many possible designs would've avoided the quoting
| chaos and made parsing sort of trivial.
| polyrand wrote:
| As someone who likes modern formats like parquet, when in doubt,
| I end up using CSV or JSONL (newline-delimited JSON). Mainly
| because they are plain-text (fast to find things with just
| `grep`) and can be streamed.
|
| Most features listed in the document are also shared by JSONL,
| which is my favourite format. It compresses really well with gzip
| or zstd. Compression removes some plain-text advantages, but
| ripgrep can search compressed files too. Otherwise, you can:
| zcat data.jsonl.gz | grep ...
|
| Another advantage of JSONL is that it's easier to chunk into
| smaller files.
| sitkack wrote:
| I switched to JSONL over a decade ago and I would recommend
| everyone else to also have switched then.
|
| This whole thread is an uninformed rehash of bad ideas.
| theLiminator wrote:
| I think that might make sense ingest side, but that's very
| expensive to deal with if you're doing anything remotely
| large.
|
| I think sinking into something like delta-lake or iceberg
| probably makes sense at scale.
|
| But yeah, I definitely agree that CSV is not great.
| jszymborski wrote:
| > 4. CSV is streamable
|
| This is what keeps me coming back.
| deathanatos wrote:
| ...ndjson is streamable, too...
| jszymborski wrote:
| I like ndjson and jsonl just fine, but unless I need a more
| complicated structure, it's not worth the extra hassle of
| parsing JSON.
| lxe wrote:
| TSV > CSV
|
| Way easier to parse
| emmelaich wrote:
| pipe (|) separated or gtfo!
|
| sqlite3 gets it right.
| brazzy wrote:
| Funny how the "specification holds in a tweet" yet manages to
| miss at least three things: 1) character encoding, 2) BOM or not,
| 3) header or no header.
| nly wrote:
| Always UTF-8. Never a BOM. Always a header
| brazzy wrote:
| Great if you're the one producing the CSV yourself.
|
| But if you're ingesting data from other organizations, they
| _will_ , at one time or another, fuck up every single one of
| those (as well as the ones mentioned in TFA), no matter how
| clearly you specify them.
| circadian wrote:
| Kudos for writing this, it's always worth flagging up the utility
| of a format that just is what it is, for the benefit of all.
| Commas can also create fun ambiguity, as that last sentence
| demonstrates. :P
|
| CSV is lovely. It isn't trying to be cool or legendary. It works
| for the reasons the author proposes, but isn't trying to go
| further.
|
| I work in a work of VERY low power devices and CSV sometimes is
| all you need for a good time.
|
| If it doesn't need to be complicated, it shouldn't be. There are
| always times when I think to myself CSV fits and that is what
| makes it a legend. Are those times when I want to parallelise or
| deal with gigs of data in one sitting. Nope. There are more
| complex formats for that. CSV has a place in my heart too.
|
| Thanks for reminding me of the beauty of this legendary format...
| :)
| lyu07282 wrote:
| Because if there is anything we love in data exchange formats
| its ambiguity.
| mccanne wrote:
| Relevant discussion from a few years back
|
| https://news.ycombinator.com/item?id=28221654
| Maro wrote:
| I hate CSV (but not as much as XML).
|
| Most reasonably large CSV files will have issues parsing on
| another system.
| lyu07282 wrote:
| It makes me a bit worried to read this thread, I would've
| thought its pretty common knowledge why CSV is horrible and
| widely agreed upon. I also have hard time taking anybody
| seriously who uses "specification" and "CSV" in the same
| sentence unironically.
|
| I suspect its 1) people who worked with legacy systems AND
| LIKED IT, or 2) people who never worked with legacy systems
| before and need to rediscover painful old lessons for
| themselves.
|
| It feels like trying to convince someone, why its a bad idea to
| store the year as a CHAR(2) in 1999, unsuccessfully.
| TrackerFF wrote:
| Excel hates CSV only if you don't use the "From text / csv"
| function (under the data tab).
|
| For whatever reason, it flawlessly manages to import most CSV
| data using that functionality. It is the only way I can reliably
| import data to excel with datestamps / formats.
|
| Just drag/dropping a CSV file onto a spreadsheet, or "open with
| excel" sucks.
| tacker2000 wrote:
| Yea they seem to have added this about a year ago and it works
| pretty well, to be fair.
|
| Now if they would just also allow pasting CSV data as "source"
| it would be great.
| tgtweak wrote:
| It's a carry over from powerbi actually, separate function
| entirely.
| mitchpatin wrote:
| CSV still quietly powers the majority of the world's "data
| plumbing."
|
| At any medium+ sized company, you'll find huge amounts of CSVs
| being passed around, either stitched into ETL pipelines or sent
| manually between teams/departments.
|
| It's just so damn adaptable and easy to understand.
| deathanatos wrote:
| > _It 's just so damn adaptable_
|
| Like a rapidly mutating virus, yes.
|
| > _and easy to understand._
|
| Gotta disagree there.
|
| For example, one of the CSVs my company shovels around is our
| Azure billing data. There are several columns that I just have
| absolutely no idea what the data in them is. There are several
| columns we discovered are essentially nullable1 The Hard Way
| when we got a bill for which, e.g., included a charge that I
| guess Azure doesn't know what day that charge occurred on? (Or
| almost anything _else_ about it.)
|
| (If this format is documented anywhere, well, I haven't found
| the docs.)
|
| Values like "1/1/25" in a "date" column. I mean, I did say it
| was an Azure-generated CSV, so obviously the bar wasn't exactly
| high, but then _it never is_ , because anyone wanting to build
| something with some modicum of reliability, or discoverability,
| is sending data in some higher-level format, like JSON or
| Protobuf or almost literally anything but CSV.
|
| If I can never see the format "JSON-in-CSV-(but-we-fucked-up-
| the-CSV)" ever again, that would spark joy.
|
| (1after parsing, as CSV obviously lacks "null"; usually, "" is
| a serialized null.)
| testudovictoria wrote:
| Insurance. One of the core pillars of insurance tech is the CSV
| format. You'll never escape it.
| hermitcrab wrote:
| >You'll never escape it.
|
| I see what you did there.
| primitivesuave wrote:
| One thing that has changed the game with how I work with CSVs is
| ClickHouse. It is trivially easy to run a local database, import
| CSV files into a table, and run blazing-fast queries on it. If
| you leave the data there, ClickHouse will gradually optimize the
| compression. It's pretty magical stuff if you work in data
| science.
| tgtweak wrote:
| I feel the same way about elastic.
|
| That being said I noticed .parquet as an export format option
| on Shopify recently and an hopeful more providers offer the
| choice.
| emmelaich wrote:
| Simon W's https://datasette.io/ is also excellent.
| primitivesuave wrote:
| Datasette is a wonderful tool that I've used before, and I
| have the highest admiration for its creator, but the
| underlying Sqlite3 database doesn't handle large datasets
| (i.e. hundreds of millions of rows) nearly as well as
| ClickHouse does.
|
| It's worth noting that I only ran into this limitation when
| working with huge federal campaign finance datasets [1] and
| trying to do some compute-intensive querying. For 99% of use
| cases, datasette is a similarly magical piece of software for
| quickly exploring some CSV files.
|
| 1. https://www.fec.gov/data/browse-data/?tab=bulk-data
| inglor_cz wrote:
| "the controversial ex-post RFC 4180"
|
| I looked at the RFC. What is controversial about it?
| tgtweak wrote:
| You mean aside from the fact it's ex-post ...
| inglor_cz wrote:
| Doesn't it make sense to have a common document in the usual
| format (RFC) which every newbie can consult when in doubt?
|
| I much prefer that to any sort of "common institutional
| memory" that is nevertheless only talked about on random
| forums. People die, other people enter the field... hello
| subtle incompatibilities.
| sakjur wrote:
| Look at how it handles escaping of special characters and
| particularly new lines (RFC 4180 doesn't guarantee that a new
| line is a new record) and how it's written in 2005 yet still
| doesn't handle unicode other than via a comment about "other
| character sets".
| inglor_cz wrote:
| "how it's written in 2005 yet still doesn't handle unicode
| other than via a throwaway comment about "other character
| sets""
|
| Yeah, you are spot on with this one (cries in Czech, which
| used to be encoded in several various ways).
| Someone1234 wrote:
| > RFC 4180 doesn't guarantee that a new line is a new record
|
| Correctly. A good parser should step through the line one
| column at a time, and shouldn't even consider newlines that
| are quoted.
|
| If you're naively splitting the entire file via newline, that
| isn't 4180's fault, that is your fault for not following the
| standard or industry norms.
|
| I'll happily concede the UNICODE point however; but I don't
| know if that makes it controversial.
| slg wrote:
| >This is so simple you might even invent it yourself without
| knowing it already exists while learning how to program.
|
| This is a double-edged sword. The "you might even event it
| yourself" simplicity means that in practice lots of different
| people do end up just inventing their own version rather than
| standardizing to RFC-4180 or whatever when it comes to "quote
| values containing commas", values containing quotes, values
| containing newlines, etc. And the simplicity means these type of
| non-standard implementations can go completely undetectable until
| a problematic value happens to be used. Sometimes added
| complexity that forces paying more attention to standards and
| quickly surfaces a diversion from those standards is helpful.
| owlstuffing wrote:
| CSV is everywhere. I use manifold-csv[1] it's amazing.
|
| 1. https://github.com/manifold-
| systems/manifold/tree/master/man...
| hajile wrote:
| The argument against JSON isn't very compelling. Adding a name to
| every field as they do in their strawman example isn't necessary.
|
| Compare this CSV field1,field2,fieldN
| "value (0,0)","value (0,1)","value (0,n)" "value
| (1,0)","value (1,1)","value (1,n)" "value (2,0)","value
| (2,1)","value (2,n)"
|
| To the directly-equivalent JSON
| [["field1","field2","fieldN"], ["value (0,0)","value
| (0,1)","value (0,n)"], ["value (1,0)","value
| (1,1)","value (1,n)"], ["value (2,0)","value
| (2,1)","value (2,n)"]]
|
| The JSON version is only marginally bigger (just a few brackets),
| but those brackets represent the ability to be either simple or
| complex. This matters because you wind up with terrible ad-hoc
| nesting in CSV ranging from entries using query string syntax to
| some entirely custom arrangement.
| person,val2,val3,valN
| fname=john&lname=doe&age=55&children=[jill|jim|joey],v2,v3,vN
|
| And in these cases, JSON's objects are WAY better.
|
| Because CSV is so simple, it's common for them to avoid using a
| parsing/encoding library. Over the years, I've run into this
| particular kind of issue a bunch. //outputs
| `val1,val2,unexpected,comma,valN` which has one too many items
| ["val1", "val2", "unexpected,comma", "valN"].join(',')
|
| JSON parsers will not only output the expected values every time,
| but your language likely uses one of the super-efficient SIMD-
| based parsers under the surface (probably faster than what you
| are doing with your custom CSV parser).
|
| Another point is standardization. Does that .csv file use commas,
| spaces, semicolons, pipes, etc? Does it use CR,LF, or CRLF? Does
| it allow escaping quotations? Does it allow quotations to escape
| commas? Is it utf-8, UCS-2, or something different? JSON doesn't
| have these issues because these are all laid out in the spec.
|
| JSON is typed. Sure, it's not a LOT of types, but 6 types is
| better than none.
|
| While JSON isn't perfect (I'd love to see an official updated
| spec with some additional features), it's generally better than
| CSV in my experience.
| croes wrote:
| > Because CSV is so simple, it's common for them to avoid using
| a parsing/encoding library.
|
| A but unfair to compare CSV without parser library to JSON with
| library.
| hajile wrote:
| Essentially nobody uses JSON without a library, but tons of
| people (maybe even most people) use CSV without a library.
|
| Part of the problem here is standards. There's a TON of
| encoding variations all using the same .csv extension. Making
| a library that can accurately detect exactly which one is
| correct is a big problem once you leave the handful of most
| common variants. If you are doing subfield encoding, you are
| almost certainly on your own with decoding at least part of
| your system.
|
| JSON has just one standard and everyone adheres to that
| standard which makes fast libraries possible.
| pphysch wrote:
| The flexibility of JSON is a downside when you just want to
| stream large volumes of row-oriented tabular data
| hajile wrote:
| If you want to stream large volumes of row-oriented data, you
| aren't reading yourself and you should be using a binary
| format which is going to be significantly smaller (especially
| for numeric data).
| munchler wrote:
| > the directly-equivalent JSON
| [["field1","field2","fieldN"], ["value (0,0)","value
| (0,1)","value (0,n)"], ["value (1,0)","value
| (1,1)","value (1,n)"], ["value (2,0)","value
| (2,1)","value (2,n)"]]
|
| I think it's a big stretch to use that JSON for comparison. In
| practice, one is much more likely to see this:
| [ { "field1": "value (0,0)",
| "field2": "value (0,1)", "fieldN": "value (0,n)"
| }, { "field1": "value (1,0)",
| "field2": "value (1,1)", "fieldN": "value (1,n)"
| }, { "field1": "value (2,0)",
| "field2": "value (2,1)", "fieldN": "value (2,n)"
| } ]
| boricj wrote:
| I've recently written a library at work to run visitors on data
| models bound to data sets. One of these visitors is a CSV
| serializer that dumps a collection as a CSV document.
|
| I've just checked and strings are escaped using the same
| mechanism for JSON, with backslashes. I should've double-checked
| against RFC 4180, but thankfully that mechanism isn't currently
| triggered anywhere for CSV (it's used for log exportation and no
| data for these triggers that code path). I've also checked the
| code from other teams and it's just handwritten C++ stream
| statements inside a loop that doesn't even try to escape data. It
| also happens to be fine for the same reason (log exportation).
|
| I've also written serializers for JSON, BSON and YAML and they
| actually output spec-compliant documents, because there's only
| one spec to pay attention to. CSV isn't a specification, it's a
| bunch of loosely-related formats that look similar at a glance.
| There's a reason why fleshed-out CSV parsers usually have a ton
| of knobs to deal with all the dialects out there (and I've almost
| added my own by accident), that's simply not a thing for properly
| specified file formats.
| nly wrote:
| The joy of CSV is everyone knows roughly what you mean and the
| details can communicated succintly.
|
| The python3 csv module basically does the job.
| meemo wrote:
| Quick question while we're on the topic of CSV files: is there a
| command-line tool you'd recommend for handling CSV files that are
| malformed, corrupted, or use unexpected encodings?
|
| My experience with CSVs is mostly limited to personal projects,
| and I generally find the format very convenient. That said, I
| occasionally (about once a year) run into issues that are tricky
| to resolve.
| williamcotton wrote:
| Essential CSV shell tools:
|
| csvtk: https://bioinf.shenwei.me/csvtk/
|
| gawk: https://www.gnu.org/software/gawk/manual/html_node/Comma-
| Sep...
|
| awk: https://github.com/onetrueawk/awk?tab=readme-ov-file#csv
| dbro wrote:
| Forgive me for promoting this that I wrote:
|
| csvquote: https://github.com/dbro/csvquote
|
| Especially for use with existing shell text processing tools,
| eg. cut, sort, wc, etc.
| saint_yossarian wrote:
| Also VisiData is an excellent TUI spreadsheet.
| Yomguithereal wrote:
| I would add xan to this list: https://github.com/medialab/xan
|
| But of course, I am partial ;)
| evnp wrote:
| Anyone with a love of CSV hasn't been asked to deal with CSV-
| injection prevention in an enterprise setting, without breaking
| various customer data formats.
|
| There's a dearth of good resources about this around the web,
| this is the best I've come across:
| https://georgemauer.net/2017/10/07/csv-injection.html
| Suppafly wrote:
| That mostly breaks down to "excel is intentionally stupid with
| csv files if you don't use the import function to open them"
| along with the normal "don't trust customer input without
| stripping or escaping it" concerns you'd have with any input.
| evnp wrote:
| That was my initial reaction as well - it's a vulnerability
| in MS software, not ours, not our problem. Unfortunately,
| reality quickly came to bear: our customers and employees
| ubiquitously use excel and other similar spreadsheet
| software, which exposes us and them to risk regardless where
| the issue lies. We're inherently vulnerable because of the
| environment we're operating in, by using CSV.
|
| "don't trust customer input without stripping or escaping it"
| feels obvious, but I don't think it stands up to scrutiny.
| What exactly do you strip or escape when you're trying to
| prevent an unknown multitude of legacy spreadsheet clients
| that you don't control from mishandling data in an unknown
| variety of ways? How do you know you're not disrupting
| downstream customer data flows with your escaping? The core
| issue, as I understand it, stems from possible unintended
| formula execution - which can be prevented by prefixing
| certain cells with a space or some invisible character
| (mentioned in the linked post above). This _does_ modify
| customer data, but hopefully in a way that unobtrusive enough
| to be acceptable. All in all, it seems to be a problem
| without a perfect solution.
| togakangaroo wrote:
| Hey, I'm the author of the linked article, cool to see this
| is still getting passed around.
|
| Definitely agree there's no perfect solution. There's some
| escaping that seems to work ok, but that's going to break
| CSV-imports.
|
| An imperfect solutions is that applications should be
| designed with task-driven UIs so that they know the
| intended purpose of a CSV export and can make the decision
| to escape/not escape then. Libraries can help drive this by
| designing their interfaces in a similar manner. Something
| like `export_csv_for_eventual_import()`,
| `export_csv_for_spreadsheet_viewing()`.
|
| Another imperfect solution would be to ... ugh...generate
| exports in Excel format rather than CSV. I know, I know,
| but it _does_ solve the problem.
|
| Or we could just get everyone in the world to switch to
| emacs csv-mode as a csv viewer. I'm down with that as well.
| evnp wrote:
| Appreciate your work! Your piece was pivotal in changing
| my mind about whether this should be considered in our
| purview to address.
|
| The intention-based philosophy of all this makes a lot of
| sense, was eye opening, and I agree it should be the
| first approach. Unfortunately after considering our use
| cases, we quickly realized that we'd have no way of
| knowing how customers intend to use the csv exports
| they've requested - we've talked to some of them and it's
| a mix. We could approach things case by case but we
| really just want a setup which works well 99% of the time
| and mitigates known risk. We settled on the prefixing
| approach and have yet to receive any complaints about it,
| specifically using a space character with the mind that
| something unobtrusive (eg. easily strippable) but also
| visible, would be best - to avoid quirks stemming from
| something completely hidden.
|
| Thank again for your writing and thoughts, like I said
| above I haven't found much else of quality on the topic.
| aorth wrote:
| Someone filed a bug report on a project I work on, saying
| that it was a security vulnerability that we don't prefix
| cell values with a single quote (') when the cell content
| contains certain values like an equal sign (=). They said
| this can cause Excel to evaluate the content and potentially
| run unsafe code.
|
| I responded that this was Excel's problem, not ours, and that
| nobody would assign a CVE to our product for such a
| "vulnerability". How naive I was! They forwarded me several
| such CVEs assigned to products that create CSVs that are
| "unsafe" for Excel.
|
| Terrible precedent. Ridiculous security theater.
| evnp wrote:
| There are a lot of these sorts of bug reports running
| around, to the point that Google's bug bounty program has
| classified them as invalid:
| https://bughunters.google.com/learn/invalid-
| reports/google-p...
|
| I agree with the characterization ("security theater") of
| these bug reports. The problem is that the intentions of
| these reports don't make the potential risk less real,
| depending on the setting, and I worry that the "You're just
| looking for attention" reaction (a very fair one!) leads to
| a concerning downplaying of this issue across the web.
|
| As a library author, I agree this very well may not be
| something that needs to be addressed. But as someone
| working in a company responsible for customers, employees,
| and their sensitive information, disregarding this issue
| disregards the reality of the tools these people will
| invariably use, downstream of software we _are_ responsible
| for. Aiming to make this downstream activity as safe as
| possible seems like a worthy goal.
| notatallshaw wrote:
| What isn't fun about CSV is quickly written parsers and
| serializers repeatedly making the common mistake of not handling,
| or badly handling, quoting.
|
| For a long time I was very wary of CSV until I learnt Python and
| started using it's excellent csv standard library module.
| goatlover wrote:
| Why not Pandas, since you're working with tabular data anyway?
| codeulike wrote:
| Thats true, in recent years its been less of a disaster with
| lots of good csv libraries for various languages. In the 90s
| csv was a constant footgun, perhaps thats why they went crazy
| and came up with XML
| Macha wrote:
| Even widely used libraries that you might expect get it right,
| don't. (Like Spark, which uses Java style backslash escaping)
| uoaei wrote:
| I think I understand the point being made, but all this reliance
| on _text-based data_ means we require proper agreement on text
| _encodings_ , etc. I don't think it's very useful for number-
| based data anyway, it's a massively bloated way to store float32s
| for instance and usually developers truncate the data losing
| about half of the precision in the process.
|
| For numerical data, nothing beats packing floats into blobs.
| zzo38computer wrote:
| I think binary formats have many advantages. Not only for
| numbers but other data as well, including data that contains
| text (to avoid needing escaping, etc; and to declare what
| character sets are being used if that is necessary), and other
| structures. (For some of my stuff I use a variant of DER, which
| adds a few new types such as key/value list type.)
| jacobsenscott wrote:
| In abstract CSV is great. In reality, it is a nightmare not
| because of CSV, but because of all the legacy tools that product
| it in slightly different ways (different character encodings
| mostly - excel still produces some variant of latin1, some tools
| drop a BOM in your UTF8, etc).
|
| Unless you control the producer of the data you are stuck trying
| to infer the character encoding and transcoding to your
| destination, and there's no foolproof way of doing that.
| liotier wrote:
| CSV is the bane of my existence. There is no reason to use it
| outside of legacy use-cases, when so many alternatives are not so
| brittle that they require endless defensive hacks to avoid erring
| as soon as exposed to the universe. CSV must die.
| Someone1234 wrote:
| CVS isn't brittle, and I'm not sure what "hacks" you're
| referring to. If you or your parser just follow RFC4180
| (particularly quote every field, and double quoting to cancel-
| quote), that will get you 90%+ compatibility.
| liotier wrote:
| /me laughs in legacese
|
| RFC4180 is a late attempt at CSV standardization, merely
| codifying a bunch of sane practices. It also provides a nice
| specification for generating CSV. But anyone taking care to
| code from a specification might as well use a proper file
| format.
|
| The real specification for CSV is as follows: "Valid CSV is
| whatever is designated as CSV by its emitter". I wish I was
| joking.
|
| There is literally an infinity of ways CSV can be broken. The
| developer will bump his head on each as he encounters them,
| and add a specific fix. After a while, his code will be
| robust against the local strains of CSV... Until the next
| mutation is encountered after acquiring yet another company
| with a bunch of ERP way past their last extended maintainance
| era, a history of local adaptations and CSV as a message bus.
| bb01100100 wrote:
| Surely you've come across situations where line number
| 10,000,021 of a 60m line CSV fails to parse because there
| aren't enough fields in that line of the file...? The issue
| is that you can't definitively know which of the 50 fields is
| missing, so you have to fail the line or worse the file.
|
| In my experience (perhaps more niche than yours since you
| mentioned it has been your day job), the lack of fall back
| options makes for brittle integrations. Failing entire files
| due to a borked row can be expensive in terms of time.
|
| Having to ingest large CSV files from legacy systems has made
| me rethink the value of XML, lol. Types and schemas add
| complexity for sure, but you get options for dealing with
| variances in structure and content.
| Someone1234 wrote:
| That is a problem, but it is also a problem with XML.
| Parsing the XML file to discover e.g. unmatching tags is
| far more CPU and memory expensive than correctly passing a
| CVS.
|
| In both cases you'd fail the entire file rather than
| partial recovery.
| masfuerte wrote:
| The fact that you can parse CSV in reverse is quite cool, but you
| can't necessarily use it for crash recovery (as suggested)
| because you can't be sure that the last thing written was a
| complete record.
| nly wrote:
| Last field rather than last record. The first row will give you
| column count.
| masfuerte wrote:
| The column count doesn't help because you don't know where
| the last record starts because you don't know whether you're
| in a string or not. Unless you scan the entire file from the
| beginning, which defeats the object.
| nly wrote:
| Unless newlines in strings are escaped
| 999900000999 wrote:
| The best part about csv, anyone can write a parser in 30 minutes
| meaning that I can take data from the early '90s and import it
| into a modern web service.
|
| The worst part about CSV, anyone can ride a parser in about 30
| minutes, meaning that it's very easy to get incorrect
| implementations, incorrect data, and other strange undefined
| behaviors. But to be clear json, and yaml also have issues with
| everyone trying to reinvent the wheel constantly. XML is rather
| ugly, but it seems to be the most resilient.
| Xelbair wrote:
| until you find someone abusing XSD schemas, or someone
| designing a "dynamically typed" XML... or sneaks in extra data
| in comments - happened to me way often than it should.
| 999900000999 wrote:
| My condolences. Any open standard runs the risk of this
| happening. It's not a problem I think we'll ever solve.
| MathMonkeyMan wrote:
| You know what grinds my gears about using XSD for message
| definitions? Namespaces. Namespaces are a good idea and were
| done well in XML, as far as I can see, but with XSD you run
| into this [problem][1]:
|
| Namespaces are used to qualify tags and attributes in XML
| elements. But they're also used by XSD to qualify the names
| of types defined in the schema. A sequence element's type is
| indicated by the value of its "type" attribute. The attribute
| value is a string that is the namespace-qualified name of the
| type.
|
| So, if you want to change the alias of an XML namespace in an
| XSD schema, you can't just use your XML library's facilities
| for namespace management. You also have to go find the "type"
| attributes (but not _all_ of the "type" attributes), parse
| their values, and do the corresponding alias change in the
| type name.
|
| Don't use a string for a thing that is not a string! I guess
| in XML attributes you have no choice. XAML improved on the
| situation a bit.
|
| [1]: https://github.com/dgoffredo/stag/tree/master/src/stag/x
| sd-u...
| wglb wrote:
| How much easier would all of this be if whoever did CSV first had
| done the equivalent of "man ascii". There are all these wonderful
| codes there like FS, GS, RS, US that could have avoided all the
| hassle that quoting has brought generations of programmers and
| data users.
| nelblu wrote:
| Also CSV can be queried :
| https://til.simonwillison.net/sqlite/one-line-csv-operations
| johnea wrote:
| I have to agree.
|
| It was pretty straightforward (although tedious) to write custom
| CSV data exports in embedded C, with ZERO dependencies.
|
| I know, I know, only old boomers care about removing pip from
| their code dev process, but, I'm an old boomer, so it was a great
| feature for me.
|
| Straight out of libc I was able to dump data in real-time, that
| everyone on the latest malware OSes was able to import and
| analyze.
|
| CSV is awesome!
| baumschubser wrote:
| Just last week I was bitten by a customer's CSV that failed due
| to Windows' invisible BOM character that sometimes occurs at the
| beginning of unicode text files. The first column's title is not
| ,,First Title" then but ,,&zwnbsp;First Title". Imagine how long
| it takes before you catch that invisible character.
|
| Aside from that: Yes, if CSV would be a intentional, defined
| format, most of us would do something different here and there.
| But it is not, it is more of a convention that came upon us. CSV
| ,,happened", so to say. No need to defend it more passionate than
| the fact that we walk on two legs. It could have been much worse
| and it has surprising advantages against other things that were
| well thought out before we did it.
| k_bx wrote:
| I've recently been developing a raspberry pi based solution which
| works with telemetry logs. First implementation used an SQLite
| database (with WAL log) - only to find it corrupted after just
| couple of days of extensive power on/off cycles.
|
| I've since started looking at parquet files - which turned out to
| not be friendly to append-only operations. I've ended up
| implementing writing events into ipc files which then
| periodically get "flushed" into the parquet files. It works and
| it's efficient - but man is it non-trivial to implement properly!
|
| My point here is: for a regular developer - CSV (or jsonl) is
| still the king.
| theoryofx wrote:
| > _First implementation used an SQLite database (with WAL log)
| - only to find it corrupted after just couple of days of
| extensive power on /off cycles._
|
| Did you try setting `PRAGMA synchronous=FULL` on your
| connection? This forces fsync() after writes.
|
| That should be all that's required if you're using an NVMe SSD.
|
| But I believe most microSD cards do not even respect fsync()
| calls properly and so there's technically no way to handle
| power offs safely, regardless of what software you use.
|
| I use SanDisk High Endurance SD cards because I _believe_ (but
| have not fully tested) that they handle fsync() properly. But I
| think you have to buy "industrial" SD cards to get real power
| fail protection.
| theLiminator wrote:
| > I've since started looking at parquet files - which turned
| out to not be friendly to append-only operations. I've ended up
| implementing writing events into ipc files which then
| periodically get "flushed" into the parquet files. It works and
| it's efficient - but man is it non-trivial to implement
| properly!
|
| I think the industry standard for supporting this is something
| like iceberg or delta, it's not very lightweight, but if you're
| doing anything non-trivial, it's the next logical move.
| BrenBarn wrote:
| I always feel like CSV gets a bad rap. It definitely has problems
| if you get into corner cases but for many situations it's just
| fine.
| relistan wrote:
| CSV is bad. Furthermore it's unnecessary. ASCII has field and
| record separator characters that were for this purpose.
| munchler wrote:
| That would be great if keyboards had keys for those characters
| and there was a common way to display them on a screen, but
| they don't and there isn't.
| nextts wrote:
| 10. CSV doesn't need commas!
|
| Use a different separator if you need to.
|
| CSV is the Vim of formats. If you get a CSV from 1970 you can
| still load it.
| fbn79 wrote:
| CSV is too new and without a good standard quoting strategy.
| Better staying with the boring old fixed length column format :))
| athenot wrote:
| CSV is awesome for front-end webapps needing to fetch A LOT of
| data from a server in order to display an information-dense
| rendering. For that use-case, one controls both sides so the
| usual serialization issues aren't a problem.
| hermitcrab wrote:
| There is a lot not to like about CSV, for all the reasons given
| here. The only real positive is that you can easily create, read
| and edit CSV in an editor.
|
| Personally I think we missed a trick by not using the ASCII US
| and RS characters:
|
| Columns separated by \u001F (ASCII unit separator).
|
| Rows separated by \u001E (ASCII record separator).
|
| No escaping needed.
|
| More about this at:
|
| https://successfulsoftware.net/2022/04/30/why-isnt-there-a-d...
| eximius wrote:
| Welp, now I know my weekend project.
| hermitcrab wrote:
| I wrote my own CSV parser in C++. I wasn't sure what to do in
| some edge cases, e.g. when character 1 is space and character 2
| is a quote. So I tried importing the edge case CSV into both MS
| Excel and Apple Numbers. They parsed it differently!
| diegolo wrote:
| People that talk about readability: if you store using jsonl (one
| json per line) - you can get your csv by using the terminal
| command jq.
___________________________________________________________________
(page generated 2025-03-26 23:00 UTC)