Using command line to process CSV files (2022)

https://muhammadraza.me/2022/data-oneliners/

by mr_o47 • 3 years ago

161 110 3 years ago

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).

https://csvkit.readthedocs.io/en/latest/

dgsb2• 3 years ago

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.

sklarsa• 3 years ago

Personally, I use xsv and it’s been tremendously helpful, especially when working with larger files. https://github.com/BurntSushi/xsv

elmolino89• 3 years ago

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.

By giant I mean 25G gzipped files with >10^9 rows like these VCFs: https://ftp.ncbi.nlm.nih.gov/snp/latest_release/VCF/

rzmk• 3 years ago

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.

[1] https://github.com/jqnatividad/qsv/blob/master/src/cmd/sqlp....

[2] https://github.com/jqnatividad/qsv/blob/master/src/cmd/to.rs...

keybored2• 3 years ago

I shared the qsv fork [1] yesterday which is more active.

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...

wenc6• 3 years ago

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).

  % duckdb -c "from 'test.csv'"
  ┌───────┬───────┬───────┐
  │   A   │   B   │   C   │
  │ int64 │ int64 │ int64 │
  ├───────┼───────┼───────┤
  │     1 │     2 │     3 │
  │     4 │     5 │     6 │
  │     7 │     8 │     9 │
  └───────┴───────┴───────┘

  % duckdb -c "select sum(C) from 'test.csv'"
  ┌────────┐
  │ sum(C) │
  │ int128 │
  ├────────┤
  │     18 │
  └────────┘

  % duckdb -c "select sum(A + 2*B + C^2) from 'test.csv'"
  ┌────────────────────────────────┐
  │ sum(((A + (2 * B)) + (C ^ 2))) │
  │             double             │
  ├────────────────────────────────┤
  │                          168.0 │
  └────────────────────────────────┘

theLiminator3• 3 years ago

iirc it's not possible to read csv in a parallel manner if your parsing true to spec?

pyeri2• 3 years ago

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".

wenc6• 3 years ago

My understanding is that the CSV is read in chunks, and then each chunk is parsed in a different thread.

This is the description in the experimental PR (parallel CSV has since merged into the main branch)

https://github.com/duckdb/duckdb/pull/5194

gabagool• 3 years ago

I didn't know that and my Google fu is lacking, does anyone have a resource I could read to understand that limitation?

hermitcrab5• 3 years ago

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.

See also: 'Why isn’t there a decent file format for tabular data?' https://news.ycombinator.com/item?id=31220841

up2isomorphism• 3 years ago

It is kinda sad that one has to go through all the database technologies just in order to read a csv file nowadays.

chaxor• 3 years ago

You don't have to, but you have to admit DuckDB is quite nice for handling csvs quite a bit more intuitively than awk

wenc6• 3 years ago

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.

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.

bjornasm• 3 years ago

You don't gave to, but it makes it easier. New tools that helps my work makes me happy.

psanford• 3 years ago

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.

dgsb2• 3 years ago

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.

petre• 3 years ago

I use miller, as it's available on my distro.

ldmosquera• 3 years ago

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.

https://www.visidata.org/

cool-RR• 3 years ago

VisiData is amazing. It's one of the coolest open source projects I've seen in years. I use it extensively in my research.

laktak• 3 years ago

An intro to the SQL interface (vdsql) can be seen here: https://www.visidata.org/blog/2022/connect-visidata-to-sql-d...

leonim• 3 years ago

Also, there is the VisiData Tutorial by Jeremy Singer-Vine. This is a very helpful introduction to VisiData.

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

woodruffw• 3 years ago

It's worth noting that a lot of these tricks will break on non-trivial CSV inputs, e.g. ones that contain escaped commas.

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

renewiltord2• 3 years ago

A thing Python's csv module enables that many others don't is ragged CSVs (column numbers not all same).

dima55• 3 years ago

Lots of tools do this sort of thing. An incomplete list is in the vnlog docs: https://github.com/dkogan/vnlog/#description

LeonB• 3 years ago

Very nice … will see what I can snaffle for adding to this list: https://github.com/secretGeek/AwesomeCSV

(With credit of course, if I do so.)

snthpy• 3 years ago

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.

zX41ZdbW3• 3 years ago

My favorite tool is clickhouse-local: https://clickhouse.com/blog/extracting-converting-querying-l...

It is the most powerful (works with any formats, with remote datasets, and supports all the ClickHouse SQL) and the most performant.

andreypopp• 3 years ago

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.

pradeepchhetri• 3 years ago

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.

elyobo• 3 years ago

clickhouse.com is down for me, so for those looking for it the github repo[1] may be more useful

[1] https://github.com/ClickHouse/ClickHouse/tree/master#readme

zX41ZdbW3• 3 years ago

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.

renewiltord2• 3 years ago

Great tip, thank you.

pyeri2• 3 years ago

Python's pandas library has ability to seamlessly work with csv by importing/exporting them as data frames.

jiehong• 3 years ago

If you use powershell, you can directly run Import-Csv and don’t really have to think about it.

vajdagabor• 3 years ago

Nushell is also quite powerful for this. For example:

  > open people.csv | where status == 'customer' | unique-by email | select surname forename email | sort-by email | save customers.json

https://www.nushell.sh/

bobnamob• 3 years ago

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

tanin4• 3 years ago

In my day job, a customer would ask me to reconcile between 2 giant CSVs very often. Think GBs.

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).

wodenokoto• 3 years ago

You’re hiding the price quite well (although at least you’re not hiding the fact that it has a price)

It’s $40/year for those wondering.

tanin4• 3 years ago

I wasn't trying to hide its price.

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.

xarope• 3 years ago

OMG $40/year?!? Most outfits would charge $40/month/user/basic, add $100/month/user/professional, $CALLUS for enterprise.

Almost a breath of fresh air!

adammarples• 3 years ago

Right on the main page under a Pricing subheader...

hfkwer3• 3 years ago

When I tell them to do it in Excel by themselves, they would say Excel couldn't open a CSV larger than 1M rows...

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...

maleldil• 3 years ago

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.

tanin4• 3 years ago

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.

hermitcrab5• 3 years ago

Presumably this command:

awk -F, '{print $1}' file.csv

Doesn't work if the data contains commas (with escapes)? If so, that might be worth spelling out.

benhoyt3• 3 years ago

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!

midasuni3• 3 years ago

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.

meepmorp• 3 years ago

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.

benhoyt3• 3 years ago

avoid quotes when generating them

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?

zokier• 3 years ago

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.

hfkwer3• 3 years ago

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.

keybored2• 3 years ago

but fall apart on edge cases.

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.

dbro• 3 years ago

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.

<https://github.com/dbro/csvquote>

Using it with the first example command from this article would be

  csvquote file.csv | awk -F, '{print $1}' | csvquote -u

By using the "-u" flag in the last step of the pipeline, all of the problematic quoted delimiters get restored.

globular-toast2• 3 years ago

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?

benhoyt3• 3 years ago

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

ufo• 3 years ago

A second edition is coming?? Awesome!

antegamisou• 3 years ago

FPAT declaration is a workaround for ignoring commas

https://stackoverflow.com/questions/43477652/ignoring-comma-...

chrishill893• 3 years ago

That deals with commas but (since AWK is line-oriented) I doubt that deals with multi-line fields?

jjice• 3 years ago

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!

chasil• 3 years ago

The first edition has been online for free for some time, in case you want to share it or use it in cases where the hardcopy is less convenient.

https://archive.org/details/pdfy-MgN0H1joIoDVoIC7

tinus_hn• 3 years ago

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.

hfkwer3• 3 years ago

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.

dash2• 3 years ago

There's a nice tool called dplyr-cli which lets you use R's dplyr data manipulation language on the command line.

    cat mtcars.csv | group_by cyl | summarise "mpg = mean(mpg)" | kable
    #> | cyl|      mpg|
    #> |---:|--------:|
    #> |   4| 26.66364|
    #> |   6| 19.74286|
    #> |   8| 15.10000|

https://github.com/coolbutuseless/dplyr-cli

asicsp• 3 years ago

For field extraction (like the first example: `awk -F, '{print $1}'`), you can also use `cut -d, -f1`

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/)

thibran• 3 years ago

Nushell can open and write CSV. Those AWK commands look horrible compared to the nu syntax.

https://www.nushell.sh/commands/docs/from_csv.html

karlmdavis• 3 years ago

Nu is using a data frame abstraction to drive this and it’s really quite powerful and fast.

I was rather skeptical on the value of having this stuff as a shell built in, but it won me over. Very convenient!

9735194• 3 years ago

Can’t wait for the tutorial on ‘ls’. It’s not like there is a manual.

kohlerm• 3 years ago

xsv works pretty well for me

greazy• 3 years ago

I recommend everyone checkout the very cool csvtk by the amazing bioinformstician Shen Wei

https://github.com/shenwei356/csvtk

zh3• 3 years ago

'tr' and 'cut' are also very useful; 'tr' can be used to get rid of extra spaces and to conver commas to spaces and vice versa (and to handle text data with pretty much any character used as a separator).

  'cat $SPACE_SEPARATED_FILE | tr -s ' ' | tr ' ' ',' > out.csv

Allied with 'cut', it becomes easy to pull particular fields out of a text file:-

  'cat $FILE_WITH_COMMAS_AND_SPACES | tr ',' ' ' | tr -s ' ' | cut -d ' ' -f1,2,17 > out.txt

globular-toast2• 3 years ago

None of this stuff works.

Whenever CSV comes up I always feel a bit sad. ASCII includes a set of four out-of-band delimiters[0] that can be used instead of silly formats like CSV that use in-band delimiters which necessitate complicated quoting rules.

You can't just treat CSV as text. It's not and woe betide you if you ever use this stuff in a script instead of using a proper CSV parsing tool. If we used the ASCII delimiters instead it would be possibly to treat it as text and stuff like this would work.

[0] https://en.wikipedia.org/wiki/Delimiter#ASCII_delimited_text

chrishill893• 3 years ago

I agree. And some tools support it, e.g. Miller.[1]

There are a lot of power tools for CSV. All they need to do to support this format is to support it as input and output. It should be possible for example to add support to Csvkit[2] as input/output ("format").

Just based on HN comments alone (these things always come up) there is a decent demand for this kind of format. And there are a few implementations out there. But the implementations often look more like standards or proofs of concepts rather than full-fledged implementations. (A full-fledged implementation should probably provide functions/programs to convert to and from CSV as described in RFC-4180.)

We could make this happen.

[1] https://news.ycombinator.com/item?id=31258316

[2] https://csvkit.readthedocs.io/en/latest/scripts/in2csv.html

ykonstant• 3 years ago

Preach; the POSIX standard also understands this, and partitions control characters into portable and non-portable ones; the ASCII field/record/group delimiters are designated as non-portable, and therefore can be safely used to delimit portable character data. The fact that the whole file is then non-portable is a feature, not a bug; if you need to pack an entire table into a field, encode it in some portable encoding (eg base64) and add a decoder field.

The "arguments" against this are absurd; mainly that the resulting file is not plain text and cannot be read/modified immediately by plain text editors. This ignores the fact that plain text editors already deal with control characters because they have to decide what to do with <tab>, and makes a mountain out of a molehill when the in-band horrors of escaping delimiters create trouble every single day.

chrishill893• 3 years ago

The fact that the whole file is then non-portable is a feature, not a bug; if you need to pack an entire table into a field, encode it in some portable encoding (eg base64) and add a decoder field.

I guess you could:

1. Use unit-separator and record-separator for text

2. Use group-separator for (1)

3. Use file-separator for (2)

The "arguments" against this are absurd; mainly that the resulting file is not plain text and cannot be read/modified immediately by plain text editors. This ignores the fact that plain text editors already deal with control characters because they have to decide what to do with <tab>, and makes a mountain out of a molehill when the in-band horrors of escaping delimiters create trouble every single day.

Could not agree more.

hermitcrab5• 3 years ago

Yes, has been discussed before. https://news.ycombinator.com/item?id=31220841

The only thing needed for this to work really well for text editors to treat the end of record character like a carriage return (I'm not aware of any that do).

nbk_2000• 3 years ago

Just thought I'd plug Octosql[1] which I've enjoyed using for this. It parses CSV and JSON, which are the file types I parse the most.

[1] https://github.com/cube2222/octosql/

activiation• 3 years ago

Interesting but not very intuitive.

two_handfuls• 3 years ago

I think the lesson here is don’t use awk for CSV. Instead, use one of the many tools discussed in comments that knows how to handle CSV.

Some are very close to awk in spirit, like my own attempt: `pawk` (1). It will parse your csv just fine. Or tsv. Or JSON or YAML or TOML. Or Parquet, even.

1: https://github.com/jean-philippe-martin/pawk

oofnik• 3 years ago

Many command-line CSV parsing tools mentioned here, adding my choice to the list:

http://harelba.github.io/q/

cutler• 3 years ago

Title should have been "Using awk to process CSV files".

replwoacause• 3 years ago

I use PowerShell for this and it works very well.

SPBS• 3 years ago

I don't understand why this is so high when it's just using awk to naively split on commas. Any programming language could do that! I was expecting an actual command line tip that can handle CSV files in general, looks like it's still basically impossible without resorting to a full blown CSV parser (none of which come installed by default).

LispSporks222• 3 years ago

Ruby's CSV module can be handy:

    ruby -rcsv -ne 'CSV($<).each { |r| puts r[0] }'

I like the seen example the dude has:

    '!seen[$1]++'

mr_o47• 3 years ago

I didn’t know you could do that in ruby but its pretty cool

afefers2• 3 years ago

As someone who's new to programming and doesn't know Ruby at all, can you (or someone) please explain how it works, what it does, and how would someone use it?

I find it beautiful, tho :)

LispSporks222• 3 years ago

    -rcvs

Load the (built-in) CSV module in Ruby.

-e

Eval the following string as Ruby code.

    CSV($<)

Create CSV parser with standard input `$<` as the source.

    .each

Run the code in the block that follows for each row (automatically skips the CSV header):

    { |r| puts r[0] }

For a row, print the first/0th element. Can be simplified in recent Ruby:

    { puts _1[0] }

chrisshroba2• 3 years ago

I have this shell function defined:

csv_to_json () { python -c 'import csv, json, sys; print(json.dumps([dict(r) for r in csv.DictReader(sys.stdin)]))' | jq . }

It converts a csv to a json list of objects, mapping column names to values. I find it way easier to then operate on json by filtering with jq or gron, or just pasting it into other tools for post-processing. The jq at the end isn't necessary but makes for nice formatting!

sgarland• 3 years ago

That won’t scale well to files approaching or exceeding system RAM, though.

Also, unless it’s the coloring you’re concerned with, you could replace the pipe to jq with pprint, a native Python library.

chrisshroba2• 3 years ago

pprint won't work here because the output is a json string, not a python object, so pprint'ing it will just print the string as is. And plus, pprint doesn't output valid json (it uses single quotes, for example), so the output isn't usable for piping into other filters and programs.

And it's true that it won't scale well but it still has worked every time I've wanted it to :)

vladxyz• 3 years ago

I'm two weeks late, but you could do something like

   python -c 'import csv, json, sys; [print(json.dumps(r)) for r in csv.DictReader(sys.stdin)]' | jq -s .

to stream the csv rows instead of reading the whole file into ram.

PhilippGille• 3 years ago

Many of the tools shared in this thread simplify working with CSV files, but only some allow running proper SQL queries.

SQLite, DuckDB and Clickhouse-local have been mentioned, but another very simple one, a single dependency-free binary, is https://github.com/multiprocessio/dsq

Not affiliated, just a happy user

zX41ZdbW3• 3 years ago

Just to note, ClickHouse is also dependency-free single binary, following https://notes.volution.ro/v1/2022/01/notes/fbf3f06c/

You can get it as

    https://clickhouse.com/ | sh

Using command line to process CSV files (2022)

Related Stories