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
# 1  
Old 06-16-2009
Removing commas within semicolon in a flat file

i am recieving a flat file ( comma seperated ) with comma in between double quotes in any of the source fields . i need to remove the comma in double quotes and process the file thereafter

fields in file
=========
col1,col2,col3,col4

input can be any of the followng
=======================
rohan,rahul,kunal,"sw,ati"
rohan,rahul,"kun,al",swati
rohan,"rah,ul",kunal,swati
"ro,han",rahul,kunal,swati

the output should be
=============
rohan,rahul,kunal,swati

how can we achieve this ..
thanks in advance
# 2  
Old 06-16-2009
if you have Python, you can use its csv module
Code:
#!/usr/bin/env python
import csv
filename = "file"
reader = csv.reader(open(filename),delimiter=",")
for row in reader: 
    for n,item in enumerate(row):
        row[n]=row[n].replace(",","")
    print ','.join(row)

output
Code:
# more file
rohan,rahul,kunal,"sw,ati"
rohan,rahul,"kun,al",swati
rohan,"rah,ul",kunal,swati
"ro,han",rahul,kunal,swati

# ./test.py
rohan,rahul,kunal,swati
rohan,rahul,kunal,swati
rohan,rahul,kunal,swati
rohan,rahul,kunal,swati

# 3  
Old 06-16-2009
we already did this using sed before 2 hours !!!!!

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

BR
# 4  
Old 06-16-2009
can you please tell relevant unix code
# 5  
Old 06-16-2009
Quote:
Originally Posted by ahmad.diab
we already did this using sed before 2 hours !!!!!

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

BR
its not that simple.
# 6  
Old 06-17-2009
May try below perl.Smilie

Code:
while(<DATA>){
	s/,(?=[^,"]+")//;
	s/"//g;
	print;
}
__DATA__
rohan,rahul,kunal,"sw,ati"
rohan,rahul,"kun,al",swati
rohan,"rahul",kunal,"sw,ati"
"ro,han",rahul,kunal,swati

# 7  
Old 06-17-2009
Try the below code. This works even if you have comma more than once in a line.

Code:
 
use strict;
use warnings;
while(<DATA>) {
        s/\"(.*?)\,(.*?)\"/$1.$2/ge;
        print;
}
__END__
"ro,han",rahul,kunal,"sw,ati"
rohan,"r,ahul","kun,al",swati
rohan,"rah,ul",kunal,swati
"ro,han",rahul,kunal,"s,wati"


Last edited by balaji_red83; 06-17-2009 at 07:09 AM..
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