Read files in directory in order of filename prefix with batch?

Question

I have a number of SQL scripts in a directory, prefixed with a number, e.g.

Currently, I'm able to loop through them with this script:

@ECHO OFF
FOR /r . %%F IN (*.sql) DO (
    ECHO File:"%%F"
)

However, because of the way that Windows reads files in a directory, 10 and 11 are read before 2, etc.:

How can I loop through each file in the directory, in order of the prefix?

Answer 1

The Batch file below insert a leading zero in filenames that need it.

@echo off
setlocal EnableDelayedExpansion
for /F "delims=" %%f in ('dir /B *.sql') do (
   set "filename=%%f"
   if "!filename:~1,1!" equ "-" ren "%%f" "0%%f"
)

EDIT: New solution added

The Batch file below show the files in right order without rename them.

@echo off
setlocal EnableDelayedExpansion
rem Create an array with filenames in right order
for %%f in (*.sql) do (
   for /F "delims=-" %%n in ("%%f") do (
      set "number=00000%%n"
      set "file[!number:~-6!]=%%f"
   )
)
rem Process the filenames in right order
for /F "tokens=2 delims==" %%f in ('set file[') do (
   echo %%f
)

Answer 2

Alternative approach!

This isn't a direct answer to your question, but may achieve same result:

FOR /L %%i IN (1,1,11) DO FOR %%f IN (%%i-*.sql) DO ECHO %%f

Answer 3

As far as I see there is no FOR flag for that.

BUT here is pure PowerShell solution using some kind of weirdish Regular Expressions.

Or You can list files, sort them, put those sorted paths to the file in correct order and then read.

3th solution would be trying to build some sorting function using standart Windows Shell commands and operators.

Answer 4

EDIT - New simple solution using JSORT.BAT

The native batch SORT command is extremely limited. I have written a convenient JSORT.BAT sorting utility that offers many convenient features, including the ability to sort strings with numbers based on the numeric value. JSORT.BAT is pure script (hybrid JScript/batch) that runs natively on any Windows machine without the need of any 3rd party exe files.

You can list the files numerically using

dir /b /a-d *.sql^|jsort /n /i

Simply put the command in a FOR /F loop if you want to iterate the results

for /f "delims=" %%F in ('dir /b /a-d *.sql^|jsort /n /i') do echo %%F

Full documentation is available from the command line using jsort /?.

Original solution that predates JSORT.BAT

It is possible to do what you want with batch without modifying the file names, and it is farily efficient if you use a hybrid JScript/batch utility called REPL.BAT that performs a regex search and replace on stdin and writes the result to stdout. The utility is pure script that runs on any Windows machine from XP onward - no 3rd party executable required. Full documentation is embedded within the script.

Assuming REPL.BAT is in your current directory, or better yet, somewhere within your PATH, then the following will iterate .sql files that start with a number in the desired sort order. As written, it supports numbers up to 5 digits long. It can easily be extended to support larger numbers.

@echo off
for /f "tokens=2 delims=:" %%F in (
  'dir /b /a-d *.sql^|repl "^(\d+).*" "00000$1:$&" a^|repl ".*(\d{5}:)" "$1"^|sort'
) do echo %%F

This really shows the power of pipes :)

The complex IN() clause works as follows:

1. List all .sql files
2. Look for files that begin with a digit, and replace each matching name with the leading number prefixed with five leading zeros, followed by a colon, followed by the original name
3. Replace the leading number, preserving only the last 5 digits
4. Sort the result

The parent FOR /F then reads the result, parsing out the original file names as the second : delimited token.

Answer 5

Rename your scripts with leading 0's as needed so that you always have the same number of digits in the prefix.