Hi,
I need help to convert the date format in .DAT file in unix.
I want to convert
10@@|SWIFT MT568 Extract@@|Apr 14 2014 5:47:52:563PM@@|Apr 14 2014 4:33:47:663PM@@||##|
into
10@@|SWIFT MT568 Extract@@|04/14/2014/ 5:47:52:563PM@@|04/14/2014 4:33:47:663PM@@||##|
Appreciate your help int hsi regard.
Welcome to forums,
if you have gawk try
$ cat file
10@@|SWIFT MT568 Extract@@|Apr 14 2014 5:47:52:563PM@@|Apr 14 2014 4:33:47:663PM@@||##|
awk --re-interval 'BEGIN{
mon="JanFebMarAprMayJunJulAugSepOctNovDec"
}
{
if(match($0,/[[:alpha:]]{3}[[:space:]][[:digit:]]{2}[[:space:]][[:digit:]]{4}/,m))
{
split(m[0],A)
gsub(m[0],sprintf("%02d/%02d/%04d",(match(mon,A[1])+2)/3,A[2],A[3]) )
}
}1
' file
10@@|SWIFT MT568 Extract@@|04/14/2014 5:47:52:563PM@@|04/14/2014 4:33:47:663PM@@||##|
Hi, Thanks for your code. When I am executing your code I am getting the below error. I do not know why? Coudl you please help...I appreciate your help in this regard. I am not a expert in UNIX shell. 
awk: syntax error near line 5
awk: illegal statement near line 5
awk: bailing out near line 5
May be you are on solaris/sunos , try this
nawk 'BEGIN{
mon="JanFebMarAprMayJunJulAugSepOctNovDec"
}
{
if(match($0,/...[[:space:]]..[[:space:]]..../))
{
m = substr($0,RSTART,RLENGTH); split(m,A)
gsub(m,sprintf("%02d/%02d/%04d",(match(mon,A[1])+2)/3,A[2],A[3]) )
}
}1
' file
---------- Post updated at 03:32 PM ---------- Previous update was at 03:30 PM ----------
Please Note : I assumed that date will be same and time is changing in your file
Thanks for your quick reply. Yes. I am using Solaris 5.10. While I am using your code, the screen stays idle and does not look processing....When I change nawk ino awk then I am gettign below error.
awk: syntax error near line 6
awk: illegal statement near line 6
awk: syntax error near line 9
awk: illegal statement near line 9
awk: syntax error near line 12
awk: bailing out near line 12
--Thanks
Change awk to nawk
As Don says always,
If you want to try this on a Solaris/SunOS system, change awk at the start of this script to /usr/xpg4/bin/awk , /usr/xpg6/bin/awk , or nawk
Thanks for your reply again. I changed as per your instruction. /usr/xpg4/bin/awk. But the screen is idle and nothing seems to be running. Cursor stays in next line and no execution at all.
Please show how did you try with codetags!
This si how I changed. test is my file name.
/usr/xpg4/bin/awk 'BEGIN{
mon="JanFebMarAprMayJunJulAugSepOctNovDec"
}
{
if(match($0,/...[[:space:]]..[[:space:]]..../))
{
m = substr($0,RSTART,RLENGTH); split(m,A)
gsub(m,sprintf("%02d/%02d/%04d",(match(mon,A[1])+2)/3,A[2],A[3]) )
}
}1
'test
Please use Code tags!
There should be space not 'test , it should be ' test
Hi....Thanks....It is working fine now. Thanks a lot. I would like to write the output in another file instead of printing in the screen. Could you please help me for this too...
Just redirect like this
inputfile > outputfile
' test > test_outputfile
--
Please use codetag next time, here is video regarding codetag which will guide you The UNIX and Linux Forums: Using Code Tags - YouTube
Thanks a lot. It works perfectly. But now my data is screwing me....
My data has the below patters....
Mon DD YYYY
Mon DD YYYY
Mon D YYYY
Mon D YYYY
So I am not able to predict the pattern. some of my patterns are Mon DD YYYY and not getting converted. Not sure why....
change
match($0,/...[[:space:]]..[[:space:]]..../)
to
match($0,/...[[:space:]]..?[[:space:]]..../)
I tried this option....but it is not converting any of the dates...I tried all the below combinations.....
if(match($0,/...[[:space:]]..?[[:space:]]..../))
if(match($0,/...[[:space:]]?.[[:space:]]..../))
if(match($0,/...[[:space:]]?..[[:space:]]..../))
Please use codetags!
Post your original input
Here is my sample data file....
B6SX9N8 @@|SD@@|COMPANY NAME@@|HK @@|HKD@@|Dec 16 2011 10:02:50:693AM@@|TESTTST@@|Jan 15 2012 12:00:00:000AM@@|HK0000094810@@|IS@@| @@| @@|0@@|0
.00000000000@@|0@@|0.00000000000@@|EQ@@|B6SX9N8@@|SD|##|B3KCDV8 @@|SD@@|COMPANY NAME@@|GB @@|USD@@|Dec 5 2011 2:02:15:4
20AM@@|TESTTST@@|Dec 5 2011 12:00:00:000AM@@|XS0403958571@@|IS@@|G079K9AB1@@|CU@@|0@@|0.00000000000@@|0@@|0.00000000000@@|DB@@|B3KCDV8@@|SD|##|B79C4S1
@@|SD@@|COMPANY NAME@@|AU @@|AUD@@|Dec 28 2011 9:16:20:926PM@@|TESTTST@@|Dec 14 2011 12:00:00:000AM@@|AU00000BSLR9@@|IS@@| @@| @@|0@@|0.0000000000
0@@|0@@|0.00000000000@@|EQ@@|B79C4S1@@|SD|##|
Okay try this, it checks each field (column) in a line, if match is true then it will substitute new date format.
/usr/xpg4/bin/awk 'BEGIN {
mon="JanFebMarAprMayJunJulAugSepOctNovDec"
}
{
for(i=1;i<=NF;i++)
{
if(match($i,/...[[:space:]]..?[[:space:]]..../))
{
m = substr($i,RSTART,RLENGTH); split(m,A,/ /)
gsub(m,sprintf("%02d/%02d/%04d",(match(mon,A[1])+2)/3,A[2],A[3]),$i )
}
}
}1
' FS='|' file
Hi, Thanks a lot for your help. It solved all my purpose. Thanks for your quick help.