Awk: `Begin { ` Part 1

The best description of dates and CSV files I've heard by far. Bravo.

"Csv files are a little bit like like dates: superficially simple, with lots of corner cases. Largely for the same reason: lack of standardization."

CSV standards are whatever the application implemented. We'd pull a "CSV" out of one system, then have to massage it before uploading to system A and then make other changes so it would be recognized by system B.

I'm torn. On one hand, it's easy. It lets people work with the data, albeit using error-prone tools like NotePad++ and Excel.

On the other hand, it's just flat out lazy to use.

Tangent, but another interesting problem with date parsing besides lack of standardization is that it's ridiculously context-sensitive.

For example, if I schedule an event for Oct 1-Jan 1 right now, the most likely (least surprising) parse would be Oct 1, 2021 through January 1, 2022. Which is both surprising because parsing Oct 1 depends on the current date, and because the parsing of Jan 1 depends on the parse of Oct 1, and because both of these depend on the fact that you're scheduling (in all likelihood) a future event, rather than describing a past event. So whatever date parser you use (no matter how good it is at handling different notations) will return garbage if it doesn't take all these into account.

Yeah, here's what a robust solution for parsing csv with awk looks like: https://stackoverflow.com/questions/45420535/whats-the-most-...

There is https://tools.ietf.org/html/rfc4180

Most CSV files do not follow this standard of course. But you could normalize all CSV files to RFC4180 (or any other consistent format) as the first step of your processing pipeline.

I would love to have a command-line tool that reads CSV and has a ton of features to cover different quirks and errors, which can output cleaner formats that I can pipe into other command-line tools.

csvkit [0] might be that tool; I discovered it after my last painful encounter with CSV files and haven't used it in anger yet. Among other things, it translates CSV to JSON, so you can compose it with jq.

[0] https://csvkit.readthedocs.io/en/latest/index.html

Segue to something that I've been fighting with for years: Do you have a suggestion for working with CSVs in the CLI?

Something that will show a table, with a single pixel border between cells, that allows searching for a value, copying a cell?

I have used awk a lot on csv files. I basically agree with your comments, for me the use case is preliminary exploration, not really much more. There is also the issue of one liners not really ending themselves to good coding practice generally, but it sure is great to dive into a file quickly.

What I came here to say is that when I have a csv file I'm looking at with awk, thos first thing I do is a `wc -l`, then get NF (number of fields) for the first row of the csv (here e.g. 80), followed by `awk -F, 'NF==80'` | wc -l`. If the numbers don't match, then I know it's not parsing properly.

The most common issue of course is commas in quotes strings. I have a small script that removes these, so I can still use awk easily. Anything more complex like newlines in quotes strings and maybe it's time to question if awk is still worth it.

CSV to TSV via robust program, use AWK, and convert back if needed. Then you only have to remember the \\t\n\r escapes, not the whole quoting shenanigans.

Everything you just described seems like reasons to not use CSV files in general, rather than not use AWK on CSV.

I parse a lot of CSV files and few are well-formed.

People are careless when writing CSV files because they think it is simple: just put a comma between columns, right?

    $ ruby -rcsv -ne 'puts $_.parse_csv[1]' education.csv
    Enrolment in primary, secondary and tertiary education levels
    Total, all countries or areas

AWK is a nice little language. Perl is all of it and shell, sed, grep. I am not proficient in Perl but I know some Ruby

    $ ruby -rEnglish -ne 'puts $_ if $NR <= 5' education.csv 
    $ ruby -rEnglish -aF, -ne 'puts $F[1] if $NR <= 5' education.csv 
    $ ruby -rEnglish -ne 'END { puts $NR }' education.csv
    $ ruby -rEnglish -ne 'puts $_ if $NR % 500 == 0' education.csv

Module English provides AWK names

    $ perl -MEnglish -ne 'print if $NR < 5' education.csv

https://ruby-doc.org/stdlib-2.3.0/libdoc/English/rdoc/Englis...

It depends what you are doing. in my experience when you write a script to do something with a CSV it's been created by a specific piece of software and you only feed that csv to the script. I doubt anyone is writing anything generic in ask for parsing a multitude of different csvs

q is a great tool for CSV manipulation http://harelba.github.io/q/ . Awk is not

What format of data file is best for working with Awk? I assume some kind of tab delimited file? With a reliable ingestion layer you can convert all your files CSV-ish files to something ergonomic for Awk scripting.

JSON data files seem to be a similar deal. Sometimes they are actually properly formed JSON arrays and sometimes they are individual line-delimited objects.

Data files are a mess, is there a command line tool that is well suited to taking in many inconsistent formats and outputting something ergonomic?

> Csv is not standardized and the quoting rules are weird (and not standardized)

There is almost always some normalisation ("scrubbing") needed to prep a CSV. But CSV is viable and awk can rip through massive amounts of data. It is a brilliant and powerful tool.

> It's small enough to fit in your brain, unlike Perl

Perl is also brilliant and powerful. Setting aside the bigotry of people who dislike sigils and using braces for scope, many people who fail to learn Perl well have not tried to use Perl-OOP objects as primitives. Once you do this, Perl's versatility and speed are hard to beat.

This seems very interesting.

Making this reproducable and usable for multiple people has always been an annoyance in semi-ad-hoc data collection.

+1 for Miller. It's a really slick tool for exploring large csv datasets. I've typically used it to do some prototyping and exploration of 200-500GB csv datasets before doing more hefty work in Java+PigLatin (our use-case is more long-term than just a single run for analysis, so that's re reason for moving out of just Miller). It's great to get a feel for the data and run some initial analysis before diving into the larger, more cumbersome system and tooling.

> I've been using awk for past 2-3 years or so and I wrote a book on GNU awk one-liners

not to criticise your work, but this does not sit well with me.

GNU Awk has lots of extensions and is a relatively big language, but I definitely think you can learn most of the original Awk in two weeks - and you can certainly learn enough to be useful in two hours. It's been a decade now, but my recollection is that when I spent a day or two idly reading the Awk book, and was able to immediately employ it in simple shell pipelines after reading not that many pages. I think Awk is useful once you've learned just a bit about it, and I don't think learning a lot about Awk is very useful - there are languages that are better suited for complex tasks.

This HN post inspired me to pick up and work through "The AWK Programming Language", so I have been doing that for the past two days. I'm currently at the beginning of chapter 5 (of 8).

That quote is definitely ridiculous. He may have been referring to just chapter 2, in which the "whole language" is outlined. But later chapters also take time, there even is an exercise about writing an assembler in AWK as well as implementing multiple algorithms.

Some stuff was already somewhat familiar to me from studying/practicing C earlier this year.

Overall a 10/10 book so far, but takes time and patience for sure!

You can also trivially come across data designed for consumption with AWK. Whenever I write a script or utility to dump some data for later analysis, I take into consideration if I can shape the output in a way that will make it easy to work with in AWK.

Because I won't know ahead of time what all the interesting values or summaries will be, but if I know I can get it through AWK it will be cheap to extract or compute those values and summaries later, once I know what they are.

The biggest problem with awk is that unless you use it regularly, you'll easily forget how to use it. So it's important to keep a running cheatsheet like you did. Nothing more frustrating than forgetting the awk command and searching for it in your history only to find it aged out.

It's one of my favorite technical books, with many examples of the application of the language, including the design of small languages and experimentation with algorithms.

It is freely available at archive.org: https://archive.org/details/pdfy-MgN0H1joIoDVoIC7

There's also the POSIX standard which is quite readable: https://pubs.opengroup.org/onlinepubs/9699919799/utilities/a...

By the way, "Learning Perl" is also a great introductory book to a language.

Happy to see this mentions https://github.com/onetrueawk/awk

The reason to write one is to record what you learn, to further embed it into your brain. Why post it? Whoever posted it found it interesting.

I had not seen the others so this is an intro to awk for me. But in general if something has made it to the front page of HN it was interesting enough for enough people to put it there.

mawk also has a nice and short manual. since GNU decided to make its money selling books, their manuals are usually huge and that's by design.

I learned awk before Perl and it helped a lot. I still use awk (and sed) regularly but for more complex tasks involving regexes and such Perl is my favourite.

Awk is a full blown programming language though. comparable to python but allowing for much more compact code. Though I completely agree on avoiding shell. the only good shell I've seen is rc. but the relying on external utilities is a double edged sword. forking is too costly over time.