So you want to write your own CSV code (2014)
thomasburette.com
thomasburette.com
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.
2) You took on exactly the responsibilities the article author said you'd have to take on. Based on the screenshots for your "Strukt" application, it looks like the user controls configuration/validation for parsing CSV and they will fix any issues with parsing the CSV file themselves.
> If a supplied CSV is arbitrary, the only real way to make sure the data is correct is for an user to check it and eventually specify the delimiter, quoting rule,… Barring that you may end up with a error or worse silently corrupted data.
3) I think the better question is, how do you handle malformed CSV? Is your error correction as robust as Ruby's? Do you handle malformed CSV the same way as other libraries? If it's handled differently by different parsers, you can run into issues like Apple recently did [0]
Yep, and having been there, I’m reporting: it’s not nearly as bad as this post makes it sound.
I’ve written web apps, too, and (for example) CSS is 100 times worse. Nobody is suggesting every website should use only browser default styles, though.
> I think the better question is, how do you handle malformed CSV?
What does “malformed” mean? The good/bad thing about CSV is that virtually every text file is valid! The only malformation I can think of is an open quote with no matching close quote (so the entire rest of the document is one value). My implementation is streaming, so there’s no great way to flag it: any future data could have the other quote!
We show the data as it’s parsed, so it should be obvious to the user what is going on, and where.
There are areas of computing that are too complex. I’m usually the first to complain about such things. I really don’t think CSV parsing is one of them.
- if your file starts with \x49\x44 ("ID"), Excel will interpret the file as their symbolic link .SLK format. So if you're writing files, the ID should be wrapped in double quotes even if it isn't necessary according to RFC4180
- Excel will proactively try to "evaluate" fields that start with \x3d ("="). You can see this in action with the sample file
1,2,3
=SUM(A1:C1)
CSV parsing / writing certainly isn't going to be a value driver for most companies (if you're supporting user imports, you really care about XLSX/XLSB/XLS files and Google Sheets import), but it's not a trivial problem.
An example of improper csv code is this:
hello,world,"And this, contains "bad quotes"...",1,2,3
Lest you think this is made up, I ran across this when someone cut and paste Excel into a text field.
I also have seen batch processing of user files break hard when a quote issue like this caused a hand-rolled CSV parser to conclude that half the file was a single very long field.
> Easy right? You can write the code yourself in just a few lines.
My take-away from the post is: if you are parsing arbitrary CSV files, you need to make parsing configurable because there's no one, true CSV format. If you are writing CSV files, you may need to escape your fields in a weird, outdated manner.
P.S. By "malformed", I meant whether the 2D matrix of byte arrays is read exactly as intended. It could be caused by an open quote, but it could be incorrect escaping or inconsistent delimiters. Since there's no inline schema saying which CSV parsing configuration is being used, you must ask the user to configure the CSV parser and validate the output.
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.
I always thought people just used libcsv in cases like this. If that's missing something though, then I believe my Rust csv library[1] would be good enough if I added a C API, which would be pretty easy and I would be happy to do it if it really filled a niche. I think the only hurdle at that point would be whether you could tolerate a Rust compiler in your build dependencies.
FYI: CsvReader [1] is a more modern alternative to the old standard csv.rb library in ruby.
Here is the entire logic of the CSV parser (test code is in a separate path) https://gitbox.apache.org/repos/asf?p=commons-csv.git;a=tree...
Were I to use the JVM for my background processing needs (the thought has occurred to me!), I'd definitely use Clojure -- 'data.csv' is less than 150 lines, including comments, for both the parser and formatter!
https://adoptopenjdk.net/faq.html
A lot of people have been digging into the general insanity of Java licensing since Java 8's divergence. Here is a good overview https://medium.com/@javachampions/java-is-still-free-2-0-0-6...
- The JVM historically has included the kitchen sink (Java 9 started addressing this).
- People's impressions that it is slow or a memory hog. Edit: (Looking at you Eclipse/Intellij/Glassfish/JBoss/Atlassian)
- Experiences with bad code bases due to it's ubiquity and age. Old code bases tend to evolve in interesting ways.
- It's not the new shiny thing.
I have meet plenty of developers who dismiss Java out of hand. I've also seen DBA's dismiss FKs. I don't think how good/bad it is has anything to do with people's dislike of it, more that there is a certain vocal group that dislike it for being the "enterprise" solution.
Java and the JVM are mature, battle-tested, widely used technologies in the industry, easy to hire for and with multitude of libraries for almost whatever you want to do.
It may not be the latest shiny new thing, but that's different. Pariah means an outcast. Neither Java nor the JVM are outcasts.
On the other hand writing a custom parser using some off-the-shelf parser combinator library is easy and straightforward (ie I know how much time it will take to implement one and what exactly it does).
Sometimes having multiple implementations of the same is better than a single generalised implementation struggling from combinatorial explosion.
And here we are, 60 years later still struggling to work out where a record ends...
And if they had that key on their keyboard then you'd have the comma problems all over again: What if a ASCII record seperator shows up in the field?
I prefer the | pipe character as a delimiter - easy to see, not part of common speech or titles, and enterable via keyboard. Yes, it can exist in the field but less likely.
I think it's like cryptography. Why bother to roll your own when there are people who are cleverer (certainly than me. I don't know about you) who've already put a lot of effort in to this, so just use one of the well tested standard libraries and don't mess with it
point is, being not human readable and having no keyboard key, you can reasonably expect those special separators not to.
I suppose I'd consider such special-separated files (26 to 29 from memory?) to be machine generated and machine readable only, not intended as human readable without a bit of extra software or eg. a special emacs mode
Anyone can edit a csv. I often have. An important feature.
The only real stumper in the CSV format is why they didn't use \ as the escape in strings like everyone else. Probably some good reason.
OTOH the ability to just look at it... that I've found very valuable and agree there.
As much as I wish that beauty or usability was the primary indicator of market success, the simpler explanation that explains all of these (including CSV) is: Microsoft Office supported them.
Microsoft’s binary Excel format is not XML -- you’re thinking of its replacement. It was COM/OLE or some such.
It's basically use the right tool for the job. For pixels that's a binary format. For mostly text, text itself works pretty well.
The whole article pretty well rams home the fact there is no standard B because there is no standard which is adhered to.
But you’re correct that I was using the word “standard” imprecisely to mean something more like “file extension” rather than an IETF RFC.
Do people usually compose CSV data by hand? I thought they would use a program like Excel, enter or generate their data in separate cells and then save the file as a CSV. There's no reason why a program like Excel (or any other program for that matter) couldn't use the record separator instead of a comma as a delimiter when generating or consuming such files.
I do a lot of troubleshooting using CLI tools like grep and wc.
Also, is not necessary to use excel, and a lot of the issues brought up in the article wouldn't be the case with a different delimiter character. Editors could easily be updated to make entering that delimiter character easier to enter by mapping it to a key like tab whenever a file like that is opened.
It just seems like a problem that could be solved, but can't be due to inertia.
Markdown (the original one by John Gruber) does not include any syntax for tables. Other implementations have included it, but there is no standard. As far as I know, using the pipe character to separate columns is indeed quite common, but it is not the only way.
In a business context, this happens far more often than you may expect. Sure you can build a custom platform to validate and make people connect to a form connecting to a database - but Excel is a great user interface and everyone knows excel.
A funny incident - we were struggling to build a complex workflow in KNIME where at some points we need user input. Nothing out of the box was great - tools either assume a dashboard paradigm or a data flow paradigm - nothing In between.
One of our creative folks came up with the solution of writing to a CSV and getting KNIME to open it in excel. The user would make changes and save, close excel and continue the workflow in KNIME. Even completely non technical people got it.
It does this without any notification or warning when you save.
https://genomebiology.biomedcentral.com/articles/10.1186/s13...:
“A programmatic scan of leading genomics journals reveals that approximately one-fifth of papers with supplementary Excel gene lists contain erroneous gene name conversions.”
That paper is from 2016, at least 12 years after the problem was first identified.
If you intended the data to be a string value, then it should have been enclosed in quotes.
Adding a ' before it should work, but for data extracted from arbitrary systems you'll have few guarantees of the sort. Formatting the cells in Excel to display the full value instead of the truncated value also works from memory, but you won't always know ahead of time if this happened later in the file.
In our case it was often mailing identifier barcodes so any loss of precision made them entirely useless.
That's not part of any CSV specification I've seen, including RFC4180.
Fun fact, Excel will truncate numbers beyond 15 digits unless prefixed with a single quote mark.
I do a fair amount of work with companies that do "EDI" over CSV (or worse CSV-like - think 2 CSVs jammed together with different formats, no headers, no support for escaping or quoting) and fixed width documents. I can absolutely assure you that humans do open these files in notepad far more often than I'd like.
Often one of the main reasons they don't use things like X12, ASCII separators, etc. is because a "human needs to open it at some point" was a prevailing business decision some number of years ago (think "what happens if the IT system fails? how can we still ship stuff even in a complete emergency") and now it's baked into their documented process so deeply its like shouting into the wind to alter things. Third party warehouses are the worst at this.
Modifying Csvs with notepad is rife. I'd wager more than using any one particular code editor.
A few years back while working on something that unavoidably used large quantities of CSV data we would sternly warn people not to use Excel, but people still did it ("But I just looked at it!").
They do. People write code to create all sorts of bastardized abominations of “csv” or tab delim or whatever. It’s why the featured article gets reposted every few years. You can define a standard for csv files, but then Excel does it’s own thing and here we are.
Given the inability to standardise on line endings "\n" "\r\n" or "\r" I don't think we can standardise on using unit or record separators
The Library of Congress lists CSV files as one of its preferred formats for archival datasets: https://www.loc.gov/preservation/resources/rfs/data.html
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.
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.
One actually looks like a table the other is a garbled mess.
But users can see the commas in CSV, and they can trivially enter them. Yeah, the result is messy.
The lesson here is that the separator control characters should have been visual and had a visual indicator on keyboard keycaps to indicate how to enter them. Because they aren't and didn't, they are essentially useless for text.
EDIT: I do happen to know how to enter these on *nix systems. The ascii(7) man page tells you how:
034 28 1C FS (file separator) 134 92 5C \ '\\'
035 29 1D GS (group separator) 135 93 5D ]
036 30 1E RS (record separator) 136 94 5E ^
037 31 1F US (unit separator) 137 95 5F _
- non printable character - no single keyboard key - intended for systems, not people - etc
A lot of the crufty edge cases are artifacts of whatever people had to deal with in 1995 to get data into spreadsheets. We all got used to pushing data around with CSV because the people consuming the data needed it.
As a fun exercise write a CSV-like spec but using those ASCII chars.
table = [row.split(unit_sep) for row in data.split(rec_sep)]
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.
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.
In my case (I do a LOT of .CSV work) though none of these problems exist. I haven't reached for Python's .CSV library in years and neither have my co-workers. We simply loop through the file, split the strings on commas, and have a few if statements to parse on a situation by situation basis. Extremely old school, but it works very well and is easier than dealing with the objects that are generated from using the library. I realize this probably doesn't work with all use cases on HN though, or even most.
My ideal language has direct support for making file I/O, dictionaries, sets, whatever all in core and easy to use. This keeps me from having to write my own helper classes and modules or bring in 3rd party libraries. Other languages like to have a community package for everything to where you have 9 options for everything. This seems to be common in JS and Perl and is certainly valid, but I'm not a big fan. Python hits a sweet spot for me.
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.
Generating a valid CSV string that Excel wont complain about is approximately 2 lines of LINQ if you have a clean starting point (i.e. a list of class instances with strongly-typed members).
For cases where I have to read garbage CSV, I first go to the source and determine if we can't get a better export or interface into the data. Most shit CSV I see is a consequence of someone hacking together a SQL query to produce the output. I'd be inclined to just request a database export at that point. Obviously not always an option, but you should always insist on having the best representation of the business data before you start banging your head against the wall on parsing it.
Lots of pitfalls are easy to avoid if you already know them, which it sounds like you do. The author is saying that most engineers aren't aware of these issues, and the author is correct.
Writing a for loop is easy, but so is using a language's built in CSV writer.
https://pandas.pydata.org/pandas-docs/stable/reference/api/p...
And I've used them all.
And the figuring out of encoding is also complex [2].
Once again, this is just to demonstrate that writing your own CSV parser from scratch is a total waste of time. Just use tools provided by your language. Many languages provide native support for CSV parsing. For example see docs for Microsoft C# [3].
----------
[1] https://github.com/pandas-dev/pandas/blob/v1.0.3/pandas/io/p...
[2] https://github.com/pandas-dev/pandas/blob/v1.0.3/pandas/io/p...
[3] https://docs.microsoft.com/en-us/dotnet/csharp/programming-g...
If you don’t want to use third-party libraries then TextFieldParser[0] is part of the .NET framework.
I use CsvHelper[1] in my projects. It can do pretty much anything although the newer versions have some dependencies.
[0] https://docs.microsoft.com/en-us/dotnet/api/microsoft.visual...
Data.table’s fread is leagues ahead of pandas.
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.
I also use fwrite to send it back to the shell to continue my processing pipeline.
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.
Getting the parts that work with CSVs to "just work" when presented with an arbitrary CSV file has been super interesting - even with Pandas at our disposal.
https://github.com/bluelabsio/records-mover/blob/d18ec02fdf5...
I wrote up some of the experience here:
https://github.com/bluelabsio/records-mover/blob/master/docs...
import csv
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#I...
Years later they released an updated API. Upon checking it I discovered it's now JSON wrapping .csv, so, you know, progress.
<path d="M 100 100 L 300 100 L 200 300 z"
fill="red" stroke="blue" stroke-width="3" />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.
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.
Here's the library, btw: https://github.com/semitrivial/csv_parser
Proper CSV handling is simple, just don't try to take shortcuts to make it even simpler than it is. There just happens to be a lot of CSV around that is too broken to be read without a bit of handholding, but no gold standard library will be able take that problem out of your hands (an argument might be made for libraries on the producer side of files). That little list of pitfalls in the article would be a good guideline for "things you should know before" but it doesn't make a particularly compelling point against rolling your own.
Personally I'd even argue that CSV is the only format where, on the consumer side, rolling your own is actually advisable. Because every beyond-corner-case is different and the format is such fertile soil for imperfect as-hoc processing that happens outside of your code.
I'll take your encoding work if you take my timezone work.
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.
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.
Agreed, they are excellent.
[1]: https://github.com/secretGeek/AwesomeCSV [2]: https://github.com/csvspecs/awesome-csv
CSV is the Keith Richards of file formats.
- 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 ),
- how to encapsulate code better,
- how to use some great CSV tools out there (thanks to Leon's 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
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 [2] https://github.com/python/cpython/blob/4a21e57fe55076c77b0ee...
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.
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.
https://www.washingtonpost.com/news/wonk/wp/2016/08/26/an-al...
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/ (.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.
Debian packages python3-odf and https://github.com/eea/odfpy seems to still have some activity (vs the other listed).
https://kokes.github.io/blog/2019/07/09/losing-data-apache-s...
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 ;)
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.
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: /
User-agent: googlebot
User-agent: yandex
Disallow: /
if a whole line is commented out
User-agent: googlebot
#User-agent: bing
User-agent: yandex
Disallow: /
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.
User-agent: googlebot
#User-agent: bing
User-agent: yandex
Disallow: /
User-agent: googlebot
User-agent: yandex
Disallow: / User-agent: googlebot
User-agent: bing #behaved-badly
User-agent: yandex
Disallow: /
User-agent: googlebot
User-agent: bing User-agent: yandex
Disallow: /
Edit: I don't know how to escape in HN formatting. Obviously there are italics where literal asterisks should be.
*** You can just use three asterisks. ***
Unfortunately you need something after them though. ***
*(`^#.$`), and others will interpret it as you have (`^#.?\n`)*
Have you observed one/both behaviours?
> 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
“,”
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.)
> “,”
> I'm not even sure in which encoding e280 9c2c e280 9d corresponds to that
That'll be MacRoman (not very surprisingly).
We can place the last modernization effort to this piece of code, then.
I sincerely do not think the solution is instruct receiving person to install an even less familiar Office alternative.
In recent versions there finally seems to just be a new "UTF-8 CSV" option in the Save As dialog.
Still, please don't import packages with just one goddamned 3-line function in it.
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
Yeah - have fun maintaining dependencies when every other module depends on one-liners that can be pulled or broken at random...
* Open it in a text exitor
* Add "sep=;" on the first line (without the quotes)
* Now at least Excel will open it as intended
If you want to specify separators you have to go via Text Import.
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"
It's written in Rust so it's one binary - no runtime dependencies, and will happily chunk through multi-gigabyte files.
https://twitter.com/jwiechers/status/1205515440543424513 + the thread
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.
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.
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. :)
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
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.
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.
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.
(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.
with open(...,newline='') as f: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
It's easy to customize, declarative, and compiles down to about ~15KB.
dont be scared of coding guys, just differentiate between environments: do it for your 4fun project, not prod.
Now, if you define your own set of rules on what is and is not allowed then it's fine.
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.
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.
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
VS Code and a suitable extension handled this rather better than Excel.
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
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.
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?
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.
I kind of wish we could all just pick something and stick with it.
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?
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.