Removing unwanted Commas in a CSV File

[NickC111]

I have a basic CSV file containing lines of data but I have unwanted commas in the file and I want to delete them

Example

7678676,04,07/10/2012
545545,12,06/12/2012
,,
,,
5545435,44,01/01/2011
,,
43434,05,12/12/2012

I want to delete the double commas in the file

 

[Geates]

Open the file as a stream. Read the file line by line. If the line does not equal ,, copy it to a "content" string. Close the file and open it for writing. Write the new content.

set objFSO = CreateObject("Scripting.FileSystemObject")
strFileName = "C:\folder\file.csv"

set objStream = objFSO.OpenTextFile(strFileName, 1, true, 0)
do while not objStream.AtEndOfStream
   strLine = objStream.ReadLine
   if (strLine <> ",,") then
      strContent = strContent & strLine & vbNewLine
   end if
loop
objStream.close

set objStream = objFSO.OpenTextFile(strFileName, 2, true, 0)
objStream.Write strContent
objStream.Close

-Geates

"I hope I can chill and see the change - stop the bleed inside and feel again. Cut the chain of lies you've been feeding my veins; I've got nothing to say to you!"
-Infected Mushroom

"I do not offer answers, only considerations."
- Geates's Disclaimer

 

[NickC111]

Thank you but what happens if the comma's appear anywhere in the string

 

[Geates]

They would be over-looked. Your csv example didn't include such possibilities so it wasn't considered. To account for that change the do..loop to

do while not objStream.AtEndOfStream
   strLine = replace(objStream.ReadLine, ",,", "")
   if (len(strLine)) then
      strContent = strContent & strLine & vbNewLine
   end if
loop

-Geates

"I hope I can chill and see the change - stop the bleed inside and feel again. Cut the chain of lies you've been feeding my veins; I've got nothing to say to you!"
-Infected Mushroom

"I do not offer answers, only considerations."
- Geates's Disclaimer

 

[MasterRacker]

Keep in mind that removing double commas in the middle of a line could cause column misalignment errors in your data.

Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]