batch script to read multiple files and count commas on each line-CodePudding

I am a newbie in batch script and I am trying to achieve the following: loop through multiple files, count the # of commas on each line then remove extra commas if it is greater than 10. I can only get to the point where I get the count but I am stuck there. All fields are required. No carriage return. The extra comma will only happen in the field after the 9th comma

Example of data in csv file:

Row 1, (good data)

123,235252,6376,test1,08/11/2022,2,0,1,EA,Required text, pencil ,pen

Row 2, (bad data)

456,235252,6376,test2,08/11/2022,2,0,1,EA,Required,text, pencil ,pen

In row 2, Required text has an extra comma and should be removed. It should look like the row above

So the logic I would like to have is If the number of commas is 10 for the row, I will go to the next line If the number of commas greater than 10, then I will remove the one after the 9th comma since extra commas will only happen in that field Please note, I cannot put double quote around the field

@echo on
setlocal enabledexpansion enableddelayedexpansion

pause


set "inputFile=test.csv"
set "searchChar=,"

set count16=16
pause
for /f "delims=" %%a in ('
 findstr /n "^" "%inputFile%"
 ') do for /f "delims=:" %%b in ("%%~a") do (
    set "line=%%a"

    pause
    for /f %%c in ('
     cmd /u /v /e /q /c"(echo(!line:*:=!)"^|find /c "%searchChar%"
     ') do  set count=%%c echo  %%c echo here echo %count% echo  %count16% echo %%c line %%b has %%c characters 
        if %count16% equ %count% (echo   ***hit)
    )
    pause
)
pause

CodePudding user response：

@ECHO OFF
SETLOCAL
rem The following settings for the directories and filenames are names
rem that I use for testing and deliberately include names which include spaces to make sure
rem that the process works using such names. These will need to be changed to suit your situation.

SET "sourcedir=u:\your files"
SET "filename1=%sourcedir%\q73491865.txt"
SET "destdir=u:\your results"
SET "outfile=