1

I have a bunch of .xlsx files that are generated every month. I would like to be able to batch move the files to folders which have basically the same name.

Example: 123456 Action.xlsx, 123456 RC.xlsx, 123456 PF.xlsx would be the files. The folder would be 123456 Random Center.

Is there a way to move those files to that folder using a batch command or something else through the command prompt?

Here is the the code I have been trying to use/modify.

@echo off
pushd "C:\New folder"
rem Process all files in this folder separating the names at " "
for /F "tokens=1* delims=-" %%a in ('dir /B .xlsx') do (
   rem At this point %%a have the name before the " " and %%b the rest after " "
   rem Create the folder, if not exists
   if not exist "%%a" md "%%a"
   rem Move the file there
   move "%%a-%%b" "%%a"
)
popd

That creates a folder named %%a but puts nothing in it. I'm stuck and need some help.

Marijn
  • 10,367
  • 5
  • 59
  • 80
DHuber
  • 17
  • 1
  • 6

1 Answers1

3

First of all, welcome to Stack Overflow

In the code you provided you try to loop through files using the output of dir, and immediately split that up using spaces. Instead of this, you should use a for loop to loop through all files ending with *.xlsx first, and then brake that up in before and after the space.

Try this:

@echo off
pushd "C:\New folder"
FOR %%G IN (*.xlsx) DO (
  FOR /F "tokens=1 delims= " %%a IN ("%%G") do (
    if not exist "%%a Random Center" md "%%a Random Center"
    move "%%G" "%%a Random Center"
  )
)
popd
pause

In this code I first loop through all files ending with xlsx, by looping through xlsx ( is a wildcard) without a / switch. After that I loop through %%G (wchich are the filenames) as string using the /F switch.

Note that you're trying to use the - as a delimiter, instead of . You make the same error in your move command. If the files use a - instead of a , you should change the delimiter in my code as well.

EDIT:

This looks if there is a folder which starts with the same word as the files and moves them there:

@echo off
setlocal EnableDelayedExpansion
pushd "C:\New folder"
FOR %%G IN (*.xlsx) DO (
  FOR /F "tokens=1 delims= " %%a IN ("%%G") do (
    set "outFolder=%%a Random Center"
    for /D %%i in (*.*) do (
      for /F "tokens=1 delims= " %%b IN ("%%i") do (
        if "%%a"=="%%b" set "outFolder=%%i"
      )
    )
    if not exist "!outfolder!" md "!outfolder!"
    move "%%G" "!outfolder!"
  )
)
popd
pause
Dennis van Gils
  • 3,487
  • 2
  • 14
  • 35
  • If you feel like my answer solved your problem you can click the check to the left of it to mark it as correct. If you still have problems with it you can leave a comment and I'll try to help. – Dennis van Gils Dec 24 '15 at 14:39
  • Ok when I run that it creates a folder named %%a Random Center but does not move the files into that folder. How can I get that command to recognize the file names and move them into the folder with the same name? – DHuber Dec 24 '15 at 15:00
  • The folder name should be 123456 Random Center, and the files should be moved in there. As I said, are the file names 123456 Action.xlsx or 123456-Action.xlsx? – Dennis van Gils Dec 24 '15 at 15:03
  • 123456 Action.xlsx Also would it matter if some of the files are say 000123456 and 123456 - would the leading zeros be an issue? – DHuber Dec 24 '15 at 15:06
  • No. Try the same code again but put echo in front of move, and tell me what the output is. Also, just to be sure, what did you call the .bat file? – Dennis van Gils Dec 24 '15 at 15:07
  • Oh i'm trying to run this off the command prompt...would i need to remove one of the % from each line? – DHuber Dec 24 '15 at 15:10
  • You should copy this code to notepad, and save it as a moveXlsx .bat. Then execute that file. – Dennis van Gils Dec 24 '15 at 15:11
  • As for the leading zeros, it doesn't break the script, but the new folder will have the same leading zeros as the files do. – Dennis van Gils Dec 24 '15 at 15:12
  • Ok so now it is creating the folders, but not moving any files, we have progress! – DHuber Dec 24 '15 at 15:15
  • What is the output with echo before the move command? So `echo move "%%G" "%%a Random Center"`? – Dennis van Gils Dec 24 '15 at 15:16
  • @echo off pushd "C:\Users\DHuber\Desktop\New folder" FOR %%G IN (*.xlsx) DO ( FOR /F "tokens=1,* delims= " %%a IN ("%%G") do ( if not exist "%%a Random Center" md "%%a Random Center" echo move "%%G" "%%a Random Center" ) ) popd pause Do forgive me I'm fairly new to this – DHuber Dec 24 '15 at 15:20
  • No, what is the output. what does the cmd window show you, when you execute that (as a batch-file, ofcourse)? It should output something like `move "123456 Action.xlsx" "123456 Random Center"` – Dennis van Gils Dec 24 '15 at 15:23
  • move "123456 pf.xlsx" "123456 Random Center" for each file, then Press any key to continue...at the end – DHuber Dec 24 '15 at 15:26
  • Did you remember to remove the word `echo` when you first ran it as a batch-script? And what did you call the batch-file? – Dennis van Gils Dec 24 '15 at 15:29
  • filename is movexXlsx.bat, ah yes the echo was still there, now it is moving the files! not to muddy the waters but if I have the folders already there how can this be modified to just move the files to the folders which already exist? – DHuber Dec 24 '15 at 15:34
  • If the folder 123456 Random Center already exists this will simply copy files starting with 123456 in there, instead of overwriting the folder – Dennis van Gils Dec 24 '15 at 15:35
  • By the way, you can click the check next to my answer to mark it as correct. – Dennis van Gils Dec 24 '15 at 15:39
  • ok so I tried it and it won't move the files to the folders that already exists it creates it own, not the end of the world. so basically i have folders that are 123456 ABC Pediatrics and it skipped over that folder and created a folder 123456 and put the files there. – DHuber Dec 24 '15 at 15:44
  • Just finished the final version with the new requirements, Happy Holidays! – Dennis van Gils Dec 24 '15 at 16:28
  • Thank you Dennis! Works perfect! – DHuber Dec 28 '15 at 15:11
  • Hey Dennis, I was wondering, I have been using this batch file and was wondering if there is a way for it to look in two folders with "new folder" named Region 1 and Region 6 for the folders with same name? – DHuber Apr 29 '16 at 15:03
  • @DennisvanGils hi Dennis, your example, is perfect for a batch that I'm doing, to put files back to their home directories. these directories have the same name as the files. however, these directories do not have the file extension. in your example, when you create the directory it will look like this: "file name.ext + Random Center". I managed to suppress the "Random Center", but I was unable to suppress the extension. Could you explain to me how I can create a directory without an extension in it, in your example ?! – songa Jan 12 '21 at 13:23