Assistance with script to copy files to directories based on substring of filename

Question

Working on a script to copy files to specific existing folders based on a substring of the file name.

The files are named in a format that ends with year and month numbers, such as:

RawData_MJE_2019_7.xlsx
Checklist_2019_7.xlsx
RawData_MJE_2019_6.xlsx
Checklist_2019_6.xlsx

I have existing destination directories named with the year and month in a similar format, such as:

C:\Save\2019_7
C:\Save\2019_8

Looking to have source files containing a year and month in the file name copied to the similarly named destination directory? So any source files containing 2019_7 would be copied to the C:\Save\2019_7 folder.

I'm getting error on the copy-item; wondering if it's due to spaces in the folder names of the destination directory:

$SourceFolder = "C:\Users\hy85170\Documents\VBTemp\Output\Formatted"
$TargetFolder = "C:\Users\hy85170\OneDrive - Chemours\Documents\Chemours\CONTROLLERSHIP\Data Analytics\MJE\0. Data\1. Output Files\To Upload"
$Files = Get-ChildItem $SourceFolder | where {$_.extension -in ".xlsx"} | select -expand basename
Foreach ($File in $Files)
{
    $Year = $File.Substring(0,4)
    $Month = $File.Substring(4,1)
    $Directory = $TargetFolder + "\" + $Year+$Month
    $files | Copy-Item -Destination $Directory
}

Error:

Copy-Item : Cannot find path 'C:\Users\hy85170\RawDataWhole_MJE_2019_7' because it does not exist.
At C:\Users\hy85170\Desktop\VBTemp\VBScripts\CopyOutputFiles.ps1:11 char:11
+     $files | Copy-Item -Destination $Directory
+              ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ObjectNotFound: (C:\Users\hy85170\RawDataWhole_MJE_2019_7:String) [Copy-Item], ItemNotFoundException
    + FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.CopyItemCommand

1. Why did you post a new question rather than edit your previous one? as soon as your edit is fine, it is going to be reopened anyway. 2. The code you posted is definitely not a batch-file! 3. Your code does not reflect the destination location you described! — aschipfl
1) I didn't see a way to edit the original post - it said it was closed; 2) Yes, I realize it's not a batch file - pardon me for accidentally not changing the tag; 3) Yes, I realize the location I described is not the same as the destination - it was meant to be a simple example of what I'm trying to do. 4) Any other problems you want to scold me on? — InvertedPJ
1. The edit button (which I even linked in the original question) is still available; when the question becomes on topic, there is a voting system for re-opening (just like for closing). 2. Fixed now anyway; note that tags are important to attract the proper audience for answering your question (you should really take the tour at least). 3. Examples and code should match, so potential answerers can easily experiment with your script and data; the easier it is for them, the more likely you will receive a satisfactory answer. 4. There is no reason to feel upset... — aschipfl
you are using $Files [the plural] in your copy command. that is NOT the name you used for the current loop item. [grin] ///// you expanded the file basename and then don't add the extension back on before you do the copy. that makes your copy command fail since there are no such files in the source location. — Lee_Dailey

AdminOfThings AdminOfThings · Accepted Answer · 2019-08-29T18:23:42

You can do something similar to the following:

$SourceFolder = "C:\Users\hy85170\Documents\VBTemp\Output\Formatted"
$TargetFolder = "C:\Users\hy85170\OneDrive - Chemours\Documents\Chemours\CONTROLLERSHIP\Data Analytics\MJE\0. Data\1. Output Files\To Upload"
$Files = Get-ChildItem $SourceFolder | where {$_.extension -in ".xlsx" -and $_.basename -match "\d{4}_\d{1,2}$"}

foreach ($file in $Files) {
    $date = ($file.BaseName | Select-String -Pattern "\d{4}_\d{1,2}$").Matches.Value
    $year,$month = $date -split "_"
    $Directory = Join-Path -Path $TargetFolder -ChildPath $date
    $file.FullName | Copy-Item -Destination $Directory
}

Explanation:

The regex pattern \d{4}_\d{1,2}$ is used to match the Year_Month string at the end of the file base name. $date contains the Year_Month string. When we split $date on _, an array containing the year and month will be output. The first element of the array (year) is stored in $year. The second element of the array (month) is stored in $month. The Join-Path command can be used to join multiple folders into a single path, which will automatically add the \ characters.

In your attempt, you received an error because your Copy-Item command looked for the file you wanted to copy in your current directory. So unless you change into $SourceFolder before the Copy-Item executes, you will receive the error.

Assistance with script to copy files to directories based on substring of filename

1 Answers