CODEWITHSUNDEEP
for-loopbatch-filecmd
Nick
Nick

Reputation: 3

Spurious spaces in output of for loop

I'm trying to use a batch file to convert a file containing sql code into a single environment variable for use with the MSSQL utility bcp. For example, if InFile.sql contains

-- This is a simple statement
SELECT *
    FROM table

The output of ECHO %query% should be SELECT * FROM people

The code below works for me most of the time

SETLOCAL=ENABLEDELAYEDEXPANSION 
:: Replace VarOld with VarNew
FOR /f "delims=" %%a IN ('TYPE InFile.sql') DO ( SET line=%%a & ECHO !line:table=people! >> TmpFile1 )

:: Remove comment lines starting with  '-'  and remove newline characters
(FOR /f "eol=- delims=" %%a in (TmpFile1) DO SET/p=%%a ) <nul >TmpFile2

:: Create variable 'Query'
FOR /f "delims=" %%a IN ('TYPE TmpFile2') DO SET query=%%a

however, the first FOR loop adds 3 space characters at the end of each line and the second FOR loop adds another space character so the result is SELECT * FROM people

I could cope with the additional spaces (although the purist in me wasn't happy!) until I had to use it with a long SQL query and multiple replacement steps - every line in the file was having 12 space characters added. The additional spaces are enough to make the resulting query around 8300 characters long - too much for Windows' 8196 character limit for a batch file line.

Can anybody see how I can remove these spurious spaces?

Upvotes: 0

Views: 1432

Answers (2)

Magoo
Magoo

Reputation: 80033

The fundamental issue is that trailing spaces ARE significant in SET statements and ECHO statements before the redirectors.

In your code, you need to remove the spaces after %%a and people! in the first FOR Thus:

FOR /f "delims=" %%a IN ('TYPE InFile.sql') DO (SET line=%%a&ECHO !line:table=people!>> TmpFile1)

The next problem is a little more subtle. In

(FOR /f "eol=- delims=" %%a in (TmpFile1) DO SET/p=%%a ) <nul >TmpFile2

the space following /p=%%a is REQUIRED because it provides the separator between the text taken from the lines when building TmpFile2 - and that leads to a superfluous trailing space. Try replacing that space with a Q for instance - just for testing.

Hence, you need to delete the final space from QUERY after it's been constructed in your final FOR

SET query=%query:~0,-1%

Upvotes: 0

rojo
rojo

Reputation: 24466

Using tokens=* in a for loop should trim whitespace as you're capturing a line of infile.sql. Here's a proof of concept, echoing %query% contained within quotation marks to illustrate the trimming:

@echo off
setlocal enabledelayedexpansion

set query=
if "%~1"=="" goto usage
if not exist "%~1" goto usage

for /f "usebackq eol=- tokens=*" %%I in ("%~f1") do (
    set "sub=%%I"
    set query=!query! !sub:table=people!
)

:: strip the leading space from %query%
echo "%query:~1%"
goto :EOF

:usage
echo Usage: %~nx0 sqlfile

Example output:

C:\Users\me\Desktop>type infile.sql
-- This is a simple statement
SELECT *
    FROM table

C:\Users\me\Desktop>test.bat infile.sql
"SELECT * FROM people"

Upvotes: 1

Related Questions