0

I have a CSV file (CSV UTF-8 (Comma delimited) (.csv)) with 5 columns: customer data, customer first name, customer email, cancel reason, cancel reason comments

How would I filter the rows that have "Wanted item later, choose not to get it later" in the cancel reason column and then read it into a new file called input1.csv?

Below is what I tried, but input1.csv came out blank:

awk -F , '$4 == "Wanted item later, choose not to get it later" { print }' input.csv > input1.csv

Here is a sample input from input.csv file:

cancellation date,customer first name,customer email,cancel reason,cancel reason comments
1/22/17,aaaa,xxxxx@xxxxx.com,"Wanted item later, choose not to get it later", test
  • awk knows nothing about quoted fields - once you set its field separator to , it will treat every comma as a separator, and hence consider your string to be two fields. You will need to use one of the more CSV-aware solutions proposed in the answers to your earlier question – steeldriver Jan 30 '17 at 16:18
1

In line with my answer to your earlier question, using csvkit, and assuming the CSV is properly quoted and uses commas as delimiters:

$ csvgrep -c "cancel reason" \
    -m "Wanted item later, choose not to get it later" input.csv >input1.csv

or

$ csvgrep -c 4 -m "Wanted item later, choose not to get it later" input.csv >input1.csv
  • When I run your first command this is error I get: usage: csvgrep [-h] [-d DELIMITER] [-t] [-q QUOTECHAR] [-u {0,1,2,3}] [-b] [-p ESCAPECHAR] [-z FIELD_SIZE_LIMIT] [-e ENCODING] [-S] [-H] [-v] [-l] [--zero] [-n] [-c COLUMNS] [-m PATTERN] [-r REGEX] [-f MATCHFILE] [-i] [FILE] csvgrep: error: unrecognized arguments: Wanted item later, choose not to get it later unsubscribe1_22.csv – eulerprime Jan 30 '17 at 17:15
  • When I run your second command and type cat input1.csv this is what I get: cancellation date,customer first name,customer email,cancel reason,cancel reason comments – eulerprime Jan 30 '17 at 17:16
  • @CharlesMarshall You may have to update your installation of cvskit. I'm using version 1.0.1. However, all the argument used by the commands that I suggested are listed as valid by the error message. – Kusalananda Jan 30 '17 at 17:25
  • I made sure the latest version csvkit was installed and when I ran the above two commands, I received the same exact output as before. @Kusalananda – eulerprime Jan 30 '17 at 17:34
  • @CharlesMarshall Did you forget the -m or did you not quote the string after it? – Kusalananda Jan 30 '17 at 17:37
0

It looks like you are expecting to have 5 fields. This will identify lines with more fields:

awk -F, 'NF > 5' input > output

This does not specifically look for a comma in the fourth field.

0
grep -E ',"[^"]*,[^"]*"|"[^"]*,[^"]*",' input.csv > input1.csv

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.