6

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?

Justin Garrick
  • 14,767
  • 7
  • 41
  • 66
  • The solution of @Aacini below is a good one. But is you use Powershell you could also do a one-liner `Get-ChildItem | Sort-Object { [regex]::Replace($_.Name, '\d+', { $args[0].Value.PadLeft(20) }) }`. [See here for the topic on SO](http://stackoverflow.com/questions/5427506/how-to-sort-by-file-name-the-same-way-windows-explorer-does) (that's why i didn't add it as an answer). – Rik Sep 11 '13 at 19:14

7 Answers7

7

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
)
Aacini
  • 65,180
  • 12
  • 72
  • 108
  • Can you please explain briefly how your new solution works and provide links to documentation detailing it further? I want to be able to improve on it to, e.g., skip the second `for` loop if no files were listed. Thanks! – Chry Cheng Dec 08 '16 at 08:43
4

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
LS_ᴅᴇᴠ
  • 10,823
  • 1
  • 23
  • 46
2

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.

Community
  • 1
  • 1
Kamiccolo
  • 7,758
  • 3
  • 34
  • 47
  • 1
    Weirdish regular expressions, indeed. – Justin Garrick Sep 11 '13 at 18:57
  • 1
    The "one-liner" [here](http://stackoverflow.com/questions/5427506/how-to-sort-by-file-name-the-same-way-windows-explorer-does) is easier. `Get-ChildItem | Sort-Object { [regex]::Replace($_.Name, '\d+', { $args[0].Value.PadLeft(20) }) }` – Rik Sep 11 '13 at 19:18
2

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.

Community
  • 1
  • 1
dbenham
  • 127,446
  • 28
  • 251
  • 390
  • EDIT - I simplified by using FOR /F to parse out the original file name. The original post had one extra REPL step to do the parsing. – dbenham Sep 11 '13 at 20:01
1

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

RGuggisberg
  • 4,630
  • 2
  • 18
  • 27
  • 2
    Thanks, this works for my current use case, but it feels crufty because you have to add another leading zero each time your fileset grows by an order of 10. – Justin Garrick Sep 11 '13 at 18:56
1

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

foxidrive
  • 40,353
  • 10
  • 53
  • 68
0

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
snipsnipsnip
  • 2,268
  • 2
  • 33
  • 34