Cool stuff! But it’s criminal to not call attention to JQ’s elder sibling CSVKit. It’s invaluable for playing with csvs. Much easier to parse out columns, allows you to generate new csvs and even merge them. More importantly, it allows SQL on csvs (via SQLite iirc) which empowers all sorts of csv shenanigans. The bash scripting this enables us incredible (good and bad).
I agree it's indeed one of the best tool to process csv files. Using basic coreutils command are going to be soon very limited and brittle to process csv files.
xsv is great for a quick sanity checks (i.e. number of columns, unique values counts in a given column) but for a more serious tasks/giant files I switch to either polars or duckdb converting CSV/TSV files to parquet or parquet data sets.
Maybe try the to [1] and sqlp [2] commands from the qsv fork. From the README:
sqlp: Run blazing-fast Polars SQL queries against several CSVs - converting queries to fast LazyFrame expressions, processing larger than memory CSV files.
to: Convert CSV files to PostgreSQL, SQLite, XLSX, Parquet and Data Package.
For any kind of tabular data, I use DuckDB (parallel CSV reads, works on small to humongous CSVs). It supports full Postgres-like SQL, so you can do arbitrarily complex manipulations on columns and rows. The CSV essentially becomes a dataframe object that can be manipulated in performant ways (DuckDB vectorizes and paralllelizes, so it's way faster than awk on large CSV files). You can even manipulate multiple CSVs in a single statement (JOINs, UNIONs etc).
You can't read SAME csv in a parallel manner but you can still read two different csvs in two separate threads? I think that's what they meant by "parallel csv".
I think the fundamental issue is that a single 'cell' (value) can contain newlines, so you can't just assume each line is a 'row' and trivially parallelize.
The problem is perhaps most obvious when you consider that the value of a single cell could itself be a CSV, and this could be recursive
I think you can use a heuristic approach. E.g. pick a comma in the middle of the CSV, assume it is a delimiter (not inside data) and parse from there in a separate thread. If you find it doesn't parse correctly (perhaps because it is inside data) you might have to throw the results of that thread away. Kind of ugly but would probably allow faster parsing in 99% of cases.
CSVs are a bit of a mess. Non-trivial to parse properly, due to escaping, and the standard (such as it is) is poorly followed. E.g. sometimes people use semi-colon or pipe instead of comma.
I've found that for tabular data parquet can't be beat, the only time textual formats are superior are for human readabilty (which imo can be solved by tooling)
Edits might be a little tricky, there's no concept of writing in place in parquet afaict. But there's absolutely no reason why reads/creation can't be supported in text editors (I'm sure some do).
This is also my use case where I have GBs of data in S3 as Parquet and a small CSV file that I have to join them to.
And DuckDB reads CSVs and Parquet and SQLite and others. I can join all these heterogeneous data types in a single SQL statement and have the assurance that it’ll be done correctly.
With raw string parsing approaches (awk, perl) you have to handle all kinds of corner cases because CSV is an underspecified spec. CSV is deceptive in that it looks like it parses easily, but the truth is CSV is one of the most difficult formats to reliably parse. I mean, just look at the number of arguments that pandas.read_csv has.
Parsing a CSV by hand is almost always the wrong thing to do unless you also wrote the CSV generating process and can limit the variance.
But if not, instead of working with CSVs at the string level and risk running into issues, I've found that the best practice is to farm the CSV parsing out to a battle-tested engine (I use DuckDB, but sqlite, pandas, polars, clickhouse-local or any number of engines work), and work with the parsed object instead.
Better yet, avoid CSV and use DuckDB + Parquet (paired with visidata for quick vizes) for anything nontrivial.
With Perl, I wouldn't do raw string parsing; I'd use a Text::CSV (a battle-tested engine). For example, this filters a file foo.csv where `some_header` field is positive (you could also use sort or map or whatever instead of grep):
Yeah, sqlite has been my preferred method for working with csv files for a long time. I will say that one nice thing about DuckDB over SQLite is that it does a better job inferring column types than SQLite does.
I do use sqlite too to process csv file, but I either rely on csvkit to generate the schema from the csv or create the table with raw sql before inserting data in the sqlite database.
Have a look at visidata, a TUI table viewer/editor with vim keybindings. It's incredibly powerful and fully automatable - everything you do gets recorded as a series of commands which you can save and replay.
Not the right tool for everything, but it shines for quickly glancing at the shape of tabular data and making sense of it. Sorting, filtering, joins across files, column histograms and even column splitting/rejoining are all keystrokes away.
It groks anything even remotely table shaped like CSV, JSONL, JSON, even Excel files, it can even directly connect to databases and parse tables out of HTML.
I am a big fan of VisiData for working with data in the terminal. I also like John Kerl's miller[1] and Simon Willison's sqlite-utils [2] when I want to do queries. These are useful for working with data in simple shell scripts.
Shameless plug: I created prql-query (https://github.com/PRQL/prql-query) in order to scratch my own itch and use PRQL (prql-lang.org) with DataFusion and DuckDB for things like this.
pq is overdue some maintenance but I will update it with the imminent PRQL 0.9 release.
I've used pq in anger at my $dayjob and found it incredibly productive to have the full power of SQL combined with the terse and logical syntax of PRQL.
Agreed here, it's impressive how easy it is to use and how performant it is.
Would be nice to add a seamless ability to call executable UDFs from clickhouse-local, last time I checked clickhouse-local required executables to be in a special directory (as in proper clickhouse). Instead it'd be nice to be able to reference any executable in an ad-hoc way.
As an SRE, I used to install one tool for every data format until I came across clickhouse-local. Now I use it for everything.
- It works with every data format which I come across in my daily job. It support data formats like Protobuf, Avro, Cap'n Proto which regular tools don't support. Funny thing, it can even read mysql dumps.
- I can read the data stored in local or remote location like http, s3, gcs, azure and whatever location I can think of.
- I can SQL queries on the raw data and improve my SQL skills on daily basis.
We bought the clickhouse.com domain two years ago, and it was listed in some adblockers due to abuse by the previous owner. We have cleaned up most of the block lists, but there is still a chance it is filtered by your ad-blocker.
I've defaulted to doing all my data munging in a clojure repl.
The datasets that I work with are well small enough to fit in memory on my machine and having the full java ecosystem + clojure ergonomics is worth more to me than the performance full db tooling might offer
Hate to agree, but yes right there, 100 pixels above the download link, very nice and clear site, I heartily approve. Not sure how you'll get $1b of funding for something that user friendly though -- I don't see any dark patterns at all!
> Does your workplace reimburse software's cost?
I don't need your app, but my workplace certainly doesn't. I can buy all sorts of hardware from preferred suppliers through our purchasing system, I can with a few hoops generate a one-time credit card to buy a random thing from a random person (how I pay for starlink for example), and for some specific cases I can pay myself and claim back later
However software is blocked from all this.
> Even if Superintendent.app only saves you 30 minutes per month, it's already worth the minimal price tag that we will charge after beta.
To buy software in my company would take far longer than the 6 hours that I'd save each year! This of course is entirely the fault of my company, but I wonder how many other companies have similar policies.
What I can do though is spin up a AWS ami with a software charge and nobody bats an eyelid. I'm not sure how much of a "cut" aws takes, but something to think about.
(If it really helped me I'd just eat the $40 personally and take a day off in lieu, but I've known some people put in receiptless claims to get around the policy)
I was a user of superintendent until about a year ago (it doesn't make sense for my current job), and I liked it a lot. Tanin was responsive by email, and the tool was very helpful for standard CSV tasks. Well worth the price for me. If I ever need to do those kinds of tasks again, I'll gladly become a customer again.
Really appreciate your kind words! I'm glad we crossed path, though, based on your username, I can't tell who you are. haha.
To be honest, I'm still trying to find more a product market fit.
It feels like a travel planning app where people might need it once every few months, so it doesn't quite catch on.
On another hand, I do have a few passionate users who analyze 40+ CSVs at the same time, but I'm struggling to improve UX for these users. Building great UI is hard. But maybe this is what I need to do.
Unfortunately "awk -F," (field separator of comma) doesn't work with most real CSV files, because of quoted fields, commas in fields, and (less frequently) multiline fields. My GoAWK implementation has a CSV mode activated with "goawk -i csv" (input mode CSV) and some other CSV features that properly handle quoted and multiline fields: https://benhoyt.com/writings/goawk-csv/
The frawk tool (written in Rust) also supports this.
Interestingly, Brian Kernighan is currently updating the book "The AWK Programming Language" for a second edition (I'm one of the technical reviewers), and Gawk and awk are adding a "--csv" option for this purpose. So real CSV mode is coming to an AWK near you soon!
I think “most” is pulling a lot of work there. I make and process csvs a lot, and just avoid quotes when generating them.
Sure technically you can do things like having new lines on a line which cause all sorts of grief, but if you control the end to end workflow just avoid it.
I have a tool which monitors performance of loading various websites via various proxies at various points ok our network with pupetteer. That outputs a csv which I can then extract data from and make handy graphs to show manglement.
Quotes aren't the problem, it's having an in-band separator character. Maybe you're lucky enough to only need naive splitting on commas, but lots of us need to jam in human-produced text stings where the punctuation needs to be preserved without breaking file parsing.
Yeah, I guess that kinda works if you're the one generating them. But you must not have too many generalised strings -- like a name field containing "Smith, John" or a book title like "The Lion, the Witch and the Wardrobe"? When I deal with CSV's those kind of things are quote common. I actually prefer TSV because you can (more easily) process them with standard AWK.
What do you output for a string field with ',' in it? Just strip the commas, or replace it with something else?
This is a common theme with the classic unixy text processing based workflows, they work really nicely when the incoming data is nice and simple but fall apart on edge cases. And because often also the error handling is incomplete it might be difficult to know that the pipeline is producing garbage output.
Related example is the recently discussed problems with filenames, and the many pitfalls that scripts can stumble upon when handling them.
> And because often also the error handling is incomplete it might be difficult to know that the pipeline is producing garbage output.
The thing is that from the point of view of each individual tool, there isn't even an error! You ask awk to split on commas and give you the second field, it splits on commas and gives you the second field. It opened the file no problem, it didn't run out of memory writing its output to stdout... There were no errors to speak of from what it could see. The "error" is an error in the overall logic of the code.
The problem is that edge cases are not really edge cases… in certain locales. ;) Someone could be using commas as the decimal separator since that’s how they do it ’round these parts.[1][2] So it’s not like they’re storing “weird” stuff in tabular format, like HTML blog articles; they’re just storing numbers.
Of course, Excel and other software has tried to be way too clever about it with different separators depending on locale, which makes the whole pipeline complex and context-dependent.
[1] A surprising amount of countries have this convention, including my own… doesn’t make sense to me since it becomes harder to list numbers.
[2] And the number one selling point of CSV is that it is “human editable”, so of course someone is going to do that at some point in the pipeline.
That is correct, the data needs to be simple where the delimiter characters are never embedded inside a quoted field. I wrote a simple (and fast) utility to ensure that CSV files are handled properly by all the standard UNIX command line data tools. If you like using awk, sed, cut, tr, etc. then it may be useful to you.
Another problem is CSV isn't really a text format. It's a binary format that just happens to look like text and most implementations will handle it if you edit it as text. But the record separator is meant to be specifically CRLF and there might be other complications. This can be confusing when using these tools to try to output another CSV. Do the `--csv` modes support also writing back to CSV?
No, unfortunately the Gawk/awk --csv option doesn't support writing back to CSV. FWIW, GoAWK supports CSV input mode with "-i csv" and CSV output mode with "-o csv" for this purpose, as well as special syntax for fetching a @"named-field" rather than a $numbered_field. Docs: https://github.com/benhoyt/goawk/blob/master/docs/csv.md
2nd edition?! I just purchased a copy of the first and it's fantastically concise. I've always had a great experience with books Kernighan has been a part of, and it's awesome to see that the book's team are domain experts. Looking forward to it!
Also localized csv files in a language where the comma is the decimal separator (and the field separator is ; ), column headers and unexpected character encodings. CSV is truly the worst.
These days I just use powershell. It has built-in csv import and then I'm dealing with familiar pwsh/.net objects. I don't miss the days of learning bespoke tools to handle slightly different cases.
116 comments
[ 3.0 ms ] story [ 188 ms ] threadhttps://csvkit.readthedocs.io/en/latest/
By giant I mean 25G gzipped files with >10^9 rows like these VCFs: https://ftp.ncbi.nlm.nih.gov/snp/latest_release/VCF/
sqlp: Run blazing-fast Polars SQL queries against several CSVs - converting queries to fast LazyFrame expressions, processing larger than memory CSV files.
to: Convert CSV files to PostgreSQL, SQLite, XLSX, Parquet and Data Package.
[1] https://github.com/jqnatividad/qsv/blob/master/src/cmd/sqlp....
[2] https://github.com/jqnatividad/qsv/blob/master/src/cmd/to.rs...
xsv is more lean while qsv tries to support every action that you might want to perform on CSV files.[2]
[1] https://github.com/jqnatividad/qsv
[2] https://github.com/jqnatividad/qsv/discussions/290#discussio...
This is the description in the experimental PR (parallel CSV has since merged into the main branch)
https://github.com/duckdb/duckdb/pull/5194
The problem is perhaps most obvious when you consider that the value of a single cell could itself be a CSV, and this could be recursive
An unescaped cell within a CSV will break most CSV parsers in the world even with recursion handling.
https://datatracker.ietf.org/doc/html/rfc4180
CSVs are a bit of a mess. Non-trivial to parse properly, due to escaping, and the standard (such as it is) is poorly followed. E.g. sometimes people use semi-colon or pipe instead of comma.
See also: 'Why isn’t there a decent file format for tabular data?' https://news.ycombinator.com/item?id=31220841
This is also my use case where I have GBs of data in S3 as Parquet and a small CSV file that I have to join them to.
And DuckDB reads CSVs and Parquet and SQLite and others. I can join all these heterogeneous data types in a single SQL statement and have the assurance that it’ll be done correctly.
I believe clickhouse-local can do the same.
https://pandas.pydata.org/docs/reference/api/pandas.read_csv...
Parsing a CSV by hand is almost always the wrong thing to do unless you also wrote the CSV generating process and can limit the variance.
But if not, instead of working with CSVs at the string level and risk running into issues, I've found that the best practice is to farm the CSV parsing out to a battle-tested engine (I use DuckDB, but sqlite, pandas, polars, clickhouse-local or any number of engines work), and work with the parsed object instead.
Better yet, avoid CSV and use DuckDB + Parquet (paired with visidata for quick vizes) for anything nontrivial.
Not the right tool for everything, but it shines for quickly glancing at the shape of tabular data and making sense of it. Sorting, filtering, joins across files, column histograms and even column splitting/rejoining are all keystrokes away.
It groks anything even remotely table shaped like CSV, JSONL, JSON, even Excel files, it can even directly connect to databases and parse tables out of HTML.
https://www.visidata.org/
https://jsvine.github.io/intro-to-visidata/index.html
I am a big fan of VisiData for working with data in the terminal. I also like John Kerl's miller[1] and Simon Willison's sqlite-utils [2] when I want to do queries. These are useful for working with data in simple shell scripts.
[1] https://github.com/johnkerl/miller [2] https://github.com/simonw/sqlite-utils
I like using the shell (and Awk in particular!) as much as anyone, but for CSV I tend to reach for Python's standard csv module[1].
[1]: https://docs.python.org/3/library/csv.html
(With credit of course, if I do so.)
pq is overdue some maintenance but I will update it with the imminent PRQL 0.9 release.
I've used pq in anger at my $dayjob and found it incredibly productive to have the full power of SQL combined with the terse and logical syntax of PRQL.
It is the most powerful (works with any formats, with remote datasets, and supports all the ClickHouse SQL) and the most performant.
Would be nice to add a seamless ability to call executable UDFs from clickhouse-local, last time I checked clickhouse-local required executables to be in a special directory (as in proper clickhouse). Instead it'd be nice to be able to reference any executable in an ad-hoc way.
- It works with every data format which I come across in my daily job. It support data formats like Protobuf, Avro, Cap'n Proto which regular tools don't support. Funny thing, it can even read mysql dumps.
- I can read the data stored in local or remote location like http, s3, gcs, azure and whatever location I can think of.
- I can SQL queries on the raw data and improve my SQL skills on daily basis.
[1] https://github.com/ClickHouse/ClickHouse/tree/master#readme
The datasets that I work with are well small enough to fit in memory on my machine and having the full java ecosystem + clojure ergonomics is worth more to me than the performance full db tooling might offer
When I tell them to do it in Excel by themselves, they would say Excel couldn't open a CSV larger than 1M rows...
At first, I was using sqlite through shell. I hated it so much that I built a desktop app on top of it. It wasn't slick enough with all the typing.
It is quite a joy to use, and I'd love for people to try it out: https://superintendent.app (disclaimer: I'm the creator).
It’s $40/year for those wondering.
The price is very obvious on the website.
Users can't use it for an extended period of time without paying.
You said it like I was tricking people into paying somehow.
Hate to agree, but yes right there, 100 pixels above the download link, very nice and clear site, I heartily approve. Not sure how you'll get $1b of funding for something that user friendly though -- I don't see any dark patterns at all!
> Does your workplace reimburse software's cost?
I don't need your app, but my workplace certainly doesn't. I can buy all sorts of hardware from preferred suppliers through our purchasing system, I can with a few hoops generate a one-time credit card to buy a random thing from a random person (how I pay for starlink for example), and for some specific cases I can pay myself and claim back later
However software is blocked from all this.
> Even if Superintendent.app only saves you 30 minutes per month, it's already worth the minimal price tag that we will charge after beta.
To buy software in my company would take far longer than the 6 hours that I'd save each year! This of course is entirely the fault of my company, but I wonder how many other companies have similar policies.
What I can do though is spin up a AWS ami with a software charge and nobody bats an eyelid. I'm not sure how much of a "cut" aws takes, but something to think about.
(If it really helped me I'd just eat the $40 personally and take a day off in lieu, but I've known some people put in receiptless claims to get around the policy)
One thing I'm interested in is the dark pattern you accused me of.
Users literally cannot use it beyond the trial period if they don't pay, and I don't collect credit card info before the trial.
Are you saying someone might accidentally pay for this?
Almost a breath of fresh air!
Sure it can. Just use the "Import from Text/CSV" feature instead of directly opening the CSV. https://support.microsoft.com/en-us/office/what-to-do-if-a-d...
To be honest, I'm still trying to find more a product market fit.
It feels like a travel planning app where people might need it once every few months, so it doesn't quite catch on.
On another hand, I do have a few passionate users who analyze 40+ CSVs at the same time, but I'm struggling to improve UX for these users. Building great UI is hard. But maybe this is what I need to do.
awk -F, '{print $1}' file.csv
Doesn't work if the data contains commas (with escapes)? If so, that might be worth spelling out.
The frawk tool (written in Rust) also supports this.
Interestingly, Brian Kernighan is currently updating the book "The AWK Programming Language" for a second edition (I'm one of the technical reviewers), and Gawk and awk are adding a "--csv" option for this purpose. So real CSV mode is coming to an AWK near you soon!
Sure technically you can do things like having new lines on a line which cause all sorts of grief, but if you control the end to end workflow just avoid it.
I have a tool which monitors performance of loading various websites via various proxies at various points ok our network with pupetteer. That outputs a csv which I can then extract data from and make handy graphs to show manglement.
Yeah, I guess that kinda works if you're the one generating them. But you must not have too many generalised strings -- like a name field containing "Smith, John" or a book title like "The Lion, the Witch and the Wardrobe"? When I deal with CSV's those kind of things are quote common. I actually prefer TSV because you can (more easily) process them with standard AWK.
What do you output for a string field with ',' in it? Just strip the commas, or replace it with something else?
Related example is the recently discussed problems with filenames, and the many pitfalls that scripts can stumble upon when handling them.
The thing is that from the point of view of each individual tool, there isn't even an error! You ask awk to split on commas and give you the second field, it splits on commas and gives you the second field. It opened the file no problem, it didn't run out of memory writing its output to stdout... There were no errors to speak of from what it could see. The "error" is an error in the overall logic of the code.
The problem is that edge cases are not really edge cases… in certain locales. ;) Someone could be using commas as the decimal separator since that’s how they do it ’round these parts.[1][2] So it’s not like they’re storing “weird” stuff in tabular format, like HTML blog articles; they’re just storing numbers.
Of course, Excel and other software has tried to be way too clever about it with different separators depending on locale, which makes the whole pipeline complex and context-dependent.
[1] A surprising amount of countries have this convention, including my own… doesn’t make sense to me since it becomes harder to list numbers.
[2] And the number one selling point of CSV is that it is “human editable”, so of course someone is going to do that at some point in the pipeline.
<https://github.com/dbro/csvquote>
Using it with the first example command from this article would be
By using the "-u" flag in the last step of the pipeline, all of the problematic quoted delimiters get restored.https://stackoverflow.com/questions/43477652/ignoring-comma-...
https://archive.org/details/pdfy-MgN0H1joIoDVoIC7
GNU datamash (https://www.gnu.org/software/datamash/) provides features like groupby, statistical operations, etc.
See also this free ebook: Data Science at the Command Line (https://jeroenjanssens.com/dsatcl/)
https://www.nushell.sh/commands/docs/from_csv.html
I was rather skeptical on the value of having this stuff as a shell built in, but it won me over. Very convenient!