[ILUG] joining columns/removing commas in a CSV file?

[Conor Daly]

On Wed, Nov 15, 2000 at 04:44:48PM +0000 or so it is rumoured hereabouts, 
Conor Daly thought:
> On Wed, Nov 15, 2000 at 11:32:34AM -0000 or so it is rumoured hereabouts, 
> Gary McCloskey thought:
> > big CSV file, need to merge particular columns. 
> > => removing particular commas, e.g. to join the 4th,5th, 6th and 7th columns, remove the 4th, 5th, 6th commas
> > 1,2,3,4,5,6,7,8,9 => 1,2,3,4567,8,9
> > 
> > thought that tr, cut or paste might help : just gave me a headache
> > 
> > perl is obvious answer, but perl skills have left me this morning. 
> > 
> > I've made life easier by having no quotes, and hence no commas inside quotes.
> > 
> > Suggestions please, either a utility or perl snippet. 
> > 
> > If I manage to get both brain hemispheres working and figure it out for myself, I'll post an answer.
> > 
> 
> #!/bin/bash
> 
> LINES=`wc -l <file>`
> 
> for i in `seq 1 $LINES`; do
>    LINE=`read`
>    LINE1=`echo $LINE | tr , <some odd character not in your file>`
>    LINE=`echo $LINE1`
>    for j in `seq 1 <number of commas you want to keep first>`; do
>       LINE1=`echo $LINE | sed -e "s/<odd character>/,/"`
>       LINE=`echo $LINE1`
>    done
>    
>    for j in `seq 1 <number of commas you want to lose>`; do
>       LINE1=`echo $LINE | sed -e "s/<odd character>/ /"`
>       LINE=`echo $LINE1`
>    done
> 
>    for j in `seq 1 <number of commas you want to keep last>`; do
>       LINE1=`echo $LINE | sed -e "s/<odd character>/,/"`
>       LINE=`echo $LINE1`
>    done
> 
> done
> I know it's probably 'orrible but it's more visible.  Could tidy up a lot
> here also.

What am I saying? You want tr and cut? You shall have tr and cut...

#!/bin/bash
read LINE
while [ !EOF ]; do #Except I'm not sure how to trap the end of file
        LINE1=`echo $LINE | cut -f1-3 -d,`
        LINE2=`echo $LINE | cut -f4-7 -d, | tr , '\40'`
        LINE3=`echo $LINE | cut -f8-99 -d,`
        echo $LINE1,$LINE2,$LINE3
	read LINE
done

-- 
Conor Daly <conor.daly at oceanfree.net>

Domestic Sysadmin :-)