9 ms·
Using command line to process CSV files (2022)
- kippinitreal 3y agoCool 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/ https://csvkit.readthedocs.io/en/latest/
- dgsb 3y agoI 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 3y agoPersonally, I use xsv and it’s been tremendously helpful, especially when working with larger files. https://github.com/BurntSushi/xsv https://github.com/BurntSushi/xsv
- elmolino89 3y agoxsv 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/ https://ftp.ncbi.nlm.nih.gov/snp/latest_release/VCF/
- rzmk 3y agoMaybe 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.rs#L2 https://github.com/jqnatividad/qsv/blob/master/src/cmd/sqlp.... [2] https://github.com/jqnatividad/qsv/blob/master/src/cmd/to.rs#L2 https://github.com/jqnatividad/qsv/blob/master/src/cmd/to.rs...
- keybored 3y agoI 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 https://github.com/jqnatividad/qsv [2] https://github.com/jqnatividad/qsv/discussions/290#discussion-4075202 https://github.com/jqnatividad/qsv/discussions/290#discussio...
- deleted 3y ago[deleted]
- wenc 3y agoFor 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 │ └────────────────────────────────┘
- theLiminator 3y agoiirc it's not possible to read csv in a parallel manner if your parsing true to spec?
- pyeri 3y agoYou 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".
- wenc 3y agoMy 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 https://github.com/duckdb/duckdb/pull/5194
- bombolo 3y agoSounds like a good way to fail to parse all the possible files properly.
- petre 3y agoI use miller, as it's available on my distro.
- ldmosquera 3y agoHave 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/ https://www.visidata.org/
- cool-RR 3y agoVisiData is amazing. It's one of the coolest open source projects I've seen in years. I use it extensively in my research.
- laktak 3y agoAn intro to the SQL interface (vdsql) can be seen here: https://www.visidata.org/blog/2022/connect-visidata-to-sql-databases-with-vdsql/ https://www.visidata.org/blog/2022/connect-visidata-to-sql-d...
- leonim 3y agoAlso, 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 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 https://github.com/johnkerl/miller [2] https://github.com/simonw/sqlite-utils https://github.com/simonw/sqlite-utils
- woodruffw 3y agoIt'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 https://docs.python.org/3/library/csv.html
- renewiltord 3y agoA thing Python's csv module enables that many others don't is ragged CSVs (column numbers not all same).
- snthpy 3y agoShameless plug: I created prql-query (https://github.com/PRQL/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.
- dima55 3y agoLots of tools do this sort of thing. An incomplete list is in the vnlog docs: https://github.com/dkogan/vnlog/#description https://github.com/dkogan/vnlog/#description
- LeonB 3y agoVery nice … will see what I can snaffle for adding to this list: https://github.com/secretGeek/AwesomeCSV https://github.com/secretGeek/AwesomeCSV (With credit of course, if I do so.)
- zX41ZdbW 3y agoMy favorite tool is clickhouse-local: https://clickhouse.com/blog/extracting-converting-querying-local-files-with-sql-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 3y agoAgreed 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 3y agoAs 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 3y agoclickhouse.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 https://github.com/ClickHouse/ClickHouse/tree/master#readme
- zX41ZdbW 3y agoWe 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.
- renewiltord 3y agoGreat tip, thank you.
- pyeri 3y agoPython's pandas library has ability to seamlessly work with csv by importing/exporting them as data frames.
- jiehong 3y agoIf you use powershell, you can directly run Import-Csv and don’t really have to think about it.
- vajdagabor 3y agoNushell 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/ https://www.nushell.sh/
- deleted 3y ago[deleted]
- bobnamob 3y agoI'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
- tanin 3y agoIn 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 https://superintendent.app (disclaimer: I'm the creator).
- wodenokoto 3y agoYou’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.
- tanin 3y agoI 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.
- iso1631 3y ago> The price is very obvious on the website. 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)
- hermitcrab 3y agoPresumably 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.
- benhoyt 3y agoUnfortunately "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/ 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!
- midasuni 3y agoI 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 3y agoQuotes 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.
- benhoyt 3y 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?
- deleted 3y ago[deleted]
- hfkwer 3y agoThese 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 3y agoThere'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 https://github.com/coolbutuseless/dplyr-cli
- asicsp 3y agoFor 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/ 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://jeroenjanssens.com/dsatcl/)
- thibran 3y agoNushell can open and write CSV. Those AWK commands look horrible compared to the nu syntax. https://www.nushell.sh/commands/docs/from_csv.html https://www.nushell.sh/commands/docs/from_csv.html
- karlmdavis 3y agoNu 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 3y agoCan’t wait for the tutorial on ‘ls’. It’s not like there is a manual.
- kohlerm 3y agoxsv works pretty well for me
- greazy 3y agoI recommend everyone checkout the very cool csvtk by the amazing bioinformstician Shen Wei https://github.com/shenwei356/csvtk https://github.com/shenwei356/csvtk
- zh3 3y 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-toast 3y agoNone 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 https://en.wikipedia.org/wiki/Delimiter#ASCII_delimited_text
- chrishill89 3y agoI 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 https://news.ycombinator.com/item?id=31258316 [2] https://csvkit.readthedocs.io/en/latest/scripts/in2csv.html https://csvkit.readthedocs.io/en/latest/scripts/in2csv.html
- ykonstant 3y agoPreach; 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.
- nbk_2000 3y agoJust 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/ https://github.com/cube2222/octosql/
- activiation 3y agoInteresting but not very intuitive.
- two_handfuls 3y agoI 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 https://github.com/jean-philippe-martin/pawk
- oofnik 3y agoMany command-line CSV parsing tools mentioned here, adding my choice to the list: http://harelba.github.io/q/ http://harelba.github.io/q/
- cutler 3y agoTitle should have been "Using awk to process CSV files".
- replwoacause 3y agoI use PowerShell for this and it works very well.
- SPBS 3y agoI 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).
- LispSporks22 3y agoRuby'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 3y agoI didn’t know you could do that in ruby but its pretty cool
- afefers 3y agoAs 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 :)
- LispSporks22 3y 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] }
- afefers 3y agoThank you so much! This got me looking at the Ruby documentation about blocks. Very cool feature!
- chrisshroba 3y agoI 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!
- vladxyz 3y agoI'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.
- sgarland 3y agoThat 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.
- chrisshroba 3y agopprint 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 :)
- PhilippGille 3y agoMany 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 https://github.com/multiprocessio/dsq Not affiliated, just a happy user
- zX41ZdbW 3y agoJust to note, ClickHouse is also dependency-free single binary, following https://notes.volution.ro/v1/2022/01/notes/fbf3f06c/ https://notes.volution.ro/v1/2022/01/notes/fbf3f06c/ You can get it as https://clickhouse.com/ | sh