Replace the | with Comma

Shell Programming and Scripting
1

Hi,

The input file structure is given below: The Col1 and Col2 will be there always. But from Col3 there can be more columns.
And Col3 will be always Col4 and Col5 will always be with Col6. I need to replace the | with comma. There are scnearios where there
wont be no data.Below, the row 2 does not have data for col5,col6,col6,col8. I am using the command

cat Testin1|sed -e 's/||/,,,/g' -e 's/|/,/g'

How i use this command to achieve my requirement Or what is the best way to handle this?

Input:

Col1,Col2|Col3,Col4|Col5,Col6|Col7,Col8
Market1,"Chain1"|3,3|3,3|3,3|3,3
Market1,"Chain1"|3,3|||
Market1,"Chain1"||3,3||
Market1,"Chain1"|||3,3|
Market1,"Chain1"||||3,3

Expected Output:

Market1,"Chain1",3,3,3,3,3,3,3,3
Market1,"Chain1",3,3,,,,,,,
Market1,"Chain1",,,3,3,,,,,
Market1,"Chain1",,,,,3,3,,,
Market1,"Chain1",,,,,,,3,3

Current Output:

Market1,"Chain1",3,3,3,3,3,3,3,3
Market1,"Chain1",3,3,,,,
Market1,"Chain1",,,3,3,,,
Market1,"Chain1",,,,3,3,
Market1,"Chain1",,,,,,3,3

Thanks,
bharathappriyan

2 
awk -F"|" -v OFS="," ' { if($2==""){$2=","};if($3==""){$3=","};if($4==""){$4=","};if($5==""){$5=","};$1=$1""} 1 ' file
3

Could you please explain how it works?

Thanks
bharathappriyan

4

-F"|" Input field delimiter is pipe

OFS="," Output field delimiter is comma

if($2==""){$2=","} Using pipe as delimiter, if second field is empty then assign comma to it

$1=$1"" This helps to set output field separator to comma if none of fields are blank

1 Means true. So the entire line is printed

5

Hi,

There can be more columns in the input file. It seems you have written the code for the current data only.

6 
awk -F"|" -v OFS="," ' { for(i=2;i<=NF;++i){if($i==""){$i=","}};$1=$1""} 1 ' file

Or

sed "s/||/,,,/;s/||/,,,/;s/|/,/g;s/,$/,,/" file
7 
[ctsgnb@shell ~]$ cat input
Col1,Col2|Col3,Col4|Col5,Col6|Col7,Col8
Market1,"Chain1"|3,3|3,3|3,3|3,3
Market1,"Chain1"|3,3|||
Market1,"Chain1"||3,3||
Market1,"Chain1"|||3,3|
Market1,"Chain1"||||3,3
[ctsgnb@shell ~]$ sed "s/||/,,,/;s/||/,,,/;s/|/,/g;s/,$/,,/" input   <------- i tried the anbu23 proposal
Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8
Market1,"Chain1",3,3,3,3,3,3,3,3
Market1,"Chain1",3,3,,,,,
Market1,"Chain1",,,3,3,,,,
Market1,"Chain1",,,,3,3,,
Market1,"Chain1",,,,,,3,3
[ctsgnb@shell ~]$ sed 's/||/|,|/g;s/|$/,,/;s/|/,/g' input  <---- you can get exactly the same output with 1 less substitution
Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8
Market1,"Chain1",3,3,3,3,3,3,3,3
Market1,"Chain1",3,3,,,,,
Market1,"Chain1",,,3,3,,,,
Market1,"Chain1",,,,3,3,,
Market1,"Chain1",,,,,,3,3

Since i am not sure it fits your requirement here are some others (i would go for the blue one), depending on the numbre of comas ... funny to see that sed cannot handle a the 2 substitution of || even if specified with the global flag g in ||| because of the odd number of pipe it requires another substitution see :

[ctsgnb@shell ~]$ sed 's/||/|,|/g;s/||/|,|/g;s/|$/,,/;s/|/,/g' input
Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8
Market1,"Chain1",3,3,3,3,3,3,3,3
Market1,"Chain1",3,3,,,,,,
Market1,"Chain1",,,3,3,,,,
Market1,"Chain1",,,,,3,3,,
Market1,"Chain1",,,,,,,3,3
[ctsgnb@shell ~]$ sed 's/|$/,,,/;s/||/,,,/g;s/|/,/g' input
Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8
Market1,"Chain1",3,3,3,3,3,3,3,3
Market1,"Chain1",3,3,,,,,,
Market1,"Chain1",,,3,3,,,,
Market1,"Chain1",,,,3,3,,,
Market1,"Chain1",,,,,,3,3
[ctsgnb@shell ~]$

hehe there is an issue with x|||x (assuming x is a column that has a value not empty)

What to you expect : should ||| be replaced by 6 comas or 4 comas ? or 5 comas ? x,,,,,,x or x,,,,,x ???