Script bash to change the format of the date in .csv files

advertisements

I am currently processing a batch -50plus csv files every week that have the timestamp displayed as Tue Oct 01 10:59:59 PDT 2013. I need to be able to go through line by line and change the format to 10/01/13 10:59:59. Some of the files have the timestamp as the first string, some have it in say the third string. I am having no luck...

Here is a snipit of two of the csv files.

1.csv

Tue Oct 01 10:59:59 PDT 2013,data1,1,Databcd,Dataxyz,0,0,431,0

Tue Oct 01 11:59:59 PDT 2013,data1,1,Databcd,Dataxyz,0,0,401,0

2.csv

data1,0,Databcd,0,0,0,Tue Oct 01 11:59:59 PDT 2013,Dataxyz

data1,0,Databcd,0,0,0,Tue Oct 01 12:59:59 PDT 2013,Dataxyz

Thanks in advance -

here is the script as I last ran it..

#!/bin/bash

for f in $*
do
echo "Processing [$f]..."

ftemp=$f.TMP
    #echo "ftemp=$ftemp"
#this uses sed to delete the day(word) frm the timestamp.
sed -e 's/Mon //g' <$f >$ftemp
mv $ftemp $f   #copy it back over the original
sed -e 's/Tue //g' <$f >$ftemp
mv $ftemp $f   #copy it back over the original
sed -e 's/Wed //g' <$f >$ftemp
mv $ftemp $f   #copy it back over the original
sed -e 's/Thu //g' <$f >$ftemp
mv $ftemp $f   #copy it back over the original
sed -e 's/Fri //g' <$f >$ftemp
mv $ftemp $f   #copy it back over the original
sed -e 's/Sat //g' <$f >$ftemp
mv $ftemp $f   #copy it back over the original
sed -e 's/Sun //g' <$f >$ftemp
mv $ftemp $f   #copy it back over the original

#strip out the PDT & Year from end of each line
sed -e 's/\ PDT / /g' -e 's/\ PST / /g' <$f >$ftemp
mv $ftemp $f   #copy it back over the original
sed --date="Oct 01 00:59:59 2013" +%D <$f >$ftemp
mv $ftemp $f   #copy it back over the original
#echo "10/01/2013" |    sed -E 's/([a-z ]?)\/([0-9][0-9 ]?)\/([0-9][0-9][0-9][0-9]
#/\3-\2-\1/' <$f >$ftemp
#   tr  'Oct' '10/' <$f >$ftemp
#   mv $ftemp $f   #copy it back over the original
done

echo "Done."

As you can see, I have a few options that I have tried commented out


Here is an attempt using sed:

sed -i.bak -r -e 's,[[:alpha:]]{3}\s+([[:alpha:]]{3})\s+([0-9]{2})\s+([0-9]{2}:[0-9]{2}:[0-9]{2})\s+[A-Z]{3}\s+[0-9]{2}([0-9]{2}),\1/\2/\4 \3,g' -e 's/Jan/01/; s/Feb/02/; s/Mar/03/; s/Apr/04/; s/May/05/; s/Jun/06/; s/Jul/07/; s/Aug/08/; s/Sep/09/; s/Oct/10/; s/Nov/11/; s/Dec/12/;' *.csv

Working for me on your sample inputs.