Remove extra commas in a CSV as source allows manual entry in one of the fields

[Danton1970]

I have a dump of a large database in CSV format. One of the fields in the source has a comment section where the user can enter freeform text including commas which corrupts the CSV.


Looking to remove extra commas is a CSV file where there are extra
commas in the file. I think a macro along the lines of the following would fix the CSV,



Data Source

in a "good" row there will be 15 commas.
any extra commas will always by after the 10th comma

for each row count the number of commas as "x".
If the number of commas is greater than 15, let "y" be the number of extra commas ie let y= x-15"
If y>0 remove y commas after the 10th comma.

 

[kennypete]

I don't think using Excel to do this is ideal. Using a scripting language is.

A solution like this VB Script works (tested with the data as shown; VB Script to keep the language close to VBA for simplicity). With minor adjustments it should do the trick for what you are doing:

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFileRead = objFSO.OpenTextFile("In.csv", 1)
Set objFSO2 = CreateObject("Scripting.FileSystemObject")
Set objFileWrite = objFSO2.OpenTextFile("Out.csv", 2, True)

Do Until objFileRead.AtEndOfStream
    a = Split(objFileRead.ReadLine, ",")
    n = Ubound(a)
    i = 0
    l = ""
    for each x in a
        if i < 10 then
            l = l & x & ","
        elseif n-i>4 then
            l = l & x & " "
        else
            l = l & x & ","
        end if
        i = i + 1
    next
    l = left(l, len(l)-1)
    objFileWrite.write(l & vbCrLf)

Loop

objFileWrite.Close
objFileRead.Close

In.csv
a,b,c,d,e,f,g,h,i,j,"k,l,m,n,o",p,q,r,s
a,b,c,d,e,f,g,h,i,j,ha ha,p,q,r,s
a,b,c,d,e,f,g,h,i,j,"yeah, nah",p,q,r,s

Out.csv
a,b,c,d,e,f,g,h,i,j,"k l m n o",p,q,r,s
a,b,c,d,e,f,g,h,i,j,ha ha,p,q,r,s
a,b,c,d,e,f,g,h,i,j,"yeah nah",p,q,r,s

Save the script as a text file and rename it convert.vbs or suchlike with the In.csv in the same folder - the .vbs should then be executable by double-clicking and produce Out.csv

 

[kennypete]

Did you solve what you were trying to do @Danton1970?