powershell to move files based on part of file name

Question

I have thousands of files like the following in a directory called D:queries

#TIM_DV_ORDERINQUERY.SQL
#TIM_QA_ORDERINQUERY.SQL
#TIM_P1_ORDERINQUERY.SQL
#JACK_DV_SALESQUERY.SQL
#JACK_P1_PRODUCTQUERY.SQL
#ERIK_P1_EMPLOYEE-NY.SQL
#ERIK_P1_EMPLOYEE-TX.SQL

: :

I am need a script to move them to folders 
G:queries#TIMDVORDERINQUERY.SQL
G:queries#TIMQAORDERINQUERY.SQL
G:queries#TIMP1ORDERINQUERY.SQL
G:queries#JACKDVSALESQUERY.SQL
G:queries#JACKP1PRODUCTQUERY.SQL
G:queries#ERIKP1EMPLOYEE-NY.SQL
G:queries#ERIKP1EMPLOYEE-TX.SQL

In other words the character before the first _ is the sub directory within G:queries and then the characters between the first _ and the second _ is the a sub directory within it and then the test of the name is the file name.

I have searched a lot on the web and I can't figure it out. I am new to powershell or any kind of scripting. Any help is appreciated.

Answer 1

You'll probably want to use a regular expression for this. That will help to ensure that only files that match the specified pattern get moved, and you don't have any "accidents."

# 1. Get a list of files in the d:queries folder
$FileList = Get-ChildItem -Path d:queries;

# 2. Parse file names, create folder structure, and move files
foreach ($File in $FileList) {
    $File.Name -match '(?<folder>.*?)(?:_)(?<subfolder>w{2})(?:_)(?<filename>.*)';
    if ($matches) {
        $Destination = 'd:queries{0}{1}{2}' -f $matches.folder, $matches.subfolder, $matches.filename;
        mkdir -Path (Split-Path -Path $Destination -Parent) -ErrorAction SilentlyContinue;
        Move-Item -Path $File.FullName -Destination $Destination -WhatIf;
    }
    $matches = $null
}