批处理脚本清理CSV第13字段多余逗号时正常行异常排查

我编写了一个批处理脚本，用于移除CSV文件第13字段（LineDetail）中的多余逗号，该脚本在处理存在问题的行时运行正常。但当存在该字段为空的行时，部分原本正常的行（如输入中的第3、7、11行，不含表头）会受到影响，出现新增逗号导致数据在Excel中显示错位的情况。

输入输出示例 #

输入异常行示例 #

8455,343248202,6528,IVAN ,08/29/2022,2,0,1,EA,Required Supplies,08/22/2022,12/31/2022,,37449257,ABC~~~EFG@YAHOO.COM,31917105,Required Supplies   

受影响的正常行示例 #

8455,343248202,6528,IVAN,08/29/2022,3,0,1,EA,Handling Charges * Applicable only to books & supplies,08/22/2022,12/31/2022,,37449257,ABC~~~EFG@YAHOO.COM,31917105

完整输入CSV #

StoreNumber,PurchaseOrderNumber,Last4DIG,ParticipantName,PurchaseOrderDate,POLineNumber,UnitPrice,Quantity,UnitOfMeasure,PartDescription,StartDate,EndDate,LineDetail,ParticipantID,BuyerContacts,FacilityCode,DetailsFreeText
8455,343248202,6528,IVAN,08/29/2022,1,0,1,EA,Required Books,08/22/2022,12/31/2022,,37449257,ABC~~~EFG@YAHOO.COM,31917105,Required Books         
8455,343248202,6528,IVAN ,08/29/2022,2,0,1,EA,Required Supplies,08/22/2022,12/31/2022,,37449257,ABC~~~EFG@YAHOO.COM,31917105,Required Supplies         
8455,343248202,6528,IVAN,08/29/2022,3,0,1,EA,Handling Charges * Applicable only to books & supplies,08/22/2022,12/31/2022,,37449257,ABC~~~EFG@YAHOO.COM,31917105,Handling Charges * Applicable only to books & supplies         
8455,343248202,6528,IVAN,08/29/2022,4,0,1,EA,Mandatory Taxes,08/22/2022,12/31/2022,,37449257,ABC~~~EFG@YAHOO.COM,31917105,Mandatory Taxes         
8094,459244329,7152,ALFRED,08/29/2022,1,0,1,EA,Required Books,08/22/2022,12/18/2022,Textbooks, ebooks and manuals,32818318,ABC~~~EFG@YAHOO.COM,31016230,Required Books        
8094,459244329,7152,ALFRED,08/29/2022,2,0,1,EA,Required Supplies,08/22/2022,12/18/2022,$50 worth of school supplies,32818318,ABC~~~EFG@YAHOO.COM,31016230,Required Supplies         
8094,459244329,7152,ALFRED,08/29/2022,3,0,1,EA,Handling Charges * Applicable only to books & supplies,08/22/2022,12/18/2022,No more than 10%,32818318,ABC~~~EFG@YAHOO.COM,31016230,Handling Charges * Applicable only to books & supplies         
8094,459244329,7152,ALFRED,08/29/2022,4,0,1,EA,Mandatory Taxes,08/22/2022,12/18/2022,Detailed,32818318,ABC~~~EFG@YAHOO.COM,31016230,Mandatory Taxes         
8257,344249022,5745,GUILLERMO,08/29/2022,1,0,1,EA,Required Books,08/22/2022,08/22/2023,,38424677,ABC~~~EFG@YAHOO.COM,29001332,Required Books         
8257,344249022,5745,GUILLERMO,08/29/2022,2,0,1,EA,Required Supplies,08/22/2022,08/22/2023,$50 PER TERM MAX UNLESS APPROVED BY VRC,38424677,ABC~~~EFG@YAHOO.COM,29001332,Required Supplies         
8257,344249022,5745,GUILLERMO,08/29/2022,3,0,1,EA,Handling Charges * Applicable only to books & supplies,08/22/2022,08/22/2023,,38424677,ABC~~~EFG@YAHOO.COM,29001332,Handling Charges * Applicable only to books & supplies         
8257,344249022,5745,GUILLERMO,08/29/2022,4,0,1,EA,Mandatory Taxes,08/22/2022,08/22/2023,,38424677,ABC~~~EFG@YAHOO.COM,29001332,Mandatory Taxes        

处理后输出CSV #

StoreNumber,PurchaseOrderNumber,Last4DIG,ParticipantName,PurchaseOrderDate,POLineNumber,UnitPrice,Quantity,UnitOfMeasure,PartDescription,StartDate,EndDate,LineDetail,ParticipantID,BuyerContacts,FacilityCode,DetailsFreeText
8455,343248202,6528,IVAN,08/29/2022,1,0,1,EA,Required Books,08/22/2022,12/31/2022,,37449257,ABC~~~EFG@YAHOO.COM,31917105,Required Books         
8455,343248202,6528,IVAN ,08/29/2022,2,0,1,EA,Required Supplies,08/22/2022,12/31/2022,,37449257,ABC~~~EFG@YAHOO.COM,31917105,Required Supplies         
8455,343248202,6528,IVAN,08/29/2022,3,0,1,EA,Handling Charges * Applicable only to books & supplies,08/22/2022,12/31/2022,,,37449257,ABC~~~EFG@YAHOO.COM,31917105
8455,343248202,6528,IVAN,08/29/2022,4,0,1,EA,Mandatory Taxes,08/22/2022,12/31/2022,,37449257,ABC~~~EFG@YAHOO.COM,31917105,Mandatory Taxes         
8094,459244329,7152,ALFRED,08/29/2022,1,0,1,EA,Required Books,08/22/2022,12/18/2022,Textbooks  ebooks and manuals,32818318,ABC~~~EFG@YAHOO.COM,31016230,Required Books         
8094,459244329,7152,ALFRED,08/29/2022,2,0,1,EA,Required Supplies,08/22/2022,12/18/2022,$50 worth of school supplies,32818318,ABC~~~EFG@YAHOO.COM,31016230,Required Supplies         
8094,459244329,7152,ALFRED,08/29/2022,3,0,1,EA,Handling Charges * Applicable only to books & supplies,08/22/2022,12/18/2022,,No more than 10%,32818318,ABC~~~EFG@YAHOO.COM,31016230
8094,459244329,7152,ALFRED,08/29/2022,4,0,1,EA,Mandatory Taxes,08/22/2022,12/18/2022,Detailed,32818318,ABC~~~EFG@YAHOO.COM,31016230,Mandatory Taxes         
8257,344249022,5745,GUILLERMO,08/29/2022,1,0,1,EA,Required Books,08/22/2022,08/22/2023,,38424677,ABC~~~EFG@YAHOO.COM,29001332,Required Books         
8257,344249022,5745,GUILLERMO,08/29/2022,2,0,1,EA,Required Supplies,08/22/2022,08/22/2023,$50 PER TERM MAX UNLESS APPROVED BY VRC,38424677,ABC~~~EFG@YAHOO.COM,29001332,Required Supplies         
8257,344249022,5745,GUILLERMO,08/29/2022,3,0,1,EA,Handling Charges * Applicable only to books & supplies,08/22/2022,08/22/2023,,,38424677,ABC~~~EFG@YAHOO.COM,29001332
8257,344249022,5745,GUILLERMO,08/29/2022,4,0,1,EA,Mandatory Taxes,08/22/2022,08/22/2023,,38424677,ABC~~~EFG@YAHOO.COM,29001332,Mandatory Taxes         

使用的批处理代码 #

@echo off
setlocal EnableDelayedExpansion

rem Process all files with .csv extension in current folder
for %%F in (*.csv) do (
    ECHO/
    ECHO Input: "%%F"
    TYPE "%%F"

    rem Each file have comma-separated columns: may be 17 columns or more
    rem Keep columns 1-12 the same. After that, generate 5 columns more:
    rem the last 4 columns are the same
    rem the 5th before last column contain the rest of columns separated by space

    (for /F "usebackq tokens=1-12* delims=," %%a in ("%%F") do (
            set "restAfter12=%%m"
            set "last="
            set "lastBut1="
            set "lastBut2="
            set "lastBut3="
            set "lastBut4="

            for %%A in ("!restAfter12:,=" "!") do (    
                set "lastBut4=!lastBut4! !lastBut3!"
                set "lastBut3=!lastBut2!"         
                set "lastBut2=!lastBut1!"
                set "lastBut1=!last!"
                set "last=%%~A"
            )
            echo %%a,%%b,%%c,%%d,%%e,%%f,%%g,%%h,%%i,%%j,%%k,%%l,!lastBut4:~5!,!lastBut3!,!lastBut2!,!lastBut1!,!last!
        )) > "%%~NF.out"
    ECHO Output: "%%~NF.out"
    TYPE "%%~NF.out"
)

缺陷分析 #

1. 空字段拆分逻辑错误：当LineDetail为空时，restAfter12的内容是,ParticipantID,...，用逗号拆分后会产生空元素。循环处理这些空元素时，会错误地将后续字段的位置提前，导致lastBut4等变量被错误赋值，最终输出时多了逗号。
2. 固定截取长度问题：代码中用!lastBut4:~5!去除前缀空格，但这个长度是固定的。当lastBut4内容不足5个字符时（比如空字段场景），截取会产生意外结果，甚至留下多余的逗号分隔符。
3. 字段计数逻辑缺陷：脚本假设restAfter12拆分后最后4个字段是固定的后续列，但当LineDetail为空时，拆分后的元素数量变化，导致字段归属错误，把原本属于LineDetail的空值错误分配到后续列位置，进而产生多余逗号。

修复方案 #

修改后的批处理代码，通过数组存储拆分元素、动态计算字段位置来解决问题：

@echo off
setlocal EnableDelayedExpansion

for %%F in (*.csv) do (
    ECHO/
    ECHO Input: "%%F"
    TYPE "%%F"

    (for /F "usebackq tokens=1-12* delims=," %%a in ("%%F") do (
            set "restAfter12=%%m"
            set "lineDetail="
            set "participantID="
            set "buyerContacts="
            set "facilityCode="
            set "detailsFreeText="
            
            rem 拆分restAfter12为数组
            set "index=0"
            for %%A in ("!restAfter12:,=" "!") do (
                set "elem[!index!]=%%~A"
                set /a index+=1
            )
            
            rem 计算元素总数，确定最后4个字段的位置
            set /a totalElems=index-1
            if !totalElems! geq 3 (
                rem 最后4个字段分别是第totalElems-3到totalElems位
                set /a ldEnd=totalElems-4
                if !ldEnd! geq 0 (
                    rem 合并前面的元素作为LineDetail，用空格替换逗号
                    for /L %%i in (0,1,!ldEnd!) do (
                        if defined elem[%%i] (
                            set "lineDetail=!lineDetail! !elem[%%i]!"
                        )
                    )
                    rem 去除前缀空格
                    set "lineDetail=!lineDetail:~1!"
                ) else (
                    rem 当元素不足5个时，LineDetail为空
                    set "lineDetail="
                )
                rem 获取最后4个字段
                set /a pidIdx=totalElems-3
                set /a bcIdx=totalElems-2
                set /a fcIdx=total