replacing comma with newline

[sumittyagi]

Hi all,
In ksh script, I have a comma separated list. I want to replace every third comma with newline. Please assist me in this.

Thanks a lot!

[ashitpro]

Hi all,
In ksh script, I have a comma separated list. I want to replace every third comma with newline. Please assist me in this.

Thanks a lot!

I'm assuming that comma separated list is in another file. I've taken that file name as test.txt.
file should look like
a,b,c,d,e,f,g,h,i,j

result file will be res.txt and result should look like
a,b,c
d,e,f
g,h,i
j

prev=""
for i in `cat test.txt`
do
    counter=0
    line_length=`echo $i | wc -L`
    for (( j=1; j <= $line_length; j++ ))
    do
        char=`echo $i | cut -c $j`
        if [ $char = ',' ]
        then
            counter=`expr $counter + 1`
            val=`expr $counter % 3`
            if [ $val -eq 0 ]
            then
                echo -e $prev >> res.txt
                prev=""
            else
                prev=$prev$char
            fi
        else
            prev=$prev$char
        fi
    done
    echo -e $prev >> res.txt
done
 
 

I've tested this script using "bash"
no copy rights, you can modify code as per your choice so that it can work with "ksh"

[radoulov]

awk 'ORS=NR%3?RS:"\n"' RS="," filename

Use nawk on Solaris.

[sumittyagi]

awk 'ORS=NR%3?RS:"\n"' RS="," filename

Use nawk on Solaris.

Thanks a lot for your replies!
It worked!!!

[sumittyagi]

Thanks a lot for your replies!
It worked!!!

I actually wanted to get a csv file for a result from a sql query.

The file is getting created, but I suppose unix don't treat \n as newline. It is replacing \n by a space in the file.

I also tried \r\n like this:

nawk 'ORS=NR%3?RS:"\r\n"' RS="," filename

In this case, newline is done but the file gets truncated abruptly in between when I use cat, pg or vi to see the content. But when I transferred the file to windows, the file looks well in excel, and shows all records.

Is there any way I can see the content at unix side as well.

Thanks a lot!!!

[docdiesel]

Hi,

try to use 'more' or 'less' (depends on what's installed on your system; am not familiar with Solaris), or use the 'tail' command to check at least the last lines to see if everything is there.

As far as I know all Unix systems are using \n as newline character, while just the software originated in Redmond relies on \r\n. Is there a 'hexdump' command on your *nix? Try to check if you see the 0x0A hex values of the \n in the right places.

Regards,

Bernd


P.S.: With DB2 I'd try someting like

echo "
Select col1,',',col2,'
',col3,',',col4
from my.table " | db2 -x

to avoid having to transform it with shell methods. Which db are you using?

[docdiesel]

Hi,

just did some tests and with DB2 and my swiss army knife (bash shell) the following works for breaking up one row into several lines:

$ db2 -x "select 'echo -e ', current_date,'\\\\n',current_time  from sysibm.sysdummy1 " | bash
2008-03-07
 14.17.58
 

This could work with other db systems, too. As far as you own a swiss army knife, or know somebody like MacGyver.

Regards,

Bernd

[sumittyagi]

Hi,

just did some tests and with DB2 and my swiss army knife (bash shell) the following works for breaking up one row into several lines:

$ db2 -x "select 'echo -e ', current_date,'\\\\n',current_time  from sysibm.sysdummy1 " | bash
2008-03-07
 14.17.58
 

This could work with other db systems, too. As far as you own a swiss army knife, or know somebody like MacGyver.

Regards,

Bernd

Thanks a lot Bernd,
I will try it out!!!

Regards,
Sumit Tyagi