csvquote

Forgive me for promoting this that I wrote: csvquote: https://github.com/dbro/csvquote Especially for use with existing shell text processing tools, eg. cut, sort, wc, etc.

Yes, this is what csvquote does. It does nothing else, just this so that programs like awk, sed, cut, etc. can work properly. https://github.com/dbro/csvquote

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.

Same. You can go a long way with cut, sort, etc. and also awk with its pattern matching. But if you're handy with SQL, that can often feel more natural and certainly things like joins among separate CSV files, as well as sums and other aggregates, are easier. If you have "unclean" CSV data, e.g. where the data contains delimiters and/or newlines in quoted fields, you might want to pipe it through csvquote. https://github.com/dbro/csvquote

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

There is a small program I wrote called csvquote[1] that can be used to sanitize input to awk so it can rely on delimiter characters (commas) to always mean delimiters. The results from awk then get piped through the same program at the end to restore the commas inside the field values. In principle: cat textfile.csv | csvquote | awk -f myprogram.awk | csvquote -u > output.csv Also works for other text processing tools like cut, sed, sort, etc. [1] https://github.com/dbro/csvquote

CSVs with quoted fields and imbedded newlines can be troublesome in awk. Years ago I had found a script that worked for me, I'm not sure but I think it was this: http://lorance.freeshell.org/csv/ There's also https://github.com/dbro/csvquote which is more unix-like in philosophy: it sits in a pipeline, and only handles transforming the CVS data into something that awk (or other utilities) can more easily deal with. I haven't used it but will probably try it next time I need something like that.

Good idea! Looks similar to something I wrote called csvquote https://github.com/dbro/csvquote , which enables awk and other command line text tools to work with CSV data that contains embedded commas and newlines.

I work in data processing and I use awk occasionally to work with csv files often gigabytes in size. I join csv files that each have a header with awk '(NR == 1) || (FNR > 1)' *.csv > joined.csv Note this only works if your csv files don't contain new lines. However if they do, I recommend using https://github.com/dbro/csvquote to circumvent the issue. Yesterday I used awk as a QA tool. I had to subtract a sum of values in the last column of one csv file from another, and I produced a expr $(tail -n+2 file1.csv | awk -F, '{s+=$(NF)} END {print s}') - $(tail -n+2 file2.csv | awk -F, '{s+=$(NF)} END {print s}') beauty. This allowed me to quickly check whether my computation was correct. Doing same in pandas would require loading both files into RAM and writing more code. However I avoid writing awk programs that are longer than a few

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

While not exactly what you asked for, I wrote something similar called csvquote ( https://github.com/dbro/csvquote ) which transforms "typical" CSV or TSV data to use the ASCII characters for field separators and record separators, and also allows for a reverse transform back to regular CSV or TSV files. It is handy for pipelining UNIX commands so that they can handle data that includes commas and newlines inside fields. In this example, csvquote is used twice in the pipeline, first at the beginning to make the transformation to ASCII separators and then at the end to undo the transformation so that the separators are human-readable. > csvquote foobar.csv | cut -d ',' -f 5 | sort | uniq -c | csvquote -u It doesn't yet have any built-in awareness of UTF or multi-byte characters, but I'd be happy to receive a pull request if it's something

You might want to check out https://github.com/dbro/csvquote which helps awl and other text tools handle csv files which have quoted strings as values.

Regarding quoting support, check out https://github.com/dbro/csvquote