Bash script to convert a date and time column to a 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 in unix timestamps. So I need to get a date and time column from each row, convert it and insert it into an extra column at the end containing the timestamp.

Can anyone help me? So far, I have discovered the unix command to convert any time and date dates to unixstamp:

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

I have no experience with a bash script so that someone runs me?

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 a lot!

Answer 1

You do not provide exrpt from your csv file, so I use this file:

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

Here is 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 : unhandled variable removed.)

( EDIT2 : The date command has changed, so the script works.)

Answer 2

Now two functions:

First: there is no need for cat foo.csv, just a stream that through <foo.csv into a while loop.

Secondly: there is no need for echo and tr to create a dateformat string. Just use bash internal template and replace it and do it in place

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

Answer 3

this should complete the task:

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

note

you did not give a single example. and you mentioned csv , so I assume that the column delimiter in your file should be a 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