Working with CSV files on shell/terminal

Re:Working with CSV files on shell/terminal by wenc on Wednesday June 28, 2023 @01:53AM (#36502792)

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.

[ Parent | on HN ]

Re:Working with CSV files on shell/terminal by kwoff on Wednesday June 28, 2023 @12:20PM (#36508682)

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

  perl -MText::CSV=csv -e'my $aoh = csv(in=>"foo.csv",headers=>"auto",keep_headers=>\my @h); @$aoh = grep { $_->{some_header} > 0 } @$aoh; csv(in=>$aoh,out=>*STDOUT,headers=>\@h)'

[ Parent | on HN ]