CODEWITHSUNDEEP
windowsbatch-filesqlcmd
S. Guillaume
S. Guillaume

Reputation: 67

read each lines of a file - Batch script

I've to write a batch script. The script read the lines of an input files and for each line execute a sqlcmd. But when i execute my script it's doesn't work like i want, in fact it's doesn't make the job...

My batch script :

    @echo off

SETLOCAL EnableDelayedExpansion

set Chemin=C:\users\documents\Communication\Test\MSG_IN

 set NomFichierU=UHMSO_1
 set user=admin
 set pwd=admin
 set db=DTBASE_001

cd /d %Chemin%
set nomfic=%NomFichierU%
set HHMMSS=A
set HHMMSS=%LTIME:~0,2%%LTIME:~3,2%%LTIME:~6,2%
for %%f IN (%NomFichierU%*.txt) DO (
    for /f tokens^=*^ delims^=^ eol^= %%l IN (%%f) DO (
      echo test
      REM echo %%l
      ECHO %%G
      set nomficr=%%a 
      set nomfic=!nomficr:~0,-1!.tmp
      REN %%a !nomfic!
      rem executer sript MHUHMS.sql avec sqlplus
      sqlcmd -S MILCS02 -U %user% -P %pwd% -d %db% -i c:\users\documents\SQL\MHUHMS.sql
      move !nomfic! SLD_SLDHI\SAVE
    )
)

And the MHUHMS.SQL just make update in database. My problem is when i'm execute the batch script, it's just place me into the folder 'Chemin', but i want it read the file like UHMSO_1_XXXXX.txt. And after, for each lines of my files, it runs my sql script.

My error at this moment : The system cannot find the file UHMSO_1*.txt.

And i'm palced in the folder...

If someone have any idea, because i'm not really good in batch script... and i don't know too if my sqlcmd is correct or not...

Thank's for help !

EDIT: After help from wOxxOm & MichaelS.

Upvotes: 0

Views: 611

Answers (2)

woxxom
woxxom

Reputation: 73506

Okay, after troubleshooting it in the chat discussion let's sum up the issues:

  • for /f can't be used with wildcards. Enumerate the files in an additional outer for loop and use the loop variable in for /f
  • the text files were in UTF-16 encoding, use for ... ('type "filename"') ...
  • sqlcmd can only see normal batch variables, not loop variables like %%l so pass it with -v
  • all lines with nomfic, %%a and %%G aren't needed
  • use cd /d c:\some\path to actually change the working drive AND path.
    Or better even pushd c:\some\path + popd to save and restore the previous working folder.
  • move the file only after having processed all its lines
  • SETLOCAL EnableDelayedExpansion isn't needed for the posted code fragment
set Chemin=C:\users\documents\Communication\Test\MSG_IN
set NomFichierU=UHMSO_1
set HHMMSS=%TIME:~0,2%%TIME:~3,2%%TIME:~6,2%

pushd %Chemin%
for /f "delims=" %%f IN ('dir /b "%NomFichierU%*.txt"') DO (
    for /f tokens^=*^ delims^=^ eol^= %%l IN ('type "%%f"') DO (
        sqlcmd -S MILCS02 -U %user% -P %pwd% -d %db% ^
               -i c:\users\documents\SQL\MHUHMS.sql -v l ="%%l"
    )
    move "%%f" "SLD_SLDHI\SAVE\%%f.%HHMMSS%"
)
popd
pause

P.S. In case the file names can contain spaces use usebackq and quote the variable:

for /f usebackq^ tokens^=*^ delims^=^ eol^= %%l IN ("%%f") DO (

Upvotes: 1

MichaelS
MichaelS

Reputation: 6032

I'm not sure if there are more errors in the code but one is definitely that you are missing SETLOCAL EnableDelayedExpansion. You are using the delayed expansion (e.g. !nomfic!) but !...! is useless without SETLOCAL EnableDelayedExpansion at the beginning of your script.

Upvotes: 2

Related Questions