Sending sqlcmd output file to directory that is a variable

Question

Currently, the following code in a batch file works: it runs all SQL scripts in %SCRIPTFOLDER% and sends each script's output to a CSV within the same folder as the scripts.

FOR /F "tokens=*" %%S IN (
    'DIR /B "%SCRIPTFOLDER%\*.sql" '
) DO (
    sqlcmd -b -S %INSTANCE% -d %DATABASE% -i "%%~fS" -s "|" -o "%%~dpnS.csv" -W
    IF ERRORLEVEL 1 GOTO errorhandling
    ECHO %%~nS.csv successfully created
)

What I'd like to do is allow the user to specify where the generated CSVs get sent to using a variable %OUTPUTFOLDER%.

I tried placing %OUTPUTFOLDER%, which is a full path, drive, and folders (e.g. D:\some folder\output) in various positions within %%~dpnS.csv. Specifically,

%%~dp%OUTPUTFOLDER%nS.csv

and

%%~dpn%OUTPUTFOLDER%S.csv

but they didn't work and I'm (probably obviously to you) woefully inept at batch file syntax!

I understand that dp is the drive and path and that S is the file name, but I'm not sure how to integrate that with a the variable that is the path.

user6811411 · Accepted Answer · 2018-06-07 17:38:24Z

2

The iterating variable is %%S, the modifier ~dpn forces an evaluation of drive path and name.

In this case you want to specify the drive and path yourself so depending on wether %OUTPUTFOLDER% has a trailing backslash

-o "%OUTPUTFOLDER%%%~nS"

or not use:

-o "%OUTPUTFOLDER%\%%~nS"

answered Jun 7, 2018 at 17:38

user6811411

Sign up to request clarification or add additional context in comments.

2 Comments

marky Over a year ago

I was in the middle of answering my own question when you posted this. I did a LOT of trial and error, but finally came to the solution in your answer. The one with the slash added is what I needed. Thanks!

aschipfl Over a year ago

If in doubt add the \ in between, because double backslashes do not disturb, but a missing one does...

Collectives™ on Stack Overflow

Sending sqlcmd output file to directory that is a variable

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related