3 ms·

Last week I threw out AWK and replaced it with Ruby (Could've been Python, Perl or PHP even). Because AWK is not suited for CSV. Please prove me wrong! I had

by berkes 7y ago

Last week I threw out AWK and replaced it with Ruby (Could've been Python, Perl or PHP even). Because AWK is not suited for CSV. Please prove me wrong! I had to parse 9million lines. Some of which contain "quoted records", others, same column, are unquoted. Some contain comma's, in the fields, most don't. CSV is like that: more like a guideline than actual sense. Two hours of googling and hacking later, I gave up and rewrote the importer in Ruby, in under 5 minutes. Lesson learned: I'll stay clear of AWK, when I know a oneliner of Ruby (or Python) can solve it just as well. Because I know for certain the latter can deal with all the edgecases that will certainly pop up.

ende 7y ago
If you’re doing a lot of csv ops on the cli, you might like https://csvkit.readthedocs.io/en/latest/ https://csvkit.readthedocs.io/en/latest/ (It’s Python, and you can use it as a library as well)
mandelbrotwurst 7y ago
I'm just starting to learn awk, would you mind sharing some of the issues / edge cases that you ran into that you found it didn't handle well? Was it maybe just that you found it tricky to write the regular expressions?
F-0X 7y ago
> I had to parse 9million lines. Awk would chew through that no problem. > Some of which contain "quoted records", others, same column, are unquoted. In which case, there is the FPAT variable which can be used to define what a field is. FPAT="\"[^\"]\"|[^,]", which means "stuff between quotes, or things that are not commas", would probably have worked for you. (EDIT: Looks like formatting has gotten hold of my FPAT and I don't know how to stop it... hopefully it is still clear where asterisks should be) > Some contain comma's, in the fields, most don't. CSV is like that: more like a guideline than actual sense. Well, I would say that's absolutely false. You can't just put the delimiter wherever you fancy and call it a well-formed file. Quoting exists for the unfortunate cases your data includes the delimiting character (ideally the author would have the sense to use a more suitable character, like a tab). This is just a retort to prevent your post from dissuading readers from awk, which is a fantastic tool. If you actually sit for half and hour and learn it rather than google to cobble together code that works, it is wonderful. I also don't think it is valid to base your judgement of a tool on what was apparently garbage data.
- ajanuary 7y ago
  Garbage and poorly specified csv files are a fact of life and people have to deal with them all the time. But if you want to be in a world where people only deal with well specified files like RFC 4180 (for some definition of well specified), your quick field pattern doesn’t conform. It doesn’t handle escaped double quotes or quoted line breaks. If you’re using your quick awk command to transform an RFC 4180 file into another RFC 4180 file you’ve just puked out the sort of garbage you were railing against. While awk is a great tool if you’re dealing with a csv format with a predictable specification, and probably could be made to bend to the GP will with a little more knowledge, it gets trickier if you’re dealing with handling some of the garbage that comes up in the real world. What’s worse is the programming model leads you down the path of never validating your assumptions and silently failing. I love awk for interactive sessions when I can manually sanity check the output. But if I’m writing something mildly complex that has to work in a batch on input I’ve never seen, I too would reach for ruby.
billjings 7y ago
I had the exact same experience, but sub python for Ruby. The lesson I took wasn't that awk sucks, though. The lesson was that CSV is not trivial, and should not be parsed with regex or string matching. It's a standard with variants, and rolling in a library will pay dividends, especially if you're parsing a wide variety of different dialects of CSV. A related lesson I took is that once your awk script grows beyond a certain level, graduate it up to a real language. I love awk, but it excels at small scale text munging. It's not suited to anything more involved than that. If translating an awk program is a major task, then the program was already too big to begin with.
tgv 7y ago
Sure, for some things awk is a good fit, for others it isn't. But if your file is correct csv, and you use gawk, this does the trick: https://www.gnu.org/software/gawk/manual/html_node/Splitting-By-Content.html https://www.gnu.org/software/gawk/manual/html_node/Splitting...
dbro 7y ago
To simplify working with CSV data using command line tools, I wrote csvquote ( https://github.com/dbro/csvquote https://github.com/dbro/csvquote ). There are some examples on that page that show how it works with awk, cut, sed, etc.
- jimbokun 7y ago
  That's very clever.
throwseayeswe 7y ago
The author of ripgrep (Andrew burntsushi Gallant) has 2 tools for working with CSV data - 'xsv' (a command line swiss army knife for working with CSV data) and 'CSV parser'. Check those out: https://blog.burntsushi.net/projects/ https://blog.burntsushi.net/projects/
- defined 7y ago
  xsv is awesome!
tsuyoshi 7y ago
I wrote this for parsing CSV in Awk: http://yumegakanau.org/code/awk-csv/ http://yumegakanau.org/code/awk-csv/ It maybe doesn't handle all the CSV out there, but the cases you mention (quoting and commas) it does handle. I usually load CSV data into PostgreSQL to do anything with it; mostly wrote this Awk library for fun. So I'm not going to argue that Awk is the best language for doing this kind of thing, but it is possible.
hnick 7y ago
CSV is not really a standard, various programs have their own interpretation (even MS Excel allows a header line before the data that contain specifications for the file, such as delimiter, which you probably won't account for in a home-grown solution). So use a dedicated tool or library or you will run into trouble one day. For one off jobs, I open it in Excel or similar and save as tab or pipe delimited text. This usually plays much more nicely with command line utilities, assuming it didn't mangle any numbers.

by berkes 7y ago

ende 7y ago

If you’re doing a lot of csv ops on the cli, you might like https://csvkit.readthedocs.io/en/latest/ https://csvkit.readthedocs.io/en/latest/ (It’s Python, and you can use it as a library as well)

mandelbrotwurst 7y ago

I'm just starting to learn awk, would you mind sharing some of the issues / edge cases that you ran into that you found it didn't handle well? Was it maybe just that you found it tricky to write the regular expressions?

F-0X 7y ago

> I had to parse 9million lines. Awk would chew through that no problem. > Some of which contain "quoted records", others, same column, are unquoted. In which case, there is the FPAT variable which can be used to define what a field is. FPAT="\"[^\"]\"|[^,]", which means "stuff between quotes, or things that are not commas", would probably have worked for you. (EDIT: Looks like formatting has gotten hold of my FPAT and I don't know how to stop it... hopefully it is still clear where asterisks should be) > Some contain comma's, in the fields, most don't. CSV is like that: more like a guideline than actual sense. Well, I would say that's absolutely false. You can't just put the delimiter wherever you fancy and call it a well-formed file. Quoting exists for the unfortunate cases your data includes the delimiting character (ideally the author would have the sense to use a more suitable character, like a tab). This is just a retort to prevent your post from dissuading readers from awk, which is a fantastic tool. If you actually sit for half and hour and learn it rather than google to cobble together code that works, it is wonderful. I also don't think it is valid to base your judgement of a tool on what was apparently garbage data.

ajanuary 7y ago
Garbage and poorly specified csv files are a fact of life and people have to deal with them all the time. But if you want to be in a world where people only deal with well specified files like RFC 4180 (for some definition of well specified), your quick field pattern doesn’t conform. It doesn’t handle escaped double quotes or quoted line breaks. If you’re using your quick awk command to transform an RFC 4180 file into another RFC 4180 file you’ve just puked out the sort of garbage you were railing against. While awk is a great tool if you’re dealing with a csv format with a predictable specification, and probably could be made to bend to the GP will with a little more knowledge, it gets trickier if you’re dealing with handling some of the garbage that comes up in the real world. What’s worse is the programming model leads you down the path of never validating your assumptions and silently failing. I love awk for interactive sessions when I can manually sanity check the output. But if I’m writing something mildly complex that has to work in a batch on input I’ve never seen, I too would reach for ruby.

billjings 7y ago

I had the exact same experience, but sub python for Ruby. The lesson I took wasn't that awk sucks, though. The lesson was that CSV is not trivial, and should not be parsed with regex or string matching. It's a standard with variants, and rolling in a library will pay dividends, especially if you're parsing a wide variety of different dialects of CSV. A related lesson I took is that once your awk script grows beyond a certain level, graduate it up to a real language. I love awk, but it excels at small scale text munging. It's not suited to anything more involved than that. If translating an awk program is a major task, then the program was already too big to begin with.

tgv 7y ago

Sure, for some things awk is a good fit, for others it isn't. But if your file is correct csv, and you use gawk, this does the trick: https://www.gnu.org/software/gawk/manual/html_node/Splitting-By-Content.html https://www.gnu.org/software/gawk/manual/html_node/Splitting...

dbro 7y ago

To simplify working with CSV data using command line tools, I wrote csvquote ( https://github.com/dbro/csvquote https://github.com/dbro/csvquote ). There are some examples on that page that show how it works with awk, cut, sed, etc.

jimbokun 7y ago
That's very clever.

throwseayeswe 7y ago

The author of ripgrep (Andrew burntsushi Gallant) has 2 tools for working with CSV data - 'xsv' (a command line swiss army knife for working with CSV data) and 'CSV parser'. Check those out: https://blog.burntsushi.net/projects/ https://blog.burntsushi.net/projects/

defined 7y ago
xsv is awesome!

tsuyoshi 7y ago

I wrote this for parsing CSV in Awk: http://yumegakanau.org/code/awk-csv/ http://yumegakanau.org/code/awk-csv/ It maybe doesn't handle all the CSV out there, but the cases you mention (quoting and commas) it does handle. I usually load CSV data into PostgreSQL to do anything with it; mostly wrote this Awk library for fun. So I'm not going to argue that Awk is the best language for doing this kind of thing, but it is possible.

hnick 7y ago

CSV is not really a standard, various programs have their own interpretation (even MS Excel allows a header line before the data that contain specifications for the file, such as delimiter, which you probably won't account for in a home-grown solution). So use a dedicated tool or library or you will run into trouble one day. For one off jobs, I open it in Excel or similar and save as tab or pipe delimited text. This usually plays much more nicely with command line utilities, assuming it didn't mangle any numbers.