Windows批处理脚本VLOOKUP输出异常，需修正CSV差异对比功能

问题说明 #

数据文件1 #

Account,Department,Location,Amount  
10000,DEPT_100,LOC_101,1000  
20000,DEPT_200,LOC_200,2000  
30000,DEPT_300,LOC_300,3000  
40000,DEPT_400,LOC_400,4000

数据文件2 #

Account,Department,Location,Amount  
10000,DEPT_100,LOC_100,1000  
20000,DEPT_200,LOC_200,2000  
30000,DEPT_300,LOC_300,3000  
40000,DEPT_400,LOC_400,4000  

期望输出 #

10000,DEPT_100,LOC_101,1000 - 10000,DEPT_100,LOC_100,1000 - Difference: Location: LOC_101 vs LOC_100

当前错误输出 #

10000,DEPT_100,LOC_100,1000 - 10000DEPT_100LOC_1001000,

原错误脚本 #

@echo off
setlocal enabledelayedexpansion

set "file1=input_file1.csv"
set "file2=input_file2.csv"
set "output_file=differences_output.csv"

:: Concatenate columns in both files
for /f "usebackq tokens=1-4,* delims=," %%a in ("%file1%") do (
    set "line=%%a,%%b,%%c,%%d - %%a%%b%%c%%d,%%e"
    echo !line!>>temp_file1.csv
)

for /f "usebackq tokens=1-4,* delims=," %%a in ("%file2%") do (
    set "line=%%a,%%b,%%c,%%d - %%a%%b%%c%%d,%%e"
    echo !line!>>temp_file2.csv
)

:: Perform VLOOKUP
findstr /v /g:temp_file1.csv temp_file2.csv > %output_file%

:: Loop through records in the output file and display differences
for /f "usebackq tokens=1-5,* delims=-," %%a in ("%output_file%") do (
    echo Record: %%a,%%b,%%c,%%d,%%e - Difference: %%f
)

:: Cleanup temporary files
del temp_file1.csv
del temp_file2.csv

echo Done. Differences written to %output_file%

修正后的脚本 #

@echo off
setlocal enabledelayedexpansion

set "file1=input_file1.csv"
set "file2=input_file2.csv"
set "output_file=differences_output.csv"

:: 清空输出文件
type nul > "%output_file%"

:: 读取文件1，以Account为键存储完整记录和各字段
for /f "usebackq skip=1 tokens=1-4 delims=," %%a in ("%file1%") do (
    set "rec1_%%a=%%a,%%b,%%c,%%d"
    set "dept1_%%a=%%b"
    set "loc1_%%a=%%c"
    set "amt1_%%a=%%d"
)

:: 读取文件2，对比相同Account的记录
for /f "usebackq skip=1 tokens=1-4 delims=," %%a in ("%file2%") do (
    if defined rec1_%%a (
        :: 对比各字段，找出差异
        set "diff="
        if not "%%b"=="!dept1_%%a!" set "diff=Department: !dept1_%%a! vs %%b"
        if not "%%c"=="!loc1_%%a!" set "diff=Location: !loc1_%%a! vs %%c"
        if not "%%d"=="!amt1_%%a!" set "diff=Amount: !amt1_%%a! vs %%d"
        
        :: 如果有差异，按期望格式输出
        if defined diff (
            echo !rec1_%%a! - %%a,%%b,%%c,%%d - Difference: !diff! >> "%output_file%"
            echo !rec1_%%a! - %%a,%%b,%%c,%%d - Difference: !diff!
        )
    )
)

echo Done. Differences written to %output_file%
endlocal

关键修正点 #

• 放弃临时文件+findstr的模糊匹配方案，改为以Account作为唯一关联键，直接存储文件1的记录和字段值，实现精准对比
• 添加skip=1跳过CSV表头，避免处理标题行
• 逐个字段对比Department、Location、Amount，精准定位差异内容并格式化显示
• 直接按期望格式输出结果，无需二次处理
• 移除冗余的临时文件操作，简化流程

内容的提问来源于stack exchange，提问作者user7571572