Add additional numbers to file

Shell Programming and Scripting
1

I need to change the following field from:

"7/3/2009 7:07:12 PM","12345676","ok","8674"
"6/3/2009 8:07:12 PM","12345676","ok","8674"
"5/1/2009 7:07:12 PM","12345676","ok","8674"
"4/9/2009 3:07:12 AM","12345676","ok","8674"
"3/8/2009 3:07:12 PM","12345676","ok","8674"
"2/7/2009 4:07:12 PM","12345676","ok","8674"
"1/6/2009 5:07:12 PM","12345676","ok","8674"
"8/5/2009 6:07:12 PM","12345676","ok","8674"
"9/4/2009 9:07:12 PM","12345676","ok","8674"
"10/2/2009 10:07:12 PM","12345676","ok","8674"

to

"07/03/2009 19:07:12","12345676","ok","8674"
"06/03/2009 20:07:12","12345676","ok","8674"
"05/01/2009 19:07:12","12345676","ok","8674"
"04/09/2009 3:07:12","12345676","ok","8674"
"03/08/2009 15:07:12","12345676","ok","8674"
"02/07/2009 16:07:12","12345676","ok","8674"
"01/06/2009 17:07:12","12345676","ok","8674"
"08/05/2009 18:07:12","12345676","ok","8674"
"09/04/2009 21:07:12","12345676","ok","8674"
"10/02/2009 22:07:12 ","12345676","ok","8674"

I was trying sed which does not work, but I have about 1000 records, others with this type of format so it would probably need to be done in a loop?

I cannot get to add the 0 at the front of the date and cannot convert the 24hour process

I am using Korn Shell (solaris)

2

This Perl code will solve your requirement ,

use strict;
use warnings;


#my $a ="\"7/3/2009 7:07:12 PM\",\"12345676\",\"ok\",\"8674\"";
open FH , "log.txt" ;  # Log file name 
my $a ;
while($a= <FH>)
{
        $a =~ /\s([0-9]{1,2}):([0-9]{1,2}):([0-9]{1,2})\s+(AM|PM)/;
        my $format = $4 ;
                if ( $format eq "PM" )
                {
                        my $hr=12+$1;
                        if ( $hr ==24 )
                        {
                            $hr ="00";
                        }
my $fin= " $hr:$2:$3" ;
        $a =~ s/\s([0-9]{1,2}):([0-9]{1,2}):([0-9]{1,2})\s+(AM|PM)/$fin/;
                }
                else {
                        $a=~ s/AM//;
                }
        print $a ;
    }
3

apologies I don't use perl, so i don't understand the code

4

You try the following code.

sed 's/,/ /g;s/"//g;' file > tmp
while read line
do
date=`echo $line | cut -d ' ' -f 1`

day=`echo $date | cut -d '/' -f 1`

mon=`echo $date | cut -d '/' -f 2`

year=`echo $date | cut -d '/' -f 3 `

time=`echo $line | cut -d ' ' -f 2`

format=`echo $line | cut -d ' ' -f 3`

data=`echo $line | cut -d ' ' -f 4- --output-delimiter='","'`

hour=`echo $time | cut -d ':' -f 1 `

Minsec=`echo $time | cut -d ':' -f 2-`

if [[ $format == 'PM' ]]
then
let hour+=12
fi
hour=`printf "%02d" $hour`
mon=`printf "%02d" $mon`
day=`printf "%02d" $day`
echo \"$day/$mon/$year $hour:$Minsec\",\"$data\"
done < tmp
rm -rf tmp
5

Try this: easy to understand. "file" is your input file.

#!/bin/sh

while read line
do
newstr=`echo $line | awk '{print $1}' | sed 's/"//g'`
day=`echo $newstr | cut -d "/" -f2`
month=`echo $newstr | cut -d "/" -f1`
year=`echo $newstr | cut -d "/" -f3`

timestr=`echo $line | awk '{print $2}'`
time_ind=`echo $line | awk '{print $3}' | cut -c 1-2`
hour=`echo $timestr | cut -d ":" -f1 `
min=`echo $timestr | cut -d ":" -f2 `
sec=`echo $timestr | cut -d ":" -f3 `

if [ "${time_ind}" == "PM" ]; then
   new_hour=$(( $hour + 12 ))
else
  new_hour=$hour
fi

if [ $day -lt 10 ];then
  new_day=`echo "0$day"`
else
  new_day=$day
fi

if [ $month -lt 10 ]; then
  new_month=`echo "0$month"`
else
  new_month=$month
fi

part_str=`echo $line | awk '{print substr($0,index($0,"M\"")+ 2)}'`
echo "\"$new_month/$new_day/$year $new_hour:$min:$sec\"$part_str"

done < file

cheers,
Devaraj Takhellambam

6

hi thanks for all the replies

Thanks to Devaraj, your code worked great

7

You can easily do this with the help of sed and date commands.

Try the following,

while read line
do
line=`sed -r "s/^\"([0-9]{1}\/)(.+)/\"0\1\2\n/g" <<<$line`
time=`sed -r "s/^\".+\s([^\"]+\s[^\"]+)\".+/\1/g" <<<$line`
hour=`date -d "$time" | cut -d ' ' -f 5`
result=`sed -r "s/^(\".+\s)([^\"]+\s[^\"]+)(\".+)/\1$hour\3/g" <<<$line`
echo $result
done <"inputfile"
8

with Awk...

awk -F" |," 'BEGIN{OFS=",";t="\""}{gsub("\"","",$0)
split($1,a,"/")
if($3=="PM"){split($2,b,":")
print t (length(a[1])==1?"0"a[1]:a[1])"/"(length(a[2])==1?"0"a[2]:a[2])"/"a[3]" "b[1]+12":"b[2]":"b[3] t OFS t $4 t OFS t $5 t}
else{print t (length(a[1])==1?"0"a[1]:a[1])"/"(length(a[2])==1?"0"a[2]:a[2])"/"a[3]" "$2 t OFS t $4 t OFS t $5 t}}' infile
9 
awk -F[\"/\ :] '{if ($8~/PM/) $5=$5+12}; 
                     {printf "\"%02d\/%02d\/%d %02d:%02d:%02d\",\"%s\",\"%s\",\"%s\"\n",$2,$3,$4,$5,$6,$7,$10,$12,$14}'
10 
cat input7.txt | sed 's@\(["/]\)\([0-9]\)/@\10\2/@
s@\(["/]\)\([0-9]\)/@\10\2/@
s@\( \)\([0-9]\):@\10\2:@'

This could probably be simplified with a look ahead expression for the '/' character in dates.

11

...just because i love my good friend awk, here's that as a one liner

cat testfile | awk '{ split($2, b, ":"); if (b[2]+12 == 24) b[2]=00; split($1, d, "\/"); split(d[1], e, "\""); printf("\"%02d\/%02d\/%d %02d:%02d:%d %s\n", e[2], d[2], d[3], b[1]+12, b[2], b[3],$3); }'

Or in easy to read form:

cat testfile | awk '{ 
split($2, b, ":"); 
if (b[2]+12 == 24) b[2]=00; 
split($1, d, "\/"); 
split(d[1], e, "\""); 
printf("\"%02d\/%02d\/%d %02d:%02d:%d %s\n", 
e[2], d[2], d[3], b[1]+12, b[2], b[3],$3); }'

Turns this:

"7/3/2009 7:07:12 PM","12345676","ok","8674"

Into this:

"07/03/2009 19:07:12 PM","12345676","ok","8674"

You could even run it against tail -f with with tee to correct it live:

tail -f testfile | tee -a newfile | awk '{ split($2, b, ":"); if (b[2]+12 == 24) b[2]=00; split($1, d, "\/"); split(d[1], e, "\""); printf("\"%02d\/%02d\/%d %02d:%02d:%d %s\n", e[2], d[2], d[3], b[1]+12, b[2], b[3],$3); }'

I dunno, it was already solved but i feel like awk is still a cool tool