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,[email protected],too much,later 
10
  • 1
    can you provide the sample file contents.. you should redirect the output using greater than symbol ( > input1.csv )
    – Kamaraj
    Jan 30, 2017 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, 2017 at 9:03
  • Data User Email Comment 1/22/17 0:00 aaaa [email protected] too much, later 1/22/17 0:00 aaaa [email protected] It's too expensive 1/22/17 0:00 aaaa [email protected] Other Reason 1/22/17 0:00 aaaa [email protected] It's too expensive 1/22/17 0:00 aaaa [email protected] Other Reason 1/22/17 0:00 aaaa [email protected] I don't like
    – eulerprime
    Jan 30, 2017 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, 2017 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, 2017 at 10:16

4 Answers 4

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
4
  • 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, 2017 at 9:01
  • Works fine here with csvgrep 1.0.2. shrug Jan 30, 2017 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, 2017 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 Jan 30, 2017 at 11:53
2

Assuming properly formatted CSV:

Date,User,Email,Comment
11/22/16,aaaa,[email protected],"too much, later"

This works, using csvkit:

$ csvgrep -c Comment -m 'too much, later' data.csv
Date,User,Email,Comment
11/22/16,aaaa,[email protected],"too much, later"

$ csvgrep -c 4 -m 'too much, later' data.csv
Date,User,Email,Comment
11/22/16,aaaa,[email protected],"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,[email protected],"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,[email protected],too much,later 
2
  • 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, 2017 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, 2017 at 10:38
2

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.

8
  • 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, 2017 at 9:10
  • Ops, I fixed the second example. I pasted a " too much
    – andcoz
    Jan 30, 2017 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, 2017 at 9:17
  • Data User Email Comment 1/22/17 0:00 aaaa [email protected] too much, later 1/22/17 0:00 aaaa [email protected] It's too expensive 1/22/17 0:00 aaaa [email protected] Other Reason 1/22/17 0:00 aaaa [email protected] It's too expensive 1/22/17 0:00 aaaa [email protected] Other Reason 1/22/17 0:00 aaaa [email protected] I don't like 1/22/17 0:00 Brenda [email protected] Other Reason
    – eulerprime
    Jan 30, 2017 at 9:30
  • I tried the revision @andcoz and input1.csv came out blank again.
    – eulerprime
    Jan 30, 2017 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

You must log in to answer this question.

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