Changing date format in CSV file


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Changing date format in CSV file
# 1  
Old 11-22-2012
Changing date format in CSV file

I have a CSV file with a date format like this;
Code:
11/19/2012 17:37:00,1.372,121.6
11/19/2012 17:38:00,0.743,121.6

Want to change the time stamp to seconds after 1970 so I can get the data in rrdtool. For anyone interested, this is data from a TED5000 unit and is Kwatts and volts.

Needs to look like;
Code:
1353364620,1.372,121.6
1353364680,0.743,121.6

I know that the date command will convert the time;
Code:
date +"%s" -d '11/19/2012 17:38:00'

And that the sed command will search and replace. Just trying the best way to get the data in the correct format and then use the update command in rrdtool to get the data in the rrd data base.

Last edited by Franklin52; 11-23-2012 at 03:10 AM.. Reason: Please use code tags for data and code samples
# 2  
Old 11-22-2012
Try:
Code:
while IFS="," read d a b; do d=`date +"%s" -d "$d"`; echo "$d,$a,$b"; done < input > output

This User Gave Thanks to bartus11 For This Post:
# 3  
Old 11-22-2012
Thanks, works great, just what I needed.
# 4  
Old 11-23-2012
If your CSV file is large, you might be considering this awk alternative:

Code:
 awk -F, '{"date +%s -d\""$1 "\""| getline dte;$1=dte}1' OFS="," file

This should be *much* faster on large files. About 350 times faster on a 3000 lines sample file.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Changing CSV files with date . Subtracting date by values

Hi All, I have a CSV file which is as below. Basically I need to take the year column in it and find if the year is >= 20152 . If that is then I should subtract all values by 6. In the below example in description I am having number mentioned as YYWW so I need to subtract those by -5. Whereever... (8 Replies)
Discussion started by: arunkumar_mca
8 Replies

2. Shell Programming and Scripting

Date format change in a csv file

Hi, We have csv file where date is coming in MM/DD/YYYY HH:MM:SS (06/23/2015 20:59:12) in multiple places But we need to change the date format to DD/Mon/YYYY HH:MM:SS (23/Jul/2015 20:59:12) using shell script. Please let us know how can we achieve the same. (16 Replies)
Discussion started by: dholea
16 Replies

3. Shell Programming and Scripting

Need to change date format in a csv file using awk

Example: Input csv file 00245DLS,Sitel Ocala,12/31/2014,18:45,1.00,7.00,0.00,0.00 00245DLS,Sitel Ocala,12/31/2014,19:00,-1.00,-1.00,-1.00,-1.00 00245HB,Charlotte,01/01/2015,00:00,-1.00,-1.00,-1.00,0.00 Output csv file 00245DLS,Sitel Ocala,2014/12/31,18:45,1.00,7.00,0.00,0.00 00245DLS,Sitel... (8 Replies)
Discussion started by: adit
8 Replies

4. Shell Programming and Scripting

Changing Date Format

How can i make the date command output yesterday's date, current date and the date 4 days ago, in the following format: 2012-10-03 code: date +% ???? (3 Replies)
Discussion started by: SkySmart
3 Replies

5. Shell Programming and Scripting

Changing the date format

Hi all, I have a file with below data af23b|11-FEB-12|acc7 ad23b|12-JAN-12|acc4 as23b|15-DEC-11|acc5 z123b|18-FEB-12|acc1 I need the output as below:-(date in yyyymmdd format) af23b|20120211|acc7 ad23b|20120112|acc4 as23b|20111215|acc5 z123b|20120218|acc1 Please help me on this.... (7 Replies)
Discussion started by: gani_85
7 Replies

6. Shell Programming and Scripting

Command for changing date format in a file

Hi... I have an inputfile name as :- abc_test_20120213.dat (date in yyyymmdd format) I need the output file name as abc_test_13022012.dat (date in ddmmyyyy format) Please help me on this... Thanks in advance. (5 Replies)
Discussion started by: gani_85
5 Replies

7. UNIX for Dummies Questions & Answers

Changing from Excel date format to MySQL date format

I have a list of dates in the following format: mm/dd/yyyy and want to change these to the MySQL standard format: yyyy-mm-dd. The dates in the original file may or may not be zero padded, so April is sometimes "04" and other times simply "4". This is what I use to change the format: sed -i '' -e... (2 Replies)
Discussion started by: figaro
2 Replies

8. Shell Programming and Scripting

Format a date in a csv file

So I have a csv file where the 3rd field is a date string in the format yyyy-mm-dd. I need to change it to mm/dd/yyyy. So each line in the csv file looks like: StringData,StringData,2009-02-17,12.345,StringData StringData,StringData,2009-02-16,65.789,StringData Any idea how I can keep... (5 Replies)
Discussion started by: rpiller
5 Replies

9. Shell Programming and Scripting

changing the format of CSV file

Hi Experts, Please help me to get the following from script for Unix ENvironment(shell, perl, tr, sed, awk). INPUT FILE: 20K,ME,592971 20K,YOU,2 20K,HE,1244998 50K,YOU,480110 50K,ME,17 50K,HIS,10 50K,HE,1370391 OUTPUT FILE: K,ME,YOU,HE,HIS 20K,592971,2,1244998,0... (5 Replies)
Discussion started by: ashis.tewari
5 Replies

10. UNIX for Dummies Questions & Answers

Changing the format of date

Hi, There are lots of threads about how to manipulate the date using date +%m %....... But how can I change the default format of the commad date? $ date Mon Apr 10 10:57:15 BST 2006 This would be on fedora and SunOs. Cheers, Neil (4 Replies)
Discussion started by: nhatch
4 Replies
Login or Register to Ask a Question