Matching pattern with a comma embedded in csv column

Question

I have a daily script that needs to extract an error log and remove users from the master csv file, before uploading into the database. I'm able to use awk to extract the first column with great results. However, the error I get back from my 3rd party application contains a comma in the error column. This is preventing an exact match and causing issues.

This is a sample of the error file I get back

"USER_ID","FIRSTNAME","LASTNAME","ERROR"
"CA781558","Dani","Roper","parent is inactive, cannot update record"
"BT055163","Alexis","Richardo","parent is inactive, cannot update record"
"LN764767","Peter","Rajosz","no parent record, update denied"
"SG839717","Jerry","Alindos","parent is inactive, cannot update record"

I need to match exactly for "parent is inactive, cannot update record", to use this to update the parent record, so it can be updated. Likewise, I need to match the "no parent record" so I can add a parent for this record and process. In reality, I have a slew of similar error messages that require different actions. Matching on the exact string, with the comma, is critical.

The expected output is:

"USER_ID"
"CA781558"
"BT055163"
"SG839717"

Use a language with a proper CSV parser.

chepner
– chepner

2016-04-26 17:31:31 +00:00
Commented Apr 26, 2016 at 17:31 — chepner
– chepner, Commented Apr 26, 2016 at 17:31

anubhava · Accepted Answer · 2016-04-26 17:43:19Z

1

Using awk you can do this:

s='parent is inactive, cannot update record'
awk -v s="\"$s\"" -F, 'NR==1 || $0 ~ s{print $1}' file

"USER_ID"
"CA781558"
"BT055163"
"SG839717"

answered Apr 26, 2016 at 17:43

anubhava

790k67 gold badges603 silver badges671 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vrata Blazek · Accepted Answer · 2016-04-26 21:15:14Z

1

Simply use this (awk is overkill for this purpose):

cat infile.txt | grep 'parent is inactive, cannot update record' | cut -d ',' -f1

answered Apr 26, 2016 at 21:15

Vrata Blazek

4643 silver badges12 bronze badges

Comments

jaypal singh · Accepted Answer · 2016-04-26 17:58:20Z

0

I would go with a proper csv parser. Following is an example that uses core module Text::ParseWords so you don't need to download it from CPAN.

perl -MText::ParseWords -lne '
    @line = parse_line(",", 1, $_);
    print $line[0] if $.==1;
    print $line[0] if $line[3] =~ /parent is inactive, cannot update record/;
' file
"USER_ID"
"CA781558"
"BT055163"
"SG839717"

answered Apr 26, 2016 at 17:58

jaypal singh

77.6k24 gold badges108 silver badges147 bronze badges

Collectives™ on Stack Overflow

Matching pattern with a comma embedded in csv column

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related