0

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
  • If abcd exists in a row does that satisfy your abc exists criteria or not? In other words are you looking for full matches on a field or partial matches? – Ed Morton Sep 28 '19 at 17:32
  • No, it doesn't satisfy. Looking for full matches not partial matches. Thanka @EdMorton. – ramu suri Sep 29 '19 at 2:24
  • You're welcome. OK, I added an answer given that. – Ed Morton Sep 29 '19 at 14:21
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.

  • Hey @Kusalananda, Thanks for the answer. It works. Thanks a lot for the effort. – ramu suri Sep 29 '19 at 2:40
  • Can I get any suggestion on how to copy the s_marks column which have abc in their row and have an s_marks value above 15 to a text file along with headers. Please note that, the s_marks values should be sorted and printed in a text file. Thanks in advance @Kusalananda – ramu suri Sep 29 '19 at 6:08
1

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
0

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
  • how can I copy the header also i.e s_dob into the output file. Thanks in advance. – ramu suri Sep 29 '19 at 15:15
  • I updated my answer. You should show that as part of the expected output in your question. – Ed Morton Sep 29 '19 at 15:20

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.