field format

[viseshu]

Hi,
I have a file in the format

1,type1,20090510,20090511,1000,1100
2,type2,20090611,20090714,1200,1300
3,type3,20090414,20090515,1600,1700

I want the file to be present in this format:
type1,2009-05-10 10:00,2009-05-11 11:00
type2,2009-06-11 12:00,2009-07-14 13:00
type3,2009-04-14 16:00,2009-05-15 17:00

i.e the first field to be removed and the date and time stamps to be merged.Please help

[Richard Hepworth]

Hi,

awk -F, '{print $2","$3,$5","$4,$6}' /file

should do the trick.

Richard

[Mel Burslan]

cat myfile | while read line;do
typ=`echo $line | cut -d"," -f2`
date1=`echo $line | cut -d"," -f3`
date2=`echo $line | cut -d"," -f4`
time1=`echo $line | cut -d"," -f5`
time2=`echo $line | cut -d"," -f6`

y1=`echo $date1 | cut -c 1-4`
m1=`echo $date1 | cut -c 5-6`
d1=`echo $date1 | cut -c 7-8`
h1=`echo $time1 | cut -c 1-2`
mm1=`echo $time1 | cut -c 3-4`

y2=`echo $date2 | cut -c 1-4`
m2=`echo $date2 | cut -c 5-6`
d2=`echo $date2 | cut -c 7-8`
h2=`echo $time2 | cut -c 1-2`
mm2=`echo $time2 | cut -c 3-4`

echo "${typ},${y1}-${m1}-${d1} ${h1}:${mm1},${y2}-${m2}-${d2} ${h2}:${mm2}"
done


Hope this helps.. it is not most elegant but easy to follow...

[viseshu]

Hi Rich,
Thanks for the hint.
Hi Mel,
I have around 1000 records in the file. So opening up a new variable for each record is impossible.

Please suggest something quick as i have 1000 records in the file.

[Mel Burslan]

you do not have to open up 1000s of valiables for each line. The while loop will go line-by-line to give you the output you need. all the variables defined are for one line. Regardless how many lines you have, you can run this script to print them out in the format specified.

[James R. Ferguson]

Hi:

# awk -F"," 'BEGIN{OFS=","};{print $2,substr($3,1,4)"-"substr($3,5,2)"-"substr($3,7,2),substr($5,1,2)":"substr($5,3,2),substr($4,1,4)"-"substr($4,5,2)"-"substr($3,7,2),substr($6,1,2)":"substr($6,3,2)}' file

Regards!

...JRF...

[James R. Ferguson · Accepted solution]

Hi (again):

Oops, you want commas only at selected points, so:

# awk -F, {print $2","substr($3,1,4)"-"substr($3,5,2)"-"substr($3,7,2),substr($5,1,2)":"substr($5,3,2)","substr($4,1,4)"-"substr($4,5,2)"-"substr($3,7,2),substr($6,1,2)":"substr($6,3,2)} file

Regards!

...JRF...

[viseshu]

Hi James,
that was splendid!
i have few more fields after the time stamp like,

1,type1,20090510,20090511,1000,1100,D,34.0,,,MI
2,type2,20090611,20090714,1200,1300,,D,78.0,,,KI
3,type3,20090414,20090515,1600,1700,,K,90.8,,LI
How do i accomodate if the fields are more.

Thanks in advance.

[James R. Ferguson]

Hi (again):

> I have few more fields after the time stamp ...How do i accomodate if the fields are more.

You simply reference fields in'awk' as $0 (for the whole line) and $1..$199 for the first through the 199th field. Awk automatically splits a line into fields based on the record seperator. For commandline scripts you can use the '-F' switch like I did to designate it. The manpages offer an overview of functions like 'substr()' which we used to dissect fixed spans of various fields.

Regards!

...JRF...

[Peter Nikitka]

Hi,

to get the rest of the fields unchanged in your output, use
# awk -F, '{printf("%s,%s-%s-%s %s:%s, %s-%s-%s %s:%s",
$2,substr($3,1,4),substr($3,5,2),substr($3,7,2),
substr($5,1,2),substr($5,3,2),
substr($4,1,4),substr($4,5,2),substr($3,7,2),
substr($6,1,2),substr($6,3,2));
for(i=7;i<=NF;i++) printf(",%s",$i); printf("\n")}' file

mfG Peter