0

I have a CSV file with 4 columns: Date, User, Email and Comment

How would I filter the rows that have too much, later in the Comment 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 == "too much, later" { print }' input.csv > input1.csv

Here is the sample input:

Date,User,Email,Comment
11/22/16,aaaa,xxxxx@xxxxx.com,too much,later 
  • 1
    can you provide the sample file contents.. you should redirect the output using greater than symbol ( > input1.csv ) – Kamaraj Jan 30 '17 at 8:18
  • Welcome to Unix Stackexchange! You can take the tour first and the learn How to Ask a good question. That makes it easier for us to help you. – andcoz Jan 30 '17 at 9:03
  • Data User Email Comment 1/22/17 0:00 aaaa xxxxxx@xxxxx.com too much, later 1/22/17 0:00 aaaa xxxxxx@xxxxx.com It's too expensive 1/22/17 0:00 aaaa xxxxxx@xxxxx.com Other Reason 1/22/17 0:00 aaaa xxxxxx@xxxxx.com It's too expensive 1/22/17 0:00 aaaa xxxxxx@xxxxx.com Other Reason 1/22/17 0:00 aaaa xxxxxx@xxxxx.com I don't like – eulerprime Jan 30 '17 at 9:29
  • @CharlesMarshall edit your question and add the example input to it. Please format it according to site format. I.e. Inserting 4 spaces to the start of each line. – andcoz Jan 30 '17 at 9:51
  • 1
    Exporting inte CSV from a spreadsheet program usually adds the necessary quotes around the data that needs it. The fourth column is not quoted, and the header line contains spaces in the names of each header. It is not properly formatted CSV. – Kusalananda Jan 30 '17 at 10:16
2

It can be done with awk, but it's awkward. :) It's much better to do it with a real CSV parser, such as csvkit:

csvgrep -c Comment -m 'too much, later' file.csv
  • This is what I got when I ran that command: 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: input.csv – eulerprime Jan 30 '17 at 9:01
  • Works fine here with csvgrep 1.0.2. shrug – Satō Katsura Jan 30 '17 at 9:50
  • Do you have an email that I can send the file to? You could try running it on your end and see if it works @Satu Katsura – eulerprime Jan 30 '17 at 10:16
  • Right: it doesn't work with the sample input you have now shown, because the last field in your sample is not properly quoted. It works with proper CSV files though. shrug – Satō Katsura Jan 30 '17 at 11:53
2

Assuming properly formatted CSV:

Date,User,Email,Comment
11/22/16,aaaa,xxxxx@xxxxx.com,"too much, later"

This works, using csvkit:

$ csvgrep -c Comment -m 'too much, later' data.csv
Date,User,Email,Comment
11/22/16,aaaa,xxxxx@xxxxx.com,"too much, later"

$ csvgrep -c 4 -m 'too much, later' data.csv
Date,User,Email,Comment
11/22/16,aaaa,xxxxx@xxxxx.com,"too much, later"

Alternatively, using sed (since we know it's in the last column):

$ sed -n -e '1p' -e '/too much, later"$/p' data.csv
Date,User,Email,Comment
11/22/16,aaaa,xxxxx@xxxxx.com,"too much, later"

With the data as shown in the question:

$ sed -n -e '1p' -e '/too much,later$/p' data.csv
Date,User,Email,Comment
11/22/16,aaaa,xxxxx@xxxxx.com,too much,later 
  • I appreciate the help @Kusalananda, but now it is just returning the headers in the new file. Can I send it to your email and you can run on from your command line? – eulerprime Jan 30 '17 at 10:34
  • @CharlesMarshall I've update the answer. Your CSV is severely broken (at least the way it's shown in the question). I will not give out my email on this site. – Kusalananda Jan 30 '17 at 10:38
1

I see a simple problem: the string too much, later contains a , (comma) and your field separator is also ,, so the $4 parameter will be too much and $5 will be later.

You could change you code to:

awk -F , '$4 == "too much" && $5 == "later " { print }' input.csv > input1.csv

Note that this solution will work only if last field is exactly too much,later (note the ending space in the string).

A regex based solution is more robust:

awk -F , '/,\s*too\s+much,\s*later\s*/{ print }' input.csv > input1.csv

In this solution, you can have any amount of space character in the string.

  • I tried the your first suggestion and input1.csv came out blank. Your 2nd option produced this error: awk: non-terminated string { print }... at source line 1 context is >>> <<< awk: giving up source line number 2 – eulerprime Jan 30 '17 at 9:10
  • Ops, I fixed the second example. I pasted a " too much – andcoz Jan 30 '17 at 9:15
  • As @kamaraj commented, an example of you input will be useful. Details can change according to it. You have to adapt our examples to your real input files. – andcoz Jan 30 '17 at 9:17
  • Data User Email Comment 1/22/17 0:00 aaaa xxxxxx@xxxxx.com too much, later 1/22/17 0:00 aaaa xxxxxx@xxxxx.com It's too expensive 1/22/17 0:00 aaaa xxxxxx@xxxxx.com Other Reason 1/22/17 0:00 aaaa xxxxxx@xxxxx.com It's too expensive 1/22/17 0:00 aaaa xxxxxx@xxxxx.com Other Reason 1/22/17 0:00 aaaa xxxxxx@xxxxx.com I don't like 1/22/17 0:00 Brenda xxxxxx@xxxxx.com Other Reason – eulerprime Jan 30 '17 at 9:30
  • I tried the revision @andcoz and input1.csv came out blank again. – eulerprime Jan 30 '17 at 9:31
0
awk -F"," 'NF>4 { 
    b=$0; $1=$2=$3=""
    if (match($0,/too much, ?later/)) 
        print b
}' OFS="," 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.