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!

Conversion only without parsing part: stackoverflow.com/questions/10990949/… — Ciro Santilli OurBigBook.com
– Ciro Santilli OurBigBook.com, Commented Jul 25, 2015 at 9:00

bos · Accepted Answer · 2016-10-28 17:04:44Z

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

edited Oct 28, 2016 at 17:04

answered Nov 25, 2011 at 10:36

bos

6,5554 gold badges32 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Euan Hume Over a year ago

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?

bos Over a year ago

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.

f4m8 Over a year ago

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

bos Over a year ago

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.

Youda008 Over a year ago

What exactly does the "$t" after date -d?

|

Kent · Accepted Answer · 2011-11-25 10:31:33Z

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

answered Nov 25, 2011 at 10:31

Kent

197k36 gold badges248 silver badges317 bronze badges

Comments

f4m8 · Accepted Answer · 2011-11-25 12:02:56Z

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

answered Nov 25, 2011 at 12:02

f4m8

4102 silver badges4 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related