35 ms·

So you want to write your own CSV code (2014)

303 points by ColinWright 7y ago

cjfd 7y ago
Well, considerations like these are typical for more or less any kind of software you will ever write. Because of this all but the very simplest software needs automated tests. But one may also note that your particular usage of CSV may be a bit simpler than supporting all of the possible complications. Especially if one is not reading CSVs that could come from anywhere. It depends, having dependencies is always a risk and a nuisance as well. The library you are using may have bugs, acquire bugs in the future, change in incompatible ways and so on.
papito 7y ago
Implementing your own CSV parser is one of the first lessons a green software engineer will learn in how to NOT try to re-invent the wheel. Still, please don't import packages with just one goddamned 3-line function in it.
- qayxc 7y ago
  It's what happens when DRY becomes dogma instead of a guideline... This leads to debacles like the npm left-pad & kik affairs and other scary shit like https://github.com/parro-it/awesome-micro-npm-packages https://github.com/parro-it/awesome-micro-npm-packages Yeah - have fun maintaining dependencies when every other module depends on one-liners that can be pulled or broken at random...
swiley 7y ago
>Some countries use a comma as decimal separator instead of a colon. I kind of wish we could all just pick something and stick with it.
- ccccc0 7y ago
  Like the metric system?
  - BlueTemplar 7y ago
    The metric system, being French in origin, would certainly use the comma!
- akie 7y ago
  Yes, and we picked a comma. You?
  - deleted 7y ago
    [deleted]
- unwind 7y ago
  That sentence must have a typo in it, right? Wikipedia [1] doesn't list any country that does use the colon (:) as the decimal separator, so possibly the period/dot (.) as used in most English-speaking countries, was meant? 1: https://en.wikipedia.org/wiki/Decimal_separator https://en.wikipedia.org/wiki/Decimal_separator
  - deleted 7y ago
    [deleted]
  - yoz-y 7y ago
    Yeah, I think what author meant "some countries don't use the period but a comma, thus hilarity ensues" (especially when your code respects the locale and the csv file is written in append mode)
joshlk 7y ago
The Pandas CSV reader (Python) is one of the best and most versatile implementations out there. Also very fast. https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_csv.html https://pandas.pydata.org/pandas-docs/stable/reference/api/p...
- andreareina 7y ago
  N.B. Floats won't rountdrip by default (you need to call `read_csv(..., float_format="round_trip")`)
- nojito 7y ago
  Disagree. Data.table’s fread is leagues ahead of pandas. https://www.rdocumentation.org/packages/data.table/versions/1.12.8/topics/fread https://www.rdocumentation.org/packages/data.table/versions/... Fread has automatic footer detection and has automatic skip logic to help parse out mangled headers in some csvs.
  - vharuck 7y ago
    fread can also accept a shell command which it will run and use the result as input. I use that to grep the lines I want from files too big for RAM.
    - nojito 7y ago
      Same here! I also use fwrite to send it back to the shell to continue my processing pipeline. https://www.rdocumentation.org/packages/data.table/versions/1.12.8/topics/fwrite https://www.rdocumentation.org/packages/data.table/versions/... It's mind boggling how little fanfare data.table has despite it being the best way to handle tabular data in 2020.
  - jsmith99 7y ago
    fread is amazing. Whenever I look at the docs and see all the options for dealing with nested lists (like the within-column-seperator) I always pray never to have need of such a thing.
barbegal 7y ago
I think you need to be careful with choosing to use CSV format. CSV works fine as long as you are not handling character stings. Character strings get messy as soon as they might introduce commas or characters outside of ASCII. For tabular data which has character strings I prefer to used the ODF format http://opendocumentformat.org/developers/ http://opendocumentformat.org/developers/ (.ods file extension) which has good import and export capabilities from Excel, or Google sheets. If the user needs the data in CSV for entry into another application then they can convert within Excel and handle any conversion problems themselves.
- guerby 7y ago
  Do you have a preferred library for python for example)? Debian packages python3-odf and https://github.com/eea/odfpy https://github.com/eea/odfpy seems to still have some activity (vs the other listed).
  - barbegal 7y ago
    I don't. In the past I have just used an XML and a zip library like this article explains https://www.codeguru.com/csharp/csharp/cs_data/xml/article.php/c16311/How-to-Read-and-Write-ODFODS-Files-OpenDocument-Spreadsheets.htm https://www.codeguru.com/csharp/csharp/cs_data/xml/article.p...
  - shrimp_emoji 7y ago
    https://docs.python.org/3/library/csv.html https://docs.python.org/3/library/csv.html ? :o
tester34 7y ago
doesn't seem insanely difficult dont be scared of coding guys, just differentiate between environments: do it for your 4fun project, not prod.
- yoz-y 7y ago
  The problem isn't really that it's difficult to solve. It's because it's impossible. There is no real formal spec. The RFC does not specify edge cases and nobody respects it anyways. "Generic" CSV parser is a pipe dream, don't try to do it. Now, if you define your own set of rules on what is and is not allowed then it's fine.
  - Brian_K_White 7y ago
    Indeed it's a perfect example to illustrate the difference and the importance of an under-defined spec and a well defined one. Whatever you don't define, will be undefined. But you still somehow have to do something definite without a definition. The customer expects predictable output at the end, and until we have not only clairvoyance, but clairvoyance that can be built in a machine, you can't have predictable output without either predictable input, or a spec that actually provides an answer for any input.
DougBTX 7y ago
I think the most fun I had parsing a CSV file was when I found out it switched from UTF8 to UTF16 after several thousand lines, as differently encoded CSV files had been concatenated together directly into a single file...
- tonyedgecombe 7y ago
  I've seen some terrible CSV files where people tried to squeeze hierarchical data into a flat file format. Usually the customer can't provide any sort of schema or documentation because if they could they would have recognised CSV isn't appropriate. This means you are in an endless cycle of fixing the code as unexpected items keep appearing in the data.
  - jointpdf 7y ago
    I tend to think of dataframes (R, Python pandas) as the standard way for representing and working with the data contained in a .csv. “List columns” (https://jennybc.github.io/purrr-tutorial/ls13_list-columns.html https://jennybc.github.io/purrr-tutorial/ls13_list-columns.h...) inside dataframes can actually be a convenient way to represent data that is mostly flat, but has some hierarchical data associates it (recent example: dataframe representing a corpus of documents, with a column containing a list of citations). So while .csv probably isn’t the best format to store hierarchical data, it’s fairly trivial to work with if encountered.
- GolDDranks 7y ago
  I have a similar story: parsing a CSV file that had an unexpected 0x1A byte in the middle. Turns out the file was concatenated together in multiple passes from multiple files using the COPY DOS command, which by default adds ASCII SUB to the end as an EOF marker: https://en.wikipedia.org/wiki/Substitute_character https://en.wikipedia.org/wiki/Substitute_character
  - SanchoPanda 7y ago
    I have done this to myself in the past. Those were....dark days.
jtvjan 7y ago
Would've been a lot nicer if they used the record and unit separator instead of commas and newlines. There are characters made specifically for storing tables like this and they instead chose to reuse characters which might appear in regular text.
csunbird 7y ago
Reminds me of https://infiniteundo.com/post/25326999628/falsehoods-programmers-believe-about-time https://infiniteundo.com/post/25326999628/falsehoods-program...
- tonyedgecombe 7y ago
  It applies to everything, dates, floating point numbers, strings, etc. What we would consider the basics always turn out to be much deeper than they appear on the surface.
  - atulatul 7y ago
    Dating advice: if you habitually, unironically, and without good cause use any dating format but that which is mandated by ISO 8601 we cannot be friends, let alone more. https://twitter.com/jwiechers/status/1205515440543424513 https://twitter.com/jwiechers/status/1205515440543424513 + the thread
cesaref 7y ago
One thing that has always puzzled me is why CSV exists, given the ASCII unit/record seperators appear to do exactly what the comma and newline characters are attempting to do with codes which could appear within records, and have been well defined since some point in the 60s. And here we are, 60 years later still struggling to work out where a record ends...
- skrebbel 7y ago
  Bacuse csv is human readable and human writeable
  - nojito 7y ago
    So is tsv, but for some reason csv is more popular despite being the worst character to use.
    - hrktb 7y ago
      For a lot of people, meaningful space is a hard concept to understand. It's made worse by the number of applications where they will see spaces reformatted willy nilly (for instance web forms eating their line breaks. From there explaining there are spaces and tabs, and that both can look the same on screen but they are different is just asking for trouble outside of our circles.
      - mgkimsal 7y ago
        > is just asking for trouble outside of our circles it's asking for trouble in our circles too. Imagine getting a tab-sep file, opening it up in an editor and having it automatically convert to 4 spaces "because", then sending it back without checking something that doesn't look wrong.
        SAI_Peregrinus 7y ago
        Or an input/editor where the `tab` key moves your focus from the text field to the next button. Like Slack. You can't just hit `tab` in Slack when typing a code snippet for something that needs them like a Makefile.
SanchoPanda 7y ago
To be fair, sometimes calling some giant java library or installing tons of dependencies is just not realistic. Use the library for the mission critical stuff or the automated reporting without a doubt, but it's important to be practical and thinking on your feet as well. In the meantime for quick analysis and testing 90% + can be accomplished with one line of (g)awk. awk -v FPAT='"[^"]*"|[^,]*' -v OFS='\t' '{$1=$1; print $0}' "$filename" More on FPAT [here](https://www.gnu.org/software/gawk/manual/gawk.html#Splitting-By-Content https://www.gnu.org/software/gawk/manual/gawk.html#Splitting...)
- nicoburns 7y ago
  You can also use xsv for this kind of analysis of CSV files. Which is a unix-style tool designed for CSVs and with a proper CSV parser. It's written in Rust so it's one binary - no runtime dependencies, and will happily chunk through multi-gigabyte files. https://github.com/BurntSushi/xsv https://github.com/BurntSushi/xsv
nbevans 7y ago
I prefer to use a SQLite database. And if a customer insists on CSV then I still use a SQLite database to import and/or export the CSV without them realising it. SQLite brings sanity to CSVs.
- DarkWiiPlayer 7y ago
  You still have to import the CSV though, so the problems are still there, you just don't have to deal with them after a successful import.
  - nbevans 7y ago
    You misunderstand how enterprise integrations work. It's easy to tell a customer: "your CSV is not compatible with standard off the shelf tooling, indeed the most popular database engine in the world" than "your CSV doesn't seem to work with our software/library/<proprietary ETL framework>". The former makes it their problem, the latter will almost undoubtably make it your problem. In case you don't know: SQLite has a really well designed and very performant CSV import function. BTW did you really downvote me for that? Wow.
Hitton 7y ago
It was not emphasized in the article, but the quotes can be both single and double. That said, it's futile to try to write universal CSV parser. If you need to write your own, just make it possible to choose delimiter and type of quotes and call it a day. LibreOffice Calc does same.
oefrha 7y ago
> What if there is non ASCII data? > Just use utf8 right? But wait… > What if the program reading the CSV use an encoding depending on the locale? > A program can’t magically know what encoding a file is using. Some will use an encoding depending on the locale of the machine. Excel (for Mac at least) is a fucking pain in this regard. Just try this minimal UTF-8 example: tmpfile=$(mktemp /tmp/XXXXX.csv); echo '“,”' > $tmpfile; open -a 'Microsoft Excel' $tmpfile Hooray, you successfully opened ‚Äú,‚Äù I'm not even sure in which encoding e280 9c2c e280 9d corresponds to that (not the usual suspect cp1252, nor any code page in the cp1250 to cp1258 range; easy to confirm with iconv). One remedy is to add a BOM (U+FEFF) to the beginning of the file, but of course no one other than Microsoft (at least in my experience) uses this weird UTF-8 with BOM encoding (which the Unicode standard recommends against), so it breaks other programs correctly decoding UTF-8. This means I can never share a non-ASCII CSV file with non-technical people. Always have to convert to .xlsx although it's usually easier for me to generate CSV. Then .xlsx opens me up to formatting problems, like phone numbers being treated as natural numbers and automatically displayed in scientific notation... Which means ssconvert or other naive conversion tools aren't enough, I need to use a library like xlsxwriter. I'm not sure why it's so hard to just fucking ask when you don't know which encoding to use. (Plus it's not super hard to detect UTF-8. uchardet works just fine. Plus my locale is en_US.UTF-8, maybe that's a hint.)
- jfk13 7y ago
  > Hooray, you successfully opened > ‚Äú,‚Äù > I'm not even sure in which encoding e280 9c2c e280 9d corresponds to that That'll be MacRoman (not very surprisingly). https://en.wikipedia.org/wiki/Mac_OS_Roman https://en.wikipedia.org/wiki/Mac_OS_Roman
  - oefrha 7y ago
    Ah, I thought it has to be something Microsoft. We can place the last modernization effort to this piece of code, then.
- tasogare 7y ago
  Yes, it’s a pain. However there is a solution to open an UTF-8 CSV file (with Data > From Text and a few clicks), but it’s true that googling something like that is out of the mind of most people.
larschdk 7y ago
Just today I needed to write a .csv file in Python on Windows and got a file with CR CR LF line endings by default (\r\r\n). That was using the standard 'import csv' in Python 3.
- hprotagonist 7y ago
  yeah you gotta pass with open(...,newline='') as f:
  - JoBrad 7y ago
    I think the docs now include this.
    - hprotagonist 7y ago
      yes, they have for some time now i think. I wish it was better known, though.
arethuza 7y ago
A good one I saw recently was a text file that, even though it had a .csv file extension was actually using another character as a delimiter (in this case colons) but as it contained large amounts of textual data actually had a enough commas to be parsed as a CSV file. VS Code and a suitable extension handled this rather better than Excel.
- tigerstripe 7y ago
  Sounds like CSV in this case stood for Colon Separate Data
  - fomine3 7y ago
    Further generalization: Character Separated Values
aww_dang 7y ago
Even when using libraries to read CSV data the need for sanitization isn't uncommon. Sure there's RFC4180, but variations are common. Mysterious records that are out of sync are typical. For my own sanity I like to verify that columns are of the right data type where possible. That said, I still prefer CSV over heavier formats (XML,JSON) where the data conforms.
polm23 7y ago
At a former job we processed files, many CSV, from hundreds of different sources and I was in charge of cleaning up the code that did this once. There were a few tiny binaries without source called csv2tsv and csv2tsv2. No documentation of any kind of course. csv2tsv just handled quoted fields and I was able to replace it with a few lines of Python without issue. The csv2tsv2 program was used for CSV files from exactly one company. We couldn't ask them for technical assistance - it's likely their systems had been written years ago and running without updates since then - so I tried to figure out what the binary was doing. The input file had some null characters in it, but they seemed to be used inconsistently, and I was never able to figure out what that binary was supposed to do. I left that binary alone and kept using it for a few years before a new guy joined and took over the system from me. I mentioned this weird old binary to him and over the course of a week he poked at it now and again before figuring out what it was doing. He used to work at a bank and realized it was using the same quoting method that some old data format they'd used there did - something about doubling characters and a few other tricks. There's nothing simple someone won't make complicated.
- SanchoPanda 7y ago
  You are thinking of these: https://www.w3.org/Tools/csvtotab-vv https://www.w3.org/Tools/csvtotab-vv Agreed, they are excellent.
t0astbread 7y ago
What about implementing something that works for your environment and making it fail-fast so that any unexpected input will just abort+log the current operation? You could then expand on that on-demand.
- Brian_K_White 7y ago
  This assumes you can recognize unexpected input.
  - t0astbread 7y ago
    I assume unexpected input results in either an error (or exception, whatever your language calls it), a crash or a user complaint. I also assume it's unlikely for an input parser to introduce a security issue into your app if it's written in a safe language and your app has proper validation set up (i.e. don't trust input as soon as it passes the parser).
the_dripper 7y ago
i wanted to... but not anymore :/
franze 7y ago
I coded a few robots.txt parsers i.e.: (these are just the ones i cared to publish) https://github.com/franzenzenhofer/simple-functional-robots-txt-parser https://github.com/franzenzenhofer/simple-functional-robots-... https://github.com/franzenzenhofer/robotstxt https://github.com/franzenzenhofer/robotstxt and even though robots.txt seems like a very, very simple text based protocoll there are unanswered mysteries the biggest mystery is user agent groups and comments i.e.: User-agent: googlebot User-agent: bing User-agent: yandex Disallow: / so i am disallowing everything for google, bing, yandex; easy enough, but: User-agent: googlebot User-agent: yandex Disallow: / means that googlebot has no instructions, but yandex is disallowed all. if a whole line is commented out User-agent: googlebot #User-agent: bing User-agent: yandex Disallow: / is a commented out line a blank line or a non existing line? if it is interpreted as a blank linke, the disallow only counts for yandex, if the line is non existant, it counts for googlebot and yandex. i like simple things, but sometimes the complexity is between the lines.
- ThreeFx 7y ago
  What's the solution? Semantically it makes sense if it counts for both googlebot and yandex. Have you observed one/both behaviours?
  - franze 7y ago
    i am consulting a lot of aggregators, and we coose to interpret sometimes as this, sometimes in the other scenario. the way i interpret the spec, it is not defined behaviour.
- iams 7y ago
  User-agent: googlebot #User-agent: bing User-agent: yandex Disallow: / why would it be interpreted as a blank line? If you remove everything after the #, that includes the new line characters at the end of the line. Leaving: User-agent: googlebot User-agent: yandex Disallow: /
  - franze 7y ago
dehesa 7y ago
I can relate. I have been one of those who started writing a CSV library and went down the rabbit hole of edge cases and configurability. Looking back (and only thinking on productivity) my time would have been better spent doing something else. I did, however, learn quite a lot along the way: - how text is represented in a computer (and all its caveats), - how to identify and tune slow code, - how to vectorize code (thanks to Daniel Lemire's https://www.youtube.com/watch?v=wlvKAT7SZIQ https://www.youtube.com/watch?v=wlvKAT7SZIQ ), - how to encapsulate code better, - how to use some great CSV tools out there (thanks to Leon's https://github.com/secretGeek/awesomeCSV https://github.com/secretGeek/awesomeCSV ) - how to better manage and communicate an open source project. All in all, I am still not sure it was a mistake. Moreover when Swift didn't really had, at the time, a good Codable interface to CSV. Next time I encounter a similar problem I will probably just wrap a C library and create a good interface, though. In any case, if you are coding in Swift and find yourself in need of a CSV encoder/decoder, you might want to check https://github.com/dehesa/CodableCSV https://github.com/dehesa/CodableCSV
geraldbauer 7y ago
FYI: I've put together a collection of CSV specifications [1]. CSV Dialects / Flavors include CSV v1.0, CSV v1.1, CSV Strict, CSV <3 Numerics, CSV<3 JSON, CSV <3 YAML and some more. [1]: https://github.com/csvspecs https://github.com/csvspecs
geraldbauer 7y ago
CSV is awesome :-). Leon Bambrick curates an Awesome CSV Page [1] and another Awesome CSV Page [2] by my humble self. [1]: https://github.com/secretGeek/AwesomeCSV https://github.com/secretGeek/AwesomeCSV [2]: https://github.com/csvspecs/awesome-csv https://github.com/csvspecs/awesome-csv
- JoBrad 7y ago
  > When the final cockroach breathes her last breath, her dying act will be to scratch her date of death in a CSV file for posterity. CSV is the Keith Richards of file formats.
- LeonB 7y ago
  Thanks Gerald ;)
drej 7y ago
There's this system, which has become extremely popular for processing data (Apache Spark). And its default configuration cannot parse CSVs properly and can lead to pretty severe data loss. I wrote about this a while ago and it's still an issue. (Yes, we did lose data his way, it's not a theoretical exercise.) https://kokes.github.io/blog/2019/07/09/losing-data-apache-spark.html#csvs-dont-have-a-standard-per-se-but-lets-be-reasonable https://kokes.github.io/blog/2019/07/09/losing-data-apache-s...
mrweasel 7y ago
> Some countries use a comma as decimal separator instead of a colon. And some companies... like Klarna, fixes this wrongly. At least they did a few years back. We'd get a CSV file from them, containing payment information, but they ran into the issue that they really wanted to use comma for field separation, but also for krone/øre (decimal) separation. They ingenious solution: Fields are now separated by ", " that's a comma and a space. Pretty much no CSV parser will accept a multi character field separator. So many fields would just have a space prepended to whatever value was in that field and you'd get a new field, when the parser split the field containing the amount. So now you have say 5 headers but each row would have 6 fields, because the money amount became two fields. Being on the receiving end you now have to choose, do you want to strip spaces and reconstruct the amount field manually, or parse the CSV file yourself.
- tjalfi 7y ago
  If you use .NET then CsvHelper[0] supports multi character separators. [0] https://joshclose.github.io/CsvHelper/ https://joshclose.github.io/CsvHelper/
sharpercoder 7y ago
Can we somehow start to use the ascii 30 character aka the Record Separator (SP)? This would solve a lot of the problems!
- wombatpm 7y ago
  Not as fun as you might think. I had to parse and write inkjet control files for commercial printing. It used record separators, group separators, and unit separators. The problem? Records began WITH the RS character and there were NO line breaks. Ended up writing my own library https://github.com/Wombatpm/VIPNT https://github.com/Wombatpm/VIPNT
jmull 7y ago
> Do you really still want to roll your own code to handle CSV? No one really does, but we get backed into it due to these various issues. The general issue is that there is no real standard for csv, yet it's often used as the touch-point for integration of disparate systems! The integration goes something like this: 1. A and B decide to integrate! A will accept data from B. 2. A and B want to use a good interchange format, but after weeks of intense negotiations B can only get her IT department to agree to deliver csv (and feels lucky to get that much). 3. B uses excel to provide sample and test files, which are used by A for development or even in early stages of the live integration. Things seem to be going smoothly. 4. At some the export process at B changes, a new csv generator is used and now things break on A's side. Or something on the data input side changes at B so that new forms of data are now present that their csv generator does handle the way A expects, etc.
- 3pt14159 7y ago
  Eh—I had to do it recently. There was a file format that I was dealing with that was basically a combination of CSV for data and Yaml for headers (and some other edge case stuff) and the Python CSV reader couldn't handle some edge case that we needed handling, mostly around reporting back to the file provider where they messed up one thing or another in the file. It didn't really take me that long. Lots of easy to write tests and the thing is processing a ton of data now for a well known stock exchange. Far easier than, say, the machine learning stuff I've written.
- cryptonector 7y ago
  The breakage can be subtle and unnoticed for a while, and it can be in the future. It sucks.
bproctor 7y ago
Well sure, if you're writing a generic CSV parser, it's very complicated. But if you know what your input looks like, CSV can be one of the simplest format to parse.
leokennis 7y ago
One small tip if you receive a CSV with a non-default separator (like ;): * Open it in a text exitor * Add "sep=;" on the first line (without the quotes) * Now at least Excel will open it as intended
- BlueTemplar 7y ago
  IIRC Excel will ask for the separator anyway when you open the file?
  - hnick 7y ago
    It does not, at least not anymore.
  - brewmarche 7y ago
    It does not and actually assumes locale-dependent separators (usually semicolons if your locale uses a comma as a decimal separator and comma otherwise). If you want to specify separators you have to go via Text Import.
- lucb1e 7y ago
  Calc just asks upon opening, no hidden tricks needed. Whenever I see people struggling with Excel and CSV, also when pasting CSV data into Excel, I can't stop wondering why people still bother with the Office suite. It has zero advantages aside from the cost of change (time spent on re-learning where things are), yet we teach it to kids in school and send them reminders that Microsoft benevolently gives them very steep discounts to reduce piracy (never mentioning that you could also just use this other free thing...), never mind the legislation saying governments and schools should use free software unless there is some strong and documented reason to divert from the rule.
ph4 7y ago
import csv
benbristow 7y ago
Whilst we're talking about libraries, had good experience with CsvHelper for C#/.NET. https://joshclose.github.io/CsvHelper/ https://joshclose.github.io/CsvHelper/ Documentation isn't 100% but it's a good tool. Been using it to work on a production 15+ column SQL->CSV mapping job with tens of thousands of records, working great.
bloak 7y ago
If you can't write a correct (according to RFC 4180) CSV parser in 15 lines of Perl then you're not a Real Programmer! (Yes, I understand that not all CSV files in the wild are correct according to RFC 4180. Yes, I also understand that only grey-beard loons still use Perl.) The strangest thing about CSV, I think, is that if there's only one field per record and the file ends with a CRLF then you can't tell whether there's a final record containing an empty field following that CRLF. It's probably best to assume there isn't.
- nmz 7y ago
  15? damn, that's 10 lines less than my awk csv parser. oh well...
redleggedfrog 7y ago
Oh .csv, how I love to loath thee. I have a funny story about .csv. Back in the day I was working on an integration with Fishbowl. Looking through their docs, the data format was your typical XML type stuff, until you got to the import requests. And there, lo and behold, was XML wrapping, you guessed it, .csv. I literally laughed out loud. https://www.fishbowlinventory.com/wiki/Fishbowl_Legacy_API#Import https://www.fishbowlinventory.com/wiki/Fishbowl_Legacy_API#I... Years later they released an updated API. Upon checking it I discovered it's now JSON wrapping .csv, so, you know, progress. https://www.fishbowlinventory.com/wiki/Fishbowl_API#Import https://www.fishbowlinventory.com/wiki/Fishbowl_API#Import
- arethuza 7y ago
  Paths in SVG are another example where the designers couldn't bring themselves to do everything strictly in XML and decided to use (space) delimited values: <path d="M 100 100 L 300 100 L 200 300 z" fill="red" stroke="blue" stroke-width="3" />
  - th0ma5 7y ago
    This is sort of a quasi standard way of describing paths as drawing operations. You'll find this in working with fonts as well and old plotters sort of.
kyberias 7y ago
I'm currently implementing a REST API that returns CSV files.
ken 7y ago
I wrote my own CSV code. That's only about 10 issues, and I understand them all, and they're easy to handle, and to test (unlike, say, threading issues, or OS bugs that don't happen on my computer). That wasn't even close to being one of the more difficult parts of my program to write. I've had to do zero work to maintain it. I don't seem to have ever had any bugs filed against it. Are these supposed to be difficult? Sure, don't write your own encryption algorithm, and avoid touching threads if at all possible, but CSV? Sure, I wish I could have used a library. Unfortunately, once you start adding requirements, the options start to vanish. It needs to be a C (or C-callable) library. It needs to accept input as Unicode strings (lines), not just files on disk, or bytes. It needs to return results line-at-a-time. It needs to have a compatible license. It needs to support the same package manager as I'm using (or be a trivial to include, like a single file). It needs to be reliable (essentially bug-free, or at the very least currently maintained). It needs to be fairly efficient. What library supports all these? > Writing CSV code that works with files out there in the real world is a difficult task. The rabbit hole goes deep. Ruby CSV library is 2321 lines. I love Ruby, but that file looks a little nuts to me right now. The block comment at the top of csv.rb is over 500 lines long. The Ruby module tries to do everything. Just "def open(filename, mode="r", options)" and its docstring are 100 lines long! This class includes methods for auto-detecting the encoding, and auto-detecting common date formats, and some kind of system "for building Unix-like filters for CSV data". I don't put all of that into my CSV parser, because I want my code to be modular. I have features like data detectors and filters and open-file-by-filename in my program, too, but they're fully independent, not tied to any one file format. When I come up with a better way to detect dates in text, I don't want to tweak a 6-line regex in every file format I've written.
- greggman3 7y ago
  > I don't put all of that into my CSV parser, The art of not cluttering a library with too much stuff is a hard lesson to acquire. Most library authors try to throw in the kitchen sink. It's insane. I'm sure I was guilty of it in the past. For parser take a string. let me do the I/O outside. If you want to offer streams then just offer an abstract interface stream and then a few examples of implementing a stream but don't include them in your library. It's just clutter. This is one if largest problems with NPM. So many libraries try to do too much. Some come with a command line utility "just because"!??!?! It then adds more surface area, more dependencies, more churn as we have to update all those un-needed dependencies.
asadjb 7y ago
My initial comment was going to be that "The Python CSV library is only 448 lines (378 LOC) [1]". Only later did I realize that the bulk of the work is done by the C library, which is ~1500 LOC [2]. I guess parsing CSVs reliably is a reasonably difficult undertaking. [1] https://github.com/python/cpython/blob/3.8/Lib/csv.py https://github.com/python/cpython/blob/3.8/Lib/csv.py [2] https://github.com/python/cpython/blob/4a21e57fe55076c77b0ee454e1994ca544d09dc0/Modules/_csv.c https://github.com/python/cpython/blob/4a21e57fe55076c77b0ee...
- JoBrad 7y ago
  I have a base “library” I’ve used for several years that wraps the csv module (specifically DictReader/Writer) to handle encoding issues as well as data parsing. Have run into minor issues, but think that module alone is one of Python’s best use-cases.
mmm_grayons 7y ago
I wrote my own CSV code rarher thsn using a library because the input CSVs were produced by guys who didn't use a library. The columns had a fixed "real width" but also had a column that contained an indeterminate number of comma-separated, values. One can escape commas in CSV for just such a purpose, but these guys didn't. So, to deal with this issue quickly, I had to basically parse the line forwards until I hit the variable-length field, then backwards until I hit it as well. Then I could parse the field to determine how many values it had and extract them.
roelschroeven 7y ago
An additional problem is that Excel, the tool many people use to open CSV files, uses the computers regional settings for decimal separator and thousands separator. CSV files are used as an interchange format, so the format of the numbers in it does not depend on the region of that specific computer. You can tell Excel to always use '.' as a decimal separator, but then it also uses it for presenting numbers to the user. It boggles my mind that software like Excel doesn't understand the difference between reading/formatting numbers for human use and reading/formatting numbers primarily meant for talking to other software.
- tlbsofware 7y ago
  Excel will also take a number with a leading zero and drop it while it converts it from csv, for ex 01234 -> 1234, which is SUPER bad if you are dealing with any sort of IDs to invoices or work orders from outside the company
  - sk5t 7y ago
    That is a good occasion to curse Excel (how does it know the number isn't octal, too?) and also curse the person or group that designed a textual coding system consisting of digits with leading zeros. Looking at you, National Drug Code.
  - JoBrad 7y ago
    FWIW, the new importer for Excel lets you easily preview and adjust this behavior before importing the entire file.
- sk5t 7y ago
  Excel, while amazing on its own, is horrible for exchanging data in so many ways. I've maintained a policy at my company (life sciences) not to accept Excel files in any automated process; while parsing csv/tsv is a pain, xlsx/xlsb/etc. would amplify that pain in so many ways both predictable and surprising. https://www.washingtonpost.com/news/wonk/wp/2016/08/26/an-alarming-number-of-scientific-papers-contain-excel-errors/ https://www.washingtonpost.com/news/wonk/wp/2016/08/26/an-al...
rietta 7y ago
I write CSV code all the time in different languages. It's a fun challenge that incorporates several nuances. It has been a form of code kata for me over the last 20 years. Biggest lesson, one does not simply split a string. You have to scan through and tokenize following the state of a quoted field.
xamuel 7y ago
Funny story on this subject. When I was transitioning from academia to industry, I was afraid (having browsed hackernews so much) that I would be harshly judged for reinventing the wheel. So I removed the CSV library I had written from Github. Shortly thereafter, I was emailed by a well-known programmer (the creator of a fairly major language) asking why I had removed my CSV library, which he was using in one of his projects! Here's the library, btw: https://github.com/semitrivial/csv_parser https://github.com/semitrivial/csv_parser
beders 7y ago
My CSV story from last night: I had these 15MB+ excel sheets and was trying to open them with Apache POI. I gave that code a generous 2GB memory: GC overhead limit reached. Then I opened them in Excel, saved them as CSV, reached for a CSV-library and was done within seconds. Ok, well, the story is more about how parsing XML can ruin your day ;)
- mike22 7y ago
  Are you using POI’s eventmodel APIs to handle such files? The SAX model isn’t too bad for reading files.
QuadrupleA 7y ago
This scary stuff only applies if you need to read garbage CSVs from a million different sources. Even then, like ken says here, that's only like 10 issues to figure out. Exporting is likely the more common case and is pretty simple. Quote fields if they contain \r\n", I'd say relax, focus on your particular problem - if a simple 2-3 line export loop fits your use case and is simpler than a csv library (e.g. in C/C++ perhaps where dependencies are a pain) then why not. This fear mongering about "you can't possibly do this, the library writers are much smarter than you" can lead to it's own ridiculousness of hundreds of dependencies and ungodly waste of processor time, memory, build time, containerization hassle, etc. to solve what should be a simple problem.
- crispyambulance 7y ago
  > This scary stuff only applies if you need to read garbage CSVs from a million different sources. Even then, like ken says here, that's only like 10 issues to figure out. Well, garbage CSV's are garbage CSV's. They can be inconsistent, and if you're reading millions of them (OK, even hundreds) it can be a real pain-in-the-ass data janitorial task-- the equivalent of cleaning a public bathroom after homeless people have sprayed diarrhea all over the floors, walls, and ceiling. The hard part of dealing with the "10 issues" is you have to figure out which ones apply to which files (or even lines). A CSV linter is often helpful to diagnose, that would be the janitorial equivalent of rubber gloves and a bucket of soap, but it's still a lot of work. If you truly have millions of inconsistent files to deal with, the only way to keep sane is to categorize them by some parameters that make sense... size, date, keywords, even word-stats. Then, you can programmatically tackle each category. It's a messy problem no matter what and gets more ugly as the scale increases. And all of this is often before you even start what you actually need to do.
- zelly 7y ago
  Learned helplessness. You will never git gud if you're just copying and pasting libraries. I blame npm.
- kristopolous 7y ago
  There's a balance. Massaging wacky csv feeds to actually be parsable because someone wrote their own incorrectly is a real problem. Seen it dozens of times. I think the real problem is programming has become too easy. Almost nobody really has to worry about data structures, memory management, any of the hard problems that filter for competency. Some wacky insane stuff runs just fine on these 8-core multi-gigabyte ram computers we literally have in our pockets. A lot of it is on a VM inside of yet another VM, even that's fine. Just about every bad idea seems to work. These kinds of issues are to be expected.
OliverJones 7y ago
I totally agree with this. Writing your own csv code is the archetypal example, in computer programming, of reinventing the flat tire. I've found that every major language has at least one reusable component / module / library / dll to to this. They're maintained by people who have lots more patience than I do.
pinopinopino 7y ago
I did this just for fun to learn a parsing library I was using. It didn't deal with utf-8 though, but even that is manageable. The complexity with csv is that it is a family of formats with small changes. But if you make your parser configurable, you are set.
- pinopinopino 7y ago
  If you want to do something really hard, try to parse PDF, that is sheer insanity.
GuB-42 7y ago
I disagree with the article, I'd say writing your own CSV code is the second best option, behind not using CSV at all. The reasons are: - CSV is really simple. A complete RFC4180 parser shouldn't take more than 100 lines of readable code to implement. Even less so for the writer. So much that most of the code is likely to be interface code (file I/O, matching your internal data structures, etc...) rather than parsing. And chances are that a library won't even make your code shorter. - As mentioned in the article, CSV is not well defined. I've seen all sorts of weirdness: semicolons vs colons, LF vs CRLF, simple vs double quotes, backslash escaping, comments, floating point numbers with comas instead of points. What your library understands as a CSV may not be the files you have to work with. I'd rather have a simple piece of code that correspond to what I am given rather than a big library that uses AI techniques to try to guess the intent of whatever wrote this file. There are cases where it would be foolish not to use a library. I will never write a XML parser for instance, it is a well defined and complex format. JSON is borderline but I still won't write my own parser. CSV is essentially a proprietary format, so it is fitting to use a proprietary parser/writer.
lxe 7y ago
Just because all kinds of inputs exist, doesn't mean you should support handling them all.
jackfoxy 7y ago
And this is just the tip of the iceberg for data importation. Managers and customer facing folks want to say Can't you just use off the shelf software for this? If your data importation needs are at all non-trivial, the problems tend to come in 2 categories. 1) Error reporting from off the shelf software is inadequate. If you are lucky, it will consistently point you to the correct row number. It's usually lacking any additional info. 2) If your application is at all non-trivial, you have specific importation needs, not easily covered by 3rd party generic solutions. For instance you want to import well formed tables with primary keys. Lots of solutions claim to handle this case, but you usually run into problems under category (1). A programmer can overcome these difficulties and muddle through 3rd party solutions, but you probably don't want to hand this over to users. It just won't be a good experience for them. I completed an importation library tailored for our specific needs and handling the real problems we have experience over many years. The front-end project to make this available to users started and stopped and changed hands over months. We are finally nearing deployment, and so far the users we have shown it to like it.
coliveira 7y ago
CSV is not a portable format across applications. That's it. You should use it only in your own application or in connection with applications that you know. The big problem is to think that you can handle a generic CSV file, because there is no such thing.
mystickphoenix 7y ago
Fantastic article! It's missing: - "what if the quotes are smart quotes?" - This applies to both quotes inside a field and quotes wrapping a field - "what if the file doesn't have a header?" - "what if a logical record is spread over multiple lines?" - Yeah... so, you have line 23 that has the majority of the data in the expected columns, line 24 has additional data in columns 23, 25, and 28 that's supposed to be appended onto the previous line's data that also appears in columns 23, 25, and 28. Line 25, same thing, but line 26 starts a new logical record. Seen all three above in the wild and Python's (pretty awesome) CSV code didn't handle them. Queue the custom code that wraps Python's core CSV parsing.
wdavidw 7y ago
I am the lead developer behind the Node.js CSV parser [0]. The project started in 2010, an hour before me leaving work for the weekend. It ended up being a long, very long journey that isn't over yet. The project grew incorporating many features I would never have expected, fixing corner cases the several hundred tests were not covering and teaching me a lot about coding and project handling. The bottom line, writing a correct CSV parser takes a little bit more than implementing an advanced string spliting function. [0] https://csv.js.org/ https://csv.js.org/
Neil44 7y ago
Me too. Everything was great until Mr O’Connor came along.
hermitcrab 7y ago
I wrote my own CSV parser for https://www.easydatatransform.com https://www.easydatatransform.com. I ran into some cases where it wasn't clear what to do. For example where you had some whitespace outside of quotes. So I checked what Excel and Numbers (the Mac spreadsheet) do. They parse the same string differently...
freefriedrice 7y ago
After working int he world of CAD tools for decades, I cannot tell you the number of times I've seen a younger engineer try to write a CSV parser only to fall into a circular hacking deathspiral. You can't just use regex/split to handle CSV, unless you have significant field cleaning BEFORE converting to csv. In reality you need lexical analysis and grammatical rules to parse any string of symbols. This is often always overlooked by naive implementations. I take issue with OP's claim that RFC4180 is not well-defined, but almost all of the cases the OP listed are literally in the spec.
hackandtrip 7y ago
Some fast implementations in C++? In particular, I tried in the last weeks to work on a OpenMP / MPI driven CSV parser, but surely accounting for new lines inside quotes can be a pain the ass, I'm wondering if there's a go-to implementation or model to implement that.
dang 7y ago
Discussed at the time: https://news.ycombinator.com/item?id=7796268 https://news.ycombinator.com/item?id=7796268
nly 7y ago
Sometimes the best thing you do is learn at least one parsing framework so that you can write your own parsers. The advantage of this is you know exactly what the behavior will be. Here's a CSV parser in ~40 lines of C++ using Boost Spirit that handles all kinds of weird cases: https://gcc.godbolt.org/z/KGUHXo https://gcc.godbolt.org/z/KGUHXo It's easy to customize, declarative, and compiles down to about ~15KB.
neilv 7y ago
I wrote a CSV-reading library 16 years ago that I'm pleasantly surprised handled most of the points of trickiness the article mentions. https://www.neilvandyke.org/racket/csv-reading/ https://www.neilvandyke.org/racket/csv-reading/ The documentation section "Reader Specs" gives a good idea of the different variations it intends to handle. I was working from a sense of variations I'd seen myself, and extrapolating. One thing I also did, which I didn't see in the article, was to support comments. Unfortunately, at the time I wrote it, there was no standard Unicode support for Scheme, so I didn't get into that, but just used the character abstraction (which might or might not involve parsing of a particular non-ASCII encoding). Fortunately, AFAIK, it's worked for people, since fixing/extending it at this point would mean relearning the code. :)
nojvek 7y ago
ndjson(new line delimited json) is what we should use as a human friendly and machine friendly alternative to csv. json is a universal format. Works in every major language. It's spec is a state machine defining what to do with every possible encountered byte. https://www.json.org/json-en.html https://www.json.org/json-en.html SIMDJSON parses json at RAM speeds (~3Ghz). CSV should die. It's a terrible format. Just send arrays of ndjson instead. You'll probably have a few more " and [, but hey, you can deeply nest arrays inside arrays of objects with arrays. https://github.com/simdjson/simdjson https://github.com/simdjson/simdjson
- tatersolid 7y ago
  JSON doesn’t handle int64 or explicit date-time data types, which makes it nearly as painful as CSV in practice when used for data integration. Both of those are explicit types present in nearly every real-world database schema. So you end up with many of the same problems as CSV. You basically get an application-specific JSON dialect to interpret. Header row or not? Is a long series of digits in quotes an int64? What happens when someone puts an ISO-formatted date string into a field that is supposed to be a simple string?
xupybd 7y ago
I don't want to but there is nothing that I've found in c# other than a helper tool that will only translate objects to CSV rows.
ungerik 7y ago
Those are the simple things to worry about, it can even get more complicated in the real world. So complicated that writing our own parser was the only way to go. So what are some of the more advanced challenges: * Banking systems that treat CSV export just as some kind of line based dump file with no regard of consistent formatting. If the banking backend was updated, the format might change within a file from one line to the next * Some misconfigured data dumping pipeline parses CSV the wrong way from another system and emits it in escaped form again in a different format. For instance putting a complete line escaped into a single field. Your parser has to detect that there is a CSV embedded within a CSV. * Dumping Pipeline treats \r\n as all kinds of silly lines and re-embedding those lines with quotes around them to look like real data * Inventing completely new specs of what CSV could mean * Use mainframe character-sets from the last millennium

303 points by ColinWright 7y ago

cjfd 7y ago

Well, considerations like these are typical for more or less any kind of software you will ever write. Because of this all but the very simplest software needs automated tests. But one may also note that your particular usage of CSV may be a bit simpler than supporting all of the possible complications. Especially if one is not reading CSVs that could come from anywhere. It depends, having dependencies is always a risk and a nuisance as well. The library you are using may have bugs, acquire bugs in the future, change in incompatible ways and so on.

papito 7y ago

Implementing your own CSV parser is one of the first lessons a green software engineer will learn in how to NOT try to re-invent the wheel. Still, please don't import packages with just one goddamned 3-line function in it.

qayxc 7y ago
It's what happens when DRY becomes dogma instead of a guideline... This leads to debacles like the npm left-pad & kik affairs and other scary shit like https://github.com/parro-it/awesome-micro-npm-packages https://github.com/parro-it/awesome-micro-npm-packages Yeah - have fun maintaining dependencies when every other module depends on one-liners that can be pulled or broken at random...

swiley 7y ago

>Some countries use a comma as decimal separator instead of a colon. I kind of wish we could all just pick something and stick with it.

ccccc0 7y ago
Like the metric system?
- BlueTemplar 7y ago
  The metric system, being French in origin, would certainly use the comma!
akie 7y ago
Yes, and we picked a comma. You?
- deleted 7y ago
  [deleted]
unwind 7y ago
That sentence must have a typo in it, right? Wikipedia [1] doesn't list any country that does use the colon (:) as the decimal separator, so possibly the period/dot (.) as used in most English-speaking countries, was meant? 1: https://en.wikipedia.org/wiki/Decimal_separator https://en.wikipedia.org/wiki/Decimal_separator
- deleted 7y ago
  [deleted]
- yoz-y 7y ago
  Yeah, I think what author meant "some countries don't use the period but a comma, thus hilarity ensues" (especially when your code respects the locale and the csv file is written in append mode)

joshlk 7y ago

The Pandas CSV reader (Python) is one of the best and most versatile implementations out there. Also very fast. https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_csv.html https://pandas.pydata.org/pandas-docs/stable/reference/api/p...

andreareina 7y ago
N.B. Floats won't rountdrip by default (you need to call `read_csv(..., float_format="round_trip")`)
nojito 7y ago
Disagree. Data.table’s fread is leagues ahead of pandas. https://www.rdocumentation.org/packages/data.table/versions/1.12.8/topics/fread https://www.rdocumentation.org/packages/data.table/versions/... Fread has automatic footer detection and has automatic skip logic to help parse out mangled headers in some csvs.
- vharuck 7y ago
  fread can also accept a shell command which it will run and use the result as input. I use that to grep the lines I want from files too big for RAM.
  - nojito 7y ago
    Same here! I also use fwrite to send it back to the shell to continue my processing pipeline. https://www.rdocumentation.org/packages/data.table/versions/1.12.8/topics/fwrite https://www.rdocumentation.org/packages/data.table/versions/... It's mind boggling how little fanfare data.table has despite it being the best way to handle tabular data in 2020.
- jsmith99 7y ago
  fread is amazing. Whenever I look at the docs and see all the options for dealing with nested lists (like the within-column-seperator) I always pray never to have need of such a thing.

barbegal 7y ago

I think you need to be careful with choosing to use CSV format. CSV works fine as long as you are not handling character stings. Character strings get messy as soon as they might introduce commas or characters outside of ASCII. For tabular data which has character strings I prefer to used the ODF format http://opendocumentformat.org/developers/ http://opendocumentformat.org/developers/ (.ods file extension) which has good import and export capabilities from Excel, or Google sheets. If the user needs the data in CSV for entry into another application then they can convert within Excel and handle any conversion problems themselves.

guerby 7y ago
Do you have a preferred library for python for example)? Debian packages python3-odf and https://github.com/eea/odfpy https://github.com/eea/odfpy seems to still have some activity (vs the other listed).
- barbegal 7y ago
  I don't. In the past I have just used an XML and a zip library like this article explains https://www.codeguru.com/csharp/csharp/cs_data/xml/article.php/c16311/How-to-Read-and-Write-ODFODS-Files-OpenDocument-Spreadsheets.htm https://www.codeguru.com/csharp/csharp/cs_data/xml/article.p...
- shrimp_emoji 7y ago
  https://docs.python.org/3/library/csv.html https://docs.python.org/3/library/csv.html ? :o

tester34 7y ago

doesn't seem insanely difficult dont be scared of coding guys, just differentiate between environments: do it for your 4fun project, not prod.

yoz-y 7y ago
The problem isn't really that it's difficult to solve. It's because it's impossible. There is no real formal spec. The RFC does not specify edge cases and nobody respects it anyways. "Generic" CSV parser is a pipe dream, don't try to do it. Now, if you define your own set of rules on what is and is not allowed then it's fine.
- Brian_K_White 7y ago
  Indeed it's a perfect example to illustrate the difference and the importance of an under-defined spec and a well defined one. Whatever you don't define, will be undefined. But you still somehow have to do something definite without a definition. The customer expects predictable output at the end, and until we have not only clairvoyance, but clairvoyance that can be built in a machine, you can't have predictable output without either predictable input, or a spec that actually provides an answer for any input.

DougBTX 7y ago

I think the most fun I had parsing a CSV file was when I found out it switched from UTF8 to UTF16 after several thousand lines, as differently encoded CSV files had been concatenated together directly into a single file...

tonyedgecombe 7y ago
I've seen some terrible CSV files where people tried to squeeze hierarchical data into a flat file format. Usually the customer can't provide any sort of schema or documentation because if they could they would have recognised CSV isn't appropriate. This means you are in an endless cycle of fixing the code as unexpected items keep appearing in the data.
- jointpdf 7y ago
  I tend to think of dataframes (R, Python pandas) as the standard way for representing and working with the data contained in a .csv. “List columns” (https://jennybc.github.io/purrr-tutorial/ls13_list-columns.html https://jennybc.github.io/purrr-tutorial/ls13_list-columns.h...) inside dataframes can actually be a convenient way to represent data that is mostly flat, but has some hierarchical data associates it (recent example: dataframe representing a corpus of documents, with a column containing a list of citations). So while .csv probably isn’t the best format to store hierarchical data, it’s fairly trivial to work with if encountered.
GolDDranks 7y ago
I have a similar story: parsing a CSV file that had an unexpected 0x1A byte in the middle. Turns out the file was concatenated together in multiple passes from multiple files using the COPY DOS command, which by default adds ASCII SUB to the end as an EOF marker: https://en.wikipedia.org/wiki/Substitute_character https://en.wikipedia.org/wiki/Substitute_character
- SanchoPanda 7y ago
  I have done this to myself in the past. Those were....dark days.

jtvjan 7y ago

Would've been a lot nicer if they used the record and unit separator instead of commas and newlines. There are characters made specifically for storing tables like this and they instead chose to reuse characters which might appear in regular text.

csunbird 7y ago

Reminds me of https://infiniteundo.com/post/25326999628/falsehoods-programmers-believe-about-time https://infiniteundo.com/post/25326999628/falsehoods-program...

tonyedgecombe 7y ago
It applies to everything, dates, floating point numbers, strings, etc. What we would consider the basics always turn out to be much deeper than they appear on the surface.
- atulatul 7y ago
  Dating advice: if you habitually, unironically, and without good cause use any dating format but that which is mandated by ISO 8601 we cannot be friends, let alone more. https://twitter.com/jwiechers/status/1205515440543424513 https://twitter.com/jwiechers/status/1205515440543424513 + the thread

cesaref 7y ago

One thing that has always puzzled me is why CSV exists, given the ASCII unit/record seperators appear to do exactly what the comma and newline characters are attempting to do with codes which could appear within records, and have been well defined since some point in the 60s. And here we are, 60 years later still struggling to work out where a record ends...

skrebbel 7y ago
Bacuse csv is human readable and human writeable
- nojito 7y ago
  So is tsv, but for some reason csv is more popular despite being the worst character to use.
  - hrktb 7y ago
    For a lot of people, meaningful space is a hard concept to understand. It's made worse by the number of applications where they will see spaces reformatted willy nilly (for instance web forms eating their line breaks. From there explaining there are spaces and tabs, and that both can look the same on screen but they are different is just asking for trouble outside of our circles.
    - mgkimsal 7y ago
      > is just asking for trouble outside of our circles it's asking for trouble in our circles too. Imagine getting a tab-sep file, opening it up in an editor and having it automatically convert to 4 spaces "because", then sending it back without checking something that doesn't look wrong.
      - SAI_Peregrinus 7y ago
        Or an input/editor where the `tab` key moves your focus from the text field to the next button. Like Slack. You can't just hit `tab` in Slack when typing a code snippet for something that needs them like a Makefile.

SanchoPanda 7y ago

To be fair, sometimes calling some giant java library or installing tons of dependencies is just not realistic. Use the library for the mission critical stuff or the automated reporting without a doubt, but it's important to be practical and thinking on your feet as well. In the meantime for quick analysis and testing 90% + can be accomplished with one line of (g)awk. awk -v FPAT='"[^"]*"|[^,]*' -v OFS='\t' '{$1=$1; print $0}' "$filename" More on FPAT [here](https://www.gnu.org/software/gawk/manual/gawk.html#Splitting-By-Content https://www.gnu.org/software/gawk/manual/gawk.html#Splitting...)

nicoburns 7y ago
You can also use xsv for this kind of analysis of CSV files. Which is a unix-style tool designed for CSVs and with a proper CSV parser. It's written in Rust so it's one binary - no runtime dependencies, and will happily chunk through multi-gigabyte files. https://github.com/BurntSushi/xsv https://github.com/BurntSushi/xsv

nbevans 7y ago

I prefer to use a SQLite database. And if a customer insists on CSV then I still use a SQLite database to import and/or export the CSV without them realising it. SQLite brings sanity to CSVs.

DarkWiiPlayer 7y ago
You still have to import the CSV though, so the problems are still there, you just don't have to deal with them after a successful import.
- nbevans 7y ago
  You misunderstand how enterprise integrations work. It's easy to tell a customer: "your CSV is not compatible with standard off the shelf tooling, indeed the most popular database engine in the world" than "your CSV doesn't seem to work with our software/library/<proprietary ETL framework>". The former makes it their problem, the latter will almost undoubtably make it your problem. In case you don't know: SQLite has a really well designed and very performant CSV import function. BTW did you really downvote me for that? Wow.

Hitton 7y ago

It was not emphasized in the article, but the quotes can be both single and double. That said, it's futile to try to write universal CSV parser. If you need to write your own, just make it possible to choose delimiter and type of quotes and call it a day. LibreOffice Calc does same.

oefrha 7y ago

> What if there is non ASCII data? > Just use utf8 right? But wait… > What if the program reading the CSV use an encoding depending on the locale? > A program can’t magically know what encoding a file is using. Some will use an encoding depending on the locale of the machine. Excel (for Mac at least) is a fucking pain in this regard. Just try this minimal UTF-8 example: tmpfile=$(mktemp /tmp/XXXXX.csv); echo '“,”' > $tmpfile; open -a 'Microsoft Excel' $tmpfile Hooray, you successfully opened ‚Äú,‚Äù I'm not even sure in which encoding e280 9c2c e280 9d corresponds to that (not the usual suspect cp1252, nor any code page in the cp1250 to cp1258 range; easy to confirm with iconv). One remedy is to add a BOM (U+FEFF) to the beginning of the file, but of course no one other than Microsoft (at least in my experience) uses this weird UTF-8 with BOM encoding (which the Unicode standard recommends against), so it breaks other programs correctly decoding UTF-8. This means I can never share a non-ASCII CSV file with non-technical people. Always have to convert to .xlsx although it's usually easier for me to generate CSV. Then .xlsx opens me up to formatting problems, like phone numbers being treated as natural numbers and automatically displayed in scientific notation... Which means ssconvert or other naive conversion tools aren't enough, I need to use a library like xlsxwriter. I'm not sure why it's so hard to just fucking ask when you don't know which encoding to use. (Plus it's not super hard to detect UTF-8. uchardet works just fine. Plus my locale is en_US.UTF-8, maybe that's a hint.)

jfk13 7y ago
> Hooray, you successfully opened > ‚Äú,‚Äù > I'm not even sure in which encoding e280 9c2c e280 9d corresponds to that That'll be MacRoman (not very surprisingly). https://en.wikipedia.org/wiki/Mac_OS_Roman https://en.wikipedia.org/wiki/Mac_OS_Roman
- oefrha 7y ago
  Ah, I thought it has to be something Microsoft. We can place the last modernization effort to this piece of code, then.
tasogare 7y ago
Yes, it’s a pain. However there is a solution to open an UTF-8 CSV file (with Data > From Text and a few clicks), but it’s true that googling something like that is out of the mind of most people.

larschdk 7y ago

Just today I needed to write a .csv file in Python on Windows and got a file with CR CR LF line endings by default (\r\r\n). That was using the standard 'import csv' in Python 3.

hprotagonist 7y ago
yeah you gotta pass with open(...,newline='') as f:
- JoBrad 7y ago
  I think the docs now include this.
  - hprotagonist 7y ago
    yes, they have for some time now i think. I wish it was better known, though.

arethuza 7y ago

A good one I saw recently was a text file that, even though it had a .csv file extension was actually using another character as a delimiter (in this case colons) but as it contained large amounts of textual data actually had a enough commas to be parsed as a CSV file. VS Code and a suitable extension handled this rather better than Excel.

tigerstripe 7y ago
Sounds like CSV in this case stood for Colon Separate Data
- fomine3 7y ago
  Further generalization: Character Separated Values

aww_dang 7y ago

Even when using libraries to read CSV data the need for sanitization isn't uncommon. Sure there's RFC4180, but variations are common. Mysterious records that are out of sync are typical. For my own sanity I like to verify that columns are of the right data type where possible. That said, I still prefer CSV over heavier formats (XML,JSON) where the data conforms.

polm23 7y ago

At a former job we processed files, many CSV, from hundreds of different sources and I was in charge of cleaning up the code that did this once. There were a few tiny binaries without source called csv2tsv and csv2tsv2. No documentation of any kind of course. csv2tsv just handled quoted fields and I was able to replace it with a few lines of Python without issue. The csv2tsv2 program was used for CSV files from exactly one company. We couldn't ask them for technical assistance - it's likely their systems had been written years ago and running without updates since then - so I tried to figure out what the binary was doing. The input file had some null characters in it, but they seemed to be used inconsistently, and I was never able to figure out what that binary was supposed to do. I left that binary alone and kept using it for a few years before a new guy joined and took over the system from me. I mentioned this weird old binary to him and over the course of a week he poked at it now and again before figuring out what it was doing. He used to work at a bank and realized it was using the same quoting method that some old data format they'd used there did - something about doubling characters and a few other tricks. There's nothing simple someone won't make complicated.

SanchoPanda 7y ago
You are thinking of these: https://www.w3.org/Tools/csvtotab-vv https://www.w3.org/Tools/csvtotab-vv Agreed, they are excellent.

t0astbread 7y ago

What about implementing something that works for your environment and making it fail-fast so that any unexpected input will just abort+log the current operation? You could then expand on that on-demand.

Brian_K_White 7y ago
This assumes you can recognize unexpected input.
- t0astbread 7y ago
  I assume unexpected input results in either an error (or exception, whatever your language calls it), a crash or a user complaint. I also assume it's unlikely for an input parser to introduce a security issue into your app if it's written in a safe language and your app has proper validation set up (i.e. don't trust input as soon as it passes the parser).

the_dripper 7y ago

i wanted to... but not anymore :/

franze 7y ago

I coded a few robots.txt parsers i.e.: (these are just the ones i cared to publish) https://github.com/franzenzenhofer/simple-functional-robots-txt-parser https://github.com/franzenzenhofer/simple-functional-robots-... https://github.com/franzenzenhofer/robotstxt https://github.com/franzenzenhofer/robotstxt and even though robots.txt seems like a very, very simple text based protocoll there are unanswered mysteries the biggest mystery is user agent groups and comments i.e.: User-agent: googlebot User-agent: bing User-agent: yandex Disallow: / so i am disallowing everything for google, bing, yandex; easy enough, but: User-agent: googlebot User-agent: yandex Disallow: / means that googlebot has no instructions, but yandex is disallowed all. if a whole line is commented out User-agent: googlebot #User-agent: bing User-agent: yandex Disallow: / is a commented out line a blank line or a non existing line? if it is interpreted as a blank linke, the disallow only counts for yandex, if the line is non existant, it counts for googlebot and yandex. i like simple things, but sometimes the complexity is between the lines.

ThreeFx 7y ago
What's the solution? Semantically it makes sense if it counts for both googlebot and yandex. Have you observed one/both behaviours?
- franze 7y ago
  i am consulting a lot of aggregators, and we coose to interpret sometimes as this, sometimes in the other scenario. the way i interpret the spec, it is not defined behaviour.
iams 7y ago
User-agent: googlebot #User-agent: bing User-agent: yandex Disallow: / why would it be interpreted as a blank line? If you remove everything after the #, that includes the new line characters at the end of the line. Leaving: User-agent: googlebot User-agent: yandex Disallow: /
- franze 7y ago

dehesa 7y ago

I can relate. I have been one of those who started writing a CSV library and went down the rabbit hole of edge cases and configurability. Looking back (and only thinking on productivity) my time would have been better spent doing something else. I did, however, learn quite a lot along the way: - how text is represented in a computer (and all its caveats), - how to identify and tune slow code, - how to vectorize code (thanks to Daniel Lemire's https://www.youtube.com/watch?v=wlvKAT7SZIQ https://www.youtube.com/watch?v=wlvKAT7SZIQ ), - how to encapsulate code better, - how to use some great CSV tools out there (thanks to Leon's https://github.com/secretGeek/awesomeCSV https://github.com/secretGeek/awesomeCSV ) - how to better manage and communicate an open source project. All in all, I am still not sure it was a mistake. Moreover when Swift didn't really had, at the time, a good Codable interface to CSV. Next time I encounter a similar problem I will probably just wrap a C library and create a good interface, though. In any case, if you are coding in Swift and find yourself in need of a CSV encoder/decoder, you might want to check https://github.com/dehesa/CodableCSV https://github.com/dehesa/CodableCSV

geraldbauer 7y ago

FYI: I've put together a collection of CSV specifications [1]. CSV Dialects / Flavors include CSV v1.0, CSV v1.1, CSV Strict, CSV <3 Numerics, CSV<3 JSON, CSV <3 YAML and some more. [1]: https://github.com/csvspecs https://github.com/csvspecs

geraldbauer 7y ago

CSV is awesome :-). Leon Bambrick curates an Awesome CSV Page [1] and another Awesome CSV Page [2] by my humble self. [1]: https://github.com/secretGeek/AwesomeCSV https://github.com/secretGeek/AwesomeCSV [2]: https://github.com/csvspecs/awesome-csv https://github.com/csvspecs/awesome-csv

JoBrad 7y ago
> When the final cockroach breathes her last breath, her dying act will be to scratch her date of death in a CSV file for posterity. CSV is the Keith Richards of file formats.
LeonB 7y ago
Thanks Gerald ;)

drej 7y ago

There's this system, which has become extremely popular for processing data (Apache Spark). And its default configuration cannot parse CSVs properly and can lead to pretty severe data loss. I wrote about this a while ago and it's still an issue. (Yes, we did lose data his way, it's not a theoretical exercise.) https://kokes.github.io/blog/2019/07/09/losing-data-apache-spark.html#csvs-dont-have-a-standard-per-se-but-lets-be-reasonable https://kokes.github.io/blog/2019/07/09/losing-data-apache-s...

mrweasel 7y ago

> Some countries use a comma as decimal separator instead of a colon. And some companies... like Klarna, fixes this wrongly. At least they did a few years back. We'd get a CSV file from them, containing payment information, but they ran into the issue that they really wanted to use comma for field separation, but also for krone/øre (decimal) separation. They ingenious solution: Fields are now separated by ", " that's a comma and a space. Pretty much no CSV parser will accept a multi character field separator. So many fields would just have a space prepended to whatever value was in that field and you'd get a new field, when the parser split the field containing the amount. So now you have say 5 headers but each row would have 6 fields, because the money amount became two fields. Being on the receiving end you now have to choose, do you want to strip spaces and reconstruct the amount field manually, or parse the CSV file yourself.

tjalfi 7y ago
If you use .NET then CsvHelper[0] supports multi character separators. [0] https://joshclose.github.io/CsvHelper/ https://joshclose.github.io/CsvHelper/

sharpercoder 7y ago

Can we somehow start to use the ascii 30 character aka the Record Separator (SP)? This would solve a lot of the problems!

wombatpm 7y ago
Not as fun as you might think. I had to parse and write inkjet control files for commercial printing. It used record separators, group separators, and unit separators. The problem? Records began WITH the RS character and there were NO line breaks. Ended up writing my own library https://github.com/Wombatpm/VIPNT https://github.com/Wombatpm/VIPNT

jmull 7y ago

> Do you really still want to roll your own code to handle CSV? No one really does, but we get backed into it due to these various issues. The general issue is that there is no real standard for csv, yet it's often used as the touch-point for integration of disparate systems! The integration goes something like this: 1. A and B decide to integrate! A will accept data from B. 2. A and B want to use a good interchange format, but after weeks of intense negotiations B can only get her IT department to agree to deliver csv (and feels lucky to get that much). 3. B uses excel to provide sample and test files, which are used by A for development or even in early stages of the live integration. Things seem to be going smoothly. 4. At some the export process at B changes, a new csv generator is used and now things break on A's side. Or something on the data input side changes at B so that new forms of data are now present that their csv generator does handle the way A expects, etc.

3pt14159 7y ago
Eh—I had to do it recently. There was a file format that I was dealing with that was basically a combination of CSV for data and Yaml for headers (and some other edge case stuff) and the Python CSV reader couldn't handle some edge case that we needed handling, mostly around reporting back to the file provider where they messed up one thing or another in the file. It didn't really take me that long. Lots of easy to write tests and the thing is processing a ton of data now for a well known stock exchange. Far easier than, say, the machine learning stuff I've written.
cryptonector 7y ago
The breakage can be subtle and unnoticed for a while, and it can be in the future. It sucks.

bproctor 7y ago

Well sure, if you're writing a generic CSV parser, it's very complicated. But if you know what your input looks like, CSV can be one of the simplest format to parse.

leokennis 7y ago

One small tip if you receive a CSV with a non-default separator (like ;): * Open it in a text exitor * Add "sep=;" on the first line (without the quotes) * Now at least Excel will open it as intended

BlueTemplar 7y ago
IIRC Excel will ask for the separator anyway when you open the file?
- hnick 7y ago
  It does not, at least not anymore.
- brewmarche 7y ago
  It does not and actually assumes locale-dependent separators (usually semicolons if your locale uses a comma as a decimal separator and comma otherwise). If you want to specify separators you have to go via Text Import.
lucb1e 7y ago
Calc just asks upon opening, no hidden tricks needed. Whenever I see people struggling with Excel and CSV, also when pasting CSV data into Excel, I can't stop wondering why people still bother with the Office suite. It has zero advantages aside from the cost of change (time spent on re-learning where things are), yet we teach it to kids in school and send them reminders that Microsoft benevolently gives them very steep discounts to reduce piracy (never mentioning that you could also just use this other free thing...), never mind the legislation saying governments and schools should use free software unless there is some strong and documented reason to divert from the rule.

ph4 7y ago

import csv

benbristow 7y ago

Whilst we're talking about libraries, had good experience with CsvHelper for C#/.NET. https://joshclose.github.io/CsvHelper/ https://joshclose.github.io/CsvHelper/ Documentation isn't 100% but it's a good tool. Been using it to work on a production 15+ column SQL->CSV mapping job with tens of thousands of records, working great.

bloak 7y ago

If you can't write a correct (according to RFC 4180) CSV parser in 15 lines of Perl then you're not a Real Programmer! (Yes, I understand that not all CSV files in the wild are correct according to RFC 4180. Yes, I also understand that only grey-beard loons still use Perl.) The strangest thing about CSV, I think, is that if there's only one field per record and the file ends with a CRLF then you can't tell whether there's a final record containing an empty field following that CRLF. It's probably best to assume there isn't.

nmz 7y ago
15? damn, that's 10 lines less than my awk csv parser. oh well...

redleggedfrog 7y ago

Oh .csv, how I love to loath thee. I have a funny story about .csv. Back in the day I was working on an integration with Fishbowl. Looking through their docs, the data format was your typical XML type stuff, until you got to the import requests. And there, lo and behold, was XML wrapping, you guessed it, .csv. I literally laughed out loud. https://www.fishbowlinventory.com/wiki/Fishbowl_Legacy_API#Import https://www.fishbowlinventory.com/wiki/Fishbowl_Legacy_API#I... Years later they released an updated API. Upon checking it I discovered it's now JSON wrapping .csv, so, you know, progress. https://www.fishbowlinventory.com/wiki/Fishbowl_API#Import https://www.fishbowlinventory.com/wiki/Fishbowl_API#Import

arethuza 7y ago
Paths in SVG are another example where the designers couldn't bring themselves to do everything strictly in XML and decided to use (space) delimited values: <path d="M 100 100 L 300 100 L 200 300 z" fill="red" stroke="blue" stroke-width="3" />
- th0ma5 7y ago
  This is sort of a quasi standard way of describing paths as drawing operations. You'll find this in working with fonts as well and old plotters sort of.

kyberias 7y ago

I'm currently implementing a REST API that returns CSV files.

ken 7y ago

I wrote my own CSV code. That's only about 10 issues, and I understand them all, and they're easy to handle, and to test (unlike, say, threading issues, or OS bugs that don't happen on my computer). That wasn't even close to being one of the more difficult parts of my program to write. I've had to do zero work to maintain it. I don't seem to have ever had any bugs filed against it. Are these supposed to be difficult? Sure, don't write your own encryption algorithm, and avoid touching threads if at all possible, but CSV? Sure, I wish I could have used a library. Unfortunately, once you start adding requirements, the options start to vanish. It needs to be a C (or C-callable) library. It needs to accept input as Unicode strings (lines), not just files on disk, or bytes. It needs to return results line-at-a-time. It needs to have a compatible license. It needs to support the same package manager as I'm using (or be a trivial to include, like a single file). It needs to be reliable (essentially bug-free, or at the very least currently maintained). It needs to be fairly efficient. What library supports all these? > Writing CSV code that works with files out there in the real world is a difficult task. The rabbit hole goes deep. Ruby CSV library is 2321 lines. I love Ruby, but that file looks a little nuts to me right now. The block comment at the top of csv.rb is over 500 lines long. The Ruby module tries to do everything. Just "def open(filename, mode="r", options)" and its docstring are 100 lines long! This class includes methods for auto-detecting the encoding, and auto-detecting common date formats, and some kind of system "for building Unix-like filters for CSV data". I don't put all of that into my CSV parser, because I want my code to be modular. I have features like data detectors and filters and open-file-by-filename in my program, too, but they're fully independent, not tied to any one file format. When I come up with a better way to detect dates in text, I don't want to tweak a 6-line regex in every file format I've written.

greggman3 7y ago
> I don't put all of that into my CSV parser, The art of not cluttering a library with too much stuff is a hard lesson to acquire. Most library authors try to throw in the kitchen sink. It's insane. I'm sure I was guilty of it in the past. For parser take a string. let me do the I/O outside. If you want to offer streams then just offer an abstract interface stream and then a few examples of implementing a stream but don't include them in your library. It's just clutter. This is one if largest problems with NPM. So many libraries try to do too much. Some come with a command line utility "just because"!??!?! It then adds more surface area, more dependencies, more churn as we have to update all those un-needed dependencies.

asadjb 7y ago

My initial comment was going to be that "The Python CSV library is only 448 lines (378 LOC) [1]". Only later did I realize that the bulk of the work is done by the C library, which is ~1500 LOC [2]. I guess parsing CSVs reliably is a reasonably difficult undertaking. [1] https://github.com/python/cpython/blob/3.8/Lib/csv.py https://github.com/python/cpython/blob/3.8/Lib/csv.py [2] https://github.com/python/cpython/blob/4a21e57fe55076c77b0ee454e1994ca544d09dc0/Modules/_csv.c https://github.com/python/cpython/blob/4a21e57fe55076c77b0ee...

JoBrad 7y ago
I have a base “library” I’ve used for several years that wraps the csv module (specifically DictReader/Writer) to handle encoding issues as well as data parsing. Have run into minor issues, but think that module alone is one of Python’s best use-cases.

mmm_grayons 7y ago

I wrote my own CSV code rarher thsn using a library because the input CSVs were produced by guys who didn't use a library. The columns had a fixed "real width" but also had a column that contained an indeterminate number of comma-separated, values. One can escape commas in CSV for just such a purpose, but these guys didn't. So, to deal with this issue quickly, I had to basically parse the line forwards until I hit the variable-length field, then backwards until I hit it as well. Then I could parse the field to determine how many values it had and extract them.

roelschroeven 7y ago

An additional problem is that Excel, the tool many people use to open CSV files, uses the computers regional settings for decimal separator and thousands separator. CSV files are used as an interchange format, so the format of the numbers in it does not depend on the region of that specific computer. You can tell Excel to always use '.' as a decimal separator, but then it also uses it for presenting numbers to the user. It boggles my mind that software like Excel doesn't understand the difference between reading/formatting numbers for human use and reading/formatting numbers primarily meant for talking to other software.

tlbsofware 7y ago
Excel will also take a number with a leading zero and drop it while it converts it from csv, for ex 01234 -> 1234, which is SUPER bad if you are dealing with any sort of IDs to invoices or work orders from outside the company
- sk5t 7y ago
  That is a good occasion to curse Excel (how does it know the number isn't octal, too?) and also curse the person or group that designed a textual coding system consisting of digits with leading zeros. Looking at you, National Drug Code.
- JoBrad 7y ago
  FWIW, the new importer for Excel lets you easily preview and adjust this behavior before importing the entire file.
sk5t 7y ago
Excel, while amazing on its own, is horrible for exchanging data in so many ways. I've maintained a policy at my company (life sciences) not to accept Excel files in any automated process; while parsing csv/tsv is a pain, xlsx/xlsb/etc. would amplify that pain in so many ways both predictable and surprising. https://www.washingtonpost.com/news/wonk/wp/2016/08/26/an-alarming-number-of-scientific-papers-contain-excel-errors/ https://www.washingtonpost.com/news/wonk/wp/2016/08/26/an-al...

rietta 7y ago

I write CSV code all the time in different languages. It's a fun challenge that incorporates several nuances. It has been a form of code kata for me over the last 20 years. Biggest lesson, one does not simply split a string. You have to scan through and tokenize following the state of a quoted field.

xamuel 7y ago

Funny story on this subject. When I was transitioning from academia to industry, I was afraid (having browsed hackernews so much) that I would be harshly judged for reinventing the wheel. So I removed the CSV library I had written from Github. Shortly thereafter, I was emailed by a well-known programmer (the creator of a fairly major language) asking why I had removed my CSV library, which he was using in one of his projects! Here's the library, btw: https://github.com/semitrivial/csv_parser https://github.com/semitrivial/csv_parser

beders 7y ago

My CSV story from last night: I had these 15MB+ excel sheets and was trying to open them with Apache POI. I gave that code a generous 2GB memory: GC overhead limit reached. Then I opened them in Excel, saved them as CSV, reached for a CSV-library and was done within seconds. Ok, well, the story is more about how parsing XML can ruin your day ;)

mike22 7y ago
Are you using POI’s eventmodel APIs to handle such files? The SAX model isn’t too bad for reading files.

QuadrupleA 7y ago

This scary stuff only applies if you need to read garbage CSVs from a million different sources. Even then, like ken says here, that's only like 10 issues to figure out. Exporting is likely the more common case and is pretty simple. Quote fields if they contain \r\n", I'd say relax, focus on your particular problem - if a simple 2-3 line export loop fits your use case and is simpler than a csv library (e.g. in C/C++ perhaps where dependencies are a pain) then why not. This fear mongering about "you can't possibly do this, the library writers are much smarter than you" can lead to it's own ridiculousness of hundreds of dependencies and ungodly waste of processor time, memory, build time, containerization hassle, etc. to solve what should be a simple problem.

crispyambulance 7y ago
> This scary stuff only applies if you need to read garbage CSVs from a million different sources. Even then, like ken says here, that's only like 10 issues to figure out. Well, garbage CSV's are garbage CSV's. They can be inconsistent, and if you're reading millions of them (OK, even hundreds) it can be a real pain-in-the-ass data janitorial task-- the equivalent of cleaning a public bathroom after homeless people have sprayed diarrhea all over the floors, walls, and ceiling. The hard part of dealing with the "10 issues" is you have to figure out which ones apply to which files (or even lines). A CSV linter is often helpful to diagnose, that would be the janitorial equivalent of rubber gloves and a bucket of soap, but it's still a lot of work. If you truly have millions of inconsistent files to deal with, the only way to keep sane is to categorize them by some parameters that make sense... size, date, keywords, even word-stats. Then, you can programmatically tackle each category. It's a messy problem no matter what and gets more ugly as the scale increases. And all of this is often before you even start what you actually need to do.
zelly 7y ago
Learned helplessness. You will never git gud if you're just copying and pasting libraries. I blame npm.
kristopolous 7y ago
There's a balance. Massaging wacky csv feeds to actually be parsable because someone wrote their own incorrectly is a real problem. Seen it dozens of times. I think the real problem is programming has become too easy. Almost nobody really has to worry about data structures, memory management, any of the hard problems that filter for competency. Some wacky insane stuff runs just fine on these 8-core multi-gigabyte ram computers we literally have in our pockets. A lot of it is on a VM inside of yet another VM, even that's fine. Just about every bad idea seems to work. These kinds of issues are to be expected.

OliverJones 7y ago

I totally agree with this. Writing your own csv code is the archetypal example, in computer programming, of reinventing the flat tire. I've found that every major language has at least one reusable component / module / library / dll to to this. They're maintained by people who have lots more patience than I do.

pinopinopino 7y ago

I did this just for fun to learn a parsing library I was using. It didn't deal with utf-8 though, but even that is manageable. The complexity with csv is that it is a family of formats with small changes. But if you make your parser configurable, you are set.

pinopinopino 7y ago
If you want to do something really hard, try to parse PDF, that is sheer insanity.

GuB-42 7y ago

I disagree with the article, I'd say writing your own CSV code is the second best option, behind not using CSV at all. The reasons are: - CSV is really simple. A complete RFC4180 parser shouldn't take more than 100 lines of readable code to implement. Even less so for the writer. So much that most of the code is likely to be interface code (file I/O, matching your internal data structures, etc...) rather than parsing. And chances are that a library won't even make your code shorter. - As mentioned in the article, CSV is not well defined. I've seen all sorts of weirdness: semicolons vs colons, LF vs CRLF, simple vs double quotes, backslash escaping, comments, floating point numbers with comas instead of points. What your library understands as a CSV may not be the files you have to work with. I'd rather have a simple piece of code that correspond to what I am given rather than a big library that uses AI techniques to try to guess the intent of whatever wrote this file. There are cases where it would be foolish not to use a library. I will never write a XML parser for instance, it is a well defined and complex format. JSON is borderline but I still won't write my own parser. CSV is essentially a proprietary format, so it is fitting to use a proprietary parser/writer.

lxe 7y ago

Just because all kinds of inputs exist, doesn't mean you should support handling them all.

jackfoxy 7y ago

And this is just the tip of the iceberg for data importation. Managers and customer facing folks want to say Can't you just use off the shelf software for this? If your data importation needs are at all non-trivial, the problems tend to come in 2 categories. 1) Error reporting from off the shelf software is inadequate. If you are lucky, it will consistently point you to the correct row number. It's usually lacking any additional info. 2) If your application is at all non-trivial, you have specific importation needs, not easily covered by 3rd party generic solutions. For instance you want to import well formed tables with primary keys. Lots of solutions claim to handle this case, but you usually run into problems under category (1). A programmer can overcome these difficulties and muddle through 3rd party solutions, but you probably don't want to hand this over to users. It just won't be a good experience for them. I completed an importation library tailored for our specific needs and handling the real problems we have experience over many years. The front-end project to make this available to users started and stopped and changed hands over months. We are finally nearing deployment, and so far the users we have shown it to like it.

coliveira 7y ago

CSV is not a portable format across applications. That's it. You should use it only in your own application or in connection with applications that you know. The big problem is to think that you can handle a generic CSV file, because there is no such thing.

mystickphoenix 7y ago

Fantastic article! It's missing: - "what if the quotes are smart quotes?" - This applies to both quotes inside a field and quotes wrapping a field - "what if the file doesn't have a header?" - "what if a logical record is spread over multiple lines?" - Yeah... so, you have line 23 that has the majority of the data in the expected columns, line 24 has additional data in columns 23, 25, and 28 that's supposed to be appended onto the previous line's data that also appears in columns 23, 25, and 28. Line 25, same thing, but line 26 starts a new logical record. Seen all three above in the wild and Python's (pretty awesome) CSV code didn't handle them. Queue the custom code that wraps Python's core CSV parsing.

wdavidw 7y ago

I am the lead developer behind the Node.js CSV parser [0]. The project started in 2010, an hour before me leaving work for the weekend. It ended up being a long, very long journey that isn't over yet. The project grew incorporating many features I would never have expected, fixing corner cases the several hundred tests were not covering and teaching me a lot about coding and project handling. The bottom line, writing a correct CSV parser takes a little bit more than implementing an advanced string spliting function. [0] https://csv.js.org/ https://csv.js.org/

Neil44 7y ago

Me too. Everything was great until Mr O’Connor came along.

hermitcrab 7y ago

I wrote my own CSV parser for https://www.easydatatransform.com https://www.easydatatransform.com. I ran into some cases where it wasn't clear what to do. For example where you had some whitespace outside of quotes. So I checked what Excel and Numbers (the Mac spreadsheet) do. They parse the same string differently...

freefriedrice 7y ago

After working int he world of CAD tools for decades, I cannot tell you the number of times I've seen a younger engineer try to write a CSV parser only to fall into a circular hacking deathspiral. You can't just use regex/split to handle CSV, unless you have significant field cleaning BEFORE converting to csv. In reality you need lexical analysis and grammatical rules to parse any string of symbols. This is often always overlooked by naive implementations. I take issue with OP's claim that RFC4180 is not well-defined, but almost all of the cases the OP listed are literally in the spec.

hackandtrip 7y ago

Some fast implementations in C++? In particular, I tried in the last weeks to work on a OpenMP / MPI driven CSV parser, but surely accounting for new lines inside quotes can be a pain the ass, I'm wondering if there's a go-to implementation or model to implement that.

dang 7y ago

Discussed at the time: https://news.ycombinator.com/item?id=7796268 https://news.ycombinator.com/item?id=7796268

nly 7y ago

Sometimes the best thing you do is learn at least one parsing framework so that you can write your own parsers. The advantage of this is you know exactly what the behavior will be. Here's a CSV parser in ~40 lines of C++ using Boost Spirit that handles all kinds of weird cases: https://gcc.godbolt.org/z/KGUHXo https://gcc.godbolt.org/z/KGUHXo It's easy to customize, declarative, and compiles down to about ~15KB.

neilv 7y ago

I wrote a CSV-reading library 16 years ago that I'm pleasantly surprised handled most of the points of trickiness the article mentions. https://www.neilvandyke.org/racket/csv-reading/ https://www.neilvandyke.org/racket/csv-reading/ The documentation section "Reader Specs" gives a good idea of the different variations it intends to handle. I was working from a sense of variations I'd seen myself, and extrapolating. One thing I also did, which I didn't see in the article, was to support comments. Unfortunately, at the time I wrote it, there was no standard Unicode support for Scheme, so I didn't get into that, but just used the character abstraction (which might or might not involve parsing of a particular non-ASCII encoding). Fortunately, AFAIK, it's worked for people, since fixing/extending it at this point would mean relearning the code. :)

nojvek 7y ago

ndjson(new line delimited json) is what we should use as a human friendly and machine friendly alternative to csv. json is a universal format. Works in every major language. It's spec is a state machine defining what to do with every possible encountered byte. https://www.json.org/json-en.html https://www.json.org/json-en.html SIMDJSON parses json at RAM speeds (~3Ghz). CSV should die. It's a terrible format. Just send arrays of ndjson instead. You'll probably have a few more " and [, but hey, you can deeply nest arrays inside arrays of objects with arrays. https://github.com/simdjson/simdjson https://github.com/simdjson/simdjson

tatersolid 7y ago
JSON doesn’t handle int64 or explicit date-time data types, which makes it nearly as painful as CSV in practice when used for data integration. Both of those are explicit types present in nearly every real-world database schema. So you end up with many of the same problems as CSV. You basically get an application-specific JSON dialect to interpret. Header row or not? Is a long series of digits in quotes an int64? What happens when someone puts an ISO-formatted date string into a field that is supposed to be a simple string?

xupybd 7y ago

I don't want to but there is nothing that I've found in c# other than a helper tool that will only translate objects to CSV rows.

ungerik 7y ago

Those are the simple things to worry about, it can even get more complicated in the real world. So complicated that writing our own parser was the only way to go. So what are some of the more advanced challenges: * Banking systems that treat CSV export just as some kind of line based dump file with no regard of consistent formatting. If the banking backend was updated, the format might change within a file from one line to the next * Some misconfigured data dumping pipeline parses CSV the wrong way from another system and emits it in escaped form again in a different format. For instance putting a complete line escaped into a single field. Your parser has to detect that there is a CSV embedded within a CSV. * Dumping Pipeline treats \r\n as all kinds of silly lines and re-embedding those lines with quotes around them to look like real data * Inventing completely new specs of what CSV could mean * Use mainframe character-sets from the last millennium