Use sed and grep to extract data for particular months in a file with timestamps

Question

I have text file with six years (2011-2016) of data. I want to extract only April and May data of all six years.

@STATION_ID,LATITUDE,LONGITUDE,TIME(GMT),DATE(GMT),AIR_TEMP(‌​°C) 
IMDE1611_14164B(PITAMPURA),28.7,77.15,0,08/09/2011,33.5 
IMDE1611_14164B(PITAMPURA),28.7,77.15,1,08/09/2011,33.3 
IMDE1611_14164B(PITAMPURA),28.7,77.15,2,08/09/2011,33.8 
IMDE1611_14164B(PITAMPURA),28.7,77.15,3,08/09/2011,33.8 
IMDE1611_14164B(PITAMPURA),28.7,77.15,4,08/09/2011,34.5 
IMDE1611_14164B(PITAMPURA),28.7,77.15,5,08/09/2011,35.0 
IMDE1611_14164B(PITAMPURA),28.7,77.15,6,08/09/2011,34.9 
IMDE1611_14164B(PITAMPURA),28.7,77.15,7,08/09/2011,35.4 

I am using grep and sed commands to filter the data but it is not showing the result I want. I am using these commands:

grep "??-0[4-5]-????" filename.txt > filename.csv
sed -n '/2016-04-01/,/2016-04-30/{/2016-04-30/d; p}' my_delhi.txt
sed -n '/2016-04-01/,/2016-04-30/p' my_delhi.txt

Answer 1

You can use something else instead of / as sed's delimiter.

sed -n '\:08/09/2011:p' file

or with regex and '#' as delimiter:

sed -nr "\#,[0-9]{2}/[0-9]{2}/[0-9]{4},#p" file

for April and May only (dd/mm/yyyy):

sed -nr "\#,[0-9]{2}/0[45]{1}/[0-9]{4},#p" file

or (mm/dd/yyy):

sed -nr "\#,0[45]{1}/[0-9]{2}/[0-9]{4},#p" file

Answer 2

In your file it looks like the date format is actually dd/mm/yyyy or mm/dd/yyyy, but in your commands you assume it is dd-mm-yyyy or yyyy-mm-dd.

You should be able to grep the April and May lines, assuming the format is mm/dd/yyyy with this expression

grep -E '(04|05)/[0-9]+/[0-9]+' file

if it is dd/mm/yyyy then you could use:

grep -E '[0-9]+/(04|05)/[0-9]+' file

Based on your file sample, these should be specific enough.

Notes

• -E use extended regex
• (04|05) match 04 or 05
• [0-9]+ at least one digit (of course we could be more strict here about the exact date formatting, for example a day could be [0-3][0-9] and a year 20[0-1][0-9], but there does not seem to be a need in this case)

Answer 3

Perl approach

$ perl -F'/,/' -ane 'print if $F[4]=~/^(04|05)/' input.txt                                                               
IMDE1611_14164B(PITAMPURA),28.7,77.15,2,04/09/2011,33.8 
IMDE1611_14164B(PITAMPURA),28.7,77.15,3,05/09/2011,33.8 

What happens here is that we use comma as separator for columns, and print if and only if the 4th column (which is date) starts with 04 or 05. This is consistent with OP's comment where they stated:

...Date format is mm:dd:yyyy.