I have csv files received on Linux, they come with their own different patterns, one example here:

$ head test.csv
CD Short-Term WAMP,2010-1-1,MA,MA,0.8763918845487475,201901,2019-01-31
CD Short-Term WAMP,2010-1-1,RI,RI,0.8576695707678873,201901,2019-01-31
CD Short-Term WAMP,2010-1-1,NH,NH,0.9038538021630779,201901,2019-01-31
CD Short-Term WAMP,2010-1-1,CT,CT,0.9699202728104309,201901,2019-01-31
CD Short-Term WAMP,2010-1-1,VT,VT,1.0631714504202636,201901,2019-01-31
CD Short-Term WAMP,2010-1-1,PGH,PGH,0.9517353522520116,201901,2019-01-31
CD Short-Term WAMP,2010-1-1,COM,COM,0.7401903422784099,201901,2019-01-31
CD Short-Term WAMP,2010-1-1,DE,DE,0.8485585323154969,201901,2019-01-31
CD Short-Term WAMP,,2010-1-1,PHI|,PHI,,,,1.0009405151305597,201901,2019-01-31

As you might have noticed, all fields are in the pattern of xxxx,xxxx,xxxx,xxxx,xxxx,xxx,xxxx

However, there is a row (the last row in the example) that has mal-formed data: xxxx,,xxxx,xxx|,xxx,,,,xxx,xxxx

I wonder how can I write a command or a script to 1. define the regex of the pattern (ideally put it in a seperate file); 2. grep out the unmatched rows from the original data, in this case, the last row should be grepped out.

  • You could start with a simple pattern (require at least one character in each field) up to a more complex one (dates must be numeric and in a certain range, regions should only be so many characters or of this list, etc). – Jeff Schaller Feb 13 at 15:10
  • How strict do you want the patterns to be? – Jeff Schaller Feb 13 at 15:11
  • -v display all unmatched lines (invert match) – Giacomo Catenazzi Feb 13 at 15:11
  • In the example shown, if two or more consecutive commas occur, then grep -v ',,' file would return the unmatched rows. – JRFerguson Feb 13 at 15:15
  • The grepped out rows must be very strictly NOT matching the pattern, as this script is intending to do some data quality check before it can be ingested and ETL'ed later. Bad data needs to be spotted and returned to client for correction. Thanks – mdivk Feb 13 at 15:25

Assuming none of the fields actually contains a quote:

awk -F, 'NF == 7' file
awk -F, 'NF != 7' file   # show the "bad lines"

That prints any line with exactly 7 comma-separated fields.

If this is a proper CSV file (where quoted fields may contain the field separator), then you'll want a CSV parser. I like ruby for one-liners:

ruby -rcsv -pe 'next unless CSV.parse_line($_).length == 7' test.csv
ruby -rcsv -pe 'next if CSV.parse_line($_).length == 7' test.csv  # show the "bad"

Regexes can provide elegant solutions, but not here IMO

grep -E '^([^,]+,){6}[^,]+$' test.csv
grep -vE '^([^,]+,){6}[^,]+$' test.csv   # show the "bad" lines
  • Thank you very much Glenn, adding -v to your command is the solution for me. would be better if you can explain the regex for later readers. – mdivk Feb 13 at 15:34
  • When you say "grep out", do you mean show or exclude the "bad" lines? – glenn jackman Feb 13 at 18:13
  • to show the "bad" lines – mdivk Feb 13 at 19:15

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.