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.
解决方案
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