Batch file to copy all .sql files in a tree to a single folder using a counter for files with same name

Question

I was looking for some help in setting up a batch script to go through an entire directory and copy all .sql files to a single folder. I can do this easy enough with a FOR loop but I hit a snag if two or more files in different directories have the same name. I'm attempting to get around this using a counter but I'm having trouble getting it to be specific to individual file names.

My code so far:

set /A n=0

for /R %source% %%G in (*.sql) do (
    if exist %destination%\sql_files\%%~nxG (
        set /A n+=1
        set "full=%%G"
        set "name=%%~nG"
        set "ext=%%~xG"
        setlocal enabledelayedexpansion
        copy "!full!" "!destination!\sql_files\!name!(!n!)!ext!" >nul
        endlocal
    ) else (
        copy "%%G" "%destination%\sql_files\" >nul
    )
)

For example if I have:

%source%\dir1\file1.sql
%source%\dir1\file2.sql
%source%\dir2\file1.sql
%source%\dir2\file2.sql
%source%\dir3\file1.sql

I end up with:

file1.sql
file1(1).sql
file1(3).sql
file2.sql
file2(2).sql

What I would like to see is:

file1.sql
file1(1).sql
file1(2).sql
file2.sql
file2(1).sql

I thought that my answer might lie in a subroutine but I can't quite figure out the logic to make that work. Any help is greatly appreciated.

Possible duplicate of [Copy file as another name if file exist](http://stackoverflow.com/questions/13659369/copy-file-as-another-name-if-file-exist) — aschipfl, Oct 04 '16 at 18:23
The core problem: you never reset the counter `n`. Your task seems non-trivial to me. I think you need to presort the returned source files, because the counter `n` is linked to the file names but not to the containing subdirectory. Is the target directory guaranteed to be empty initially, or are there perhaps already files there? — aschipfl, Oct 04 '16 at 18:40

aschipfl · Accepted Answer · 2016-10-05T19:26:28.090

Here is a script that does what you want. It relies on the fact that the target directory is initially empty:

@echo off
setlocal EnableExtensions DisableDelayedExpansion

rem // Define constants here:
set "SOURCE=."
set "DESTINATION=..\sql_files"
set "PATTERN=*.sql"
set "TMPFILE=%TEMP%\%~n0_%RANDOM%.tmp"

rem // Create target directory, check whether it is empty:
2> nul md "%DESTINATION%"
(2> nul dir /B /A "%DESTINATION%\*.*" | > nul find /V "") && (
    >&2 echo Target directory "%DESTINATION%" is not empty!
    exit /B 1
)

rem /* Write list of files to temporary file; each line holds
rem    the pure file name and the full path, separated by `:`: */
if not defined SOURCE set "SOURCE=."
> "%TMPFILE%" (
    for /R "%SOURCE%" %%F in ("%PATTERN%") do (
        echo :%%~nxF:%%~fF
    )
)

rem // Read temporary file with lines sorted by file names:
set "PREV=:" & set /A "INDEX=0"
for /F "tokens=1,* delims=:" %%E in ('sort "%TMPFILE%"') do (
    rem // Cache file name parts and full path:
    set "FILE=%%E"
    set "NAME=%%~nE"
    set "EXT=%%~xE"
    set "FULL=%%F"
    rem // Compare current with previous file name:
    setlocal EnableDelayedExpansion
    if /I not "!FILE!"=="!PREV!" (
        endlocal
        rem // Reset index if names do not match:
        set /A "INDEX=0"
    ) else (
        endlocal
        rem // Increment index if names do match:
        set /A "INDEX+=1"
    )
    rem // Build new file nase name:
    setlocal EnableDelayedExpansion
    if !INDEX! GTR 0 (
        set "NAME=!NAME!(!INDEX!)"
    )
    rem // Copy file and rename accordingly:
    > nul copy "!FULL!" "!DESTINATION!\!NAME!!EXT!"
    endlocal
    rem // Cache processed file name:
    set "PREV=%%E"
)

rem // Delete temporary file:
del "%TMPFILE%"

endlocal
exit /B

Batch file to copy all .sql files in a tree to a single folder using a counter for files with same name

1 Answers1