CSV数据提取末尾的逗号 [英] Trailing commas at the end of a data extract in CSV

问题描述

关于我的问题的一些背景:

a little background on my problem:

我有一个由MS Access中的查询创建的CSV文件. CSV文件的主体包含各列.名称，地址，邮政编码，日期等.文件的最后一行包含数据摘要；文件名，日期，时间戳和记录总数.由于所有字段都包含字符串，并且我们不希望系统截断或尝试评估日期和邮政编码，因此我们将这些字段带引号输出.

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.

最后两行如下所示:

"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.

推荐答案

感谢大家的帮助.

我使用了不同的途径来解决我的问题.我创建了一个批处理脚本，该脚本提取记录并将数据重新打印到一个新文件中，并在文件末尾省略了多余的逗号.

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

这篇关于CSV数据提取末尾的逗号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！