Removing commas within semicolon in a flat file


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Removing commas within semicolon in a flat file
# 8  
Old 06-18-2009
Try the following code:
Code:
#!/bin/sed -f
    s/"\([^"]*\)*"/"1\1"2/g
    s/"1\([^",]*\),\([^"]*\)"2/\1\2/g
    s/".//g

The above code will work on assumption that there is at most one coma in one quoted fragment. However, it will correctly handle the case in which there are quoted fragments without comas inside. So, for the following input
Code:
"aaa","bbb"

it will output
Code:
aaa,bbb

If, necessary i can also provide you with the version that supports multi comas in one fragment.
# 9  
Old 06-19-2009
relevant unix code

can anybdy help me with the relevant unix code for removing multiple instances of comma in double quotes in single row
in a comma delimited file
input
====
"ro,han","ra,hul","ku,nal","vishe,sh"
output
===
"rohan","rahul","kunal","vishesh"
# 10  
Old 06-19-2009
Quote:
Originally Posted by r_t_1601
can anybdy help me with the relevant unix code for removing multiple instances of comma in double quotes in single row
in a comma delimited file
input
====
"ro,han","ra,hul","ku,nal","vishe,sh"
output
===
"rohan","rahul","kunal","vishesh"

what do you mean by relevant unix code??????

if you can use sed command use the below


Code:
sed 's/\"\(.*?\),\(.*?\)\"/\1\2/g'  inputfile.txt

# 11  
Old 06-19-2009
Code:
echo \"ro,han\",\"ra,hul\",\"ku,nal\",\"vishe,sh\" | tr -d ',' | sed 's/""/","/g'

# 12  
Old 06-19-2009
Quote:
Originally Posted by ahmad.diab
what do you mean by relevant unix code??????

if you can use sed command use the below


Code:
sed 's/\"\(.*?\),\(.*?\)\"/\1\2/g'  inputfile.txt

this is not the requirement[COLOR="#738fbf"]

---------- Post updated at 02:36 AM ---------- Previous update was at 02:27 AM ----------

Quote:
Originally Posted by r_t_1601
can anybdy help me with the relevant unix code for removing multiple instances of comma in double quotes in single row
in a comma delimited file
input
====
"ro,han","ra,hul","ku,nal","vishe,sh"
output
===
"rohan","rahul","kunal","vishesh"
Code:
awk 'BEGIN{ FS="," }
{
    s=""    
    for(i=1;i<=NF;i++){        
        if ( $i !~ /,/ && $i !~ /\"/ && !f ){
            printf $i","
        }
        if (f && $i ~ /\"$/){
            printf s $i","            
            s=""
            f=0
        }
        if(i==NF){ print ""}
        if ( $i ~ /^\"/ && $i !~/\"$/)  {
          f=1
          s=$i          
        }
        if (f && $i !~ /^\"|\"$/ ){
          s=s $i
        }
    }
}' file | sed 's/,$//'

output
Code:
# more file
rohan,rahul,"kun,,al","sw,a,ti"
rohan,rahul,"k,un,al",swati
rohan,"rah,ul",kunal,swati
"ro,han",rahul,kunal,swati
rohan,rahul,kunal,swati
# ./testnew.sh
rohan,rahul,"kunal","swati"
rohan,rahul,"kunal",swati
rohan,"rahul",kunal,swati
"rohan",rahul,kunal,swati
rohan,rahul,kunal,swati

learn about awk with the gawk manual

Last edited by ghostdog74; 06-20-2009 at 09:02 AM..
# 13  
Old 06-19-2009
I've come up with the following code for the multicomas case.
Code:
#!/bin/sed -f
	h
 	s/\("[^",]*\),\?\([^"]*"\)/\1\2/g
	x;G;:c;tc
	s/^\(.*\)\n\1$/\1/
	t
	D

It's a bit clumsy, but i cannot think of the other way to check whether a sed substitute command actually changed anything.
# 14  
Old 06-19-2009
Quote:
Originally Posted by tkleczek
It's a bit clumsy,
and unreadable.
Quote:
but i cannot think of the other way to check whether a sed substitute command actually changed anything.
obviously a wrong choice of tools. Better to use a good programming language.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Removing commas from CSV file

Hi I'm creating a sh script to generate a csv file. The CSV contains the values from a sql table. The content looks this: a,b,c,c2,c3,,,,,,,,,,,d,e I have some code that can separate the fields using the comma as delimiter, but some values actually contain commas, such as... (2 Replies)
Discussion started by: preema
2 Replies

2. Shell Programming and Scripting

Replace semicolon within double quotes in a file with semicolon delimiter

Hello Team, Could you please help me with the below question? I have a file with the following properties 1) File Delimiter is ; 2) Text columns are within double quotes 3) Numeric columns will not have double quotes 4) File has total 6 columns Please see a sample record from file ... (3 Replies)
Discussion started by: sam99
3 Replies

3. Shell Programming and Scripting

Removing just the trailing commas :-(

Hi all, I haven't needed to do any shell based editing for nearly 20 years, and no amount of searching around has found me a solution to this very simple problem :-( I have a csv file. Some lines have three commas at the end. This means the invoice hasn't been paid. I'd like to use sed / grep... (4 Replies)
Discussion started by: chardyzulu
4 Replies

4. Shell Programming and Scripting

Help with removing additional commas in string

Hi Experts, I have below strings hello,hi,,,,,,start date age,code,,,,,61,season I am trying to format this string to hello,hi,start date age,code,61,season Can anyone please help me in achieving this? Kind Regards, RB (3 Replies)
Discussion started by: ramakanth_burra
3 Replies

5. Shell Programming and Scripting

Removing commas within semicolon in a flat file

Hi , Im relatively new to unix and have to process a comma serparated flat file . I recieve some of the fields in double quotes and i want to remove it .. INPUT ==== filed1,field2,field3,"fie,ld4" OUTPUT ===== field1,field2,field3,"field4" can anyone tell me how to achieve... (10 Replies)
Discussion started by: r_t_1601
10 Replies

6. Shell Programming and Scripting

Need help in removing commas

i have the below line as output from a script. I want to delete the string "," and get the output without comma, cat D* | grep "bytes free" | awk '{print $3}' | ????? output: 40,966,189,056 Desired O/P: 40966189056 (1 Reply)
Discussion started by: ali560045
1 Replies

7. UNIX for Dummies Questions & Answers

removing semicolon using sed in aix--urgent

hi I have an expression aaabbb; xxx xxxi i need to get the ouput as xxx xxxi i am using sed -e 's/\(*;\)//g' but it is not working..?? can some one suggest.. This is urgent (9 Replies)
Discussion started by: aixjadoo
9 Replies

8. UNIX for Dummies Questions & Answers

Removing trailer from a flat file!!!

Hi, I get some flat files with trailer which gives the totol records count and i want to remove the trailer from the file. i used the following command it works fine with a single file. cat file_name | grep -v 'Total records:' > file1 mv file file_name But i dont know how to remove the... (12 Replies)
Discussion started by: kumarsaravana_s
12 Replies

9. HP-UX

Padding zeros after removing commas in file

Hi Gurus, There is a ASCII file in which a comma is used as a seperator for the amount field when the amount exceed seven digits: e.g. 0001300,000. Now, this comma needs to be removed from this field, after padding leading zeros (to maintain the ASCII positions) e.g. 00001300000.... (1 Reply)
Discussion started by: pranag21
1 Replies

10. UNIX for Dummies Questions & Answers

removing commas from text file

Dear all I have a file which looks like this xxxxxxxxxxxxxx,xxx,xxxxxxxxxx xxxxxxxxxxxxxx,xxx,xxxxxxxxxx etc basically 14 characters then a comma, three characters, then a comma then 10 characters. We are uploading this file to our mainframe and they want the commas removed, so it... (6 Replies)
Discussion started by: hcclnoodles
6 Replies
Login or Register to Ask a Question