Writing a Batch Script to assign tokens as variables and input those variables into a string using FOR statement

Question

Here is what I am doing:

1. Exporting a table via SQL in Excel format

2. Delete columns from the XLS spreadsheet that aren't needed

3. Save the XLS in CSV format

4. Write a FOR statement to assign each token in the CSV as a variable (68 lines, 4 tokens on each line, all tokens are different, each line needs to be used for variables for a separate output that will create separate scripts using each line of variables).

=================================================================================

I was able to get this to work, but to only to an extent. The output only uses the last line (68th line) as variables and echoes what I need. Instead of having 68 different outputs, I only get 1 output, which is the last set of tokens being used as variables.

==================================================================================

CSV format looks like this:

TechID,pwhash1,pwhash2,Email

==================================================================================

Script looks like this:

@echo off

goto main

:main
setlocal enabledelayedexpansion

set path=C:\techlogin.csv
set output=c:\script.txt

for /f "tokens=1,2,3,4 delims=," %%a in (!path!) do (
        set 1=%%a
        set 2=%%b
        set 3=%%c
        set 4=%%d

    )

    echo. >>c:\Script.txt
    echo. >>c:\Script.txt
    echo use registry >>c:\Script.txt
    echo insert into techlogin (entrydate, RootCommunityID, TechID, pwhash1, pwhash2, failedattempts, expiration, IsPasswordCaseSensitive, email) >>c:\Script.txt
    echo values (GetDate(), -1, '!1!', !2!, !3!, 0,(GetDate() +360), 1, '!4!') >>c:\Script.txt
    echo. >>c:\Script.txt
    echo. >>c:\Script.txt
    echo insert into techpermission >>c:\Script.txt
    echo (TechID,RootCommunityID,CommunityID,Keys,Refund,Refresh,Permissions,AgentConfigurationAdmin,Asset,ImportBrandedAgent,ChangeTechPasswords,SuperPassword,CDOrder,OperatorKnowledge,AccessOnlineAccounts,Community,MoveAccounts,AccountChangeUser,ModifyBandwidthThrottling,RemoteAssist,Reports,ArchiveDelete,Reserve,Messages,AllocateLicenses,StatusMessage,AccountStatus,Billing,ServiceEvents,AgentConfiguration,ConnectedInternal,TapeGroup,Passwords,AccountAgentConfiguration,Policy,ClientDownload,Scripting,Communities,LDAPServer,LoginPermission) >>c:\Script.txt
    echo values ('!1!',-1,-1,-1647228430,0,1,1,0,0,1,1,1,1,0,1,0,1,1,1,0,1,0,1,0,1,1,1,0,1,1,0,1,1,1,0,0,1,1,1,0) >>c:\Script.txt
    echo.
    echo.
    echo.

endlocal
eof

==================================================================================

OUTPUT:

use registry

insert into techlogin (entrydate, RootCommunityID, TechID, pwhash1, pwhash2, failedattempts, expiration, IsPasswordCaseSensitive, email) values (GetDate(), -1, 'John Doe', -2020462407, 1791026935, 0,(GetDate() +360), 1, 'john.doe@internet.com')

insert into techpermission (TechID,RootCommunityID,CommunityID,Keys,Refund,Refresh,Permissions,AgentConfigurationAdmin,Asset,ImportBrandedAgent,ChangeTechPasswords,SuperPassword,CDOrder,OperatorKnowledge,AccessOnlineAccounts,Community,MoveAccounts,AccountChangeUser,ModifyBandwidthThrottling,RemoteAssist,Reports,ArchiveDelete,Reserve,Messages,AllocateLicenses,StatusMessage,AccountStatus,Billing,ServiceEvents,AgentConfiguration,ConnectedInternal,TapeGroup,Passwords,AccountAgentConfiguration,Policy,ClientDownload,Scripting,Communities,LDAPServer,LoginPermission) values ('John Doe',-1,-1,-1647228430,0,1,1,0,0,1,1,1,1,0,1,0,1,1,1,0,1,0,1,0,1,1,1,0,1,1,0,1,1,1,0,0,1,1,1,0)

==================================================================================

Here is what I need:

Output is correct, but is only outputting the variables for the last set of tokens. I want to output this for each line (or set) of tokens and input those variables into the outputted script. This would then give me 68 different scripts.

ALL HELP IS GREATLY APPRECIATED!

Answer 1

You should never use PATH as your own variable. It is reserved to hold paths to your installed software. Setting your own value like that could break a script later on. It wouldn't cause a problem in your script, but if you get in the habit of abusing PATH, then you will eventually run into problems.

Why are you transferring the FOR variables to environment variables? Just use the FOR variables directly in your output statements.

I put an extra set of parentheses around the entire FOR loop so that I only need to redirect once. This is much faster than redirecting each line. It takes time to open the file and position the file pointer.

I changed echo. to echo(. It looks weird, but it is more reliable. echo. can fail under certain obscure scenarios.

@echo off
setlocal

set "input=C:\techlogin.csv"
set "output=c:\script.txt"

(for /f "usebackq tokens=1-4 delims=," %%A in ("%input%") do (
  echo(
  echo(
  echo use registry
  echo insert into techlogin (entrydate, RootCommunityID, TechID, pwhash1, pwhash2, failedattempts, expiration, IsPasswordCaseSensitive, email^)
  echo values (GetDate(^), -1, '%%~A', %%~B, %%~C, 0,(GetDate(^) +360^), 1, '%%~D'^)
  echo(
  echo(
  echo insert into techpermission
  echo (TechID,RootCommunityID,CommunityID,Keys,Refund,Refresh,Permissions,AgentConfigurationAdmin,Asset,ImportBrandedAgent,ChangeTechPasswords,SuperPassword,CDOrder,OperatorKnowledge,AccessOnlineAccounts,Community,MoveAccounts,AccountChangeUser,ModifyBandwidthThrottling,RemoteAssist,Reports,ArchiveDelete,Reserve,Messages,AllocateLicenses,StatusMessage,AccountStatus,Billing,ServiceEvents,AgentConfiguration,ConnectedInternal,TapeGroup,Passwords,AccountAgentConfiguration,Policy,ClientDownload,Scripting,Communities,LDAPServer,LoginPermission^)
  echo values ('%%~A',-1,-1,-1647228430,0,1,1,0,0,1,1,1,1,0,1,0,1,1,1,0,1,0,1,0,1,1,1,0,1,1,0,1,1,1,0,0,1,1,1,0^)
  echo(
  echo(
  echo(
)) >"%output%"

endlocal
exit /b

Answer 2

So, I was able to use a FINDSTR to search for tokens that have been assigned the variables and copy over all tokens that haven't been assigned as a variable to another CSV file. I then setup secondary subroutines to alternate back and forth between the newly created CSV file and the original CSV file to use AND assign the tokens as variables. I now get the results that I need.

I understand why this is working, but I DO NOT understand why it was NOT working with my original method. Anyhow, this works out quite wonderfully.

Also, I used a ping >NULL to give the program enough time to create the new file and alternate back and forth, so there is NO maximum setlocal recursion level reachedsince there are so many variables that can be set.

Here is what I did with the code:

@echo off

goto :main

:main
setlocal enabledelayedexpansion

    set path=C:\techlogin.csv
    set output=c:\script.txt

    for /f "tokens=1,2,3,4 delims=," %%a in (!path!) do (
        set a=%%a
        set b=%%b
        set c=%%c
        set d=%%d

        goto :script

    )

:script

    echo. >>c:\Script.txt
    echo use registry >>c:\Script.txt
    echo insert into techlogin (entrydate, RootCommunityID, TechID, pwhash1, pwhash2, failedattempts, expiration, IsPasswordCaseSensitive, email) >>c:\Script.txt
    echo values (GetDate(), -1, '!a!', !b!, !c!, 0,(GetDate() +360), 1, '!d!') >>c:\Script.txt
    echo. >>c:\Script.txt
    echo. >>c:\Script.txt
    echo insert into techpermission >>c:\Script.txt
    echo (TechID,RootCommunityID,CommunityID,Keys,Refund,Refresh,Permissions,AgentConfigurationAdmin,Asset,ImportBrandedAgent,ChangeTechPasswords,SuperPassword,CDOrder,OperatorKnowledge,AccessOnlineAccounts,Community,MoveAccounts,AccountChangeUser,ModifyBandwidthThrottling,RemoteAssist,Reports,ArchiveDelete,Reserve,Messages,AllocateLicenses,StatusMessage,AccountStatus,Billing,ServiceEvents,AgentConfiguration,ConnectedInternal,TapeGroup,Passwords,AccountAgentConfiguration,Policy,ClientDownload,Scripting,Communities,LDAPServer,LoginPermission) >>c:\Script.txt
    echo values ('!a!',-1,-1,-1647228430,0,1,1,0,0,1,1,1,1,0,1,0,1,1,1,0,1,0,1,0,1,1,1,0,1,1,0,1,1,1,0,0,1,1,1,0) >>c:\Script.txt
    echo. >>c:\Script.txt
    echo. >>c:\Script.txt

    goto :csv

:csv

    Type !path! | findstr /I /V /c:"!a!" >c:\techlogin1.csv
    ping 127.0.0.1 -n 2 -w 1000 >nul

    goto :second main


endlocal
eof


:second main
setlocal enabledelayedexpansion

    set path=C:\techlogin1.csv
    set output=c:\script.txt

    for /f "tokens=1,2,3,4 delims=," %%a in (!path!) do (
        set a=%%a
        set b=%%b
        set c=%%c
        set d=%%d

        goto :second script

    )

:second script

    echo. >>c:\Script.txt
    echo use registry >>c:\Script.txt
    echo insert into techlogin (entrydate, RootCommunityID, TechID, pwhash1, pwhash2, failedattempts, expiration, IsPasswordCaseSensitive, email) >>c:\Script.txt
    echo values (GetDate(), -1, '!a!', !b!, !c!, 0,(GetDate() +360), 1, '!d!') >>c:\Script.txt
    echo. >>c:\Script.txt
    echo. >>c:\Script.txt
    echo insert into techpermission >>c:\Script.txt
    echo (TechID,RootCommunityID,CommunityID,Keys,Refund,Refresh,Permissions,AgentConfigurationAdmin,Asset,ImportBrandedAgent,ChangeTechPasswords,SuperPassword,CDOrder,OperatorKnowledge,AccessOnlineAccounts,Community,MoveAccounts,AccountChangeUser,ModifyBandwidthThrottling,RemoteAssist,Reports,ArchiveDelete,Reserve,Messages,AllocateLicenses,StatusMessage,AccountStatus,Billing,ServiceEvents,AgentConfiguration,ConnectedInternal,TapeGroup,Passwords,AccountAgentConfiguration,Policy,ClientDownload,Scripting,Communities,LDAPServer,LoginPermission) >>c:\Script.txt
    echo values ('!a!',-1,-1,-1647228430,0,1,1,0,0,1,1,1,1,0,1,0,1,1,1,0,1,0,1,0,1,1,1,0,1,1,0,1,1,1,0,0,1,1,1,0) >>c:\Script.txt
    echo. >>c:\Script.txt
    echo. >>c:\Script.txt

    goto :second csv

:second csv

    Type !path! | findstr /I /V /c:"!a!" >c:\techlogin.csv
    ping 127.0.0.1 -n 2 -w 1000 >nul

    goto :main

endlocal
eof

Answer 3

move the single ) down just before endlocal

What you do:

for each line set the variables, overwriting the old ones

When you are finished, you continue with your code with the last set of variables.

Extending the block (- ) will execute the code between for every set of variables.

(I think that was your intention, as you are already using delayed expansion)

By the way: do NOT use numbers as variable names! You don't need them anyway:

@echo off

goto main

:main
setlocal enabledelayedexpansion

set path=C:\techlogin.csv
set output=c:\script.txt

for /f "tokens=1,2,3,4 delims=," %%a in (!path!) do (

    echo. >>c:\Script.txt
    echo. >>c:\Script.txt
    echo use registry >>c:\Script.txt
    echo insert into techlogin (entrydate, RootCommunityID, TechID, pwhash1, pwhash2, failedattempts, expiration, IsPasswordCaseSensitive, email) >>c:\Script.txt
    echo values (GetDate(), -1, '%%a', %%b, %%c, 0,(GetDate() +360), 1, '%%d') >>c:\Script.txt
    echo. >>c:\Script.txt
    echo. >>c:\Script.txt
    echo insert into techpermission >>c:\Script.txt
    echo (TechID,RootCommunityID,CommunityID,Keys,Refund,Refresh,Permissions,AgentConfigurationAdmin,Asset,ImportBrandedAgent,ChangeTechPasswords,SuperPassword,CDOrder,OperatorKnowledge,AccessOnlineAccounts,Community,MoveAccounts,AccountChangeUser,ModifyBandwidthThrottling,RemoteAssist,Reports,ArchiveDelete,Reserve,Messages,AllocateLicenses,StatusMessage,AccountStatus,Billing,ServiceEvents,AgentConfiguration,ConnectedInternal,TapeGroup,Passwords,AccountAgentConfiguration,Policy,ClientDownload,Scripting,Communities,LDAPServer,LoginPermission) >>c:\Script.txt
    echo values ('%%a',-1,-1,-1647228430,0,1,1,0,0,1,1,1,1,0,1,0,1,1,1,0,1,0,1,0,1,1,1,0,1,1,0,1,1,1,0,0,1,1,1,0) >>c:\Script.txt
    echo.
    echo.
    echo.

 )

endlocal