7

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!

Kent
  • 189,393
  • 32
  • 233
  • 301
Euan Hume
  • 119
  • 1
  • 4
  • 10

3 Answers3

7

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.)

bos
  • 6,437
  • 3
  • 30
  • 46
  • Thanks for you help: I have put this into a shell script as follow: #!/ltd/bin/bash cat myfile.csv | while read line ; do t=$(echo $line | tr ',' ' '); echo $line\;$(date -d "$t" "+%s") ; done I got the error illegal option --d when i ran it? Also am i right to assume i need to replace ';' with ',' as i have a comma seperated file not semi colon sperated? – Euan Hume Nov 25 '11 at 10:47
  • Run it as a oneliner and test it prior putting it in a script-file to see if it works for you. And yes, you need to change `';'` to `','` if you use semicolon separated csv-file. – bos Nov 25 '11 at 11:03
  • 1
    @bos you can shorten the loop part of the while loop with `echo ${line}\;$(date -d "${line//;/ }" +'%s');` No need for echo and tr to get the right date string. So this could be done inplace. – f4m8 Nov 25 '11 at 11:58
  • You can remove the $t variable as well since it's not used. I don't know why I left it there. Removing it and editing the post. – bos Nov 25 '11 at 13:02
  • What exactly does the `"$t"` after `date -d`? – Youda008 Oct 27 '16 at 10:47
  • `"$t"` is a forgotten left-over variable of a previous script. – bos Oct 28 '16 at 17:03
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
Kent
  • 189,393
  • 32
  • 233
  • 301
2

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
f4m8
  • 410
  • 2
  • 4