Modernizing AWK, a 45-year old language, by adding CSV support

https://benhoyt.com/writings/goawk-csv/?m

by benhoyt • 4 years ago

258 143 4 years ago

mattewong6• 4 years ago

thanks for this. am looking at the benchmarks. how do I get huge.csv? Don't see how to fetch or generate

mattewong6• 4 years ago

FYI I ran on the worldcities data at https://github.com/petewarden/dstkdata (credit to xsv for choosing that dataset) against https://github.com/BurntSushi/xsv and https://github.com/liquidaty/zsv (full disclosure: I am one of the zsv authors). Here's what I got.

fastest to slowest: zsv (0.07), xsv (0.16), goawk (0.42), python (~1.6)

Obviously, does not tell the whole story as this test was limited to "count" and an interpreted language is expected to always be slower compared to a precompiled command, but, it might be relevant to a user deciding what tool to use. Also, might be instructive as to room for improvement in the go code (or possibly the go code could use the c lib)-- I note that even if the goawk command is '{}' the runtime is still about the same.

full results:

---

goawk:

1000001 7000007 real 0m0.435s user 0m0.435s sys 0m0.031s

1000001 7000007 real 0m0.413s user 0m0.419s sys 0m0.024s

1000001 7000007 real 0m0.425s user 0m0.430s sys 0m0.024s

xsv:

1000000 real 0m0.157s user 0m0.141s sys 0m0.013s

1000000 real 0m0.156s user 0m0.141s sys 0m0.012s

1000000 real 0m0.158s user 0m0.142s sys 0m0.013s

zsv:

1000000 real 0m0.066s user 0m0.053s sys 0m0.010s

1000000 real 0m0.077s user 0m0.060s sys 0m0.012s

1000000 real 0m0.069s user 0m0.056s sys 0m0.010s

python:

1000001 7000007 real 0m1.589s user 0m1.553s sys 0m0.026s

1000001 7000007 real 0m1.583s user 0m1.550s sys 0m0.025s

1000001 7000007 real 0m2.122s user 0m1.675s sys 0m0.037s

---

The script for this was:

---

echo 'goawk:'

(time goawk -i csv '{ w+=NF } END { print NR, w }' < worldcitiespop_mil.csv) 2>&1 | xargs

echo 'xsv:'

(time xsv count < worldcitiespop_mil.csv) 2>&1 | xargs

echo 'zsv:'

(time zsv count < worldcitiespop_mil.csv) 2>&1 | xargs

echo 'python:'

(time python3 count.py < worldcitiespop_mil.csv) 2>&1 | xargs

benhoyt10• 4 years ago

Thanks for that! zsv looks amazing. Yeah, it's definitely going to whip GoAWK, what with SIMD parsing and careful memory handling. I've done a couple of basic things for GoAWK's CSV performance, but haven't profiled or looked at allocation bottlenecks (absolute performance definitely wasn't my first focus).

Yeah, sorry about huge.csv -- I found it online somewhere originally by searching for something like "large csv example", but can't for the life of me find it now. It's a monster 1.5GB file with 286 columns including quoted fields (whereas worldcitiespop only has a few columns and it doesn't look like it has quoted fields). I can upload to a file transfer service and send a link to you if you want ... though I should really update my benchmarks to use an easily-downloadable file instead.

benhoyt10• 4 years ago

I've now updated the benchmarks to avoid huge.csv: the write benchmarks now write a big 1GB, 20-column CSV and the read benchmarks use that same CSV: https://github.com/benhoyt/goawk/commit/07eb4505a7f64cffceeb...

malkocoglu• 4 years ago

Modernizing ... by adding CSV support !?!

benhoyt10• 4 years ago

Fair comeback. I think of CSV as modern, but Wikipedia tells me it's almost as old as AWK (depending on how you count). It seems to me it's used more heavily now as an exchange format, compared to say 15-20 years ago, but I could be wrong.

londons_explore• 4 years ago

JSON is an exchange format... sqlite is an exchange format... even protocol buffers are an exchange format...

CSV is only an exchange format if there is no user generated strings in the data... If there are, then you'll almost certainly screw up the encoding when someones name has a newline or comma or speech mark in it, or some obscure unicode etc. Even moreso if awk is part of your toolkit.

imtringued2• 4 years ago

Every CSV "document" is its own file format so yes, it is as modern as your software.

rgoulter2• 4 years ago

I'd be curious to see a comparison with the csvkit suite. https://csvkit.readthedocs.io/en/latest/index.html

benhoyt10• 4 years ago

That's written in Python and uses the "agate" library which uses Python's built-in "csv" module. I did a couple of simple benchmarks against Python's csv module in the article: https://benhoyt.com/writings/goawk-csv/#performance (Go/GoAWK is a bit more than 3x as fast)

I also did a quick test using csvcut to pull out a single field, and compared it to GoAWK. Looks like GoAWK is about 4x as fast here:

  $ time csvcut -c agency_id huge.csv >/dev/null

  real 0m25.977s
  user 0m25.240s
  sys 0m0.424s
  $ time goawk -i csv -H -o csv '{ print @"agency_id" }' huge.csv >/dev/null

  real 0m6.584s
  user 0m7.434s
  sys 0m0.480s

rgoulter2• 4 years ago

I meant more in terms of user/developer experience.

I imagine there's some use case where AWK will be able to come up with output that csvkit can't.

But for simple cases, csvkit's invocation is easier to remember.

skanga2• 4 years ago

I always use mawk for its performance. This may be worth a try ...

benhoyt10• 4 years ago

If you're just using AWK features, mawk is still the fastest. GoAWK is faster than awk in most cases, on a par with gawk, but still not as fast as mawk (see https://benhoyt.com/writings/goawk-compiler-vm/#virtual-mach...) ... except for scripts that make heavy use of regexen. Unfortunately Go's regexp package is still quite slow. You could also try frawk, which is a JIT-optimized AWK written in Rust -- it's really fast, and shares some of the CSV features (but not the @"named-field" syntax).

But for most everyday usage, even for large inputs, GoAWK's performance is quite sufficient. The CSV support, and its use as a Go library -- they're more important to me than raw speed at this point.

skanga2• 4 years ago

Thanks for such a detailed and informative answer. Much appreciated.

jeroenjanssens• 4 years ago

I often use csvquote [1] whenever I need to process CSV with a command-line tool that doesn't support it. For example:

    csvquote test.csv | awk '{print $1, $2}' | csvquote -u

[1] https://github.com/dbro/csvquote

throwawayboise• 4 years ago

Yep, was going to post the same if someone hadn't already. This is the unix way. Small tools with narrow focus, strung together in pipelines.

sophacles• 4 years ago

awk is a tool for handling records in text. csv is a textual format for records.

I'd argue that needing a different tool for handling records in text so that you can pass it to a tool for handling records in text is a bit too far.

jcelerier2• 4 years ago

ah yes, awk's famously narrow focus, covering things such as networking (https://www.gnu.org/software/gawk/manual/html_node/TCP_002fI...) or video games (https://github.com/mikkun/AWKTC). What do you mean your blog isn't served by awk (https://www.gnu.org/software/gawk/manual/gawkinet/html_node/...) as a VRML world (https://www.gnu.org/software/gawk/manual/gawkinet/html_node/...) ?

dev_tty01• 4 years ago

I think getting awk to recognize that a field separator within a quoted string should be ignored is a great addition. This is not inconsistent with the "unix way." Many, many unix tools recognize that a quoted string should be treated as a separate entity. The more unix-like approach would have been to force users to remove quotes if they want awk to split strings based on field separators within quotes. In hindsight, I'm surprised the quote respecting option wasn't added a long time ago.

kazinator• 4 years ago

The article mentions a different such a filter called "csvquote", written in Go:

https://github.com/adamgordonbell/csvquote

The History section of the readme explains that they are historically related.

ognyankulev2• 4 years ago

Instead of yet another limited parser, it would be best if universal tabular data parsing is supported by allowing one to specify all important parsing parameters, as described in https://www.w3.org/TR/2015/REC-tabular-metadata-20151217/#di...

junon• 4 years ago

Those parameters are URL/Header/descriptor parameters. They don't live in the CSV itself.

ognyankulev2• 4 years ago

I meant to have these parameters as awk options, supplied on command line, from envvar, or maybe even as awk variables.

asicsp2• 4 years ago

A big thank-you to the library of the University of Antwerp, who sponsored this feature. They’re one of two major teams or projects I know of that use GoAWK – the other one is the Benthos stream processor.

That's great to hear.

Are you planning to add support for xml, json, etc next? Something like Python's `json` module that gives you a dictionary object.

benhoyt10• 4 years ago

I'm not considering adding general structured formats like XML or JSON, as they don't fit AWK's "fields and records" model or its simplistic data structure (the associative array) very well. However, I have considered adding JSON Lines support, where each line is a record, and fields are indexable using the new @"named-field" syntax (possibly nested like @"foo.bar").

asicsp2• 4 years ago

Thanks, JSON Lines support would be welcome too :)

andi9992• 4 years ago

Can you also set the decimal separator? Some countries use ',' in numbere like 10,5

ta8645• 4 years ago

Can we please, please, stop that? If we go Metric, will you please standardize on period as decimal point? Shake on it. Let's make it happen.

bloak• 4 years ago

Somebody told me that the official international standard prefers comma; a period is just a permitted alternative. Of course, comma as decimal separator isn't very compatible with programming languages.

However, the decimal separator that I was taught at primary school and have used in handwriting all my life is neither comma, nor period, but '·'.

Hackbraten• 4 years ago

Germany has been metric forever but has the comma as a decimal separator, and the dot as a thousands group separator. It’s deeply engrained in our typography, it has typographical idioms such as ,— (comma em-dash), up to the point that even brands have adopted it in their brand name.

I’m afraid we can’t really change that on a whim. Why would we, anyway?

slightwinder4• 4 years ago

There are more countries using the comma than the dot. Why should the majority follow a minority in that case?

gpvos7• 4 years ago

No. I agree it should be universally used for machine-readable data formats, but for human consumption there is no need to be anglo-centric.

You guys are already slowly encroaching on milliards and billiards in other languages with your illogical short scale numbers.

Findecanor• 4 years ago

It is not a matter of what I use. It is a matter of what the data uses.

otikik2• 4 years ago

In my country (Spain) we traditionally use commas as a decimal separator, but I think CSV should not support this.

The way I see it, CSV's purpose is information storage and transfer, not presentation.

Presentation is where you decide the font face, the font size, cell background color (if you are viewing the file through a spreadsheet editor) etc. Number formatting belongs here.

In information transfer and storage it's much more important that the parsing/generation functions are as simple as possible. So let's go with one numeric format only. I think we should use the dot as a decimal separator since it's the most common separator in all programming languages. Maybe extend it to include exponential notation as well, because that is what other languages like json support. But that's it.

I hold the same opinion about dates tbh.

(The same goes for dates, btw - yyyy-mm-dd or death)

hedora3• 4 years ago

For time formats, use ISO 8601, and be sure to append a Z to the end to denote UTC (zulu time), and consider rejecting timestamps without it:

https://en.m.wikipedia.org/wiki/ISO_8601

The only real problem with that format is the distasteful "T" in the middle (but, hey, at least it is whitespace-free!)

andi9992• 4 years ago

What do you mean by csv shd not support this. Do you mean it was a historical mistake or new parsers shdnt be able to read it?

jph2• 4 years ago

Ben this is great, thank you. Would you consider adding Unicode Separated Values (USV)?

https://github.com/sixarm/usv

USV is like CSV and simpler because of no escaping and no quoting. I can donate $50 to you or your charity of choice as a token of thanks and encouragement.

tomsmeding2• 4 years ago

no escaping

That makes no sense. Sure, they've chosen significantly less common separator characters than something like ','. but they are still characters that may appear in the data. How do you represent a value containing ␟ Unit Separator in USV?

In-band signalling is ever going to remain in-band signalling. And in-band signalling will need escaping.

jph2• 4 years ago

USV has no escaping on purpose because it's simpler and faster.

USV is for the 99.999% of cases that don't embed USV Control Picture characters in the content.

If you need escaping, then I can suggest you consider USVX which is USV + extensions for escaping and other conveniences, or you can use your content's own escaping such as ampersand escaping for HTML text, or you can use any other format such as JSON, XML, SQL, etc.

imtringued2• 4 years ago

Just reject nested USV documents in the spec. Use something else if you want nested data.

deckard1• 4 years ago

In-band signalling is [n]ever going to remain in-band signalling

This is the root of all spreadsheet evil, right here.

TSV. Replace/strip/ban tab + newline from fields when writing. Done. If you need those characters, encode them using backslashes if you absolutely must (i.e. you're an Excel weenie doing Excel weenie things that really aren't "spreadsheet" things but since you only know of one hammer you then use that hammer to do everything)

benhoyt10• 4 years ago

Thanks. I haven't tried this, but it should actually work already in standard AWK input mode with FS and RS set to those Unicode separators. I'll test it tomorrow when back at my laptop.

benhoyt10• 4 years ago

Yep, this works fine (in GoAWK, gawk, and mawk, though not in original awk):

  $ cat t.usv && echo
  id␟name␟age␞1␟Bob "Billy" Smith␟42␞2␟Jane
  Brown␟37
  $ goawk -F␟ -vRS=␞ -vOFS=, '{ print $1, $2, $3 }' t.usv 
  id,name,age
  1,Bob "Billy" Smith,42
  2,Jane
  Brown,37

I've also added explicit tests for ASCII and Unicode unit and record separators, just to ensure I don't regress: https://github.com/benhoyt/goawk/commit/215652de58f33630edb0...

cylinder714• 4 years ago

More expressions of the same idea:

https://www.ccsv.io/

And this perennial favorite here at HN:

https://ronaldduncan.wordpress.com/2009/10/31/text-file-form...

I've been toying with the idea of starting a change.org campaign, asking Microsoft to add support to Excel for importing and exporting such files. I don't see any way to make any progress if Microsoft won't push the industry forward.

hawski• 4 years ago

It is a nice addition, but I would like to see this taken further - structural regular expression awk. It is waiting to be implemented for 35 years now.

mro_name2• 4 years ago

I recently learned via https://news.ycombinator.com/item?id=31257248 that ASCII has the idea of records and fields ever since. It's just not used, but workaround CSV.

No improvement of CSV handling will ever improved on that.

cb3217• 4 years ago

See the "Conventions for lossless conversion to TSV" on the current version of https://en.wikipedia.org/wiki/Tab-separated_values . There are really only 3 chars to escape - the escape char, and 2 delimiters - to make everything easy to deal with (even binary data in fields - what "lossless" means here).

belter• 4 years ago

Discussed here before, not everybody was seeing only positives.

"ASCII Delimited Text – Not CSV or TAB delimited text": https://news.ycombinator.com/item?id=7474600

Chris20482• 4 years ago

A big problem is tooling - what they currently support, and the fact that excel will f*ck up anything.

I suppose the best way would be a suite of tools to edit them that are compatible with existing editors? TBH a great value-add for adoption would be versioning s.t. you can see (and revert) when other tools mess up your files.

RhysU• 4 years ago

Could one get away with regular AWK using these combined with a streaming to/from CSV converter?

cb3217• 4 years ago

Not just "regular awk", but "regular most things", like head, tail, grep, etc. The "things" just need to be 8-bit/NUL handling clean and the data could even have binary fields. I mean, if there is a library like Python/Go/etc. that you especially trust to write a streaming converter then that is a fine approach, but otherwise see both https://news.ycombinator.com/item?id=31352517 and https://news.ycombinator.com/item?id=31352704 (at least!)

mmcgaha• 4 years ago

Yes and I suspect that this is why it was never added to AWK. I,and I am sure most people, have an AWK filter to transform their csv or whatever format to a format that AWK can use with an appropriate FS and RS.

Their "but why" section should really go into more detail about why filters are not 100% if your data has the possibility of containing your preferred line/record separator.

But real world, I have never had a situation where a csv-to-awk filter (written in awk of course) did not work.

andylynch• 4 years ago

Closely related- I remain mystified as to why FIX Protocol chose to use control characters to separate fields, but used SOH rather than RS/US or the like.

kevin_thibedeau• 4 years ago

Because of EBCDIC.

carapace• 4 years ago

I'm just gonna leave this here.

https://git.sr.ht/~sforman/sv/tree/master/item/sv.py

(Python code for ASCII-separated values. TL;DR: it's so stupid simple that I feel any objection to using this format has just got to be wrong. It's like "Hey, we don't have to keep stabbing ourselves in the face." ... "But how will we use our knives?" See? It's like that.)

a13692099932• 4 years ago

any objection to using this format

How do I edit the data file in a text editor?

More generally, how do I edit the entire data file, including field/record/etc seperators, in a editor that can display only characters that are valid content of data fields?

(Not that CSV (barring de-facto-nonstandard extensions like rfc4180's `"b CRLF bb"` and `"b""bb"`) is any good for this either, of course.)

cb3217• 4 years ago

When you have "format wars", the best idea is usually to have a converter program change to the easiest to work with format - unless this incurs a space explosion as per some image/video formats.

With CSV-like data, bulk conversion from quoted-escaped RFC4180 CSV to a simpler-to-parse format is the best plan for several reasons. First, it may "catch on", help Microsoft/R/whoever embrace the format and in doing so squash many bugs written by "data analyst/scientist coders". Second, in a shell "a|b" runs programs a & b in parallel on multi-core and allow things like csv2x|head -n10000|b or popen("csv2x foo.csv"). Third, bulk conversion to a random access file where literal delimiters cannot occur as non-delimiters allows trivial file segmentation to be nCores times faster (under often satisfied assumptions). There are some D tools for this bulk convert in https://github.com/eBay/tsv-utils and a much smaller stand-alone Nim tool https://github.com/c-blake/nio/blob/main/utils/c2tsv.nim . Optional quoting was always going to be a PITA due to its non-locality. What if there is no quote anywhere? Fourth, by using a program as the unit of modularity in this case, you make things programming language agnostic. Someone could go to town and write a pure SIMD/AVX512 converter in assembly even and solve the problem "once and for all" on a given CPU. The problem is actually just simple enough that this smells possible.

I am unaware of any "document" that "standardizes" this escaped/lossless TSV format. { Maybe call it "DSV" for delimiter separated values where "delimiters actually separate"? Ironically redundant. ;-) } Someone want to write an RFC or point to one? It can be just as "general/lossless" (see https://news.ycombinator.com/item?id=31352170).

Of course, if you are going to do a lot of data processing against some data, it is even better to parse all the way to down to binary so that you never have to parse again (well, unless you call CPUs loading registers "parsing") which is what database systems have been doing since the 1960s.

hedora3• 4 years ago

Someone linked a wikipedia format guide for TSV, but the world seems to have settled on using the escape codes \\, \t, \n with their obvious meanings, then allowing arbitrary binary.

That should be parallelism friendly, even with UTF-8, where an ascii tab or newline byte always mean tab and newline.

cb3217• 4 years ago

That someone was me. I don't think of "could change at any time Wikipedia" as "as authoritative" as the "document" should be. :-) { EDIT: and I very much agree it is friendlier in almost any thinkable way except maybe Excel might not support it. Or maybe it does? You do need to unescape binary fields at a "higher level" of usage, of course, when delimiting is no longer an issue. Also, merged my posts. }

A fast streaming converter into my suggested "DSV" can also be faster end-to-end. These kinds of things can vary a lot based upon how many columns rows have. I could not find "huge.csv". So, to be specific/possibly reproducible, using the 151492068 bytes of data from here:

    http://burntsushi.net/stuff/worldcitiespop.csv

put into /dev/shm and making a symlink to huge.csv and then using the csvbench.sh in the goawk distro, I got (best of 3 elapsed times):

    Python     2.88user 0.01system 0:02.91elapsed 99%CPU (9460maxresident)k
    Goawk      1.15user 0.07system 0:01.11elapsed 110%CPU (8460maxresident)k
    Go         0.86user 0.04system 0:00.84elapsed 107%CPU (7300maxresident)k

Go vs. Goawk time ratios were similar to Ben's article but inverted. Probably a number of columns effect. frawk failed to compile for me because my Rust was not new enough, according to the error messages. On the same data:

    c2tsv+gawk 0.22user 0.04system 0:00.57elapsed 46%CPU (2680maxresident)k
    (as *c2tsv<huge.csv|gawk -F'\t' '{nfs+=NF} END{print NR, nfs}'*)
    c2tsv+mawk 0.62user 0.09system 0:00.46elapsed 155%CPU (2780maxresident)k
    (as *c2tsv<huge.csv|mawk -F'\t' '{nfs+=NF} END{print NR, nfs}'*)
    c2tsv V2   0.19user 0.07system 0:00.23elapsed 115%CPU (2680maxresident)k
    (as *c2tsv<huge.csv|wc -l*, very similar to c2tsv<huge.csv>/dev/null)
    c2tsv V3   0.19user 0.00system 0:00.20elapsed 99%CPU (2632maxresident)k
    (as *c2tsv<huge.csv>/dev/null*)

A little 100 line Nim program combined with a standard utility seems to be about 4x faster (0.84/0.23) than Go results even though said program writes out all the data again. How can this be? Well, my pipe IO is usually around 4.4 GB/s (as assessed by a dd piped to a read-only sink) while 151e6/.2=only 755 MB/s. So it need only use ~17% of available pipe BW.

torginus• 4 years ago

I can't tell whether the UNIX people have lost their way, or just the demands of modern shell scripts cannot be met by typical shell philosophy - that is, piping together the output of small, orthogonal utilities.

The emergence and constantly increasing complexity of these small, bespoke DSLs like this or jq does not inspire confidence in me.

jjtheblunt• 4 years ago

you made me think a possible corollary (?) question would be if the json people don't perfectly overlap with the unix people.

anthk2• 4 years ago

A single tool to parse JSON and pipe it? That's Unix too.

tonyg• 4 years ago

demands of modern shell scripts cannot be met by typical shell philosophy

That. Pipes and unstructured binary data isn't compositional enough, making the divide between the kinds of things you can express in the language you use to write a stage in a pipeline and the kinds of things you can express by building a pipeline too large.

igtztorrero• 4 years ago

one step further towards GoUnix, Gonux or any name Gophers like!

anthk2• 4 years ago

Just use 9front. The plan9 C borrows a lot of philosophy from Golang (well, the reverse), and it's from the same creators.

demouser345• 4 years ago

no thank you. i will vehemently fight this hivemind

motohagiography• 4 years ago

Thank you! In non-technical environments, shell scripting with awk is a superpower, and it's almost always on csv data.

gpvos7• 4 years ago

During a recent HN discussion on pipes and text versus structured objects to transfer data between programs, I started wondering if CSV wouldn't be a nice middle ground.

carlmr3• 4 years ago

I still think objects are great, but PowerShell makes it so hard to deal with them.

I think F#-interactive (FSI) with its Hindley-Milner type-inference, would have been a much better base for a shell.

gpvos7• 4 years ago

I'm not familiar with F#, but I do hate CSV tools that try type inference on data; in my opinion the csvkit tools should have the -I option on by default.

majkinetor2• 4 years ago

I still think objects are great, but PowerShell makes it so hard to deal with them.

LOL, this is amazing...

jerf2• 4 years ago

JSON Lines would probably beat that out: https://jsonlines.org/

I phrase that carefully. "Better"? "Worse"? Very subjective. But in the current environment, "likely to beat out CSV"? Oh, most definitely yes.

A solid upside is a single encoding story for JSON. CSV is a mess and can't be un-messed now. Size bloat from endless repetition of the object keys is a significant disadvantage, though.

uhtred• 4 years ago

Why not use FPAT: https://www.gnu.org/software/gawk/manual/html_node/Splitting...

forgotpwd162• 4 years ago

From same documentation but the "more CSV" link:

In general, using FPAT to do your own CSV parsing is like having a bed with a blanket that’s not quite big enough. There’s always a corner that isn’t covered. We recommend, instead, that you use Manuel Collado’s CSVMODE library for gawk.

forgotpwd162• 4 years ago

A good and useful addition. There's a mention to CSVMODE, a gawk library. I wonder if it could be extended to support the functionality that goawk's `-i csv` has.

db65edfc7996• 4 years ago

I have grown fond of using miller[0] to handle command line data processing. Handles the standard tabular formats (csv, tsv, json) and has all of the standard data cleanup options. Works on streams so (most operations) are not limited by memory.

[0] https://github.com/johnkerl/miller

pstuart• 4 years ago

Just starred the repo -- thanks for the tip!

llimllib• 4 years ago

xsv does similar stuff for CSV, and very rapidly: https://github.com/BurntSushi/xsv

https://miller.readthedocs.io/en/latest/why/ has a nice section on "why miller":

First: there are tools like xsv which handles CSV marvelously and jq which handles JSON marvelously, and so on -- but I over the years of my career in the software industry I've found myself, and others, doing a lot of ad-hoc things which really were fundamentally the same except for format. So the number one thing about Miller is doing common things while supporting multiple formats: (a) ingest a list of records where a record is a list of key-value pairs (however represented in the input files); (b) transform that stream of records; (c) emit the transformed stream -- either in the same format as input, or in a different format.

cb3217• 4 years ago

Out of curiosity, I tried these two on that same data & computer as https://news.ycombinator.com/item?id=31356573 , mlr --c2t cat takes 2.96 seconds while xsv cat rows to /dev/null takes 0.434 seconds. So, 14.8X and 2.17X slower than that c2tsv Nim program to do exactly (and only) that conversion. But, yes, yes I am sure perf varies depending on quoting/escaping/column/etc. densities.

nsteel• 4 years ago

I was using xsv a lot at work (it is so much faster than csvkit) but I've recently jumped to qsv, a fork with more features.

https://github.com/jqnatividad/qsv

adamgordonbell• 4 years ago

It's somewhat a chore to use but gawkextlib has a CSV extension. so you can do this in gawk if the extension is loaded.

    @include "csv"
    BEGIN { CSVMODE = 1 }
    { print $2 }

https://earthly.dev/blog/awk-csv/#gawkextlib

rufugee• 4 years ago

What’s the best resource for learning modern awk these days? I’ve used it for decades, but only via memorized snippets…

chasil• 4 years ago

The full PDF of "The AWK Programming Language" has been online for some time.

Chapter 2 is the complete description of the language in 40 pages.

I think this link works...

https://ia803404.us.archive.org/0/items/pdfy-MgN0H1joIoDVoIC...

altairprime• 4 years ago

For non-awk tools, csvformat (from csvkit) will unquote and re-delimeter a CSV file (-D\034 -U -B) into something that UNIX pipes can handle (cut -d\034, etc). It’s worth setting up as an alias, and you can store \034 in $D or whatever for convenience.

adolph• 4 years ago

For anything down and dirty, what's wrong with -F'"'? For anything fancy there are plenty of things like the below.

eBay's TSV Utilities: Command line tools for large, tabular data files. Filtering, statistics, sampling, joins and more.

includes csv to tsv: https://github.com/eBay/tsv-utils

HT: https://simonwillison.net/

WFHRenaissance3• 4 years ago

Always had CSV support, it's called `awk --field-separator=","`.

__jem• 4 years ago

I see you like to live dangerously.

WFHRenaissance3• 4 years ago

AWK is my hammer and I will abuse it to every end

gshubert17• 4 years ago

I think the article already showed that with the example -F,

The problem is commas inside quoted strings, as in the example "Smith, Bob".

I believe the field-separator option on awk will break the quoted string at the interior comma.

asdff• 4 years ago

You could just replace those commas within the quote with some other string or remove them if you wanted with some awk or sed. Here is a somewhat relevant example from stackoverflow:

awk -F'"' -v OFS='' '{ for (i=2; i<=NF; i+=2) gsub(",", "", $i) } 1' infile

1. https://unix.stackexchange.com/questions/48672/remove-comma-...

WFHRenaissance3• 4 years ago

LOL - you guys actually read the articles?

tyingq• 4 years ago

Gnu awk also has a csv extension that comes with gawkextlib. I think it may even be installed on many Linux distros by default.

parasense• 4 years ago

I alway just use awk to process csv files.

    awk -F '^"|","|"$|,' '{print $2,$3}' whatever.csv

The above works perfectly well, it handles quoted fields, or even just unquoted fields.... This snippet is taken from a presentation I give on AWK and BASH scripting.

That's the thing about AWK, it's already does everything. No need to extended it much at all.

CRConrad5• 4 years ago

What about fields with "," in them?

w0de0• 4 years ago

-F ','

wef• 4 years ago

Here's another library I've been using for several years:

http://lorance.freeshell.org/csv/

Modernizing AWK, a 45-year old language, by adding CSV support

Related Stories