Hi All,
I'm new to forum good to hear all.
I stuck in converting date format in csv file using unix
csv file contains as below
,750,0000000000000000GCJR, ,06/22/2016 14:48:44
I want to convert into as below
,750,0000000000000000GCJR, ,06/22/2016 02:48:44 PM
Please reply asap.. thanks in advance
date -d "06/22/2016 14:48:44" '+%m/%d/%Y %r'
Thanks Pravin for response.
I'm getting -d not found error
could you please post to convert file at a time command
---------- Post updated at 07:43 AM ---------- Previous update was at 07:42 AM ----------
this is the error " ksh: -d: not found"
echo ",750,0000000000000000GCJR, ,06/22/2016 11:48:44" | awk -F"," '{
hr=substr($NF,12,2);
if (hr > 11) { b="PM"
hr-12 == 0 ? nhr=hr : nhr=hr-12
} else { b="AM";nhr=hr}
printf "%s%02d%s %s\n",substr($NF,1,11),nhr,substr($NF,14),b}'
I guess your date version is not GNU. Could you please try above code
a different take:
echo ',750,0000000000000000GCJR, ,06/22/2016 14:48:44' | \
awk '{
split($NF,a,":"); pm=int(a[1]/12)
if (pm) a[1]=sprintf("%02d",a[1]%12)
$NF=a[1]":"a[2]":"a[3] OFS ((pm)?"PM":"AM")
}1'
Thanks for both its working fine but when I used this in my shell script its adding at beging like AM/22/2016 14:48:44
strange - I get the desired output with the sample line you posted.
Could you post a sample not-working line again, please.
And also what OS are you on?
yes If I used direct in unix its working fine
if I used same stmts in shell script its giving AM/22/2016 14:48:44
well... maybe the problem is in the script?
i'm using same in shell script I placed $variable in place of 06/22/2016 14:48:44
---------- Post updated at 01:09 AM ---------- Previous update was at 12:00 AM ----------
echo ',750,0000000000000000GCJR, ,$date_value' | \
awk '{
split($NF,a,":"); pm=int(a[1]/12)
if (pm) a[1]=sprintf("%02d",a[1]%12)
$NF=a[1]":"a[2]":"a[3] OFS ((pm)?"PM":"AM")
}1'
im using above stmt but its not working any idea please
---------- Post updated at 01:53 AM ---------- Previous update was at 01:09 AM ----------
in shell script im assigning value to ldr_dt vaiable then passing as below
echo $lrd_dt|\
awk '{
split($NF,a,":"); pm=int(a[1]/12)
if (pm) a[1]=sprintf("%02d",a[1]%12)
$NF=a[1]":"a[2]":"a[3] OFS ((pm)?"PM":"AM")
}1'
Out put : PM22/2016 02:48:44
but I need output like this 06/22/2016 02:48:44 PM
Might be a DOS (<CR>, \r, 0x0D) line terminator? Please post a hexdump of the result.
What exactly is the contents of the lrd_dt variable?
lrd_dt contains 06/22/2016 14:48:44
echo "$lrd_dt" |\
echo $lrd_dt
06/22/2016 02:48:44
echo $lrd_dt | od -c
0000000 0 6 / 2 2 / 2 0 1 6 0 2 : 4 8
0000020 : 4 4 \r \n
0000025
$ echo $lrd_dt| awk '{
split($NF,a,":"); pm=int(a[1]/12)
if (pm) a[1]=sprintf("%02d",a[1]%12)
$NF=a[1]":"a[2]":"a[3] OFS ((pm)?"PM":"AM")
}1'
AM22/2016 02:48:44
echo $lrd_dt| awk '{
sub(/\r/, "")
split($NF,a,":"); pm=int(a[1]/12)
if (pm) a[1]=sprintf("%02d",a[1]%12)
$NF=a[1]":"a[2]":"a[3] OFS ((pm)?"PM":"AM")
}1'
06/22/2016 02:48:44 AM
or
echo $$lrd_dt| awk '{
split($NF,a,":"); pm=int(a[1]/12)
if (pm) a[1]=sprintf("%02d",a[1]%12)
$NF=a[1]":"a[2]":"sub(/\r/, "", a[3]) OFS ((pm)?"PM":"AM")
}1'
06/22/2016 02:48:1 AM
Awesome its working fine Aia thanks a lot.
Could you please post same how can I replace derived value in csv file
Thanks again for your time 
I went back and read your first post and the example you posted could be a problem if it is not an exact representation of your csv file.
,750,0000000000000000GCJR, ,06/22/2016 14:48:44
Could you confirm that lines in your file contains ONLY lines EXACTLY like this?
If this is not the case, could you post a complete portion of your file, showing real complete lines.
Awesome its working thank a lot Aia.
Could please post how to replace derive output in csv file in shell script
Thanks for your time
perl -pe 's/\r//; s|\d+/\d+/\d{4}\s(\d{2}):\d{2}:\d{2}|$1>12?"$& PM":"$& AM"|e' raghureds.csv
Hi Aia,
It's working but its giving output like 06/22/2016 14:48:44 PM , in need output like 06/22/2016 02:48:44 PM
Thanks in Advance
Hi Raghureds,
I'm not nearly as fluent in perl as Aia, but the following awk code seems to do what you want (and it won't report that times between 12:00:00 and 12:59:59, inclusive, are AM instead of PM).
awk '
BEGIN { FS = OFS = ","
}
{ sub(/\r/, "")
split($NF, d, /[ :]/)
ampm = (d[2] > 11) ? "PM" : "AM"
if(d[2] > 12)
d[2] -= 12
if(d[2] == "00")
d[2] = 12
$NF = sprintf("%s %02d:%s:%s %s", d[1], d[2], d[3], d[4], ampm)
}
1' file.csv
If you want to try this on a Solaris/SunOS system, change awk to /usr/xpg4/bin/awk or nawk .
If file.csv contains:
,750,0000000000000000GCJR, ,06/22/2016 14:48:44
,750,0000000000000000GCJR, ,06/22/2016 12:48:44
,750,0000000000000000GCJR, ,06/22/2016 02:48:44
,750,0000000000000000GCJR, ,06/22/2016 00:48:44
it produces the output:
,750,0000000000000000GCJR, ,06/22/2016 02:48:44 PM
,750,0000000000000000GCJR, ,06/22/2016 12:48:44 PM
,750,0000000000000000GCJR, ,06/22/2016 02:48:44 AM
,750,0000000000000000GCJR, ,06/22/2016 12:48:44 AM
which, I think, is what you wanted.
Hope this helps...