Storing information from input file in a batch file variable


Let my preface this by saying I have no experience with writing batch files at all so go easy on me. What I'm currently trying to do is read a single line from a .txt file and store that line into a variable in a batch file.

I'm using the following syntax to try to set the variable value:

set /p varname=<tempres.txt

I'm then trying to append the value of this variable to an output file name (within an osql statement) as follows:

osql -E -S .\[i]instance,port[/i] -d msdb -h-1 -n -w1000 -i "D:\batchtest\tables.sql" -o "D:\batchtest\newsqlexport_D%date:~-4,4%%date:~-7,2%%date:~-10,2%_T%time:~0,2%%time:~3,2%%time:~6,2%%time:~9,2%_0%_0%varname%"

Open in new window

The output file creates successfully and has everything in the file name that it should up to the "_0". What am I doing wrong that the value of varname isn't being appended to the file name?

Thanks in advance for the help!
Who is Participating?

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

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.

David Johnson, CD, MVPOwnerCommented:
--- start of batch --
set  /p varname= %1
osql -E -S .\instance,port -d msdb -h-1 -n -w1000 -i "D:\batchtest\tables.sql" -o "D:\batchtest\newsqlexport_D%date:~-4,4%%date:~-7,2%%date:~-10,2%_T%time:~0,2%%time:~3,2%%time:~6,2%%time:~9,2%_0%_0%varname%"
--- end of batch --
mybatch < testme.txt
Hi bluegrasscell

The   _0   (that's a zero) you are referring to has been used twice at the end of that long command which creates the file name, and it has a % sign that seems to be misplaced:


Here's the complete section that concerns the creation of the file name, and it has been split into each different element:

%_0 <-------------------------------- *****

That % symbol in   %_0   shouldn't really be there because it does nothing.

There are lots of ways to get the current date from the built-in %DATE% variable, but if these commands executed in a "DOS" window:
echo _D%date:~-4,4%%date:~-7,2%%date:~-10,2%
echo _T%time:~0,2%%time:~3,2%%time:~6,2%%time:~9,2%
accurately reflects what you need, then you could make the final command line that creates the new file a little shorter like this:
set OutFile=D:\batchtest\newsqlexport
set CurrDate=%date:~-4,4%%date:~-7,2%%date:~-10,2%
set CurrTime=%time:~0,2%%time:~3,2%%time:~6,2%%time:~9,2%

osql <command params here> "%OutFile%_D%CurrDate%_T%CurrTime%_0%varname%"

Open in new window

That's just aesthetic though.

There are also quite a few ways to parse a text file.  You say that the text file has just one line?

Try this and see what happens.  If it correctly displays the command on the screen, then remove the   echo  from the start of the osql command:
@echo off

set TmpFile=tempres.txt
set InFile=D:\batchtest\tables.sql
set OutFile=D:\batchtest\newsqlexport
set CurrDate=%date:~-4,4%%date:~-7,2%%date:~-10,2%
set CurrTime=%time:~0,2%%time:~3,2%%time:~6,2%%time:~9,2%
set OsqlParams=-E -S .\[i]instance,port[/i] -d msdb -h-1 -n -w1000 -i 

if not exist "%TmpFile%" (
    echo %TmpFile% not found.
    echo Press any key to quit ...
    pause > nul
    goto :EOF

if not exist "%InFile%" (
    echo %InFile% not found.
    echo Press any key to quit ...
    pause > nul
    goto :EOF

for /f "tokens=*" %%A in ('type %TmpFile%') do set varname=%%A

echo osql %OsqlParams% "%InFile%" -o "%OutFile%_D%CurrDate%_T%CurrTime%_0%varname%"


Open in new window

NOTE: The above will not work if the "tempres.txt" file contains more than one line.
NOTE: I have not tested this.


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
bluegrasscellAuthor Commented:
I didn't test the first answer posted, but this one went above and beyond. Not only did my original question get answered, but I got a few extra tidbits for making the file better overall.
Thank you bluegrasscell.  I hope it does work for you.

I don't have the OSQL program to test this with, so all I could do was create a text file with one line of text (containing spaces, backslashes, forward slashes, and some other characters that might prove to be problematic down the line), and then look at the echoed command line to see if the variables were expanded accurately again at runtime.

Setting the variables up at the top of a batch file is really just a good way of having a batch file where you only have to change a value in one line rather than change it in several places throughout a large and complex batch file.  In your case you just have one command, so it's hardly a necessity to do this.  It might prove useful if you ever extend that batch file, or certainly for any future ones you may write that use a file path, text string, or something else  which is repeated several times and would be better storing as a variable for later re-use.

If you wondered why I used   set CurrDate=   and   set CurrTime=   as opposed to Date and Time, it's because you should never use the names of any of system or user variables that already exist.  You can see these by opening a CMD window and just typing   SET

Be aware that by setting the %CurrTime% variable at the start of the batch file, the time at that moment is stored.  If the batch file did other things that took a few seconds or longer before getting to the point where the %CurrTime% variable is used, and if this "wrong time" could be important, then it would be better to get the time at the point of execution rather than the one already stored.  You can do this either by not setting the %CurrTime% variable at the start of the batch file and just using the variable as the command is executed, or you could use something called Delayed Variable Expansion
whereby you would use !CurrTime! instead of %CurrTime%.  That's another topic though, and I would assume that a second or two variance in the name of the file will not really matter.

There is just one thing that I would normally tend to specify in a batch file.  In this case it is assumed that the file "tempres.txt" is in the same directory as the batch file, and therefore does not need to be used with the path to it.  I usually specify the path rather than leave it to chance, and to get the current directory of the batch file wherever it happens to be, I use the variable  %~dp0  (that's a zero) which is just a modification of   %0

%0 will always contain the fully qualified path to the batch file when it runs.
%1 (as was being used in ve3ofa's suggestion) will contain the first parameter passed to the batch file
%2 will contain the 2nd parameter passed to the batch file.

By inserting a  ~  after the % you can then use a number of variable modifiers which you will see if you open a CMD window and type   FOR /?   and then page down right to the end.  In this case  %~dp0  will be expanded to only the Drive Letter (the dmodifier) and the Path (the p modifier) of the batch file, but without the file name of the batch file.  Unfortunately it is suffixed by a trailing backslash, but this can be eliminated in exactly the same way as you modified your %Date% and %Time% variables by choosing where to start and how many characters to include (in this case start at the first character from the left of what's stored in the variable and move in one from the rightmost character):

set CurrDir=%~dp0
set CurrDir=%CurrDir:~0,-1%
set TmpFile=%CurrDir%\tempres.txt

It leaves less to chance if there happened to be a file of that name already in some other recognised path.

End of tutorial ;-)
Glad you found the extra little bits of interest.

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
Microsoft Legacy OS

From novice to tech pro — start learning today.