How to make for loop in windows batch file run in name order

Question

I have a windows batch file that does this:

for %%s in (*.sql) do call

It loops through all the sql script in a folder.

In the folder the file names are like:
s4.06.01.sql
s4.07.01.sql
s4.08.01.sql
s4.10.01.sql
s5.01.sql

But the for loop goes through the files randomly (not in the name order), it first run s5.01, then s4.06, then s4.08, then s4.10, then s4.07. How can I make them run in the name order?

It used to work, but now it doesn't. What may cause this problem?

Lieven Keersmaekers · Accepted Answer · 2013-09-22T17:21:10.973

10

Jerry's answer might very well be what is causing the problem.

You might solve it by changing

for %%s in (*.sql) do call

to

for %%s in (dir"*.sql ^| sort) do call

Edit cudo's to LonelyPixel

The posted solution does not work as is on a Windows 8 machine (perhaps it did back then with Windows XP but I have no idea).

Following is a working solution using Windows 8 command prompt.

for /f "tokens=*" %%s in ('dir /b *.sql ^| sort') do echo %%s

edited Sep 22 '13 at 17:21

answered Aug 31 '10 at 06:57

Lieven Keersmaekers

57,207
13
112
146

3

Or, `for /f "usebackq" %%s in (\`dir *.sql /b /ON\`) do call` – Patrick Cuff Aug 31 '10 at 10:55
Patrick, your solution cuts off all files names after a space, so it's only partially useful. – ygoe Sep 22 '13 at 14:20
1

Lieven, your solution fails completely. If I replace the " by a space, it finds "dir", "|" and "sort", too. So that doesn't work either. – ygoe Sep 22 '13 at 14:20
@LonelyPixel - I have updated the answer but as I've mentioned in the edit, I have no idea if it worked back then. *(OP accepted this as a correct answer so I assume it did)*. – Lieven Keersmaekers Sep 22 '13 at 17:22
@PatrickCuff For filenames with spaces, add `tokens=`, like this: `for /f "usebackq tokens=" %%s in (`dir "my work dir\\*.sql" /b /ON`) do call`. – Alexander Gelbukh Dec 21 '15 at 19:17
What about sort in SIZE ASC? – shzyincu Apr 28 '17 at 07:25
The `for %%s in (dir"*.sql ^| sort)` fails on wineconsole with "more?" – Johannes Schaub - litb Feb 13 '20 at 17:33

score 2 · Answer 2 · answered Aug 31 '10 at 06:45

If memory serves, it will operate on the files in the order they're returned by the file system. As such, if you run it on a disk partition that's formatted with NTFS, the names will be returned in sorted order so they'll be processed in sorted order. If the disk partition is formatted with something like FAT or FAT32, the names will be retrieved (and processed) in a more or less random order.

score 1 · Answer 3 · answered Oct 20 '15 at 16:12

I too had to deal with spaces in the filenames, so I added quotes around the input variable like so:

for /f "tokens=*" %%s in ('dir /b *.sql ^| sort') do echo "%%s"

In my case it was SQLCMD, with the scripts in a subdirectory, like so:

SET "machineName=localhost"
for /f "tokens=*" %%f in ('dir /b PostDeployScripts\*.sql ^| sort') do sqlcmd -E -S%machineName% -i"PostDeployScripts\%%f"

How to make for loop in windows batch file run in name order

3 Answers3