Moving folders based on condition

Question

I have many excel files in a single folder like below.

+----------------------+
| 01012019_LOC001.xlsx |
| 01012019_LOC002.xlsx |
| 01012019_LOC003.xlsx |
| 02012019_LOC001.xlsx |
| 02012019_LOC002.xlsx |
| 02012019_LOC003.xlsx |
| 03012019_LOC001.xlsx |
| 03012019_LOC002.xlsx |
| 03012019_LOC003.xlsx |
+----------------------+

a date plus Location combination structure DDMMYYYY_LOCXXX

I want to move these files to it's respective folders created similar to the below structure.

MAIN_FOLDER
|
|-LOC001
  |-01012019
  |-02012019
  |-03012019
  |
|-LOC002
  |-01012019
  |-02012019
  |-03012019
  |
|-LOC003
  |-01012019
  |-02012019
  |-03012019
|

I can transfer one at a time with for /r %d in (01012019_LOC001.xlsx) do move "%d" "C:MAIN_FOLDER\LOC001\01012019\".

But I'm looking for a more dynamic solution. Hence, please guide me to find a solution.

P.S.Note:- even a powershell answer would suffice.

Answer 1

For this you'll want to loop through the files with delayed expansion. Unless there are subfolders you also want to pull from, I would avoid the /r flag of for since it's recursive. The main issue is splitting the filename up since each half is a separate piece of information. It'll look something like this:

@echo off

set "dir=C:\Your\Excel\File\Directory"
set "des=C:\MAIN_FOLDER"

setlocal enabledelayedexpansion
for %%A in (%dir%\*.xlsx) do (
    set "file=%%~nA"
    if not exist "%des%\!file:~-6!" md "%des%\!file:~-6!"
    if not exist "%des%\!file:~-6!\!file:~0,8!.xlsx" move /y "%dir%\%%~nxA" "%des%\!file:~-6!\!file:~0,8!.xlsx"
)

This is a batch solution, so you can just copy and paste it into Notepad then save it as a .bat instead of a .txt.

First, I've set variables for dir (directory; where your Excel files are currently located) and des (destination; where you want to create your folders and move your files) - you'll need to edit those values to reflect your environment. Prior to doing anything else, we use setlocal enabledelayedexpansion since we are working with variables that change values every loop iteration. The for will loop through all excel files in the top-folder of our directory and assign them to parameter %%A; once inside the loop we set a variable called file as %%~nA which is just parameter %%A reduced to its filename only. We use one if statement to check to see if the folder for that file already exists - if it doesn't we make the folder with md, then we use another if statement to see if the file we're currently looking at already exists in the folder we just verified/made - if it doesn't, we move the file to the folder with move and suppress the confirmation prompt with /y (the /y isn't really necessary since we kick it off with an if statement, but it's best practice imo). The move command has a rename built into it, so we just use that to follow the naming scheme you wanted.

The cheesy part here are the variable substrings - since your dates and locations are set numbers of characters, we just count those characters and extract them as needed:
!file:~-6! just grabs the last six characters (our LOC information) while !file:~0,8! grabs the first eight characters (our date information). The only other thing worth noting is to use exclamation points within a loop that is using delayed expansion (since those are the variables that are changing); for our static variables (dir and des) the percent signs are fine throughout.

Hopefully this does what you wanted or at least gets you on the right track.

Reference: for, variable substring, move

Answer 2

For completeness, here's a PowerShell version:

$Source = 'C:\Source'
$Dest   = 'C:\Destination'

Get-ChildItem $source -filter *.xls | foreach{
   $FolderName = $_.Basename.Split('_')[1]
   If (!(Test-Path "$Dest\$FolderName")) {
      New-Item -Path $Dest -Name $FolderName -ItemType Directory | out-null
   }
   Move-Item $_.FullName "$Dest\$FolderName"
}

I opted to get the Loc### string by splitting the basename on the underscore, which by itself returns a two-element string array. The 1 subscript references the second element. If Test-Path fails, we create the folder, piping the return to out-null to avoid screen display. Then Move-Item does just what it sounds like! :)

Get-ChildItem

ForEach-Object

String.Split Method

Test-Path

New-Item

Move-Item