CODEWITHSUNDEEP
excelvbacsv
hto
hto

Reputation: 61

Trailing commas at the end of a data extract in CSV

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.

Upvotes: 4

Views: 18840

Answers (4)

T.C
T.C

Reputation: 311

I've had this issue. It's usually because the last column of the other column have white spaces. (in your case column after TotalRecords)

Try opening in excel and highlighting and deleting the column that comes after TotalRecords and try again.

It's possible you have many following columns with white spaces, which may be determined by the amount of following columns

Upvotes: 0

hto
hto

Reputation: 61

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

Upvotes: 2

Gary's Student
Gary's Student

Reputation: 96753

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

Upvotes: 1

Dan Donoghue
Dan Donoghue

Reputation: 6206

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.

Upvotes: 1

Related Questions