Visit Our UNIX and Linux User Community


DateTime Format Conversion in a File


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting DateTime Format Conversion in a File
# 1  
Old 10-17-2007
DateTime Format Conversion in a File

Hi Pals.

I have a file which is in the below format.

2007/10/15-12:04:24,'CRLift','TH-42PX75U','BestBuy','W01P0207',22,15,'User1'
2007/10/15-12:04:26,'CRLift','TH-42PX75U','BestBuy','W01P0207',22,15,'User2'
2007/10/15-12:04:29,'CRLift','TH-42PX75U','BestBuy','W01P0207',22,15,'User3'
2007/10/15-12:04:22,'CRLift','TH-42PX75U','CircuitCity','W01P0207',22,15,'User1'
2007/10/15-12:04:34,'CRLift','TH-42PX75U','CircuitCity','W01P0205',22,15,'User2'
2007/10/15-12:04:45,'CRLift','TH-42PX75U','CircuitCity','W01P0206',22,15,'User3'
2007/10/15-12:04:25,'CRLift','TH-42PX75U','CircuitCity','W01P0202',22,15,'User4'


All the fields are delimeted by "," comma

I need to convert the first field time format to MM/DD/YYYY HH24:MI:SS
Which is required later to load from SqlLoader.

Present format is YYYY/MM/DD-HH24:MI:SS
Required Format is MM/DD/YYYY HH24:MI:SS



Please Help me out...
Quick Response would be very helpfull...

Thanks & Regards
SrikGR.

Last edited by srikanthgr1; 10-17-2007 at 01:32 PM..
# 2  
Old 10-17-2007
One way:
Code:
#Present format is YYYY/MM/DD-HH24:MI:SS
#Required Format is MM/DD/YYYY HH24:MI:SS

awk -F, '{
           split($1,dateval, "-")
           split(dateval[1], date, "/");
           value=sprintf("%s/%s/%s %s",date[2],date[3],date[1],dateval[2])
           printf("%s",value)
           for(i=2; i<=NF;i++)
                {printf(",%s", $i)};
           printf("\n");
        }' filename

Code:
# output
10/15/2007 12:04:24,'CRLift','TH-42PX75U','BestBuy','W01P0207',22,15,'User1'
10/15/2007 12:04:26,'CRLift','TH-42PX75U','BestBuy','W01P0207',22,15,'User2'
10/15/2007 12:04:29,'CRLift','TH-42PX75U','BestBuy','W01P0207',22,15,'User3'
10/15/2007 12:04:22,'CRLift','TH-42PX75U','CircuitCity','W01P0207',22,15,'User1'
10/15/2007 12:04:34,'CRLift','TH-42PX75U','CircuitCity','W01P0205',22,15,'User2'
10/15/2007 12:04:45,'CRLift','TH-42PX75U','CircuitCity','W01P0206',22,15,'User3'
10/15/2007 12:04:25,'CRLift','TH-42PX75U','CircuitCity','W01P0202',22,15,'User4'

# 3  
Old 10-17-2007
sed

Hi,
This one should be ok for you.

Code:
echo 2007/10/15-12:04:24 | 
sed 's/\([0-9][0-9][0-9][0-9]\)\/\([0-9][0-9]\)\/\([0-9][0-9]\)\(-\)\(.*\)/\3\/\2\/\1 \5/'

# 4  
Old 10-23-2007
This wil be simple


for file in `cat bb`
do
var=`echo $file | cut -d"-" -f1`
var2=`echo $file | cut -d"-" -f2`
var3=`echo $file | cut -d"-" -f3`
value1=`echo $var | cut -d"/" -f1`
value2=`echo $var | cut -d"/" -f2`
value3=`echo $var | cut -d"/" -f3`
echo $value2"/$value3""/$value1""-$var2".$var3
done
# 5  
Old 10-23-2007
Hey Pals..
Thanks for the help..
Each of the above replies helped me and also vgersh sent out a another reply to this thread..
I would post that as that might be helpfull to another one like me...

nawk -f sri.awk a.dat

sri.awk:

Code:
BEGIN {
  FS=OFS=","
  SEPs="[-/:]"
}
{
  split($1, a, SEPs)
  $1 = a[2] "/" a[3] "/" a[1] " " a[4] ":" a[5] ":" a[6]
  print
}


Previous Thread | Next Thread
Test Your Knowledge in Computers #83
Difficulty: Easy
BSD, Linux, Mac OS X, and other Unix-like operating systems did not inherit aspects of the original Unix design.
True or False?

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Format conversion

Can you help me get the desired output? Below is the input CONA= 0. 5. 10. 15. 20. 25. 30. 35. 40. 45. 50. 55. 60. 65. 70. 75. 80. 85. 90. 95. 100. 105. 110. PLANA= 0. 15. 30. 45. 60. 75. 90. 105. 120. 135. 150. 165. 180. ITABLE= 87.3 171.4 242.9 297.6 322.8 325.6 306.8 284.5 273.4 272.2 270.2... (6 Replies)
Discussion started by: Ravi S M
6 Replies

2. Shell Programming and Scripting

Date conversion from 24 hr format to 12 hr format

hi i want to convert date procured from sone operation which will be in 24hr format to 12 hr format displaying AM and PM # date -d @1362545068 Tue Mar 5 23:44:28 EST 2013 # this Tue Mar 5 23:44:28 EST 2013 i want to convert it so that output is as below Tue... (2 Replies)
Discussion started by: vivek d r
2 Replies

3. Shell Programming and Scripting

XML dateTime format in shell script

How can i frame current date & time in the xml standard dateTime format in shell script 2011-10-18T12:00:00.000000 I got up to the date format using the below code date '+%Y'-'%m'-'%d'T ---------- Post updated at 09:10 AM ---------- Previous update was at 08:53 AM ---------- ... (6 Replies)
Discussion started by: vel4ever
6 Replies

4. Shell Programming and Scripting

conversion of spaces into CSV format file

INput file attached in thread : Column widths at 24,73,82,87,121 characters (sed 's/./,/24;s/./,/73;s/./,/81;s/./,/87;s/./,/121;s/ *, */,/g' fixedinputfile >output.csv ). The client wants instead of hard coding the column widths as they are not fixed .he has given the hint stating that ( ... (3 Replies)
Discussion started by: sreenath1037
3 Replies

5. Shell Programming and Scripting

Conversion of spaces Text file into CSV format file

Input file (each line is separaed by spaces )given below: Name Domain Contact Phone Email Location ----------------------- ------------------------------------------------ ------- -----... (18 Replies)
Discussion started by: sreenath1037
18 Replies

6. Shell Programming and Scripting

Conversion of below Tabs Tex file into CSV format file : shell script needed

Request if some one could provide me shell script that converts the below "input file" to "CSV format file" given Name Domain Contact Phone Email Location ----------------------- ------------------------------------------------ ------- ----- ---------------------------------... (7 Replies)
Discussion started by: sreenath1037
7 Replies

7. Ubuntu

Conversion of the format of a file in linux

How to convert a rtf file to a ttf file in ubuntu terminal? (2 Replies)
Discussion started by: poonam.gaigole
2 Replies

8. Shell Programming and Scripting

Datetime Format 'yyyymmddmiss' Calculation

Given a time format yyyymmddmiss 201007221403 How can I add or minus minutes such that it becomes 201007221348 when I minus away 15 minutes from the time? Also, given a time frame, let say 10 days, how do I check if a date 201006231403 that is not more than 10 days behind... (3 Replies)
Discussion started by: alienated
3 Replies

9. Shell Programming and Scripting

format conversion

Is there any direct way in shell to convert exponential to other formats. For example 1.5e-07 to 0.150u. Or does shell support this microns, nano meter notations? (1 Reply)
Discussion started by: abhijanvt
1 Replies

10. UNIX for Advanced & Expert Users

Flat File Conversion Format

Hi all, I've a flat file in this format: = " Record 1 Field1 -> XXXX Field2 -> 9558 Field3 -> 55AA Record 2 Field1 -> YYYY Field2 -> 12345 Field3 -> aa23 " And i want to convert it to (4 Replies)
Discussion started by: Loobian
4 Replies

Featured Tech Videos