Remove Commas between Double Quotes

Microsoft Windows xp professional sp2c e...
August 4, 2010 at 18:45:22
Specs: Windows 2003 or 2008, Intel/4GB
Hi Experts,

I request for a batch script which can remove commas between 2 double quotes. I am processing a CSV file which has around 12 columns. Out of them It has an Address column which can contain comma values in between: Please see the below pasted sample. Batch should be able to check for the commas between the double quotes and replace it with space.

Before Batch Script -

“Street1,City1,Country1”
“Street2,City2”
“Street3,City3,Country3”
“A,B,C,D,E”


After Batch Script-

“Street1 City1 Country1”
“Street2 City2”
“Street3 City3 Country3”
“A B C D E”

Thanks,
Phani Akella


See More: Remove Commas between Double Quotes

Report •


#1
August 4, 2010 at 19:17:20
download gawk for windows then do this

C:\test>more file
first,"Street1,City1,Country1","test1"
second,"Street2,City2","test2"
third,"Street3,City3,Country3","test3"
fourth,"A,B,C,D,E","test4"

C:\test>gawk "BEGIN{FS=\"\042\"}{for(i=1;i<=NF;i++){m=$i;if(gsub(\",\",\"\",m)>=1 && ($i~/.*,.[^,]*/) ) {gsub(\",\",\" \
",$i)} } }1 " OFS="\042" file

first,"Street1 City1 Country1","test1"
second,"Street2 City2","test2"
third,"Street3 City3 Country3","test3"
fourth,"A B C D E","test4"

GNU win32 packages | Gawk


Report •

#2
August 4, 2010 at 23:40:35
Hi Ghostdog,

this code is removing all the commas from the file. I need to remove only the commas which are between double quotes" ". My CSV file is of 29 columns. In that 5th column can contain commas in between double quotes. I need to remove only those commas which are between those doule quotes.

Can I do this using simple Batch Program?

Thanks,
Phani Akella


Report •

#3
August 5, 2010 at 00:32:09
>> this code is removing all the commas from the file.

where? My output shows all other commas intact , the ones inside double quotes are removed. My code remove all commas between double quotes. IF you have just 1 particular field you know you want to remove, then its a different story. Do you have just 1 field you want to remove commas? Or do you want to remove ALL commas between quotes NO MATTER which field?

>> Can I do this using simple Batch Program?

I don't do batch. maybe IVO or M2 can help you. But it won't be called a "simple" batch anymore..

GNU win32 packages | Gawk


Report •

Related Solutions

#4
August 5, 2010 at 02:51:20
Hi,

I am not able to upload the file. I would like to show you the file which I am trying to execute. If possible can you please kindly let me know your mail id. Thanks in Advance.

Thanks,
Phani Akella


Report •

#5
August 5, 2010 at 04:34:27
"this code is removing all the commas from the file."

Call me crazy but in your example lines all the commas are between pairs of double quotes.


=====================================
Helping others achieve escape felicity

M2


Report •

#6
August 5, 2010 at 21:23:32
Okay, I'll jump in and probably make a fool of myself. I think the examples given show the content of column 5 only.

Try the code below, the first For loop is only necessary because I don't know how to use " as a delim in a For loop, advice would be appreciated.

The double quote character " must NOT appear in columns 1 thru' 4.

The character set to %char% must NOT appear anywhere in the input file. For safety's sake try setting it to one of the Extended ASCII characters, 219 works well.

:: Code begins
@echo off 
cls
setlocal EnableDelayedExpansion

set char=#

set tempfile=%temp%\tempfile.csv

for /f "tokens=*" %%1 in (input.csv) do (
    set line=%%1&set line=!line:"=%char%!
    >>%tempfile% echo !line!
)
for /f "tokens=1-2* delims=%char%" %%1 in (%tempfile%) do (
    set two=%%2&set two=%char%!two:,= !%char%
    set outline=%%1!two!%%3
    set outline=!outline:%char%="!
    >>output.csv echo !outline!
)

del %tempfile%
:: Code ends

Good luck.


Report •

#7
August 6, 2010 at 02:05:56
Dear Experts,

Dear Experts,

Apologies, I might have not expressed my issue properly. I am attaching the sample file. Please click on the url. My file is in CSV format and contains around 29 fields. Out of these fields 5th field “LocationName” contains commas between two double quotes. I need to replace these commas with a space.

http://www.mediafire.com/?78hzh8zeg...

Thanks,
Phani Akella


Report •

#8
August 6, 2010 at 05:27:40
Not every 5th field has quotes.

Without them how do you define the fields?


=====================================
Helping others achieve escape felicity

M2


Report •

#9
August 6, 2010 at 07:51:25
well, since you have more sample data and the actual field is field 5 , here's the amended gawk one liner

C:\test>gawk -F"\042" "/\042/{gsub(\",\" ,\" \", $2)}1" OFS="\042" file

GNU win32 packages | Gawk


Report •

#10
August 8, 2010 at 20:38:08
Hi,

Thank you very much. Its working now. I would like to add date and time stamp in output file name like OutputFile_YYYYMMDD-HHMMSS.txt.

I tried OutputFile_%date%.txt
OutputFile_%datetime%.txt
OutputFile_"DATE".txt

Nothing worked. Please kindly suggest.

Thanks,
Phani Akella


Report •

#11
October 28, 2010 at 05:36:28
can you tell me that how i can use this gawk command in unix without downloading its software in windows as i am facing same issue as above..also when i try to run above command..error "gawk not found"

Report •

#12
October 28, 2010 at 07:17:57
If you have unix you probably HAVE gawk [or awk].

You'll probably have better luck with a new thread here or in unix forum.


=====================================
Life is too important to be taken seriously.

M2


Report •

#13
October 28, 2010 at 08:26:21
Thanks for the reply M2..as i am new to unix so i request you to pls let me give the exact command to write using awk for above situation.

Report •

#14
October 28, 2010 at 08:28:11
like gawk -F"\042" "/\042/{gsub(\",\" ,\" \", $2)}1" OFS="\042" file
..how will i write avove command using awk..

Report •

#15
October 28, 2010 at 08:57:47
I don't know unix. But this guy seems to:

http://www.grymoire.com/Unix/Awk.html


=====================================
Life is too important to be taken seriously.

M2


Report •

#16
November 22, 2010 at 21:07:39
I think below command works perfectly as I have tried many situations.

awk -v var='"' 'BEGIN{FS=OFS=var}
{ for(i=2;i<=NF;i++)
{ m=$i;
if(gsub(",","",m)>=1 && ($i~/.*,.[^,]*/))
{
gsub(",","",$i);
i++;
}
}
print
}' filename.txt

However I am not sure ($i~/.*,.[^,]*/) what it does? could you please explain the logic of ($i~/.*,.[^,]*/) as I am not comfortable with reg expression :(


Report •


Ask Question