1

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.

Improve this question
13
  • What have you actually tried and were did you script get stuck? Does it have to be a batch script? What is processing those files? Wouldn't it be possible to include the error checking in that component? – Seth Jan 23 '17 at 12:15
  • I have tried that method to find the COMMAS, but it gives me ZERO results :( – Piotruncio Jan 23 '17 at 12:18
  • stackoverflow.com/questions/7983925/… – Piotruncio Jan 23 '17 at 12:18
  • There is only a BATCH script in place that prepares the files. But if this problem described above appears everything fails. Process have to be running every 2 minutes, 24/7 and will need to process hundreds of files per run. – Piotruncio Jan 23 '17 at 12:21
  • Do you get an error when running that script? – Seth Jan 23 '17 at 12:21
2

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).
Improve this answer
7
  • +1 for not mixing any powershell stuff in there – Master Azazel Jan 23 '17 at 15:13
  • Hi David, Thanks for that. I have used your script and it works FINE. I have modified the Directory to point the script to the location of all the CSV files and modified the ERROR HANDLING part to MOVE the "problematic" files into a separate location. But what I have noticed, is that if there is 500 CSV files in that folder, it takes quite a long time to process. Is it because it has to run in a loop 500 times? – Piotruncio Jan 24 '17 at 13:31
  • David, When I tried to insert that Comma Counting code into another BAT file that does the whole job of processing those CSV files (Counting Commas is just a verification part to see if the files in a give folder have proper structure) the Comma Verification run only once. It didn't check all the files. I know that, because it run very fast and it found only 1 incorrect file in the folder (which was the first file to be checked) and then it run the rest of the BATCH script. When I run your script from a separate file it checked all CSV files and found both faulty files. – Piotruncio Jan 24 '17 at 13:36
  • Yes. The outer loop runs once for each file and the inner loop runs once for each character to count the commas. – DavidPostill♦ Jan 24 '17 at 13:37
  • Try calling CountCommas instead of including the code in the other batch file. – DavidPostill♦ Jan 24 '17 at 13:39
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.

Improve this answer
1
  • 1
    Hi Dave, thought you might want to update your answer from your DosTips code. – Squashman Dec 14 '17 at 0:18
1

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.

Improve this answer
0

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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.