for loop to get substring of filename

[warrentolentino]

i have this small piece of code that is not working. basically i need to extract the filename without the extension and make it look like only the first 20 characters.

e.g.
sanbox_BULKLOAD_EXPORT.sql

output:
sanbox_BULKLOAD_EXPO

this is because the application interface when i will move the file only accepts 20 characters in the filename.

this is the code that is not working that i am using in a batch file sample4.bat:

Code: Select all

cls
FOR %%i IN ("*.sql") DO (SET fn=%%~ni SET h=%fn:~1,20% echo %h)

these are the input:

Code: Select all

query01.sql
query02.sql
query03.sql
query04.sql
query04.~sql
query1.sql
query2.sql
query3.sql
query4.sql
sample4.bat
sanbox_BULKLOAD_EXPORT.sql

this is the output when the sample4.bat is run:

Code: Select all

R:\temp>(SET fn=final_flag_m123 SET h=anbox_BULKLOAD_EXPOR echo h )

R:\temp>(SET fn=fix_county_franklin SET h=anbox_BULKLOAD_EXPOR echo h )

R:\temp>(SET fn=query01 SET h=anbox_BULKLOAD_EXPOR echo h )

R:\temp>(SET fn=query02 SET h=anbox_BULKLOAD_EXPOR echo h )

R:\temp>(SET fn=query03 SET h=anbox_BULKLOAD_EXPOR echo h )

R:\temp>(SET fn=query04 SET h=anbox_BULKLOAD_EXPOR echo h )

R:\temp>(SET fn=query1 SET h=anbox_BULKLOAD_EXPOR echo h )

R:\temp>(SET fn=query2 SET h=anbox_BULKLOAD_EXPOR echo h )

R:\temp>(SET fn=query3 SET h=anbox_BULKLOAD_EXPOR echo h )

R:\temp>(SET fn=query4 SET h=anbox_BULKLOAD_EXPOR echo h )

R:\temp>(SET fn=sanbox_BULKLOAD_EXPORT SET h=anbox_BULKLOAD_EXPOR echo h )

please help. thank you.

[Squashman]

Code: Select all

@echo off
setlocal enabledelayedexpansion
cls
FOR %%I IN ("*.sql") DO (
   SET fn=%%~nI
   SET h=!fn:~0,20!
   echo !h!
)

[warrentolentino]

thank you that works.

[warrentolentino]

if i want to run it from the command line without using a batch file how do i do it? so i can just use run the code from a job scheduler without having to call the batch file. thanks.

i run the code in command line and i am getting this error:

Code: Select all

R:\temp>FOR %%I IN ("*.sql") DO (SET fn=%%~nI SET h=!fn:~0,20! echo !h!)
%%I was unexpected at this time.

R:\temp>

thank you.

[ShadowThief]

The command line expects FOR variables in the format %A, while batch files expect FOR variables in the format %%A.

[Squashman]

Will also need to turn on delayed expansion as well. So you will need to run the batch file with CMD.exe and specify the appropriate switches to turn on delayed expansion.