1

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,[email protected],"Wanted item later, choose not to get it later", test
1
  • 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 Commented Jan 30, 2017 at 16:18

3 Answers 3

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
6
  • 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
    Commented Jan 30, 2017 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
    Commented Jan 30, 2017 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
    Commented Jan 30, 2017 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
    Commented Jan 30, 2017 at 17:34
  • @CharlesMarshall Did you forget the -m or did you not quote the string after it?
    – Kusalananda
    Commented Jan 30, 2017 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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .