Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Replace the | with Comma

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Replace the | with Comma
# 1  
Old 11-10-2010
Replace the | with Comma
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
Code:
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:
Code:
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:
Code:
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:
Code:
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
Last edited by Scott; 11-10-2010 at 06:18 PM.. Reason: Code tags, please...
# 2  
Old 11-10-2010
Code:
awk -F"|" -v OFS="," ' { if($2==""){$2=","};if($3==""){$3=","};if($4==""){$4=","};if($5==""){$5=","};$1=$1""} 1 ' file

# 3  
Old 11-10-2010
Could you please explain how it works?

Thanks
bharathappriyan
# 4  
Old 11-10-2010
-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  
Old 11-10-2010
Hi,

There can be more columns in the input file. It seems you have written the code for the current data only.
# 6  
Old 11-10-2010
Code:
awk -F"|" -v OFS="," ' { for(i=2;i<=NF;++i){if($i==""){$i=","}};$1=$1""} 1 ' file

Or

Code:
sed "s/||/,,,/;s/||/,,,/;s/|/,/g;s/,$/,,/" file

# 7  
Old 11-10-2010

Code:
[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 :
Code:
[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 ???
Last edited by ctsgnb; 11-10-2010 at 10:51 PM..
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Replace spaces with underscores up to first comma but not after the comma

I have a comma delimited file of major codes and descriptions. I want to replace all occurrences of spaces with underscores up to the first comma (only in the first field), but not replace spaces following the comma. For instance I have the following snippet of the file: EK ED,Elementary and... (7 Replies)
Discussion started by: tdouty
7 Replies

2. Shell Programming and Scripting

Replace comma and blank with comma and number

I, I have a file and i need to replace comma and blank space with comma and 0. cat file.txt a,5 b,1 c, d, e,4 I need the output as cat file.txt a,5 b,1 c,0 d,0 (4 Replies)
Discussion started by: jaituteja
4 Replies

3. Shell Programming and Scripting

Command to replace newline with comma

Hi Experts, I need an urgent help in replacing newline characters in my single column file by a comma. I am using following command for this purpose: sed -n -e 'H;${x;s/\n/,/g;s/^,//;p;}' parameter.rtf | rev | cut -c 4- | revMy input file has: TABLE_1 TABLE_2 TABLE_3 What I need:... (11 Replies)
Discussion started by: neerndg123
11 Replies

4. UNIX for Dummies Questions & Answers

How to replace two or more spaces with one comma?

I'm using sh on hp-ux. I've got a file that looks like this. -5.65 175 -16.17 160 -13.57 270 -51.72 260 -8.30 360 -42.71 460 -.38 375 -.20 375 -4.15 170 -21.53 560 -18.84 360 I'd like to replace all the whitespace between the columns with one comma. I can't... (4 Replies)
Discussion started by: Scottie1954
4 Replies

5. Shell Programming and Scripting

Replace pipe <|> with comma <,> in a column

Hi All Gurus, I need to replace a pipe <|> with a comma <,> in a few columns with pipe delimited file. The column name are fixed for the replacement of comma <,>. For below example, Col3, Col6 and Col8 are columns need to replace with comma <,> if any pipe encountered. example:... (14 Replies)
Discussion started by: agathaeleanor
14 Replies

6. Shell Programming and Scripting

Replace newline with comma.

I have output from a file like this: 15,01,11,14:06 235 I would like to change this to: 15,01,11,14:06,235 Removing newline and change to "," I now this can be done with tr cat OUT | tr '\n' ','' My problem is that tr is not implemented in this shell. sed is, show it should be... (7 Replies)
Discussion started by: Jotne
7 Replies

7. Shell Programming and Scripting

replace space with comma in perl

arr_Ent_NameId variable holds 'Prakash pawar' 'sag' '23' '50000' this value 'Prakash pawar' 'sag' '23' '50000' I want to replace space( ) with comma (,) There are 4 fields here. I don't want to replace first field with comma. output should be: 'Prakash,pawar','sag','23','50000' ... (2 Replies)
Discussion started by: pritish.sas
2 Replies

8. Shell Programming and Scripting

Replace comma with newline

Hi, for some reason I cant seem to figure this out. I have a file which looks something like this word word word word word,word,word word word word,word,word,word,word word word Basically I want this whole thing to be a list with 1 word on each line like this... word word word... (1 Reply)
Discussion started by: eltinator
1 Replies

9. Shell Programming and Scripting

replace comma(,) with Tab

hi all, i have a file with commas(,). i want to replace all the commas with tab(\t). Plz help...its urgent... (3 Replies)
Discussion started by: vikas_kesarwani
3 Replies

10. Shell Programming and Scripting

Replace , (comma) with space

Hi, what is the better way to replace the , (comma) with a space char? Example:STRING=dir1,dir2,dir3 toSTRING=dir1 dir2 dir3 And.. how to find if in the string there is a comma? Thanks :) (6 Replies)
Discussion started by: mbarberis
6 Replies
Login or Register to Ask a Question