bluegrasscell
asked on
Storing information from input file in a batch file variable
Hello,
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:
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!
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%"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!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
http://ss64.com/nt/setlocal.html
http://ss64.com/nt/delayedexpansion.html
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.
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.
Bill
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
http://ss64.com/nt/setlocal.html
http://ss64.com/nt/delayedexpansion.html
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.
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.
Bill
--- 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
--- end of batch --
mybatch < testme.txt