Trailing commas at the end of a data extract in CS

Question

a little background on my problem:

I have CSV file that is created by a query in MS Access. The main body of the CSV file contains columns; names, address, post codes, dates, etc. The last line of the file contains a summary of the data; filename, date, timestamp and total number of records. As all fields contain strings and we did not want the system to truncate or try and evaluate the dates and post codes, we've output these fields with quotation marks.

Last two lines look something like this:

"Name","Code","Address","Address2","Office","Location","City","State","PostCode","Date"
"FileName","DDMMYYYY","HH:MM:SS",TotalRecords,"","","","","",""

I need to remove the extra commas in the final line so it looks like this:

"FileName","DDMMYYYY","HH:MM:SS",TotalRecords

I've had a look through and tried the solution here: CSV file has extra commas

This creates a copy of my original file but leaves all the commas intact. This solution works if I remove ALL the quotation marks.

Answer 1

If you are in Excel you can do this single line in VBA:

left(Activecell.text,len(trim(replace(replace(activecell.Text,","," "),""""," "))))

I will explain from the middle out:

replace(replace(activecell.Text,","," "),""""," ")

Replace all commas and double quotes with a space

trim(replace(replace(activecell.Text,","," "),""""," "))

Trim it ie remove starting and trailing spaces

len(trim(replace(replace(activecell.Text,","," "),""""," ")))

Get the length of the trimmed text

left(Activecell.text,len(trim(replace(replace(activecell.Text,","," "),""""," "))))

Finally give me the left part of the ORIGINAL string with the char count set to what we worked out as the length minus commas and double quotes on the start and end.

Answer 2

Thanks everyone for your help.

I've used a different route to solve my problem. I created a batch script that extracts the records and reprint the data into a new file, ommitting the extra commas at the end of the file.

My code is below, any critique to improve it, is appreciated.

echo off
setlocal EnableDelayedExpansion
pause
set cur=0
FOR /F "delims=" %%A in (input.csv) DO (
    set line=%%A
    set line=!line:,,=, ,!

FOR /F "tokens=1-11 delims=," %%G in (^"!line!^") DO (
    if "%%G"=="" (echo.)
    if "%%G"==""FILENAME"" (
        echo %%G,%%H,%%I,%%J >> test_%date:~-4,4%%date:~-7,2%%date:~-10,2%.csv
        goto EOF
    ) else (
        echo %%G,%%H,%%I,%%J,%%K,%%L,%%M,%%N,%%O,%%P,%%Q >> test_%date:~-4,4%%date:~-7,2%%date:~-10,2%.csv
    )
    set /a cur=cur+1
)
)
    :EOF
    echo %cur%
    pause

Answer 3

If a string ends with an unknown number of commas or an unknown number of:

,""

for example:

whatever,,,,,,,"","",,,,"",""

Then this bit of VBA code will remove that junk:

Sub StringCleaner()
   Dim s1 As String, sTest As String, sOut As String
   s1 = "," & Chr(34) & Chr(34)
   sTest = "whatever" & ",,,,,," & s1 & s1 & ",,," & s1 & s1
   sOut = sTest

   While Right(sOut, 1) = "," Or Right(sOut, 3) = s1
      If Right(sOut, 1) = "," Then sOut = Mid(sOut, 1, Len(sOut) - 1)
      If Right(sOut, 3) = s1 Then sOut = Mid(sOut, 1, Len(sOut) - 3)
   Wend

MsgBox sTest & vbCrLf & sOut
End Sub