I think the sad reality there is that it's become "the" format that users expect, and more importantly, it's what's integrated into the majority of peripheral services and tools.
Yeah, clients always expect CSV (or sometimes XLSX), but if I tell them that I'll send parquet data, they will ask if I'm having a stroke or something because they don't know what is parquet and how could they use it.
Oh, yes. And even if you can convince the client that they're wrong, and you're right - with substantial client datasets, there's always a load of "data not as previously represented" records. Resolving what is going on with those tends to be vastly easier when you can say "look at record 1,234,567" and they can easily do that in their favorite & familiar software.
An article promoting parquet over CSV. Fair enough, but parquet has been around for a while and still no support in Debian. Is there some deep and dark reason why?
What do you mean there is no parquet support in Debian? Data formats should be supported in userspace and there are plenty of parquet libraries and userspace tools an apt-get away. There is exactly as much support for tar in Debian as there is for parquet.
You have searched for packages that names contain parquet in suite(s) bookworm,
all sections, and all architectures.
Sorry, your search gave no results
You have searched for photoshop in packages names and descriptions in
suite(s) bookworm, all sections, and all architectures (including
subword matching). Found 9 matching packages.
Package abr2gbr
bookworm (stable) (graphics): Converts PhotoShop brushes to GIMP
1:1.0.2-5: amd64 arm64 armel armhf i386 mips64el mipsel ppc64el s390x
Package gimp
bookworm (stable) (graphics): GNU Image Manipulation Program
2.10.34-1+deb12u2: amd64 arm64 armel armhf i386 mips64el mipsel ppc64el s390x
:
I think this article is causing a kerfuffle because it's hitting two different audiences very differently. Honestly the article title should have been "Friends don't let friends use CSV for data pipelines".
Because when I'm wrangling data from a human - a human who is stubbornly defending their own little island of business information like their employment depended on it[1] - a CSV is about as good as I am gonna get.
I had a bear of a time just convincing people to put their data in a delimited format, instead of a table inside a powerpoint presentation, or buried in sixty levels of Access joins, or in an SVG. I need data from "what is scroll wheel" sort of users.
If I am working system to system? That's a different requirement, a requirement that is apparently where the OP author is coming from.
[1] Because it kind of does. Having a unique platform is one of those priceless keys to being skipped in the thrice-yearly layoff rituals. Unfortunately, that means anyone approaching saying words like "integration" or "API" are shot on sight.
So you grab your favorite CllaumistraCoderPT-3.14-Turbo and ask the box to deduce CSV settings from the given example. It comes back with a set of characteristics others have brought up elsewhere in comments (field separator, decimal handling, quotes, date format, etc.)
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.
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.
> 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.
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"
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.
* 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`?
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.
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.
> 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:
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.
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.
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.
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).
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`
- 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.
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.
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.
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.
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.
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.
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.
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.
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
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.
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.
> 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)
If both parties implement RFC 4180 and use a consistent character set encoding then I don't think there are actually any undefined behaviors. But in practice a lot of implementations are simply broken, including those from major tech companies that ought to know better.
I don't think RFC 4180 differentiates between an empty string and a null value. As long as you add a check that all string columns are free of empty values before writing you should be good.
Well I would consider differentiation between empty string versus null as simply being out of scope for CSV rather than undefined behavior. It was never intended as a complete database dump format.
Converting strings into other data types is out of scope for CSV, not really undefined behavior. The type conversions happen at a later stage of the import process.
I use JSON for import/export of user data (in my super app collAnon), it's more predictable and the toolings around it to transform into any other format(even csv) is underappreciated, imo.
Of course if you only consider the disadvantages, something looks bad.
The advantages of CSV are pretty massive though - if you support CSV you support import and export into a massive variety of business tools, and there is probably some form of OOTB support.
You have a (usually) portable transport format that can get the information into and out of an enormous variety of tools that do not necessarily require a software engineer in the middle.
I'm also struggling with such a quick dismissal of human readable formats. It's a huge feature.
What happens when there's a problem with a single CSV file in some pipeline that's been happily running fine for years? You can edit the thing and move on with your day. If the format isn't human readable, now you may have to make and push a software update to handle it.
Of course, CSV is a terrible format that can be horribly painful. No argument there.
But despite the pain, it's still far better than many alternatives. In many situations.
You can do tab-separated "CSV" and it'll be much better, avoid the quoting and delimiter issues that somehow trip up something half the time, and pretty much all these tools have always supported that format as well.
Exactly. The author even opens the article with the nice trivia that CSV has been in use since the 1970s. I don't think anyone disputes that CSV is a very primitive format. But I hope no one uses CSV because it is so performant or well-designed. It is used exactly because it is so universal, which makes the point of comparing against almost any other format moot unless they were around in the 1970s, too.
I just tried it in R. The relevant package seems to be "arrow", so I did
install.packages("arrow")
and then I did
?read_parquet
to get an example. I tried the example, and got the error message as follows. This sort of error is really quite uncommon in R. So my answer to the "with little effort" is "no", at least for R.
> tf<-tempfile()
> write_parquet(mtcars, tf)
Error in parquet___WriterProperties___Builder__create() :
Cannot call parquet___WriterProperties___Builder__create(). See https://arrow.apache.org/docs/r/articles/install.html for help installing Arrow C++ libraries.
The problem, as always, is that you deal with multiple data sources - which you can not control the format of. I work as a data analyst, and in my day-to-day work I collect data from around 10 different sources. It's a mix of csv, json, text, and what not.
Nor can you control the format others want. The reason I have to export to csv, is unfortunately because the people I ship out to use excel for everything - and even though excel does support many different data formats, they either enjoy using .csv (should be mentioned that the import feature in excel works pretty damn well), or have some system written in VBA that parses .csv files.
Not sure I understand right what this article is about.
From my point of view, CSV is an easy way to export data from a system to allow an end user to import it in excel and work on this data.
Apart if it's as easy with parquet to import in excel as with a CSV, I'm not sure this is not fixing a problem that doesn't exist. And making things more complicated.
Outside of the context of end user, I don't see any advantages in this compared to xml or json export.
I never liked articles about how you should replace CSV with some other format while pulling some absolutely idiotic reasons out of their rear...
1. CSV is underspecified
Okay, so specify it for your use case and you're done?
E.g use rfc3339 instead of the straw-man 1-1-1970 and define how no value looks like, which is mostly an empty string.
2. CSV files have terrible compression and performance
Okay, who in their right mind uses a plain-text-file to export 50gb of data? Some file systems don't even support that much. When you are at the stage of REGULARLY shipping around files this big, you should think about a database and not another filetype to send via mail.
Performance may be a point, but again, using it for gigantic files is wrong in the first place.
3. There's a better way (insert presentation of a filtype I have never heard of)
There is lots of better ways to do this, but:
CSV is implemented extremely fast, it is universally known unlike Apache Parquet (or Pickle or ORC or Avro or Feather...) and it is humanly readable.
So in the end: Use it for small data exports where you can specify everything you want or like everywhere, where you can import data, because most software takes CSV as input anyway.
For lots of data use something else.
Friends don't let friends write one-sided articles.
2. You would be surprised, especially on the science/university level in stat, health or bioinfo. Unfortunately a lot of people go with the path of least resistance and use excel propertiary format or csv for everything.
Like NHS with their post covid data due to excel limitations or gene name conversion problems in sci journals.
Same happens with stupid amount of laboratory management things or bioinformatics tools.
Honestly obviously the article is biased but we should at least think about moving away from csv in non customer facing fronts.
I agree with your other points but the first point misses the mark. Even you specify a format, you cannot use the file for exporting data between systems and organizations if they don't all agree on that format.
CSV does not have a reasonable way to encode that is using a specific spec. I can open your data with my tools and silently misinterpret it. But if you are only exporting data between yourself, that's another story.
Friends don't let friends export to CSV -- in the data science field.
But outside the data science field, my experience working on software programming these years is that it won't matter how beautiful your backoffice dashboards and web apps are, many non-technical business users will demand at some point CSV import and/or export capabilities, because it is easier for them to just dump all the data on a system into Excel/Sheets to make reports, or to bulk edit the data via export-excel-import rather than dealing with the navigation model and maybe tens of browser tabs in your app.
Exactly Excel is the UI they know. This trumps every technical argument you can come up with. People don't want to throw out 20 years of experience with a tool to use your custom UI.
But why the half measure of csv when it's just as simple to use a library to export to an actual excel file (which is really just xml) , which will properly preserve your data and make the business users happy.
I tend to prefer line delimited JSON myself, even if it's got redundant information. It will gzip pretty well in the data if you want to use less storage space.
Either that or use the ASCII codes for field and row delimiters on a UTF-8 file without a BOM.
Even then you're still stuck with data encoding issues with numbers and booleans. And that direct even cover all the holes I've seen in CSV in real world use by banks and govt agencies over the years.
When I've had to deal with varying imports I push for a scripted (js/TS or Python) preprocessor that takes the vender/client format and normalized to line delimited JSON, then that output gets imported.
It's far easier than trying to create a flexible importer application.
Edit: I've also advocated for using SQLite3 files for import, export and archival work.
and those thousands of apps bug because they exist in a reality where few CSVs from certain sources can have different formats, not in the fantasy world where intention to work with certain files becomes a binding specification
Normally, I wouldn't nitpick (hold for laughter) but this is just a perfect example of how CSV is not CSV and how it's probably impossible to support everything people call CSV, simply because of the tiny (or not so tiny) differences between formats.
If you had the option to use whatever you want, and you're already sacrificing not using a text editor, why would you use parquet instead of the myriad of choices.
Eh. I much prefer to produce and consume line delimited JSON. (Or just raw JSON). Its easy to parse, self descriptive and doesn't have any of CSV's ambiguity around delimiters and escape characters.
Its a little harder to load into a spreadsheet, but in my experience, way easier to reliably parse in any programming language.
JSON(newline delimited or full file) is significantly larger than csv. With csv the field name is mentioned once in the header row. In JSON every single line repeats the field names. It adds up fast, and is more of a difference than between csv to parquet.
Compression will make the size overhead disappear instantly.
I do see your point - I know that its less efficient, and its not the best format if you're handling it every day or using it for huge data sets. But for a quick and dirty handoff between programs its lovely. It takes ~5 lines to parse in just about any programming language. And you can do so without pulling in any extra dependencies.
Looking at the downvotes I can see that its a controversial choice. But I stand by it.
That is only if the JSON uses objects. JSON arrays map much better to CSV. In that case, only adding brackets to the front and end of each line.
The ability of JSON to do both objects and arrays is useful, for example the first line can be an object or array of objects describing the fields. Then there is less confusion between schema lines and data lines like there is with CSV.
458 comments
[ 2.9 ms ] story [ 297 ms ] threadLike JSON.
CSV is just too simple and "user-friendly".
This line is emblematic of the paradigm shift LLMs have brought. It’s now easier to build a better tool than change everyone’s behaviour.
> You give up human readable files, but
What are we even doing here.
Because when I'm wrangling data from a human - a human who is stubbornly defending their own little island of business information like their employment depended on it[1] - a CSV is about as good as I am gonna get.
I had a bear of a time just convincing people to put their data in a delimited format, instead of a table inside a powerpoint presentation, or buried in sixty levels of Access joins, or in an SVG. I need data from "what is scroll wheel" sort of users.
If I am working system to system? That's a different requirement, a requirement that is apparently where the OP author is coming from.
[1] Because it kind of does. Having a unique platform is one of those priceless keys to being skipped in the thrice-yearly layoff rituals. Unfortunately, that means anyone approaching saying words like "integration" or "API" are shot on sight.
How do you verify it? What happens next?
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.
About half the problems with CSV are due to encoding ambiguities, the other half are due to schema mismatches.
* 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 decimal delimiter “.”, “,”?
Most csv users don’t even know they have to be aware of all of these differences.
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.
What are the ambiguities in RFC 4180?
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.
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.
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.
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.
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).
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`
- 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.
There’s certainly XML. There’s also some very strange hand-rolled formats that are a right pain to parse.
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.
No.
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.
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.
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.
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.
And yet you can anyway if you are confident that your CSV won't contain anything that would mess it up.
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.
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)
I like that everyone knows about .csv files, and it's also completely human readable.
So for <100mb I would still use csv.
I think in polars it's
although there's probably a better way to write this.The advantages of CSV are pretty massive though - if you support CSV you support import and export into a massive variety of business tools, and there is probably some form of OOTB support.
You have a (usually) portable transport format that can get the information into and out of an enormous variety of tools that do not necessarily require a software engineer in the middle.
I'm also struggling with such a quick dismissal of human readable formats. It's a huge feature.
What happens when there's a problem with a single CSV file in some pipeline that's been happily running fine for years? You can edit the thing and move on with your day. If the format isn't human readable, now you may have to make and push a software update to handle it.
Of course, CSV is a terrible format that can be horribly painful. No argument there.
But despite the pain, it's still far better than many alternatives. In many situations.
Getting records that contain newlines would be a bit trickier.
...I found it - the question is under review by the Austin group for inclusion in POSIX.
https://austingroupbugs.net/view.php?id=249
[1] https://www.gigasheet.com/post/how-to-open-parquet-file
[2] https://www.cdata.com/kb/tech/parquet-odbc-excel-query.rst
Nor can you control the format others want. The reason I have to export to csv, is unfortunately because the people I ship out to use excel for everything - and even though excel does support many different data formats, they either enjoy using .csv (should be mentioned that the import feature in excel works pretty damn well), or have some system written in VBA that parses .csv files.
Outside of the context of end user, I don't see any advantages in this compared to xml or json export.
1. CSV is underspecified Okay, so specify it for your use case and you're done? E.g use rfc3339 instead of the straw-man 1-1-1970 and define how no value looks like, which is mostly an empty string.
2. CSV files have terrible compression and performance Okay, who in their right mind uses a plain-text-file to export 50gb of data? Some file systems don't even support that much. When you are at the stage of REGULARLY shipping around files this big, you should think about a database and not another filetype to send via mail. Performance may be a point, but again, using it for gigantic files is wrong in the first place.
3. There's a better way (insert presentation of a filtype I have never heard of) There is lots of better ways to do this, but: CSV is implemented extremely fast, it is universally known unlike Apache Parquet (or Pickle or ORC or Avro or Feather...) and it is humanly readable.
So in the end: Use it for small data exports where you can specify everything you want or like everywhere, where you can import data, because most software takes CSV as input anyway.
For lots of data use something else.
Friends don't let friends write one-sided articles.
Like NHS with their post covid data due to excel limitations or gene name conversion problems in sci journals.
Same happens with stupid amount of laboratory management things or bioinformatics tools.
Honestly obviously the article is biased but we should at least think about moving away from csv in non customer facing fronts.
Small files? Json Big files? SQLlite or parquet.
But outside the data science field, my experience working on software programming these years is that it won't matter how beautiful your backoffice dashboards and web apps are, many non-technical business users will demand at some point CSV import and/or export capabilities, because it is easier for them to just dump all the data on a system into Excel/Sheets to make reports, or to bulk edit the data via export-excel-import rather than dealing with the navigation model and maybe tens of browser tabs in your app.
Either that or use the ASCII codes for field and row delimiters on a UTF-8 file without a BOM.
Even then you're still stuck with data encoding issues with numbers and booleans. And that direct even cover all the holes I've seen in CSV in real world use by banks and govt agencies over the years.
When I've had to deal with varying imports I push for a scripted (js/TS or Python) preprocessor that takes the vender/client format and normalized to line delimited JSON, then that output gets imported. It's far easier than trying to create a flexible importer application.
Edit: I've also advocated for using SQLite3 files for import, export and archival work.
It's "Comma Separated Variables", it doesn't really need anymore specification than that.
These files have always imported into M$ and libre office suites without issue.
- supporting "" (single)
- Supporting newlines in "", oops, now you can't getline() and instead need to getdelim()
- Supporting comments # (why is this even a thing)
- Supporting multiple "" in a field
- Escaping " with "" or \"
- length based csv, so all fields are seekable.
It's a mess, which one's your csv?
No CSV I have ever encountered has comments.
So yes, I am happy with code that works for a subset of files. There are thousands of applications which work with CSVs and they all do exactly this.
(Oh and I forgot about headers, headers should be mandatory but people are aloof about it)
Normally, I wouldn't nitpick (hold for laughter) but this is just a perfect example of how CSV is not CSV and how it's probably impossible to support everything people call CSV, simply because of the tiny (or not so tiny) differences between formats.
Of course there are better formats. But for many use cases friends encourage friends to export to CSV.
Its a little harder to load into a spreadsheet, but in my experience, way easier to reliably parse in any programming language.
I do see your point - I know that its less efficient, and its not the best format if you're handling it every day or using it for huge data sets. But for a quick and dirty handoff between programs its lovely. It takes ~5 lines to parse in just about any programming language. And you can do so without pulling in any extra dependencies.
Looking at the downvotes I can see that its a controversial choice. But I stand by it.
The ability of JSON to do both objects and arrays is useful, for example the first line can be an object or array of objects describing the fields. Then there is less confusion between schema lines and data lines like there is with CSV.