Bash script to convert a date and time column to unix timestamp in .csv
-
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:
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 "$t" "+%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.)
Euan Hume at Stack Overflow Visit the source
Other answers
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
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
Related Q & A:
- How to Convert Json date string to more readable date format?Best solution by SharePoint
- How to call a function with parameter in a bash script?Best solution by tldp.org
- How do we convert past date into future date?Best solution by Stack Overflow
- How to convert a GMT timestamp to proper Date in PHP?Best solution by stackoverflow.com
- How can I get the remote server's date and time at the client side in my application in c#?Best solution by Stack Overflow
Just Added Q & A:
- How many active mobile subscribers are there in China?Best solution by Quora
- How to find the right vacation?Best solution by bookit.com
- How To Make Your Own Primer?Best solution by thekrazycouponlady.com
- How do you get the domain & range?Best solution by ChaCha
- How do you open pop up blockers?Best solution by Yahoo! Answers
For every problem there is a solution! Proved by Solucija.
-
Got an issue and looking for advice?
-
Ask Solucija to search every corner of the Web for help.
-
Get workable solutions and helpful tips in a moment.
Just ask Solucija about an issue you face and immediately get a list of ready solutions, answers and tips from other Internet users. We always provide the most suitable and complete answer to your question at the top, along with a few good alternatives below.