Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

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

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Replace pipe <|> with comma <,> in a column
# 1  
Old 08-11-2011
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:
Code:
Col1|Col2|Col3|Col4|col5|col6|col7|col8
1|2|3|3.1|3.2|4|5|6|6.1|6.2|7|8|8.1

output:
Code:
Col1|Col2|Col3|Col4|col5|col6|col7|col8
1|2|3,3.1,3.2|4|5|6,6.1,6.2|7|8,8.1

Thanks for your help.
# 2  
Old 08-11-2011
Not pretty but works,

Code:
$ echo "1|2|3|3.1|3.2|4|5|6|6.1|6.2|7|8|8.1" | awk 'BEGIN {FS=OFS="|"}{$3=$3",";$8=$8",";$12=$12","}1' | sed 's/,\|/,/g'
1|2|3,3.1|3.2|4|5|6,6.1|6.2|7|8,8.1

Please check your column positions. its 3,8 and 12th as per the output!
# 3  
Old 08-11-2011
Thanks anchal_khare for your prompt reply.

In the example input, col3 is called multivalue column.
According to the requirement, user has not specified the fix number of value it might contain in the multivalue column. Thus, i cant count the column position.

Col1|Col2|Col3|Col4|col5|col6|col7|col8
1|2|3|3.1|3.2|4|5|6|6.1|6.2|7|8|8.1
# 4  
Old 08-11-2011
Hey,

Since the columns whose delimiter's are to be replaced are fixed so their position will also be fixed in your input file.
In that case you can work with below script

Code:
cat /dev/null > file1.tmp

while read line
do
    line1=`echo "$line" | cut -d "|" -f1,2`
    line2=`echo "$line" | cut -d "|" -f3` ##
    line3=`echo "$line" | cut -d "|" -f4,5`
    line4=`echo "$line" | cut -d "|" -f6` ##
    line5=`echo "$line" | cut -d "|" -f7`
    line6=`echo "$line" | cut -d "|" -f8` ##
    
    echo "$line1,$line2,$line3,$line4,$line5,$line6" >> file1.tmp
done < file1.txt

cat file1.tmp > file1.txt

The above code is w.r.t your example of updating column 3,6 and 8
Let me know if solves your question.
# 5  
Old 08-11-2011
One solution. A kind of ... Smilie
Code:
% cat INPUTFILE
Col1|Col2|Col3|Col4|col5|col6|col7|col8
1|2|3|3.1|3.2|4|5|6|6.1|6.2|7|8|8.1
% head -n1 INPUTFILE;sed 1d INPUTFILE|perl -F\\\| -alpe'
BEGIN{$w{$_+$c++}=$w{$_+$c++}=1for split/ /,shift} 
$o=$c="";$o.=$_.($w{++$c}?",":"|")for @F;$_=$o;chop' '3 6 8'
Col1|Col2|Col3|Col4|col5|col6|col7|col8
1|2|3,3.1,3.2|4|5|6,6.1,6.2|7|8,8.1

Another version:
Code:
% awk -F'|' -vcols='3 6 8' '
BEGIN {
  split(cols, tmp, " ")
  # get from column numbers to separator numbers
  # 3 6 8 => 3 4 8 9 12 13
  for (col in tmp) {  
    seps[tmp[col] + count]=1
    count++
    seps[tmp[col] + count]=1
    count++
  }
}
NR == 1 { print }
NR != 1 {
  output = ""
  for (i = 1; i <= NF-1; i++) { # not for the last field
    sep = (i in seps) ? "," : "|"
    output = output $i sep
  }
  output = output $NF
  print output
}
' INPUTFILE
Col1|Col2|Col3|Col4|col5|col6|col7|col8
1|2|3,3.1,3.2|4|5|6,6.1,6.2|7|8,8.1

Last edited by yazu; 08-11-2011 at 11:56 PM..
# 6  
Old 08-11-2011
Here is one that doesn't rely on column positions:
Code:
awk '{ 
  d=gensub(/\..*/,"","",$0);  #store the integer part
  out= (last==d) ? l"," : l"|";   #compare current integer part with last one; append comma or pipe
  if(l) { printf "%s", out; }    #print it out
  l=$0;  last=d;      #remember for comparison
}END{printf "%s",l }' RS="|" inputfile

Last edited by mirni; 08-11-2011 at 12:14 PM.. Reason: comments
# 7  
Old 08-12-2011
Thanks all for your help.

I tried to run mirni's script, but it seems that i cant get through.
I suspected 'awk' command is not working, thus i changed to 'nawk'.

But i come to this error upon executing the script.

nawk: calling undefined function gensub
input record number 1, file <file_name>
source line number 2

Any suggestion on get rid of this error?
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Replace delimiter for a particular column in a pipe delimited file

I have an input file as below Emp1|FirstName|MiddleName|LastName|Address|Pincode|PhoneNumber 1234|FirstName1|MiddleName2|LastName3| Add1 || ADD2|123|000000000 Output : 1234|FirstName1|MiddleName2|LastName3| Add1 ,, ADD2|123|000000000 OR 1234,FirstName1,MiddleName2,LastName3, Add1 ||... (2 Replies)
Discussion started by: styris
2 Replies

2. Shell Programming and Scripting

Bring values in the second column into single line (comma sep) for uniq value in the first column

I want to bring values in the second column into single line for uniq value in the first column. My input jvm01, Web 2.0 Feature Pack Library jvm01, IBM WebSphere JAX-RS jvm01, Custom01 Shared Library jvm02, Web 2.0 Feature Pack Library jvm02, IBM WebSphere JAX-RS jvm03, Web 2.0 Feature... (10 Replies)
Discussion started by: kchinnam
10 Replies

3. 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

4. Shell Programming and Scripting

Replace pipe delimited column string to null

Hi All, I have a large dat file where each lines are pipe delimited values. I need to parse the file depending on the request. For example: sometimes I have told to remove all the values in the 7th column (this case remove values '3333' only from the first line and '3543' from the second line)... (4 Replies)
Discussion started by: express14
4 Replies

5. 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

6. Shell Programming and Scripting

Sed, replace comma with pipe. but ignore qoutes

hi, I am trying to replace comma with pipe, but the issue is that i want to ignore the commas inside qoutes. for example: i have file with the string: 1,"2,3",4,"5","6,7" the result should be : 1|"2,3"|4|"5"|"6,7" i trying to use sed and awk (match function) for that, but i did not... (4 Replies)
Discussion started by: gabik
4 Replies

7. Shell Programming and Scripting

comma replaced with pipe

Source data: "123","aaa bbb CCC","12000" "134","HHH,bbc","13000" i have a delimited file. i want to replace with the pipe.The sed command is not working for replacing a delimeter. Command : sed s/\,/\|/g filename Output : When i run the command it is replacing the columns value... (7 Replies)
Discussion started by: number10
7 Replies

8. Shell Programming and Scripting

Find and replace a column that has '' to NULL in a comma delimited using awk or sed

Hi this is my first time posting ever. I'm relatively new in using AWK/SED, I've been trying many a solution. I'm trying to replace the 59th column in a file where if I encounter '' then I would like to replace it with the word NULL. example 0 , '' , '' , 0 , 195.538462 change it to 0... (5 Replies)
Discussion started by: gumal901
5 Replies

9. Shell Programming and Scripting

Trimming fields for comma or pipe seperated file

I have file like this FileA: abc , "helloworld" , america def,asia, japan ghi, africa, ipl Output Needed: abc,"helloworld",america def,asia,japan ghi,africa,ipl I would like to implement using awk. I want to trim each field for its leading and trailing spaces. (7 Replies)
Discussion started by: pinnacle
7 Replies

10. Shell Programming and Scripting

To Replace comma with Pipe inside double quotes

Hi, I have a requirement to replace the comma's inside the double quotes. The comma's inside the double quotes will get changed dynamically. Input Record: "Washington, DC,Prabhu,aju",New York Output Record: "Washington| DC|Prabhu|aju",New York I tried with the below command but it... (3 Replies)
Discussion started by: prabhutkl
3 Replies
Login or Register to Ask a Question