Replace date_time to unixtime in csv.file

Shell Programming and Scripting
1

Hello,
since hours I am trying to replace in a csv-file the date_time to unixtime.
I tried sed, awk but not successful. I can not call a shell command within awk.
Probably there is an easier way.
Thanks in advance for your help
Regards, telemi

test.csv:

2010-04-22 01:59:01;test1;0.0;0.0;0.0
2010-04-22 01:59:02;test1;0.0;0.0;0.0
2010-04-22 01:50:01;test1;0.0;0.0;0.0
2010-04-22 01:01:04;test1;0.0;0.0;0.0
2010-04-22 02:00:11;test2;0.0;0.0;0.0
2010-04-22 01:59:06;test1;0.0;0.0;0.0
2010-04-22 01:59:11;test1;0.0;0.0;0.0
2010-04-22 01:14:01;test1;0.0;0.0;0.0
2010-04-22 01:59:01;test1;0.0;0.0;0.0

awk '
BEGIN { FS = "[;]" }
{
   sub($1, Shell-Command date -d "$1" "+%s")
   print
}
' test.csv
3

okay, so I'll confess that my system lacks the GNU version of the date util, so I'm not sure what %s relates to...however, a poor man's approach might be this:

cut -d';' -f2,3,4,5 test.csv |sed "s/^/$(date +date_string%S\; )/g"
4 
awk -F";" -v qt=\" '
{
   "date -d " qt$1qt" "qt"+%s"qt  | getline ts
   sub($1, ts)
   print
} ' file
5 
awk -F";" '{ print $1 ;}' test.csv| xargs -I{} date -d {} +%s
6

Hi,
No very nice command line but at least it works:

/tmp$ awk -F";" '{ print $1 ;}' test.csv| xargs -I{} date -d {} +%s > temp.csv ; paste -d ';' tt.csv test.csv; rm -f temp.csv


1271894341;2010-04-22 01:59:01;test1;0.0;0.0;0.0
1271894342;2010-04-22 01:59:02;test1;0.0;0.0;0.0
1271893801;2010-04-22 01:50:01;test1;0.0;0.0;0.0
1271890864;2010-04-22 01:01:04;test1;0.0;0.0;0.0
1271894411;2010-04-22 02:00:11;test2;0.0;0.0;0.0
1271894346;2010-04-22 01:59:06;test1;0.0;0.0;0.0
1271894351;2010-04-22 01:59:11;test1;0.0;0.0;0.0
1271891641;2010-04-22 01:14:01;test1;0.0;0.0;0.0
1271894341;2010-04-22 01:59:01;test1;0.0;0.0;0.0
7

Since you do have GNU date, I will assume you have gawk too. In that case this will be more efficient and simpler:

gawk -F\; -v OFS=';' '{x=$1;gsub(/:/," ",x);print mktime(x),$0}' test.csv