asked on

Batch Script Needed | Please help Folks | Thanks In Advance

I have a test_file.txt

------------------------------------------test_file.txt------------------------------------------------
3
ID | Name | Gender
x1 | A | M
x2 | B | F
x3 | C | M
------------------------------------------------------------------------------------------

3 is our record count.

I need to write a batch script where I have to store the record count value in one variable(X), need to store the data values ( 3 row) in to another variable (Y)
and have to compare both the variables if X == Y then execute a SQL job (ABC) and if they are not equal fail the script raising an ERROR.

Please help folks as I think this is the only forum which can help me solving my problem.

Bill Prew

So are you just looking to compare the 3 on the second line (indicating expected number of data rows), to the actual count of data rows between the header line and the ------------------------------------------ trailer line? So not trying to compare any values off each data line, just the count of data lines in the file?

»bp

Jasmine Sandlas

ASKER

Yes Bill you are correct.. Need to compare record count which is 3 with that of data rows which is in the given example is also 3

If record count from a file at the second line == count of data rows ( which is also 3 in the above example)

Then trigger the sql agent job (abc) if they are not equal fail the batch script

Bill Prew

Okay, here's a starting point for you that tested correctly here with the sample file you provided. You just need to add the command line you want to invoke when the counts match.

@echo off
setlocal EnableDelayedExpansion

rem Specify file to process
set FileIn=test_file.txt

rem Reset counters
set Expected=0
Set Found=0

rem Read each line of the text file and parse
for /f "usebackq tokens=1-3* delims='| " %%A in ("%FileIn%") do (

    rem Was there only one token on this line?
    if "%%B" EQU "" (
        rem Skip any lines starting with "-", otherwise this should be the expected count line, save it to a variable
        set Temp=%%A
        if "!Temp:~0,1!" NEQ "-" (
            set Expected=%%A
        )

    rem More than 1 token found, skip column header line, and count all others
    ) else (
        if /i "%%A%%B%%C" NEQ "IDNameGender" (
            set /a Found+=1
        )
    )
)

rem Display for debugging
echo Expected=%Expected%
echo Found=%Found%

rem If the expected matched the found counts, run SQL, else return error code
if %Expected% EQU %Found% (
    rem RUN SQL here
    echo Running SQL now...
    exit /b 0
) else (
    echo Incorrect data in file...
    exit /b 1
)

Open in new window

»bp

Jasmine Sandlas

ASKER

3
ID | Name | Gender
x1 | A | M
x2 | B | F
x3 | C | M

Just to avoid confusion bill.. Here is the original file.. It doesn't have any -----
I did it intentionally just to have a clear picture of the records..

Jasmine Sandlas

ASKER

Can you modify the code which you have written based on the original file which I provided you just before this comment.

3
ID | Name | Gender
x1 | A | M
x2 | B | F
x3 | C | M

ASKER CERTIFIED SOLUTION

Bill Prew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jasmine Sandlas

ASKER

Thank you so so much Bill... you are a great human being :)
+100000000000000000 points to you

Much Respect to you.. Thank you once again.

Bill Prew

Glad that was helpful, thanks for the kind feedback.

»bp