Removing commas within semicolon in a flat file - Page 4

bwhitehd · #1 (**permalink**) 06-20-2009

Quote:

Originally Posted by ghostdog74

@bwhited , i think OP wants to retain the double quotes

Sorry, this wasn't indicated in the original post. The sample output showed all of the quotes gone.

I'll update my script and repost. I also noted in the real sample input that there are spaces in the strings that need to be accounted for.

---------- Post updated at 12:06 ---------- Previous update was at 11:44 ----------

Quote:

Originally Posted by r_t_1601

here is a sample record
50119785,IRFE Asia,IRFE:3123146,18-Jun-09,29-Jun-09,MODFOLLOWING,29-Jun-12,MODFOLLOWING,IDR,USD,30900000000,3000000,13
29758,"THE BANK OF TOKYO-MITSUBISHI UFJ, LTD.",N,,,,,GREEN,B,FLOAT,FIXED,--,10.25,,,,,,,,,6M,6M,,6M,A/360,A/360,MODFOLLOWING,M
ODFOLLOWING,USD,GBP,IDR,USD,GBP,IDR,MODFOLLOWING,MODFOLLOWING,USD,GBP,IDR,USD,GBP,IDR,,,,GBP,,,--,6M-USD-LIBOR-BBA,,0,,,,,0,0,
,2,NO,1329758,0,0,0,0,23100,0,0,23100,IDOSW,JKT,665,Cross-Currency Swap,NEW,D121360,N, x

there is a comma in ,"THE BANK OF TOKYO-MITSUBISHI UFJ, LTD." which i want to delete .. sadly none of the scripts provided has worked for me so far and its kinda urgent .. GURUS !! SOS

---------- Post updated at 02:22 AM ---------- Previous update was at 02:20 AM ----------

just to update its a comma delimited file and arnd 91 colms

Okay, here you go. Next time, please post the real input and full details. It will save a lot of time and effort.

The following code takes this input:

Code:

rohan,rahul,kunal,"sw,ati"
rohan,rahul,"k,un,al",swati
rohan,"rah,ul",kunal,swati
"ro,han",rahul,kunal,swati
rohan,rahul,kunal,swati
50119785,IRFE Asia,IRFE:3123146,18-Jun-09,29-Jun-09,MODFOLLOWING,29-Jun-12,MODFOLLOWING,IDR,USD,30900000000,3000000,1329758,"THE BANK OF TOKYO-MITSUBISHI UFJ, LTD.",N,,,,,GREEN,B,FLOAT,FIXED,--,10.25,,,,,,,,,6M,6M,,6M,A/360,A/360,MODFOLLOWING,MODFOLLOWING,USD,GBP,IDR,USD,GBP,IDR,MODFOLLOWING,MODFOLLOWING,USD,GBP,IDR,USD,GBP,IDR,,,,GBP,,,--,6M-USD-LIBOR-BBA,,0,,,,,0,0,,2,NO,1329758,0,0,0,0,23100,0,0,23100,IDOSW,JKT,665,Cross-Currency Swap,NEW,D121360,N, x

and provides this output: (saving the double quotes, spaces and dash)

Code:

rohan,rahul,kunal,"swati"
rohan,rahul,"kunal",swati
rohan,"rahul",kunal,swati
"rohan",rahul,kunal,swati
rohan,rahul,kunal,swati
50119785,IRFE Asia,IRFE:3123146,18-Jun-09,29-Jun-09,MODFOLLOWING,29-Jun-12,MODFOLLOWING,IDR,USD,30900000000,3000000,1329758,"THE BANK OF TOKYO-MITSUBISHI UFJ LTD.",N,,,,,GREEN,B,FLOAT,FIXED,--,10.25,,,,,,,,,6M,6M,,6M,A/360,A/360,MODFOLLOWING,MODFOLLOWING,USD,GBP,IDR,USD,GBP,IDR,MODFOLLOWING,MODFOLLOWING,USD,GBP,IDR,USD,GBP,IDR,,,,GBP,,,--,6M-USD-LIBOR-BBA,,0,,,,,0,0,,2,NO,1329758,0,0,0,0,23100,0,0,23100,IDOSW,JKT,665,Cross-Currency Swap,NEW,D121360,N, x

Script:

Code:

#!/usr/bin/perl -w
use warnings;
use strict;

open( FH, "input.txt" ) || die "Can't open the input file: $!";

while (<FH>) {
    chomp;
    my $regex = '("\w+[-,|\w+\s+\.]+")';
    m/$regex/g;
    my $line = $1;
    $line =~ s/,//g;
    s/$regex/$line/g;
    print $_. "\n";
}

close(FH);
exit 0;

I hope this provides the full solution you're looking for.

- B

tkleczek · #2 (**permalink**) 06-20-2009

Well, i checked this code against your example, and it seems to work properly.

Code:

#!/bin/sed -f
	h
 	s/\("[^",]*\),\?\([^"]*"\)/\1\2/g
	x;G;:c;tc
	s/^\(.*\)\n\1$/\1/
	t
	D

ghostdog74 · #3 (**permalink**) 06-20-2009

have you checked your output?

Franklin52 · #4 (**permalink**) 06-20-2009

Another approach:

Code:

awk 'BEGIN{FS=OFS="\""}
{for(i=2;i<NF;i+=2){sub(",","",$i)}}
1' file

ahmad.diab · #5 (**permalink**) 06-20-2009

Quote:

Originally Posted by ghostdog74

have you checked your output?

Code:

yes I have but when using 

sed 's/\([^,]*\),\([^,]*\)/\1\2/g' file.txt 

I am getting correct output    # Iam using GNU/Linux

but when using 

sed '
{
:a
s/\([^,]*\),\([^,]*\)/\1\2/g
ta
}
' file.txt
it will also remove the , between fields.

ghostdog74 · #6 (**permalink**) 06-20-2009

Quote:

Originally Posted by ahmad.diab

Code:

yes I have but when using 

sed 's/\([^,]*\),\([^,]*\)/\1\2/g' file.txt 

I am getting correct output    # Iam using GNU/Linux

but when using 

sed '
{
:a
s/\([^,]*\),\([^,]*\)/\1\2/g
ta
}
' file.txt
it will also remove the , between fields.

try this input

Code:

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

---------- Post updated at 07:05 AM ---------- Previous update was at 07:04 AM ----------

Quote:

Originally Posted by Franklin52

Another approach:

Code:

awk 'BEGIN{FS=OFS="\""}
{for(i=2;i<NF;i+=2){sub(",","",$i)}}
1' file

my output shows it doesn't take care of more than 1 commas as well

Franklin52 · #7 (**permalink**) 06-20-2009

Quote:

Originally Posted by ghostdog74

try this input

Code:

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

---------- Post updated at 07:05 AM ---------- Previous update was at 07:04 AM ----------

my output shows it doesn't take care of more than 1 commas as well

In that case:

Code:

awk 'BEGIN{FS=OFS="\""}{for(i=2;i<NF;i+=2){gsub(",","",$i)}}1' file

Regards

More UNIX and Linux Forum Topics You Might Find Helpful
Thread	Thread Starter	Forum	Replies	Last Post
Removing commas within semicolon in a flat file	r_t_1601	Shell Programming and Scripting	10	06-16-2009 07:52 AM
removing semicolon using sed in aix--urgent	aixjadoo	UNIX for Dummies Questions & Answers	9	06-16-2008 02:41 PM
Removing trailer from a flat file!!!	kumarsaravana_s	UNIX for Dummies Questions & Answers	12	06-24-2007 04:53 AM
Padding zeros after removing commas in file	pranag21	HP-UX	1	11-09-2005 10:22 PM
removing commas from text file	hcclnoodles	UNIX for Dummies Questions & Answers	6	03-26-2003 04:43 PM

Bits Awarded / Charged to Franklin52 for this Post
Date	User	Comment	Amount
06-20-2009	ghostdog74	good idea	5,000

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode	Rate This Thread: