Remove extra commas in a field from csv file affecting other rows with good data

Question

I have a batch script which should remove extra commas in a field, (field 13), and it is working fine.

However, if I have other rows that have no data in this field, some of the good rows become affected.

In my input, only row 2, (do not count the header), has an issue

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

However, when I run the script, row 3, 7, 11 got affected, (these are good rows).

(Showing only 3 row as an example, an extra comma was added to the row causing data shifted over when displayed in xls)

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

Input:

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

Output:

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

Code:

@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"
)

I would appreciate if someone could point out what was missing in my code to cause this issue.

Aacini · Accepted Answer · 2022-08-31T16:50:58.020

The problem is that your data contain asterisks * that are expanded as filenames in FOR command, so the line must be split in fields with no use of FOR.

It is easier to fix this issue in the simpler code I provided to solve this problem:

@echo off
setlocal EnableDelayedExpansion

rem General method to keep the first N columns the same
rem and group additional fields in column N+1

rem Define the number of "same" and "total" columns:
set /A "same=12, last=17"

rem Process all files with .csv extension in current folder
for %%F in (*.csv) do (

ECHO/
ECHO Input: "%%F"
TYPE "%%F"

   rem Process all lines of current file
   (for /F "usebackq delims=" %%a in ("%%F") do (

      set "line=%%a"
      set "head="
      set "tail="

      rem Split current line in comma-separated fields
      call :splitLine

      rem Process the fields
      for /L %%i in (1,1,!n!) do (
         if %%i leq %same% (         rem Accumulate field in "head" columns
            set "head=!head!!f[%%i]!,"
         ) else if %%i leq %last% (  rem Accumulate field in "tail" columns
            set "tail=!tail!!f[%%i]!,"
         ) else (  rem Combine one field from beginning of "tail" and accumulate last field
            for /F "tokens=1* delims=," %%x in ("!tail!") do set "tail=%%x %%y!f[%%i]!,"
         )
      )

      echo !head!!tail:~0,-1!

   )) > "%%~NF.out"

ECHO Output: "%%~NF.out"
TYPE "%%~NF.out"

)
goto :EOF


:splitLine

set "n=1"
set "f[%n%]=%line:,=" & set /A "n+=1" & set "f[!n!]=%"
exit /B

Output:

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

I tried your code with some production files and they all worked. Really appreciate your help. I specially like the way you put all the comments in your code, so it is very useful for me to understand and learn from it. You are a great mentor. I have upvoted your answer already. — SForum, Aug 31 '22 at 20:25

Remove extra commas in a field from csv file affecting other rows with good data

1 Answers1