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.
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?
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)
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.
Additionally, RStudio has a built in GUI widget for importing .csv (and other) files: https://support.rstudio.com/hc/en-us/articles/218611977-Impo.... I find it quite useful for breezing through the common gotchas with importing data, and it’s probably the easiest way for inexperienced programmers (e.g. business analyst types used to working in Excel) to work with .csv.
Wow! That's a nifty little library. Just looking at the amount of options going into the main parser routine is mind blowing [1].
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].
In Rubyland there's the versatile CsvReader library / gem [1] that supports HXL, ARFF, CVY and more "exotic" or modern flavors / dialects among many others.
I wrote a Python utility to help our folks at our analytics company load/unload/move data among CSV files and SQL databases.
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.
In what ways is it better than the csv module in the Python Standard Library? Or in other words, in which cases is it worth the effort to install pandas just for reading CSV files if your project otherwise doesn't use pandas?
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/ (.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.
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.
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.
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...
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.
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.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.
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
When you hit that kind of data which isn't quite a corner case anymore but already in the realm of subtly broken, you'll actually be happy to have written your own ad-hoc CSV implementation. Figuring out that problem will be much harder if, because programming is scary, you are using a black box library.
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.
Try a UTF8 CSV with Windows-1252 free-form text in some fields. I believe dealing with character encoding is the most frustrating thing a developer can do.
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.
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.
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.
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...
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.
> 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.
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.
I think we have different definitions of human readable then. To me, human readable means if I print it in a printer, it loses no fidelity. What you describe is what I would call machine-readable. It is able to be imported into a program. The same that a binary separator would also let it be imported.
I think that stems from the fact that no software ever properly supported them, they are non-printable characters so you can't see them easily. Whereas with CSV you can fire up notepad.exe and explore the data.
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
Have you ever seen the ASCII separator characters used as they were intended? I don't think I have. It's obvious the problem they were trying to solve, but it was too little too late. It doesn't help that they're control characters that aren't meant to be displayed so they're practically invisible.
Easy: CSVs are human readable and writeable and humans tend to have a comma sign on their keyboard, while they don't have the ASCII record seperator visible (teaching them to use eg Alt + 30 will not work).
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?
But humans rarely use notepad these days. They use code editors like Coda or VSCode at the very least which have all kinds of advanced features. Surely, those can include support for ASCII separators?
Or they use Excel, which can introduce its own entertaining errors.
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!").
> But humans rarely use notepad these days. They use code editors like Coda or VSCode at the very least which have all kinds of advanced features. Surely, those can include support for ASCII separators?
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.
Let's say we have a product master. Something looks fishy and we want a brand person or product supply person to check... Push a CSV and it's trivially easy for them to open in XL and check, make corrections and send.
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.
We had to ban our non-technical users from opening CSVs in Excel because it would strip leading zeros and do other exciting things like convert large numbers to 1.23123E+20 :(
It does this without any notification or warning when you save.
Excel can only display CSV files. I had to ban myself from editing files in Excel because it’s atrocious at it. It also likes to somewhat change decimal-seperators (again, without any warning).
“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.
That behavior is technically correct because those are all valid representations of that value, e.g., 007449938 = 7449938 = 7.449938E6. Pretty much any plain text (or binary) data format will have this same issue, unless it supports additional metadata for display purposes.
If you intended the data to be a string value, then it should have been enclosed in quotes.
What if the value is an ID rather than an integer? CSV doesn't specify whether the data type is a string or a number. The schema needs to be inferred by the parser/user.
Your assertion sounded incorrect so I tested it. Quoting large numbers does not preserve them on a save.
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.
Kill me...i've dealt with flat file ETL for ~9 years now, with the first 2 pretty much dedicated to it. I hate hate hate Excel for no reason other than that. It has wasted so many hours due to customers messing up their identifiers by opening up their feeds in Excel prior to sending, and accidentally transforming like above, then having to fix changing customer identifiers used throughout the system once they figure out what happened.
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
When considering standard A that can only work with machine generated and machine readable files and standard B that can work with all those files plus human-readable/editable files, I don’t find it surprising at all that standard B wins in the market.
It happens. XPM/PBM/XBM lost to TIFF (and later PNG). Postscript lost to PDF. OpenOffice.org XML couldn't displace Excel's Binary File Format.
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.
I don't know about XPM/PBM/XBM lost but AIUI PDF is just a subset of postscript with compression. If you think MS's binary excel file format is all that secret, I suggest you try unzipping it - it is no more than compressed XML. I have rescued parts of corrupt excel and word files by doing just that.
MS completely redesigned their binary formats a few years ago. You're right about the current format, but the earlier one was completely undecipherable. Even the current XML has quirks in it to be compatible with the older format.
It was reversible enough to write the Apache POI library. I maintain code that uses the HSSF streaming API portion of the library, which mainly gives you low-level access to the structures in XLS files. The OO.org folks made some very nice documentation: http://www.openoffice.org/sc/excelfileformat.pdf
Binary formats win I think when the ASCII format is significantly greater in storage size and parsing speed. XPM/PBM/XBM are enormous compared to any binary image format. And remember these images used to get put on 1.4meg floppies and sent over 2400baud modems.
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.
If someone copy-pastes a line from one CSV to a string within a field of another, the second will have record separators from the first within a single field.
> CSVs are human readable and writeable and humans tend to have a comma sign on their keyboard
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 quite a bit for GitHub markdown tables, for making small annotations for ML, for python parsing, etc. Very common and would be annoying too open up excel every time I needed some csv.
I thought markdown tables used the pipe character as the delimiter between table columns.
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.
> I thought markdown tables used the pipe character as the delimiter between table columns.
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.
If the ASCII separators were supported regularly in text editors since the early days then it would remove 90% of the problems.
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.
But if it _might_ turn up, you have to assume it will and deal with it, and then you're into the exact same problem as before, but with something even less 'standard' than before.
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
The TSV (tab-separated values) format is supported by a lot of software (Google Sheets, for instance) and solves the "comma in the field" problem quite nicely. Tabs can easily be typed by a human, but they don't usually occur within a one-line string, so they don't cause the same problems as CSVs do.
TSV, CSV, PSV etc. all share the same class of problem. The important thing is that you are quoting your fields and escaping your quotes, regardless of the symbols used.
Importantly, tabs aren’t usually inserted into fields by Excel users. With Power BI, exporting TSV is a little easier for non-dev Excel power users, too.
Nailed it. When Excel users press tab, they mean "go to the next cell." Conveniently, this is also what the format means to the computer when it encounters the tab separator. It's not foolproof, but it's much better. The human interpretation of the character matches the machine interpretation.
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.
If those characters had a visual representation and were easily typeable and we settled on using them as separators then we would struggle escaping those special characters within records. The problem is lack of standardization, not lack of special characters for record separators.
I think CSV is more popular than one would think because Excel was awful back in the old days.
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.
Based on my understanding of CSV, unit separators are valid column delimiters. I doubt you could use record separators between as row delimiters though.
CSV is human readable and writable, but that is only part of the advantage of CSV. Its simple format is great as an archival format. It's the plain text of data formats. Decades pass and you can still read it without much of an issue, though people are rightly pointing out that the format is ambiguous is nature and subject to a lot of interpretation. I've recently been compiling a lot of decades old experimental datasets, and it's honestly great when I find plain text files from the 1980s, since we can still read them! It's the more exotic data formats that came about later that are often more difficult to read properly.
That's funny, when i was twelve i need to store some data and i didn't really know database or csv, so i actually used the ascii control character to implement tables and record.
I later discovered sqlite, soooo much simpler...
ASCII FS/RS/GS/US do not have a visual representation, and users do not know how to enter them. That means they're essentially for binary-like encodings -- they're not really text if users can't see or enter them easily.
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:
so FS is ^\ (which you have to be careful does not cause the tty to generate SIGQUIT -- you have to ^V^\), ^], ^^, and ^_. That is, <control>\, <control>], <control>^, and <control><shift>-. On U.S. keyboards anyways.
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.
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.
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.
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.
> 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.)
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.
I know. But imagine sending a file with patronizing instructions on how to open it, which will still be ignored — the other end will double click on the file, see garbage, and get back to you.
The problem is person at receiving end won't follow instructions for opening in Office, software they're moderately familiar with, but not enough to navigate a special open file process even with hand-holding.
I sincerely do not think the solution is instruct receiving person to install an even less familiar Office alternative.
Typically the biggest problem is saving from Excel to UTF-8 since it really really wants to just use its locale-dependent default charset. Opening is trouble too, but people are occasionally good about noticing that and figuring out how to get to the import options... the re-conversion to a different charset on save happens transparently so people don't notice.
In recent versions there finally seems to just be a new "UTF-8 CSV" option in the Save As dialog.
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.
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.
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.
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.
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.
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).
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.
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:
It's ambiguous, and this is why significant whitespace can be so frustrating. Unless it's specified, some people will interpret a full-line comment as a blank line with a comment ending it (`^#.$`), and others will interpret it as you have (`^#.?\n`). Neither is obviously correct (even if it's obvious to you).
Edit: I don't know how to escape in HN formatting. Obviously there are italics where literal asterisks should be.
Yeah I was hoping to keep them inline, but indenting surely would have helped. I intended them after the dots (`.*`, dot asterisk in case I get the escaping wrong, is 0 or more characters in regex).
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 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
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.
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.)
256 comments
[ 0.23 ms ] story [ 235 ms ] threadBut 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.
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...
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?
1: https://en.wikipedia.org/wiki/Decimal_separator
https://pandas.pydata.org/pandas-docs/stable/reference/api/p...
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.
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...
[1] https://joshclose.github.io/CsvHelper/
And I've used them all.
[0] https://github.com/BurntSushi/xsv
[1] https://github.com/BurntSushi/rust-csv
[1]: https://github.com/csvreader/csvreader
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...
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).
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.
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.
https://twitter.com/jwiechers/status/1205515440543424513 + the thread
And here we are, 60 years later still struggling to work out where a record ends...
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.
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
- non printable character - no single keyboard key - intended for systems, not people - etc
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?
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!").
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.
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.
Fun fact, Excel will truncate numbers beyond 15 digits unless prefixed with a single quote mark.
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.
That's not part of any CSV specification I've seen, including RFC4180.
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.
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
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.
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.
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.
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.
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.
I do a lot of troubleshooting using CLI tools like grep and wc.
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
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.
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.
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
As a fun exercise write a CSV-like spec but using those ASCII chars.
There's your ASCIISV decoder.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:
so FS is ^\ (which you have to be careful does not cause the tty to generate SIGQUIT -- you have to ^V^\), ^], ^^, and ^_. That is, <control>\, <control>], <control>^, and <control><shift>-. On U.S. keyboards anyways.In the meantime for quick analysis and testing 90% + can be accomplished with one line of (g)awk.
More on FPAT [here](https://www.gnu.org/software/gawk/manual/gawk.html#Splitting...)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
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.
> 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:
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.)
> “,”
> 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
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.
VS Code and a suitable extension handled this rather better than Excel.
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.
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.
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.:
so i am disallowing everything for google, bing, yandex; easy enough, but: means that googlebot has no instructions, but yandex is disallowed all.if a whole line is commented out
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.
Have you observed one/both behaviours?
Edit: I don't know how to escape in HN formatting. Obviously there are italics where literal asterisks should be.
- 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
[1]: https://github.com/csvspecs
[1]: https://github.com/secretGeek/AwesomeCSV [2]: https://github.com/csvspecs/awesome-csv
CSV is the Keith Richards of file formats.
https://kokes.github.io/blog/2019/07/09/losing-data-apache-s...