CODEWITHSUNDEEP
windowsbatch-file
Justin Garrick
Justin Garrick

Reputation: 14947

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

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

enter image description here

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.:

enter image description here

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

Upvotes: 6

Views: 8138

Answers (7)

snipsnipsnip
snipsnipsnip

Reputation: 2585

An example to use powershell within a batch to sort:

FOR /f "usebackq" %%F IN (
  `powershell "ls -r *.sql | sort { [int]($_.Name -replace '\D','') } | %% { $_.FullName }"`
) DO (
    ECHO File:"%%F"
)
  • ls -r *.sql (short form of Get-ChildItem -Recurse) corresponds to FOR /r . %%F IN (*.sql) part
  • sort { [int]($_.Name -replace '\D','') } extracts number from the filename before sort
  • %% { $_.FullName } (short form of ForEach-Object) prints full path of each file

Upvotes: 0

dbenham
dbenham

Reputation: 130819

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.

Upvotes: 2

LS_ᴅᴇᴠ
LS_ᴅᴇᴠ

Reputation: 11151

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

Upvotes: 4

foxidrive
foxidrive

Reputation: 41234

Take Command Console is a command interpreter that has a free version, and will do natural sorting.

Upvotes: 1

Aacini
Aacini

Reputation: 67216

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
)

Upvotes: 7

Kamiccolo
Kamiccolo

Reputation: 8497

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.

Upvotes: 2

RGuggisberg
RGuggisberg

Reputation: 4750

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

Upvotes: 1

Related Questions