remove unwanted comma in csv

September 19, 2010 at 21:26:40
Specs: Windows XP, 256
i have a csv file wtih following format
CONF6XP,20090804,Java 2 Runtime Environment, SE v1.4.2_05,Sun Microsystems, Inc.,1.4.2_05
CONF6XP,20091007,McAfee VirusScan Enterprise,McAfee, Inc.,8.7.0

here SE v1.4.2_05,Sun Microsystems, Inc. and McAfee VirusScan Enterprise,McAfee, Inc. consists of extra commas...
its a sample list of installed programs..the same column(company_name) consists of various programs with extra commas...
please give some suggestion for removing this extra commas
thanks in advance

See More: remove unwanted comma in csv

Report •

September 19, 2010 at 23:55:08
certteam, sure you don't need 'em? If the filenames are phone numbers sent over a modem, a comma is a modem pause and some modems need the time.
If they really are extras, seems to me you should be able to C/P the file into a new document in your favorite word processor, make the desired edits and re-save the file. On mine, it gets replaced.
Ed in Texas.

Report •

September 20, 2010 at 00:04:58
thank for the replay !!!!!!!!!!!!!!!! acutally this csv files is to be exported to database!!!!!!!!!

since the fields are having extra commas than column headings,it is creating problem by creaing additional columns than column failed to insert the CSVs into database;;;;;;;;;;

i tried to search in the google///////////// somebody suggested of removing commas between fields which is having double quotes using awk...
but in my case there is no double quotes in any fields...... and i want to extra comma only in 4th column..............

any suggestions';;;;;;;;;

Report •

September 20, 2010 at 00:39:25
There is no common reference, the header consists of 5 columns, the next line consists of 7 columns and the last consists of 6. What you require is easy enough if lines 2 thru' n have the same number of columns but if the number of columns in each of the input records is variable then you will have to define each comma to be removed. As your data is shown, two commas must be removed from line 2 but only one from line 3.

Please advise.

Report •

Related Solutions

September 20, 2010 at 01:24:09
thanks for the reply

the field Sun Microsystems, Inc., consists of commas in between them i want to remove commas so that i should look like.... Sun Microsystems Inc.... further for McAfee, Inc i want MCAfee Inc .....

so please suggest some batch script for auotmatic removal of the comma before INC field before uploading to database

for example

to be changed to AVIRA INC,

thanks in advnace

Report •

September 20, 2010 at 04:40:08
You don't have a properly defined CSV file. The is no automatic process to take out the extra commas unless it can be determined by other conditions. A proper CSV generator would enclose fields with commas with double quotes. Redo the program making the file.

Report •

September 20, 2010 at 08:55:51
In the time you have spent posting and reading replies, you could have manually removed those extra commas (with loads of time to spare). Or else, change your CSV format from "comma-delimited" to "Tab-delimited".

Report •

September 20, 2010 at 09:43:56
Hi Wahine ,

Amazing how great minds think alike.

Helping others achieve escape felicity


Report •

September 20, 2010 at 15:15:41
Hi M2 - yes, Wizard-Fred and IMpatient have hit the proverbial nail on the head.

Regards - W.

Report •

September 20, 2010 at 18:59:10
thanks for your regards

The output CSV is created through windows wmic command..... which i could not make much change in the windows script....

wmic /output:temp.csv product get vendor, name, version,installdate /format:csv


CONF6XP,20091007,McAfee VirusScan Enterprise,McAfee, Inc.,8.7.0
CONF6XP,20100630,Java(TM) 6 Update 20,Sun Microsystems, Inc.,6.0.200

thanks in advance

Report •

September 21, 2010 at 00:45:54
Yeah! Both lines have the same number of fields...

@echo off

set /p input=<input.csv
>output.csv echo %input% 
>>output.csv echo.

for /f "skip=1 tokens=1-6 delims=," %%1 in (input.csv) do (
    >>output.csv echo %%1,%%2,%%3,%%4%%5,%%6

Report •

September 21, 2010 at 01:55:38
thanks a lot.//////////

Report •

September 21, 2010 at 03:14:37
The script is probably still wrong, Can the script enclose all fields in double quotes?
Most parsers should be able to strip the quotes from the fields.

Report •

September 22, 2010 at 15:24:04
Wizard-Fred. Sorry, I don't understand the need for double quotes. The original file supplied by the OP doesn't appear to contain any single or multiple csv fields within double quotes so I didn't include any.

I understood that double quotes were intended to allow grouping multiple csv under one header, such as an address e.g. ...,"26 First Ave,Wilmington,Del,34567",... so that they can be extracted as one but my experience in manipulating a db is limited.

The script, after minor mods, can enclose all or individual fields in double quotes.


>>output.csv echo "%%1,%%2,%%3,%%4%%5,%%6"
would enclose the entire line in quotes whereas

>>output.csv echo "%%1","%%2",%%3,%%4%%5,"%%6"
would enclose the first, second and final field in quotes.

Your further advice appreciated although I dislike hijacking Certteam's thread.

Report •

September 22, 2010 at 22:50:45
My suggestion was directed to the original poster. If his script can enclose each field in double quotes, he should not need to remove commas within fields.
"","field1","field2, abc","field3, xyz, 123" will yield
(null field)
field2, abc
field3, xyz, 123

Report •

September 23, 2010 at 01:58:51
Ahhh yes. Thank you for your interest.

Report •

Ask Question