Hi all,

Is there anyway to set up a batch file or other program to read data from a .csv file and tell me if any from a preselected list is missing? Currently every day we run a report that lets us know how many and what types of reports have been run that day. I have to manually go through 3 pages of excel formatted names and checkmark them if they are not there(tedious and paper wasting). These program names are standard without any variation in their name, so is there a way to set it up to check if acpd001, acrd003, aurs001, etc, etc. is in the csv file and if they aren't to output it to a txt file called missing.txt for review? Let me know if this is something easily possible or not. The csv file has other title names, but in particular I check the ones under PROGRAM NAME

I wanted to brush up on my WSH skills :)

How about something like this?

'Check for correct number of arguments
If WScript.Arguments.length <> 2 Then
   WScript.Echo "Usage: cscript.exe CheckLog.vbs LogFile InputFile" & _
      vbCrLf & vbCrLf & "LogFile: Report log file." & vbCrLf & _
      "InputFile: List of report names that should appear in LogFile " & _
      "(one name per line)."
   WScript.Quit 1
End If

'Dim the variables we're going to use
Dim WshShell, strWorkDir, numFiles, numFolders, fso, logFilePath
Dim logFile, inputFilePath, inputFile, resultFile, today
Dim logFileText, inputFileText, reportNames, val

'Some useful constants
Const ForReading = 1, ForWriting = 2

'Create the almighty shell and FSO
set WshShell = WScript.CreateObject("WScript.Shell")
Set fso = CreateObject("Scripting.FileSystemObject")

'Get file names from command line
logFilePath = WScript.Arguments(0)
inputFilePath = WScript.Arguments(1)

'Make sure the files exist
If logFilePath = "" Or Not fso.FileExists(logFilePath) Then
   WScript.Echo "Invalid LogFile specified."
   WScript.Quit 1
End If

'And again...
If inputFilePath = "" Or Not fso.FileExists(inputFilePath) Then 
   WScript.Echo "Invalid InputFile specified."
   WScript.Quit 1
End If

'Begin scan
WScript.Echo "Starting scan..." & vbCrLf

'Format today's date
today = Year(Date) & "-" & Month(Date) & "-" & Day(Date)

'Open the three files we're going to use
Set logFile = fso.OpenTextFile(logFilePath, ForReading)
Set inputFile = fso.OpenTextFile(inputFilePath, ForReading)
Set resultFile = fso.OpenTextFile(today & " Report Log.log", ForWriting, true)

'Start results file with the date
resultFile.WriteLine("Missing Reports for " & today & ":" & vbCrLf)

'Read in the two input files
logFileText = logFile.ReadAll()
inputFileText = inputFile.ReadAll()

'Split the names file into an array
reportNames = Split(inputFileText, vbCrLf, -1, 1)

'For every name in the array, check and see if it's in the report log
For Each name In reportNames
   val = InStr(1, logFileText, name, 1)
   If val = 0 Or val = Null Then
      WScript.Echo "Report '" & name & "' not found."
      resultFile.WriteLine(name)
   End If
Next

'Finish up
resultFile.WriteLine(vbCrLf & "End Scan")
WScript.Echo vbCrLf & "Finished."

'Clean up
inputFile.close
logFile.close
resultFile.close
Set fso = Nothing
Set resultFile = Nothing
Set logFile = Nothing
Set inputFile = Nothing

Copy/paste that into a text file named "CheckLog.vbs". It runs from a command-line but you can see the syntax by double-clicking the file. You'll need to create a file containing a list of report names to check for to test it. This should run fine under 2000, XP, and 2003.

Does it work like you wanted? If not, please post (or email me [click the "Dr. Nick" link at the top of this post]) a copy of the report you're trying to check so I can get an idea of what input to expect.

Doh, forgot to mention that it outputs the results of the scan to a file called "YYYY-MM-DD Report Log.log" in addition to the console.

Hi AKL,

Try this:

:: chk4miss.bat
@echo off > missing.txt

:main
for %%C in (*.csv) do call :checker %%C
goto :eof

:checker
call :datalist %1
goto :eof

:datalist
for /f %%D in (chekdata.txt) do find "%%D" < %1 > nul & if errorlevel 1 echo %1 is missing %%D >> missing.txt
goto :eof
:: DONE

If at first you don't succeed, you're about average.

M2

I'm understanding Dr. Nick's script a little bit more (still trying to figure out exactly how the data needs to be entered since alot of them are matching variables) but on your's Mech, it seems pretty simple enough. Where do i insert the list that it's supposed to look for in the script or should I set it to look into the reader document- an excel sheet that is exported to csv. Let me know!

Hi AKL,

The chk4miss.bat checks all the CSV files in the current directory.

It looks for the strings in chekdata.txt which I created for testing. It contains:

acpd001
acrd003
aurs001

So it checks each csv to see if it has those strings. If any are missing it writes to MISSING.TXT as you requested.

I can modify this to take a command line string or to make a cumulative log.

HTH

If at first you don't succeed, you're about average.

M2

Maybe i'm not setting it up right. For whatever reason it was saying that it could not find any of the files inside that csv, and I even changed it to a txt and made it exactly like the other. Still put out a missing.txt file that had all the items in chekdata.txt still missing. Do you want me to e-mail you the examples so you can look for yourself? Let me know. Thanks.

Hi AKL,

Sure send the files. ZIP them up, BUT rename the ZIP to AKL.ABC because I have ZIPs filtered. [because of all the malware]

If at first you don't succeed, you're about average.

M2

Hi AKL,

Your query.txt has entries like:

ACPD004C
ACPD004D
ACPD005
ACPD013

and your chekdata.text has:

atrd401
atrd700
aupd255
aurd001
aurd002

Note that one is mostly, or all, uppercase and the other lower.

If you want the bat to find data regardless of case:

:: c4i.bat
@echo off > missing.txt

:main
for %%C in (query.txt) do call :checker %%C
goto :eof

:checker
call :datalist %1
goto :eof

:datalist
for /f %%D in (chekdata.txt) do find /i "%%D" < %1 > nul & if errorlevel 1 echo %1 is missing %%D >> missing.txt
goto :eof
:: DONE

The difference is the find /i which makes it a case-insensitive find.

Now I get 29 missing instaed of 116.

If at first you don't succeed, you're about average.

M2

Yeah, i didn't even think about looking at the capitalization. It works absolutely wonderful now. Thanks a ton!

Hi AKL,

Glad that did it.

If at first you don't succeed, you're about average.

M2