BATCH Script - how do I count occurrences of COMMA in a CSV or TXT file?

Question

I have bunch of CSV files to process daily. They have a constant form with 19 COMMAS to appear in every file. In the file, there is a mixture of strings, timestamps, integers and float digits. The file always ends with CR-LF set.

Each file has only 1 line of that text, that provides a set of information that is then processed later on.

Problem appears that from time to time (once every 1000 files) the device that is responsible for generation of that file creates INCOMPLETE file. Some information is missing.

I need to separate those files from all the good files before processing them further.

After spending some considerable amount of time trying to figure out the most robust way to resolve that I came to the conclusion, that since the file is incomplete, it has some COMMAS missing.

So I want to count the amount of COMMAS appearing in the CSV file. Correct file should have 19 commas, any problematic files that I have discovered for the last half year would have LESS than that. They also have CR-LF missing at the end of the line. They simply end with comma after some value and that is it.

Example of a good CSV file:

STRING1,STRING2,2017-01-20 17:34:08,53.808536,-7.789231,19.5,3,0,STRING3,2017-01-20 17:34:19,2,0,7.9,2,0,1,0,0,0,0

Example of incorrect CSV file:

STRING1,STRING3,2017-01-12 10:11:09,53.779093,-7.494274,

My question is: How can I count amount of COMMAS in a single CSV file, and if this amount doesn't match defined number to perform a certain operation (like move this incorrect file into ERRORS folder, that I could deal with later). If the file has the correct amount of COMMAS, just let it be. So another words I need to move Incorrectly formatted CSV files to an ERRORS folder and leave the correctly formatted CSV files as they were.

FYI: CSV files have always only 1 line of data.

I have experimented with certain solutions, that are trying to find and count instances of certain strings in a TXT file, but it is getting me nowhere. Maybe because my string is just a COMMA sign... I don't know.

I would much appreciate you help in that matter.

Answer 1

How do I count occurrences of , in a CSV file?

Use the following batch file (CountCommas.cmd):

@echo off
setlocal EnableDelayedExpansion
set _comma=,
for /f "usebackq" %%a in (`dir /b /s *.csv`)  do (
  set _file=%%a
  set count=0
  for /f "usebackq tokens=*" %%b in (`type !_file!`) do (
    set _line=%%b
    call :count
  )
)
goto :done
:count
    if !_line:~0^,1! equ !_comma! (
      set /a count+=1
      )
    if "!_line:~1!" neq "" (
      set _line=!_line:~1!
      goto :count
      )
    echo file !_file! contains !count! commas
    if !count! neq 19 (
      echo error
      rem handle error here
      )
    )
:done
endlocal

Notes:

• Replace rem handle error here with your error handling code

Example usage:

> type bad.csv
STRING1,STRING3,2017-01-12 10:11:09,53.779093,-7.494274,
> type good.csv
STRING1,STRING2,2017-01-20 17:34:08,53.808536,-7.789231,19.5,3,0,STRING3,2017-01-20 17:34:19,2,0,7.9,2,0,1,0,0,0,0
> CountCommas
file F:\test\bad.csv contains 5 commas
error
file F:\test\good.csv contains 19 commas

---

Further Reading

• An A-Z Index of the Windows CMD command line - An excellent reference for all things Windows cmd line related.
• dir - Display a list of files and subfolders.
• for /f - Loop command against the results of another command.
• if - Conditionally perform a command.
• set - Display, set, or remove CMD environment variables. Changes made with SET will remain only for the duration of the current CMD session.
• variables - Extract part of a variable (substring).

Answer 2

The FINDSTR $ regular expression only matches the position before a CR. So if your invalid files are missing the CR-LF, then you can use the following one liner to move all problem csv files to the error folder.

for /f "eol=: delims=" %F in ('findstr /vm $ *.csv') do @move /y "%F" error >nul

You must double the percents if you put the command within a batch script.

@echo off
for /f "eol=: delims=" %%F in ('findstr /vm $ *.csv') do move /y "%%F" error >nul

If you prefer, you can use a more complicated regular expression to look for lines that do not contain 19 commas:

@echo off
for /f "eol=: delims=" %%F in (
  'findstr /vm ".*,.*,.*,.*,.*,.*,.*,.*,.*,.*,.*,.*,.*,.*,.*,.*,.*,.*,.*," *.csv'
) do move /y "%%F" error >null

But simply counting commas may not be reliable, because quoted values may also contain commas, as LotPings points out in his answer.

Answer 3

The question should be How do I assure the csv file has 20 columns/fields

Presuming the strings in the file aren't quoted and don't contain commas this batch will do:

@Echo off&SetLocal EnableExtensions EnableDelayedExpansion
CD /d "X:\path\to\csv-folder"
Set Cnt=0
For %%A in (*.csv) Do Set File="%%A"&Set /P CSV=<%%A&Call :Count "!CSV:,=","!"
Goto :Eof
:Count
If "%~1" Neq "" Shift & Set /A Cnt+=1 & Goto :Count
If %Cnt% Neq 20 Echo %File% has %Cnt% Columns
Set "CSV="
Set Cnt=0

The batch encloses the line in double quotes and also replaces each single comma ,with "," so every column is quoted. All this passed to the subroutine :Count where the arguments are counted and shifted until no more present. If the count is different than 20 an echo issues the error message. This can be replaced by a move command.

Answer 4

An easy way with batch to count string characters in a file

You can use a batch script and put the simple logic in it to create a dynamic PowerShell script to do the count of the , commas characters, set the count to a variable, and use that variable accordingly with the rest of the process operations in the batch script logic.

The PS script does not need to be created dynamically and you could pass use a static PS script. You could pass the PS Script full path as the first argument to your batch script to use accordingly.

^{This is easy... done... next!!}

Batch Script Example

ECHO ON

SET file=C:\folder\file.txt

CALL :CreatePSCommaCount
SET PowerShellDir=C:\Windows\System32\WindowsPowerShell\v1.0 
CD /D "%PowerShellDir%" 
FOR /F "DELIMS=" %%A IN ('Powershell -ExecutionPolicy Bypass -Command "& '%DynPSCommaCount%'"') DO SET "commacount=%%A"

IF NOT %commacount%==19 GOTO EOF
<other batch script logic below here since count is 19 (or whatever you need it to be)>
GOTO EOF

:CreatePSCommaCount
SET DynPSCommaCount=%Temp%\TempCommaCount.ps1
IF EXIST "%DynPSCommaCount%" DEL /Q /F "%DynPSCommaCount%"
ECHO $file  = GC "%file%"                                               >>"%DynPSCommaCount%"
ECHO $Match = Select-String -InputObject $file -Pattern "," -AllMatches >>"%DynPSCommaCount%"
ECHO $Match.Matches.Count                                               >>"%DynPSCommaCount%"
GOTO EOF

You'd put a simple IF condition in your batch script and use the move command to move the file if it does or does not match a number for however you have that logic in your existing batch script.

---

Further Resources

• FOR /F
• IF
• Move
• Get-Content
• Select-String