Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems. It only takes a minute to sign up.
Sign up to join this community
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
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
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
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.
awk -F"," 'NF>4 {
b=$0; $1=$2=$3=""
if (match($0,/too much, ?later/))
print b
}' OFS="," input.csv > input1.csv