Bash script to convert a date and time column to unix timestamp in .csv

Question

I am trying to create a script to convert two columns in a .csv file which are date and time into unix timestamps. So i need to get the date and time column from each row, convert it and insert it into an additional column at the end containing the timestamp.

Could anyone help me? So far i have discovered the unix command to convert any give time and date to unixstamp:

date -d "2011/11/25 10:00:00" "+%s"
1322215200

I have no experience with bash scripting could anyone get me started?

Examples of my columns and rows:

Columns: Date, Time, 
Row 1: 25/10/2011, 10:54:36,
Row 2: 25/10/2011, 11:15:17,
Row 3: 26/10/2011, 01:04:39,

Thanks so much in advance!

Answer 1

You don't provide an exerpt from your csv-file, so I'm using this one:

[foo.csv]
2011/11/25;12:00:00
2010/11/25;13:00:00
2009/11/25;19:00:00

Here's one way to solve your problem:

$ cat foo.csv | while read line ; do echo $line\;$(date -d "${line//;/ }" "+%s") ; done
2011/11/25;12:00:00;1322218800
2010/11/25;13:00:00;1290686400
2009/11/25;19:00:00;1259172000

(EDIT: Removed an uneccessary variable.)

(EDIT2: Altered the date command so the script actually works.)

Answer 2

this should do the job:

 awk  'BEGIN{FS=OFS=", "}{t=$1" "$2; "date -d \""t"\"  +%s"|getline d; print $1,$2,d}' yourCSV.csv

note

you didn't give any example. and you mentioned csv, so I assume that the column separator in your file should be "comma".

test

kent$  echo "2011/11/25, 10:00:00"|awk  'BEGIN{FS=OFS=", "}{t=$1" "$2; "date -d \""t"\"  +%s"|getline d; print $1,$2,d}'
2011/11/25, 10:00:00, 1322211600

Answer 3

Now two imporvements:

First: No need for cat foo.csv, just stream that via < foo.csv into the while loop.

Second: No need for echo & tr to create the date stringformat. Just use bash internal pattern and substitute and do it inplace

while read line ; do echo ${line}\;$(date -d "${line//;/ }" +'%s'); done < foo.csv