Login or Register to Ask a Question and Join Our Community


UNIX for Dummies Questions & Answers

Date reformatting

 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers Date reformatting
# 1  
Old 07-10-2011
Date reformatting
I currently have the following file containing sample values for a number of dates:
Code:
Loc1 04 Jan 2007 0.95 0.9532
Loc1 05 Jan 2007 0.95 0.9513
Loc1 06 Jan 2007 0.95 0.9535

This continues for all months of the year and spans across several years.

I am trying to reformat the dates so that they become mysql-friendly, ie to yyyy-mm-dd

Firstly, is there an easier way to translate the months than this?
Code:
sed -i '' -e 's/ Jan /-01-/' file.dat
sed -i '' -e 's/ Feb /-02-/' file.dat
sed -i '' -e 's/ Mar /-03-/' file.dat
sed -i '' -e 's/ Apr /-04-/' file.dat
sed -i '' -e 's/ May /-05-/' file.dat
sed -i '' -e 's/ Jun /-06-/' file.dat
sed -i '' -e 's/ Jul /-07-/' file.dat
sed -i '' -e 's/ Aug /-08-/' file.dat
sed -i '' -e 's/ Sep /-09-/' file.dat
sed -i '' -e 's/ Oct /-10-/' file.dat
sed -i '' -e 's/ Nov /-11-/' file.dat
sed -i '' -e 's/ Dec /-12-/' file.dat

Secondly, from there I reformat the dates from dd-mm-yyyy to yyyy-mm-dd as follows:
Code:
sed -i '' -e "s#\(..\) \(..\) \(....\)#\3-\2-\1#" file.dat

Perhaps all commands can be combined into one, preferably using sed or tr?

Thank you in advance
# 2  
Old 07-10-2011
One way, keeping with your original approach:

Code:
cat my_file |while read a b c d e f 
> do 
> echo "$d-$c-$b" 
> done |sed -e 's/Jan/01/g;s/Feb/02/g;s/Mar/03/g;s/Apr/04/g;s/May/05/g;s/Jun/06/
g;s/Jul/07/g;s/Aug/08/g;s/Sep/09/g;s/Oct/10/g;s/Now/11/g;s/Dec/12/g;'

---------- Post updated at 06:27 ---------- Previous update was at 06:22 ----------

Or, maybe even better...and keeping it all within the same loop each time and therefore clearly a one-liner, though it might be just a bit slower:

Code:
cat my_file |while read a b c d e f ;do echo $d-$c-$b |sed -e 's/Jan/01/g;s/Feb/02/g;s/Mar/03/g;s/Apr/04/g;s/May/05/g;s/Jun/06/g;s/Jul/07/g;s/Aug/08/g;s/Sep/09/g;s/Oct/10/g;s/Now/11/g;s/Dec/12/g;' ;done

Of course, you may choose to disabuse your cat as you wish...
This User Gave Thanks to curleb For This Post:
figaro
# 3  
Old 07-10-2011
Code:
$ awk '{month="  JanFebMarAprMayJunJulAugSepOctNovDec"; printf ("%s-%02d-%s\n",$4,index(month,$3)/3,$2) }' test.txt
2007-01-04
2007-01-04
2007-02-04
2007-01-04
2007-03-05
2007-01-06
2007-04-05
2007-01-06
2007-08-05
2007-05-06
2007-01-05
2007-12-06

$ cat test.txt
Loc1 04 Jan 2007 0.95 0.9532
Loc1 04 Jan 2007 0.95 0.9532
Loc1 04 Feb 2007 0.95 0.9532
Loc1 04 Jan 2007 0.95 0.9532
Loc1 05 Mar 2007 0.95 0.9513
Loc1 06 Jan 2007 0.95 0.9535
Loc1 05 Apr 2007 0.95 0.9513
Loc1 06 Jan 2007 0.95 0.9535
Loc1 05 Aug 2007 0.95 0.9513
Loc1 06 May 2007 0.95 0.9535
Loc1 05 Jan 2007 0.95 0.9513
Loc1 06 Dec 2007 0.95 0.9535

This User Gave Thanks to itkamaraj For This Post:
figaro
 
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Help reformatting column

Hello UNIX experts, I'm stumped finding a method to reformat a column. Input file is a two column tab-delimited file. Essentially, for every term that appears in column 2, I would like to summarize whether that term appears for every entry in column 1. In other words, make a header for each term... (2 Replies)
Discussion started by: torchij
2 Replies

2. UNIX for Dummies Questions & Answers

Need help in reformatting the input

Hi i want to print line which is mentioned as below 615213:1;20150725;20250722;0|11;20150831;20150831;100|14;20150725;20160723;2 in below format. ' 615213: 1;20150725;20250722;0 615213: 11;20150831;20150831;100 615213: 14;20150725;20160723;2 please help me and suggest me how to... (9 Replies)
Discussion started by: scriptor
9 Replies

3. Shell Programming and Scripting

Date Reformatting function

Hi , I am trying to create a function with below requirements 1. It will take two parameters as Input. Date and Date format 2. Output will be in YYYYMMDD format. Example 1: Input: fn_date_reformatter('01-AUG-2014','DD-MON-YYYY') Output: 20140801 Example 2: Input:... (4 Replies)
Discussion started by: Anupam_Halder
4 Replies

4. Shell Programming and Scripting

Reformatting a list to table

Hi! I have a list with a lot of records that I need to work with. The problem is that the list is populated successive one record at the time in a text file, and to gain anything from these records I need them to be put out in a table. This is an example of what the list looks like: (145)... (5 Replies)
Discussion started by: ivar.friheim
5 Replies

5. UNIX for Dummies Questions & Answers

Date reformatting

I have been reformatting dates from a data file to make them mysql compliant. 31-10-2011 Loc1 1-11-2011 Loc2 The first can be captured by this: sed -i '' -e "s#\(..\)-\(..\)-\(....\)#\3-\2-\1#" data.txt and leads to: 2011-10-31 Loc1 The second line is captured as follows: sed -i... (2 Replies)
Discussion started by: figaro
2 Replies

6. UNIX for Dummies Questions & Answers

Date reformatting

I have a file with temperature measurements: Loc1,20090102,71.55 Loc1,20090103,71.65 Loc1,20090104,71.55 Loc1,20090105,71.54 Loc1,20090106,71.54 However, to load this into a database I would like to reformat the dates (column 2) from the yyyymmdd format to the yyyy-mm-dd format. I have... (2 Replies)
Discussion started by: figaro
2 Replies

7. Shell Programming and Scripting

Help reformatting output

I have a command that gives me the output below: JAVA_HOME = C:/jdk1.5.0_11 Broker Performance Report for server 'app1' RMI_URL = rmis:// Parameter Kintana ItgDS DashboardDS ---------------------------- ------- ----- ----------- Connections count 41 ... (4 Replies)
Discussion started by: bwiebe
4 Replies

8. Shell Programming and Scripting

Reformatting Data in AWK

Dear AWK Users, I have a data set that is so large (Gigabytes) that it cannot be opened in the vi editor in its entirety. But I can manipulate the entire thing in AWK. It is formatted in a regular manner such that it has the variable descriptions or listings preceeding the variables. The latter... (13 Replies)
Discussion started by: sda_rr
13 Replies

9. UNIX for Dummies Questions & Answers

Reformatting file

Hi, How can I reformat a file (text file) using unix command. This file was FTP'd from Mainframe and contains some garbage character at the end of each line. Each line contains special characters '<soh>' at the end which should have been spaces when I view it in emacs or nedit. I couldnt do find... (2 Replies)
Discussion started by: mrjunsy
2 Replies

10. Filesystems, Disks and Memory

reformatting a floppy!

i am trying to reformat a floppy i am using solaris 9 when i run this: rmformat -F quick /vol/dev/aliases/floppy0 it tells me that it cannot perform the operation on a mounted device. how do i unmount the device and format the floppy? (1 Reply)
Discussion started by: rmuhammad
1 Replies
Login or Register to Ask a Question