使用批处理对 csv 中的"自动求和"列

Question

我已经创建了批处理文件来合并文件夹中的所有csv文件。下面是我的批处理文件代码。

@ECHO OFF
SET first=y
SET newfile=Summary.csv
for %%F in (*.csv) do IF NOT %%F==%newfile% (
if defined first (
COPY /y "%%F" %newfile% >nul
set "first="
) else (
FOR /f "skip=1delims=" %%i IN (%%F) DO >> %newfile% ECHO %%i
)
)

我的问题是，如果我想为每列添加自动求和，如何添加到代码中？

下面是我运行批处理文件后的示例 csv 文件。

Name,A4 Used,A3 Used,Others
A,23,9,2
B,61,41,0
C,5,85,7

我需要为每一列创建一个自动求和，如下例所示。

Name,A4 Used,A3 Used,Others
A,23,9,2
B,61,41,0
C,5,85,7
Total,89,135,9

知道伙计们吗？

Answer 1

此任务可以使用以下注释批处理代码来完成，具体取决于已处理的 CSV 文件的内容：

@echo off
setlocal EnableExtensions DisableDelayedExpansion
rem Exit this batch file if current directory does not contain any CSV file.
if not exist *.csv goto EndBatch
rem The summary CSV file is created first in directory for temporary
rem files to avoid that outer FOR loop below tries to process also
rem the summary file. The summary file is created with header row.
set "NewFile=%TEMP%Summary.csv"
echo Name,A4 Used,A3 Used,Others>"%NewFile%"
rem Make sure there is no summary CSV file in current directory
rem from a previous execution of this batch file in this directory.
del "Summary.csv" 2>nul
rem Initialize the environment variables for total sum of each column.
set "TotalColumn2=0"
set "TotalColumn3=0"
set "TotalColumn4=0"
rem The outer loop is executed for each CSV file in current directory.
rem The inner loop reads each CSV file line by line. The first line is
rem always skipped. Skipped are also empty lines and lines starting with
rem a semicolon. All other lines are split up into four substrings using
rem comma as separator (delimiter).
for %%I in (*.csv) do (
for /F "usebackq skip=1 tokens=1-4 delims=," %%A in ("%%I") do (
if not "%%D" == "" (
set /A TotalColumn2+=%%B
set /A TotalColumn3+=%%C
set /A TotalColumn4+=%%D
>>"%NewFile%" echo %%A,%%B,%%C,%%D
) else (
del "%NewFile%"
echo ERROR: A line in "%%I" has not four comma separated values.
echo/
pause
goto EndBatch
)
)
)
rem Append to summary file the total sums and move the summary file
rem from temporary files directory to current directory. If that fails
rem unexpected, delete the summary file in temporary files directory.
>>"%NewFile%" echo Total,%TotalColumn2%,%TotalColumn3%,%TotalColumn4%
move "%NewFile%" "Summary.csv" >nul
if errorlevel 1 (
del "%NewFile%"
echo ERROR: Could not move Summary.csv to "%CD%".
echo/
pause
)
:EndBatch
endlocal

请注意 Windows 命令解释器的限制：

1. 算术表达式只能使用 32 位有符号整数来完成，这意味着值范围限制为 -2147483648 到 2147483647。不支持浮点运算。

命令 FOR 将一系列分隔符

2. 解释为将一行拆分为子字符串的一个分隔符。因此，CSV 文件中像D,80,,20这样的行会导致循环变量A被分配D，循环变量B被分配80，循环变量C被分配20和循环变量D没有分配任何内容。在这种情况下，批处理文件退出并显示错误消息。

要了解使用的命令及其工作原理，请打开命令提示符窗口，在那里执行以下命令，并仔细阅读为每个命令显示的所有帮助页面。

• del /?
• echo /?
• endlocal /?
• for /?
• goto /?
• if /?
• move /?
• pause /?
• rem /?
• set /?
• setlocal /?

另请阅读有关使用命令重定向运算符的Microsoft文章。