Or export to CSV correctly and test with Excel and/or LibreOffice. Honestly CSV is a very simple, well defined format, that is decades old and is “obvious”. I’ve had far more trouble with various export to excel functions over the years, that have much more complex third-party dependencies to function. Parsing CSV correctly is not hard, you just can’t use split and be done with it. This has been my coding kata in every programming language I’ve touched since I was a teenager learning to code.
back
13 comments
CSV is not well-defined. Data in the wild doesn't even agree that it's comma separated.
String encoding? Dates? Formatted numbers? Booleans (T/F/Y/N/etc)? Nested quotes? Nested CSV!?
How about intermediate systems that muck things up. String encoding going through a pipeline with a misconfiguration in the middle. Data with US dates pasted into UK Excel and converted back into CSV, so that the data is a mix of m/d/yy and d/m/yy depending on the magnitude of the numbers. Hand-munging of data in Excel generally, so that sometimes the data is misaligned WRT rows and columns.
I've seen things in CSV. I once wrote an expression language to help configure custom CSV import pipelines, because you'd need to iterate a predicate over the data to figure out which columns are which (the misalignment problem above).
If the use-case has these complexities, then other formats may be better. I'll go out on a limb and say that MOST data export to csv are simple column data where it works just fine - at least that's been my experience.
The problem is is that it is simple until it's not, then you have problems. You need to validate immediately after export, and work out what happens when that fails.
I agree about validating after export, which is a good practice. But if you know your use-case to be csv-friendly, then its a nice simple long-standing almost universal format. Lots of pros with that. Using a more complex format for simple data may (or may not) save you issues with a rare edge case but could cost you in other areas. Like a non-technical manager having no idea how to look at.
CSV is fine if you control both ends (in which case it's worth asking why not use something else, but CSV is a totally valid choice). The problem is typically you don't, and what you expected to be simple ends up with lots of hacks until you realise that you want a more defined format (what that is depends on your field), which would have been easier to do if CSV wasn't there already.
Intermediate systems like Excel will break anything, they aren’t constrained to CSV. Excel screws up at the level of a cell value, not at the file format.
On the topic of nested CSV, three approaches:
- treat it as a join, and unroll by duplicating non-nested CSV data in separate rows for every element in the nested CSV
- treat it as a projection, have an extraction operator to project the cells you want
- treat it as text substitution problem; I've seen CSV files where every line of CSV was quoted like it was a single cell in a larger CSV row
You get nested CSV because upstream systems are often master/detail or XML but need to use CSV because everybody understands CSV because it's such a simple file format. Good stuff.
Seems more like a problem of the exporting and importing software and not the format.
CSV gives you the freedom of choice how to write the data.
You need the french data format? You can write the french date format. You need # as separator? You can use # as separator.
Sure you can't read any file without knowing it's specification but that's not the usecase of CSV.
CSV is for data transfer between systems which know each other.
Indeed. It's like 'text file' - there are many ways to encode and decode these.
Add another munging the to list, ids that 'look like numbers' e.g. `0002345` will get converted to `2,345`. Better be sure to pre-pend ' i.e. `'0002345`
Most of the problems come from treating CSV fields as something other than US-ASCII text strings. They're not dates, they're not numbers, they're just freeform text. If you want fancy-pants features like "numbers" or the ability to represent all common words in use in the US (e.g. the California town Rancho Peñasquitos) and aren't a masochist, you don't want CSV.
And “do you have a header row”? And null/nil/nul/blank specification/expectation?
Yeah but we're not in the dark ages of computers anymore. Export to Sqlite database instead.
Sure, you tell the finance industry that. They have systems, the systems already produce CSV. They can sign a contract worth multiples of your salary if you can consume it. Do you want the money or not?
In case it wasn't clear, I want software engineers in the finance industry to implement sqlite import and export in their various pieces of software, not too give up on lucrative, existing contracts, obviously.
The inertia around CSV in the finance industry is incredibly strong, and let’s just say some of the biggest industry players aren’t exactly reknowned for their cutting-edge practices when it comes to data interchange formats.
For interchange between banks, XML is also very common, because it is usually accompanied with a schema doc.
The person in the fintec industry rarely decies "oh, I'm going to add this format no one is asking for". They'll get a specification requiring csv and start implementing it. Get fired halfway though, then someone on the other side of the planet will implement it incorrectly. The first 3 revisions won't meet the customers needs, but the customer can't move away anyway.
> Parsing CSV correctly is not hard, you just can’t use split and be done with it.
Parsing RFC-compliant CSVs and telling clients to go away with non-compliant CSVs is not hard.
Parsing real world CSVs reliably is simply impossible. The best you can do is heuristics.
How do you interpret this row of CSV data?
1,5,The quotation mark "" is used...,2021-1-1
What is the third column? The RFC says that it should just be literally
> The quotation mark "" is used...
But the reality is that some producers of CSVs, which you will be expected to support, will just blindly apply double quote escaping, and expect you to read:
The quotation mark " is used...
Or maybe you find a CSV producer in the wild (let's say... Spark: https://spark.apache.org/docs/latest/sql-data-sources-csv.ht...) that uses backslash escaping instead.
The RFC 4180 says:
5. Each field may or may not be enclosed in double quotes (however some programs, such as Microsoft Excel, do not use double quotes at all). If fields are not enclosed with double quotes, then double quotes may not appear inside the fields.
The point is your clients and partner firms don't actually care what RFC 4180 says, they just expect you to deal with whatever their CSV library spits out.
4180 is just one of many CSV variants. You can't simply examine a CSV and determine for sure which flavor of file you were given, you can only guess.
For added fun, last column should be 1-2-2021.
At least in that case you know that the last part is the year. It is much funnier when you encounter something like 3-4-17 and you don't know if it is d/m/y, m/d/y, or y/m/d.
Oh that's easy, it's Janreburary Firscond 2021.
The deliminator is a setting that can be changed. I never said hard code. This is an interesting exercise to give students, but I am struggling to look back in my mind through the last 25 years of line of business application development where any of this was intractable. My approach has been to leverage objects. I have a base class for a CsvWriter and CsvReader that does RFC compliant work. I get the business stake holders to provide samples of files they need to import or export. I look at those with my eyes and make sub-classes as needed.
And data type influencing is a fun side project. I worked for a while on a fully generic CSV to SQL converter. Basically you end up with regex matches for different formats and you keep a running tally of errors encountered and then do a best fit for the column. Using a single CSV file is consistent with itself for weird formatting induced by whatever process the other side used. It actually worked really well, even on multi gigabyte CSV files from medical companies that one of my clients had to analyze with a standard set of SQL reports.
Y'all caught me being less than rigorous in my language while posting from my phone while in the middle of making breakfast for the kids and the wife to get out the door. To the "not well defined" aspect, I disagree in part. There is an RFC for CSV and that is defined. Now the conflated part is the use of CSV as an interchange format between various systems and locales. The complexity of data exchange, in my mind, is not the fault of the simple CSV format. Nor is it the fault of the format that third party systems have decided to not implement it as it is defined. That is a different challenge.
Data exchange via CSV becomes a multiple party negotiation between software (sometimes the other side is "set in stone" such as a third party commercial system or Excel that is entrenched), users, and users' local configuration. However, while not well defined none of these are intractable from a software engineering point of view. And if your system is valuable enough and needs to ingest data from enough places it is NOT intractable to build auto detection capabilities for all the edge cases. I have done it. Is it sometimes a challenge, yes. Do project managers love it when you give 8 point estimates for what seems like it should be a simple thing, no. That is why the coding kata RFC compliance solution as a base first makes the most sense and then you troubleshoot as bug reports come in about data problems with various files.
If you are writing a commercial, enterprise solution that needs to get it right on its own every time, then that becomes a much bigger project.
But do you know what is impossible, getting the entire universe of other systems that your customers are using to support some new interchange format. Sorry, that system is no longer under active development. No one is around to make code changes. That is not compatible with the internal tools. For better or worse, CSV is the lingua franca of columns of data. As developers, we deal with it.
And yes, do support Excel XLSX format if you can. There are multiple third party libraries to do this in various languages of various quality.
As a developer, I have made a lot of my living dealing with this stuff. It can be a fun challenge, frustrating at time, but in the end as professionals we solve what we need to to get the job done.
Unfortunately that is only the case as long as you stay within the US. for non-US users Excel has pretty annoying defaults, such as defaulting to ; instead of , as a separator for "CSV", or trouble because other languages and Excel instances use , instead of . for decimal separators.
A nice alternative I've used often is to constructor an excel table and then giving is an .xls extension, which Excel happily accepts and has requires much less user explanation than telling individual users how to get Excel to correctly parse a CSV.
Localization is a thing. None of this is a show stopper. Subclasses and configuration screen for import and export.
And then you get a business analyst at your client going "I just hit export in our internal tool, what's this delimiter that you're asking me about in the upload form? Google Sheets doesn't ask me to tell them that"
And how do you fix the “some analyst” problem? Is there a better format that reduces this problem?
JSON? RON? Protobuf? Cap'n'Proto? Anything with more types than "string" which is all CSV has, and with an unambiguous data encoding. Preferably also a way to transmit the schema, since all of these formats (including CSV) have a schema but don't necessarily include it in the output.
About half the problems with CSV are due to encoding ambiguities, the other half are due to schema mismatches.
> Unfortunately, that is only the case as long as you stay outside the US. For US users Excel has pretty annoying defaults, such as defaulting to , instead of ; as a separator for "CSV", or trouble because US instances of Excel use . instead of , for decimal separators.
FTA:
* What does missing data look like? The empty string, NaN, 0, 1/1-1970, null, nil, NULL, \0?
* What date format will you need to parse? What does 5/5/12 mean?
* How multiline data has been written? Does it use quotation marks, properly escape those inside multiline strings, or maybe it just expects you to count the delimiter and by the way can delimiters occur inside bare strings?
And let me add my own question here:
what is the actual delimiter? Do you support `,`, `;` and `\t`?
What is the encoding of the text file? UTF8, windows-1252?
What is the decimal delimiter “.”, “,”?
Most csv users don’t even know they have to be aware of all of these differences.
The main issue is that "CSV" isn't one format with a single schema. It's one format with thousands of schemas and no way to communicate them. Every program picks its own schema for CSVs it produces, some even change the schema depending on various factors (e.g. the presence or absence of a header row).
RFC 4180 provides a (mostly) unambiguous format for writing CSVs, but because it discards the (implied) schema it's useless for reading CSVs that come from other programs. RFC 4180 fields have only one type: text string in US-ASCII encoding. There are no dates, no decimal separators, no letters outside the US-ASCII alphabet, you get nothing! It leaves the option for the MIME type to specify a different text encoding, but that's not part of the resulting file so it's only useful when downloading from the internet.
> RFC 4180 provides a (mostly) unambiguous format for writing CSVs,
What are the ambiguities in RFC 4180?
It allows non-ASCII text but does not provide any way to indicate charset within the file, instead requiring it out-of-band. Once the file is saved, the text encoding becomes ambiguous. Likewise for the presence or absence of a header row.
Likewise for whether double quotes (`"`) are allowed in fields (rule 5). This one gets even worse, since the following rule (6) uses double quotes to escape line breaks and commas, but they may not be allowed at all so commas in fields may not be escapable.
It only supports text, not numbers, dates, or any other data, and provides no way to indicate any data type other than text.
One example that will kill loading a csv in excel beyond the usual dates problem. If you open in excel a csv file that has some large id stored as int64, they will be converted to an excel number (I suspect a double) and rounded. Also if you have a text column but where some of the codes are numeric with leading zeros, the leading zeros will be lost. And NULL is treated as the string "NULL".
I am aware you can import a csv file in excel by manually defining the column types but few people use that.
I'd be fine with an extension of the csv format with one extra top row to define the type of each column.
> aware you can import a csv file in excel by manually defining the column types but few people use that
And what fraction of those users would be able to anything with another format?
> an extension of the csv format with one extra top row to define the type of each column
If the goal is foolproof export to Excel, use XLSX.
Excel will literally use a different value separator depending on the locale of the machine (if the decimal separator for numbers is a comma and not a dot, it’ll use a semicolon as a value separator instead of a comma).
I will hard disagree here. Always have has a clrf issue or another weirdness come up.
Especially if you work with teams from different countries, csv is hell. I always generate rfc compliant csv, not once it was accepted from day one. Once, it took us two weeks to make it pass the ingestion process (we didn't have access to the ingest logs and had to request them each day, after the midnight processing) so in the end, it was only 10 different tries, but still.
I had once an issue with json (well, not one created by me) , and it was clearly a documentation mistake. And I hate json (I'm an XML proponent usually). Csv is terrible.
> Parsing CSV correctly is not hard
Parsing the CSV you have in front of you is not hard (usually). Writing a parser that will work for all forms of CSV you might encounter is significantly harder.
Excel and data precision are really at odds. But it might be interesting to see what would happen if excel shipped with parquet import and export capabilities
> Honestly CSV is a very simple, well defined format, that is decades old and is “obvious”
This is the problem though. Everything thinks it is "obvious" and does their own broken implementation where they just concatenate values together with commas, and then outsources dealing with the garbage to whoever ends up with the file on their plate.
If a more complex, non-obvious format was required, instead of "easy I'll just concatenate values" they might actually decide to put engineering into it (or use a library)
> Parsing CSV correctly is not hard, you just can’t use split and be done with it.
And yet you can anyway if you are confident that your CSV won't contain anything that would mess it up.
CSV is so simple that if someone forgets to send you escaped CSV, you can and should simply smack them with a shoe.
> well defined format
No.