4 ms·

caveat: delimiter-based commands are not quote-aware. For example, this is a CSV line with two fields: foo,"bar,baz" However, the tools will treat it as

by sheetjs 12y ago

caveat: delimiter-based commands are not quote-aware. For example, this is a CSV line with two fields: foo,"bar,baz" However, the tools will treat it as 3 columns: $ echo 'foo,"bar,baz"' | awk -F, '{print NF}' 3

napD 12y ago
Is there any workaround?
- danielweber 12y ago
  Practically all Unix tools consider the comma-separated-but-you-can-use-quotes-to-override CSV file to be an abomination. [1] You have to have a crazy regexp to get around it. [1] Maybe someday they won't.
- sheetjs 12y ago
  I primarily deal with Excel (xls) files nowadays. I wrote a command line tool to extract data: https://www.npmjs.org/package/j https://www.npmjs.org/package/j In my current workflow, I generate JSON from the excel files and use the really awesome JQ command (http://stedolan.github.io/jq/ http://stedolan.github.io/jq/) to process
- hnha 12y ago
  use csvkit or something similar.
- RBerenguel 12y ago
  csvfix is probably the best tool to deal with it. Csvfix, awk, sed are probably my "first line of data-attack". After that usually I can get to analysing, plotting or whatever I need to do.
- jingo 12y ago
  Yes. Use lex/flex. You can write one-off (or reuseable) filters in minutes. lex/flex should be in every UNIX distribution that has a C compiler, but maybe that's changing.
- mbreese 12y ago
  Don't use CSV files... If I'm working with a datafile where I expect the delimiter to be in one of the fields, there is something wrong. This is one reason why I always work with tab delimited files. Having an actual tab character isn't very common in free-text fields, at least in the data that I work with. Commas on the other hand, are quite common. Why one would select a field separator that was common in your data is beyond me (I know it's historical). Your data files might be different, in which case, maybe you should select a different field separator. Otherwise, no, there is no work around. If you have to quote fields, then you can't use the normal unix command line tools that tokenize fields.
- dbro 12y ago
  yes! https://github.com/dbro/csvquote https://github.com/dbro/csvquote csvquote allows UNIX tools to work properly with quoted fields that contain delimiters inside the data. It is a simple translation tool that temporarily replaces the special characters occurring inside quotes with harmless non-printing characters. You do it as a first step in the pipeline, then do the regular operations using UNIX tools, and the last step of of the pipeline restores those troublesome characters back inside the data fields.

by sheetjs 12y ago

napD 12y ago

Is there any workaround?

danielweber 12y ago
Practically all Unix tools consider the comma-separated-but-you-can-use-quotes-to-override CSV file to be an abomination. [1] You have to have a crazy regexp to get around it. [1] Maybe someday they won't.
sheetjs 12y ago
I primarily deal with Excel (xls) files nowadays. I wrote a command line tool to extract data: https://www.npmjs.org/package/j https://www.npmjs.org/package/j In my current workflow, I generate JSON from the excel files and use the really awesome JQ command (http://stedolan.github.io/jq/ http://stedolan.github.io/jq/) to process
hnha 12y ago
use csvkit or something similar.
RBerenguel 12y ago
csvfix is probably the best tool to deal with it. Csvfix, awk, sed are probably my "first line of data-attack". After that usually I can get to analysing, plotting or whatever I need to do.
jingo 12y ago
Yes. Use lex/flex. You can write one-off (or reuseable) filters in minutes. lex/flex should be in every UNIX distribution that has a C compiler, but maybe that's changing.
mbreese 12y ago
Don't use CSV files... If I'm working with a datafile where I expect the delimiter to be in one of the fields, there is something wrong. This is one reason why I always work with tab delimited files. Having an actual tab character isn't very common in free-text fields, at least in the data that I work with. Commas on the other hand, are quite common. Why one would select a field separator that was common in your data is beyond me (I know it's historical). Your data files might be different, in which case, maybe you should select a different field separator. Otherwise, no, there is no work around. If you have to quote fields, then you can't use the normal unix command line tools that tokenize fields.
dbro 12y ago
yes! https://github.com/dbro/csvquote https://github.com/dbro/csvquote csvquote allows UNIX tools to work properly with quoted fields that contain delimiters inside the data. It is a simple translation tool that temporarily replaces the special characters occurring inside quotes with harmless non-printing characters. You do it as a first step in the pipeline, then do the regular operations using UNIX tools, and the last step of of the pipeline restores those troublesome characters back inside the data fields.