How do I add a substring from a filename into a CSV with the file's properties?

Question

Evening everyone, I'm currently working on a script intended to check the last modified time of .docx files in multiple subdirectories and then export the file name, path, modified date, and a substring from the filename into an Excel. The idea is guys at work can determine if files are being updated when they should be and they can quickly access them via copy/pasting the file path. I broke this into steps:

-Parse multiple subdirectories for .docx files contained at that level, -Retrieve the properties of those files and export them into a CSV, -Parse the filename (with variable lengths) for a known substring (VRXXX) -Add the substring to the CSV in a new column corresponding with the filename it came from.

I have a working script capable of achieving the first three points but am struggling on the last. Open to any suggestions on trying different coding methods, very new to this and have largely been cobbling it together from different tutorials and Microsoft guides.

This is what I am seeing:

Basename LastWriteTime FullName VR Number London (VR001) 9/06/2023 11:50:20 PM C:\etc\etc\etc\ VR001, VR002, VR112 Kuwait (VR002) 9/06/2023 11:50:53 PM C:\etc\etc\etc\ VR001, VR002, VR112 China - VR112 9/06/2023 11:51:42 PM C:\etc\etc\etc\ VR001, VR002, VR112

What I'd like to see:

Basename LastWriteTime FullName VR Number London (VR001) 9/06/2023 11:50:20 PM C:\etc\etc\etc\ VR001 Kuwait (VR002) 9/06/2023 11:50:53 PM C:\etc\etc\etc\ VR002 China - VR112 9/06/2023 11:51:42 PM C:\etc\etc\etc\ VR112

Parse and retrieve filename properties and export to CSV:

$VRNames = Get-ChildItem -Path C:\Users******.docx | Group-Object DirectoryName | ForEach-Object { $_.Group | Sort-Object BaseName -Descending | Select-Object -First 1 -Property BaseName,LastWriteTime,FullName }

$VRNames | Export-Csv -Path C:***\vrnames.csv -NoTypeInformation -UseCulture

$initialcsv | Export-Csv -Path C:\out\out\initial.csv -NoTypeInformation -UseCulture`

Parse filenames for the VRXXX substring
- from subdirectory:
$vrnumber = Get-ChildItem -Path C:\etc***.docx | ForEach-Object { Select-String -Pattern "VR[0-9][0-9][0-9]"

$matchvr = $vrnumber | ForEach-Object { $.Line.Substring($.Line.IndexOf("VR"),5) }
- from csv:

$csv1 = Import-Csv -Path C:****.csv

$substring = $csv1 | Select-String -Pattern "VR[0-9][0-9][0-9]" | ForEach-Object {
    $_.Line.Substring($_.Line.IndexOf("VR"),5) } | 
    Select-Object @{N='VR Number';E={$_}}

I've tried primarily working with the second method via:

$vrnumber = Import-Csv -Path C:\out\out\initial.csv | ForEach-Object { $VRNo = $initial | Select-String -Pattern "VR[0-9][0-9][0-9]" | ForEach-Object { $.Line.Substring($.Line.IndexOf("VR"),5) } $_ | Select-Object *,@{Name='VR Number';Expression={$VRNo}}}

But it just returns VR001,VR002,VR112 for each row. This code works if the VRXXX are just input as $VRNo = @(VRXXX,etc):

$VRNo.Split(",") | ForEach-Object {
    [PSCustomObject]@{'VR' = $_}}

So I tried on a prayer:

$vrnumber = Import-Csv -Path C:\out\out\initial.csv | ForEach-Object {
    $VRNo = $initial | Select-String -Pattern "VR[0-9][0-9][0-9]" | ForEach-Object {
    $_.Line.Substring($_.Line.IndexOf("VR"),5) }
    $_ | Select-Object *,@{Name='VR Number';Expression={
     $VRNo.Split(",") | ForEach-Object {
     [PSCustomObject]@{'VR' = $_}}
}}}

But no luck. Open to any suggestions on getting this particular method to work or if there is suggestions on a completely different method. Thanks in advance guys!

score 0 · Answer 1 · answered Jun 18 '23 at 14:15

Alright, anyone stumbling on this trainwreck, here is the simple answer!

First, define the default value via calculated properties. An explanation/example can be found in the MS Calculated Properties webpage. For this, I used:

$vrcalc = @{label="VR Number";expression={$_.BaseName.Substring($_.BaseName.IndexOf("VR"),5)}}

Then placed $vrcalc in my Select-Object as so (last part):

$VRNames = Get-ChildItem -Path C:\Users******.docx | Group-Object DirectoryName | ForEach-Object { $_.Group | Sort-Object BaseName -Descending | Select-Object -First 1 -Property $vrcalc,BaseName,LastWriteTime,FullName }

Then it was a simple export! Best of luck to anyone in a similar situation!

How do I add a substring from a filename into a CSV with the file's properties?

1 Answers1