awk or sed to add field in a text file

Hi there, I have a csv file with some columns comma sepated like this :

4502-17,PETER,ITA2,LEGUE,92,ME - HALF,23/05/10 15:00
4502-18,CARL,ITA2,LEGUE,96,ME - HALF,20/01/09 14:00
4502-19,OTTO,ITA2,LEGUE,97,ME - MARY,23/05/10 15:00

As you can see the column n. 7 is a timestamp column, I need to estract just the year from column n. 7 adding a new column ( number 8 ) to the csv file so that the final result must the the following :

4502-17,PETER,ITA2,LEGUE,92,ME - HALF,23/05/10 15:00,2010
4502-18,CARL,ITA2,LEGUE,96,ME - HALF,20/01/09 14:00,2009
4502-19,OTTO,ITA2,LEGUE,97,ME - MARY,23/05/10 15:00,2010

I think the right solution is awk but my knowledge of awk if vary poor.
Thank you in adeance and happy new year to everybody.

Try:

sed 's|/\(..\)\( ..:..\)|/\1\2,20\1|' infile

or:

awk -F, '{split($7,d,/[\/ ]/); $8="20"d[3]}1' OFS=',' infile

Or:

awk -F"[ /]" '{print $0 ",20" $(NF-1)}' file

nawk -F"[, ]" '{split($9,arr,"[/]");printf("%s,20%s\n",$0,arr[3])}'

 sed 's/\([0-9][0-9]\) \(.*\)/\1 \2,20\1/' urfile

Thank you guys for your support all solutions are working perfectly.
In case I need a further modification of my original file splitting the last column from :

4502-1,4502,PETER,ITA1,LEGUE,92,ME HALF,02/06/10 15:00

Into

4502-1,4502,PETER,ITA1,LEGUE,92,ME HALF,02/06/10,15:00

How I can do it with awk, sed or tr ?
Last colums is a mysql timestamp filed
Thanks in advance for your kind reply.

Try this:

awk -F, '{sub(" ",",",$NF)}1' OFS="," file

sed 's/ \([^ ]\+\) *$/,\1/' file

Scrutinizer,

I don't get the expected output with your command.
Another on with sed:

sed 's/\(.*\) \(.*\)/\1,\2/' file

Why not

sed 's/ /,/' file

or

awk '$1=$1' OFS="," file

Because there could be other spaces in the line and only the last space should be replace with a comma.

Regards

Hello Franklin,

I tried the code you provided ( #7th post) but it didnt work well in my solaris10 server even it looked simple,efficient. So i tried the same thing several times with nawk and at last could manage to do it but i dont know if it is the best way, i appreciate if you advise when you have time.

my code:

nawk -F"[,]" '{split($7,arr,"[ ]"); $7=arr[1]; printf("%s,%s\n",$0,arr[2])}' file

Strange, what output do you get?

I get:

$ sed 's/ \([^ ]\+\) *$/,\1/' file
4502-17,PETER,ITA2,LEGUE,92,ME - HALF,23/05/10,15:00
4502-18,CARL,ITA2,LEGUE,96,ME - HALF,20/01/09,14:00
4502-19,OTTO,ITA2,LEGUE,97,ME - MARY,23/05/10,15:00

---------- Post updated at 16:10 ---------- Previous update was at 16:04 ----------

Hi Franklin52, your statement makes use of sed's greediness to get to the last space. Therefore I think it is equivalent to:

sed 's/\(.*\) /\1,/' file

However it will fail if there are any spaces behind the last field.

Scrutinizer it didnt make any change unfortunately when i tried :

sh{root}>sed 's/ \([^ ]\+\) *$/,\1/' field_chg.txt 
4502-17,PETER,ITA2,LEGUE,92,ME - HALF,23/05/10 15:00
4502-18,CARL,ITA2,LEGUE,96,ME - HALF,20/01/09 14:00
4502-19,OTTO,ITA2,LEGUE,97,ME - MARY,23/05/10 15:00

Does this fail too?

sed 's|/\(..\) \(..:..\)|/\1,\2|' field_chg.txt

EAGL�,

On Solaris you should use nawk or /usr/xpg4/bin/awk (POSIX compliant awk).

sh{root}>sed 's|/\(..\) \(..:..\)|/\1,\2|' field_chg.txt 
4502-17,PETER,ITA2,LEGUE,92,ME - HALF,23/05/10,15:00
4502-18,CARL,ITA2,LEGUE,96,ME - HALF,20/01/09,14:00
4502-19,OTTO,ITA2,LEGUE,97,ME - MARY,23/05/10,15:00

Besides Thanks Franklin for your help,

Scrutinizer,

This is what I get, could be an old version of sed on a HP-UX:

echo '4502-1,4502,PETER,ITA1,LEGUE,92,ME HALF,02/06/10 15:00'|sed 's/ \([^ ]\+\) *$/,\1/'
4502-1,4502,PETER,ITA1,LEGUE,92,ME HALF,02/06/10 15:00

We provide solutions base on data sample and required output

Thanks for the feedback. I'll test in on HP-UX tomorrow..

---------- Post updated at 18:22 ---------- Previous update was at 18:17 ----------

Little bit shorter:

sed 's| \(..:\)|,\1|' file

(But fragile if there is a semicolon somewhere else.. I think awk is the more solid solution here...)