2

Consider a .csv file which has dates listed in the 6th column in format 7/20/2017 17:00 for example.

If I give input as 7/11/2017 then it should filter the column that has the matching input date and the output should be saved in another .csv file

I need a command to filter 6th column based on input given.

file.csv has below entries:

23,54,one,tree,56,7/20/2017 17:00,23,iii,89
23,54,one,tree,56,7/20/2017 17:00,23,iii,89
23,54,one,tree,56,7/21/2017 17:00,23,iii,89
23,54,one,tree,56,7/30/2017 17:00,23,iii,89

similarly multiple entries that date and time format in 6th column. Input given in runtime like read 7/20/2017, then it should filter the date from 6th column and save the output entries to another .csv file.

1

Simply with awk:

awk -F, -v d="7/20/2017" '$6~d' input.csv > dates.csv

The dates.csv contents:

23,54,one,tree,56,7/20/2017 17:00,23,iii,89
23,54,one,tree,56,7/20/2017 17:00,23,iii,89

If date value comes from shell variable:

d="7/20/2017"
awk -F, -v d="$d" '$6~d' input.csv > dates.csv
  • the matching column's entire row should be moved to dates.csv. Like if i give input 7/20/2017 then output 23,54,one,tree,56,7/20/2017 17:00,23,iii,89 23,54,one,tree,56,7/20/2017 17:00,23,iii,89 should be saved in dates.csv file – user8554534 Oct 11 '17 at 18:20
  • @user8554534, ok, it's even shorter – RomanPerekhrest Oct 11 '17 at 18:21
  • thanks a lot...it works. !!! Also can you share me the awk command if the date input is given in a variable like date="7/20/2017" – user8554534 Oct 11 '17 at 18:24
  • Like date="7/20/2017" Command should be like awk -F, -v $date '$6~date' input.csv > dates.csv – user8554534 Oct 11 '17 at 18:27
  • don't use date as a variable name. It's a built-in function name – RomanPerekhrest Oct 11 '17 at 18:28
0

Assuming there is only one column with a date in that format:

$ grep -F '7/20/2017' file.csv
23,54,one,tree,56,7/20/2017 17:00,23,iii,89
23,54,one,tree,56,7/20/2017 17:00,23,iii,89

You may redirect that to a new file:

$ grep -F '7/20/2017' file.csv >new.csv

To properly parse a CSV file, use a CSV parser, like the one built into the CSVkit tools:

$ csvgrep -c 6 -m '7/20/2017' file.csv
23,54,one,tree,56,7/20/2017 17:00,23,iii,89
23,54,one,tree,56,7/20/2017 17:00,23,iii,89

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.