CSV dataset to text dataset

Question

I have a csv dataset student.csv with 1000 rows and 10 columns.

The sample data looks like this:

s_id,s_name,s_dob,s_class,s_marks,s_parentname,
   1,abc,1/1/18,5,49,def,
   2,xyz,1/1/17,4,85,abc,
   3,pqr,1/2/18,2,78,mnp,

From this dataset, I want to copy all the dates that have 'abc' in their rows to a text file.

Expected output:

text.txt
1/1/18
1/1/17

Answer 1

grep abc student.csv | cut -d, -f 3 >text.txt

This would first extract every line from student.csv that contains the substring abc anywhere, and then cut out the 3rd comma-delimited column from those lines and store them in text.txt.

With the data given in the question, text.txt would end up containing

1/1/18
1/1/17

This relies on the original file being a simple CSV file, i.e. without fields containing embedded commas or newlines.

Would you want to search for abc as a complete word, then use grep -w abc in place of just grep abc. This would avoid matching lines containing strings like abcde, but would still match a field containing abc xyz.

If you are looking for fields that contain an arbitrary complex string (still in a simple CSV file) and if you need the field to be exactly equal to that string, then you would have to iterate over the fields on each line:

string='some string' awk -F, '{ for (i = 1; i <= NF; ++i) if ($i == ENVIRON["string"]) { print $3 ; next } }' student.csv

This awk code iterates over all fields on each line looking for a comma-delimited field whose value is exactly the same as the value of the environment variable string. Once such a field is found, the 3rd field is printed.

Answer 2

using Miller (https://github.com/johnkerl/miller) starting from

s_id,s_name,s_dob,s_class,s_marks,s_parentname,
1,abc,1/1/18,5,49,def,
2,xyz,1/1/17,4,85,abc,
3,pqr,1/2/18,2,78,mnp,

and running

mlr --nidx --fs "," filter -S '$2=="abc"' then cut -f 3 input.txt >output.txt

you will have

1/1/18

Answer 3

Given you need full field matches:

$ awk -F, 'FNR==1{print FILENAME} /(^|,)abc(,|$)/{print $3}' file
file
1/1/18
1/1/17

or if abc can actually contain RE metachars that you want treated literally and so you need to do a string rather than regexp match:

$ awk -F, 'FNR==1{print FILENAME} index(","$0",",",abc,"){print $3}' file
file
1/1/18
1/1/17

or to print the column header:

$ awk -F, 'FNR==1{print $3} /(^|,)abc(,|$)/{print $3}' file
s_dob
1/1/18
1/1/17

$ awk -F, 'FNR==1{print $3} index(","$0",",",abc,"){print $3}' file
s_dob
1/1/18
1/1/17