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.
Jasmine SandlasAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewIT / Software Engineering ConsultantCommented:
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 SandlasAuthor Commented:
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 PrewIT / Software Engineering ConsultantCommented:
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
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Jasmine SandlasAuthor Commented:
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 SandlasAuthor Commented:
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
Bill PrewIT / Software Engineering ConsultantCommented:
Okay, original version should still work okay, but we can remove a few lines to simplify slightly...

@echo off
setlocal

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 "" (
        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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jasmine SandlasAuthor Commented:
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 PrewIT / Software Engineering ConsultantCommented:
Glad that was helpful, thanks for the kind feedback.


»bp
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Windows Batch

From novice to tech pro — start learning today.