Remove Commas That Appear Within Double Quotation Marks in a CSV File

Removes commas that appear within double quotation marks in a CSV file.

 
 
 
 
 
2 Star
(1)
Add to favorites
Storage
8/10/2009
E-mail Twitter del.icio.us Digg Facebook
Sign in to ask a question


  • Doesn't work
    3 Posts | Last post May 18, 2017
    • Doesn't work as I would have thought intended.
      
      4,"10,000","30,000"
      
      becomes
      
      4,"10000","10000""30000"
      
      I would have thought it should be: 4,"10000","30000"
      
      This would be massively useful to the Salesforce community, as any kind of data load must have commas removed from numbers prior to loading.
    • Here's something that does work, and as an added bonus will only remove commas from numbers. It assumes a well formed CSV and is a bit brute force, but it's short and simple. Save to a VBS file and call it with:
      cscript //NoLogo myscript.vbs sourcefile.csv >targetfile.csv
      
      Set objFS=CreateObject("Scripting.FileSystemObject")
      strFile = WScript.Arguments.Item(0)
      quotechar=""""
      Set objFile = objFS.OpenTextFile(strFile)
      Do Until objFile.AtEndOfStream
      	strLine = objFile.ReadLine
      	insidequote=0
      	outputline=""
      	isnumberfield=0
      	for i=1 to len(strLine)
      		currentchar = Mid(strLine,i,1)
      		If currentchar=quotechar then 
      			If insidequote = 1 then
      				insidequote = 0
      			Else
      				insidequote = 1
      				strNumberCheck=mid(strLine,i+1,InStr(mid(strLine,i+1),quotechar)-1)
      				isnumberfield=1
      				for n=1 to len(strNumberCheck)
      					charNum=Mid(strNumberCheck,n,1)
      					If (Asc(charNum)<48 or Asc(charNum)>57) and charNum<>"," and charNum<>"." and charNum<>"-" then
      						isnumberfield=0
      					End If
      				next
      			End If
      			
      		End If
      		
      		If currentchar<>"," or insidequote=0 or isnumberfield=0 then 
      			outputline=outputline + currentchar
      		End If
      	next
      loop
    • Ouch: add
      
      WScript.Echo outputline 
      
      between the last "next" and the very last line ("loop"). I had removed it by accident when I stripped out stuff not relevant to the comma removal.