News for hackers. Stuff that matters.
Re:Working with CSV files on shell/terminal by benhoyt on Wednesday June 28, 2023 @02:56AM (#36503236)
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!

Re:Working with CSV files on shell/terminal by midasuni on Wednesday June 28, 2023 @03:56AM (#36503623)
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.

Re:Working with CSV files on shell/terminal by benhoyt on Wednesday June 28, 2023 @04:31AM (#36503834)
> 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 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.

Re:Working with CSV files on shell/terminal by midasuni on Friday June 30, 2023 @04:04AM (#36531903)
Deal with ip addresses and numbers, maybe host names, and csvs are perfect to process
Re:Working with CSV files on shell/terminal by meepmorp on Wednesday June 28, 2023 @04:11AM (#36503724)
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.
Re:Working with CSV files on shell/terminal by zokier on Wednesday June 28, 2023 @05:23AM (#36504183)
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.

Re:Working with CSV files on shell/terminal by hfkwer on Wednesday June 28, 2023 @05:34AM (#36504251)
> 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.

Re:Working with CSV files on shell/terminal by keybored on Wednesday June 28, 2023 @06:18AM (#36504521)
> 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.

Re:Working with CSV files on shell/terminal by dbro on Wednesday June 28, 2023 @02:11PM (#36510329)
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.
Re:Working with CSV files on shell/terminal by globular-toast on Wednesday June 28, 2023 @06:06AM (#36504436)
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?
Re:Working with CSV files on shell/terminal by benhoyt on Wednesday June 28, 2023 @07:14PM (#36513994)
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
Re:Working with CSV files on shell/terminal by ufo on Wednesday June 28, 2023 @07:33AM (#36504981)
A second edition is coming?? Awesome!
Re:Working with CSV files on shell/terminal by antegamisou on Wednesday June 28, 2023 @10:04AM (#36506658)
FPAT declaration is a workaround for ignoring commas

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

Re:Working with CSV files on shell/terminal by chrishill89 on Wednesday June 28, 2023 @05:04PM (#36512589)
That deals with commas but (since AWK is line-oriented) I doubt that deals with multi-line fields?
Re:Working with CSV files on shell/terminal by jjice on Wednesday June 28, 2023 @10:17AM (#36506845)
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!
Re:Working with CSV files on shell/terminal by chasil on Wednesday June 28, 2023 @12:52PM (#36509149)
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

Re:Working with CSV files on shell/terminal by tinus_hn on Wednesday June 28, 2023 @11:07AM (#36507550)
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.