0

I have a large CSV file where columns are separated by commas in each case. However, about 5% of the time the first column contains a comma within itself and is denoted this way by the fact that these fields will have quotations around them if they have a comma inside them.

What would be a good way to read in this file such that we don't treat commas as separators whenever they're contained within quotations?

The best way I am thinking of would be to do something complex with a stack or something to see if we're currently within quotations or not but I think there must surely be an easier way. Note: There are never quotations within quotations or any other corner cases beyond this one.

*examples:

  1. Apple, 10, 12, ....
  2. Banana, 5, 10, ...
  3. "Banana, green", 3, 14, ... (Notice this line has a comma in it but it has quotations to indicate this)*

I would want to read in this last line into a variable as "Banana, green" rather than the default behavior which would read in banana and green as separate variables.

3
  • 3
    The bash shell is just not the right tool for this imho - use a proper CSV parser such as that provided by the Python-based csvkit, Perl's Text::CSV module, or Miller Jun 22, 2021 at 1:22
  • edit your question to include concise, testable sample input and expected output. If your CSV has a header line then make sure to include that.
    – Ed Morton
    Jun 22, 2021 at 2:21
  • Alongside csvkit and miller, there's GoCSV which has more options than csvkit and no syntax to learn like in miller.
    – Zach Young
    Jun 24, 2021 at 6:18

1 Answer 1

2

With GNU awk for FPAT:

$ cat tst.awk
BEGIN { FPAT="[^,]*|\"[^\"]*\"" }
{
    print
    for (i=1; i<=NF; i++) {
        print "\t" i, "<" $i ">"
    }
    print "---"
}

$ awk -f tst.awk file
Apple,10,12,...
        1 <Apple>
        2 <10>
        3 <12>
        4 <...>
---
Banana,5,10,...
        1 <Banana>
        2 <5>
        3 <10>
        4 <...>
---
"Banana, green",3,14,...
        1 <"Banana, green">
        2 <3>
        3 <14>
        4 <...>
---
2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.