Batch file variable automation

Dell / OPTIPLEX 330
April 30, 2009 at 11:55:30
Specs: Microsoft Windows XP Professional, 1.595 GHz / 1012 MB
Hi,

I would like to take in variable from a text file and execute them in the batch file till it is over

Below is the batch file that I use:

@echo OFF
set /p requestid=Type requestid :
set /p itemid=Type itemid :
set /p plantid=Type plantid :
osql -Uuser -Ppassword -Sserver -Q "update imdmextendeditemtbl set status=1 where itemid=%itemid% and plantid=%plantid%"
osql -Uuser -Ppassword -Sserver -Q "update imdmrequestusertbl set statusdesc='Approved', isactive=0 where requestid=%requestid%"
osql -Uuser -Ppassword -Sserver -Q "update imdmreqtbl set status=1 where requestid=%requestid%"
pause

The above batch file run a update query on a database.


I saw this batch file from one of the forums.It is able to read lines from the file. However what I would like to do is read many variables so that the same step can be reapeted.


@echo off
setLocal EnableDelayedExpansion

for /f "tokens=* delims= " %%a in (variables.txt) do (
set /a N+=1
set v!N!=%%a
)
set hostname=!v1!
set backupdrive=!v2!
set backupdir=!v3!
set xbackupdir=!v4!

echo %hostname%
echo %backupdrive%
echo %backupdir%
echo %xbackupdir%
pause


Forexample the input file format woudl be :

requestid itemid plantid
1 2 3
5 6 7

Could some one please help me on this.


See More: Batch file variable automation

Report •


#1
April 30, 2009 at 14:04:01
@echo off & setlocal EnableDelayedExpansion
for /F "tokens=1-3" %%a in (Variables.txt) do (
  set requestid=%%a
  set itemid=%%b
  set plantid=%%c
  osql -Uuser -Ppassword -Sserver -Q "update imdmextendeditemtbl set status=1 where itemid=!itemid! and plantid=!plantid!"
  osql -Uuser -Ppassword -Sserver -Q "update imdmrequestusertbl set statusdesc='Approved', isactive=0 where requestid=!requestid!"
  osql -Uuser -Ppassword -Sserver -Q "update imdmreqtbl set status=1 where requestid=!requestid!"
  echo.  Query by requestid=%%a Itemid=%%b Plantid=%%c
)

If you want to insert a header line at the beginning of your Variables.txt file as comment, i.e.

requestid  itemid  plantid

replace "tokens=1-3" with "skip=1 tokens=1-3".

Report •

#2
April 30, 2009 at 14:19:05
Thanks thats awesome. Will try it. The variables.txt file would be comma seperate. Would that make any difference?

Does the order of the columns matter?

Could you please explain what this does :

for /f "tokens=* delims= " %%a in (variables.txt) do (
set /a N+=1
set v!N!=%%a
)

I havent tested your code but could you please explain it too.

Thanks a ton for your help!!!


Report •

#3
April 30, 2009 at 14:44:00
In Italy now time is late night so I'll answer you extensively tomorrow; for now YES the order of columns matters and to have them separated by commas replace

"tokens=1-3"

with

"tokens=1-3 delims=,"

First colum is assigned to internal variable %%a, the second to %%b and so on...

Let me know if you need other explanations (excluding the above which I'll take into account tomorrow as I said).


Report •

Related Solutions

#4
April 30, 2009 at 15:18:16
Just had the query about tokens and how you are assigning variables using !plantid!

I guess that would be answered when you explain the code to me.

I will be on vacation from tommorrow till the 11th of May and will not have access to a computer.

Look forward to your reply and thank you very much for your help.

Have a good day.


Report •


Ask Question