Validating data prior to changing values in CSV file

PowerShell Help
21

OK, I thought that’s what you wanted.

It’s just a case of adapting the regular expression to match (no pun intended) your requirements:

$data   = Import-CSV E:\Temp\Files\List-SourceNames-OLD.csv
$latest = Get-Content E:\Temp\Files\latest.txt

foreach ($update in $latest) {
    
    $update -match '(.+)(\d{5}\.\d+\s\d+$|\d{5}\.\d+)' | Out-Null
    $entry = ($data.osd_name | Where-Object {$_ -like "$($matches[1])*"}) 
    $index = $data.osd_name.IndexOf($entry)
    $data[$index].osd_name = $data[$index].osd_name -replace '(\d{5}\.\d+)',$matches[2]

}

$data

Output:

src_name                                            osd_name
--------                                            --------
Windows 10 Enterprise LTSC 2021                     Windows 10 Enterprise LTSC 2021 x64 21H2 19044.1586
Windows 10 Pro for Workstations                     Windows 10 Pro for Workstations x64 21H2 19044.1586
Windows 11 Pro for Workstations                     Windows 11 Pro for Workstations x64 21H2 22000.556
Windows 11 Pro                                      Windows 11 Pro x64 21H2 22000.556
Windows Server 2022 Datacenter (Desktop Experience) Windows Server 2022 Datacenter Desktop Experience x64 Dev 22509.1200 2203140144
Windows Server 2022 Datacenter                      Windows Server 2022 Datacenter x64 Dev 22509.1000 2203140148
22

@matt-bloomfield

“Adapting the regular expression to match” certainly did the trick! :+1:

Old Values:
from imported csv file.

osd_name
Windows 10 Enterprise LTSC 2021 x64 21H2 19044.1566
Windows 10 Pro for Workstations x64 21H2 19044.1526
Windows 11 Pro for Workstations x64 21H2 22000.493
Windows 11 Pro x64 21H2 22000.493
Windows Server 2022 Datacenter Desktop Experience x64 Dev 22509.1000
Windows Server 2022 Datacenter x64 Dev 22509.1000

New Values:
exported to csv file, replacing old values.

Windows 10 Enterprise LTSC 2021 x64 21H2 19044.1586
Windows 10 Pro for Workstations x64 21H2 19044.1586
Windows 11 Pro for Workstations x64 21H2 22000.556
Windows 11 Pro x64 21H2 22000.556
Windows Server 2022 Datacenter Desktop Experience x64 Dev 22509.1000 2203140144
Windows Server 2022 Datacenter x64 Dev 22509.1000 2203140148

Although manually updating those 6 values takes less than a minute on average, this was certainly a great learning experience working with you and @krzydoug’s code and exposing me to the topic of RegEx.

I noticed that were using -match, -like and -replace comparison operators, while matt used -replace, but called on that $match variable to match the strings being compared… I think.

I’m still going to continue with learning this topic, because this really seems like an important skill to have when it comes to gather, comparing and manipulating data.

I found a video that covers this topic that really helped me understand the -match-ing aspect much better now, but using it to replace something is where the power is in this case.

Regular Expressions (Regex) Tutorial: How to Match Any Pattern of Text - YouTube

I may just setup Python to follow along. it’s impressive to see it in action versus reading Microsoft’s documentation which always leaves room for confusion.

23

Yes, pretty much.

When you use -match any matches populate the $matches variable.

PS E:\Temp> 'hello zero269' -match 'hello'
True
PS E:\Temp> $matches

Name                           Value
----                           -----
0                              hello

When you use capture groups (), $matches[0] is the complete match, $matches[1] is the first capture group, $matches[2] the second and so on:

PS E:\Temp> 'hello zero269' -match '(hello) (zero269)'
True
PS E:\Temp> $matches
Name                           Value
----                           -----
2                              zero
1                              hello
0                              hello zero269

In my examples, I was just treating the entries in $matches as you would any other variable:

So this:

'zero269' -like "$($matches[2])*"

Isn’t any different to:

PS E:\Temp> $myString = 'zero'
PS E:\Temp> 'zero269' -like "$myString*"

Because $matches is an array of values. Instead of array index notation $matches[1] for accessing the values. You could, instead, use named capture groups:

PS E:\Temp> 'hello zero269' -match '(?<myCaptureGroup>hello) (?<anotherGroup>zero)269'
True
PS E:\Temp> $matches.myCaptureGroup
hello
PS E:\Temp> $matches.anotherGroup
zero
24

If interested, here’s what the final code looks like:

I start by declaring the csv variables for the old and new files:

### Update List-SourceNames.csv with new folder osd_names ###

# CSV Files
$csv_file = "D:\OSD\Builder\List-SourceNames.csv"
$csv_file_old = "D:\OSD\Builder\List-SourceNames-OLD.csv"

I added a conditional statement where I can enter one of three source directories to use to update the CSV file.

# Get-OSDSource
$osd_source = Read-Host "Enter OSImport, OSMedia or OSBuilds"
    if ($osd_source -eq 'OSImport'){
        $osd_path = 'D:\OSD\Builder\OSImport'
        }
    elseif ($osd_source -eq 'OSMedia'){
        $osd_path = 'D:\OSD\Builder\OSMedia'
        }
    elseif ($osd_source -eq 'OSBuilds'){
        $osd_path = 'D:\OSD\Builder\OSBuilds'
        }
    else{
        Write-Output "Invalid Input"
        break
        }

Here I’m just backing up the original csv before updating it:

# Backup CSV File
Copy-Item $csv_file -Destination $csv_file_old -Force

I get and sort the latest folders by CreationTime that will be used to -match the old values in the csv file with the new values placed in the $latest array:

# Get/Sort latest folders (excluding any "build" folders)
$latest = (Get-ChildItem $osd_path -Exclude "build*" `
    | Where { $_.PSIsContainer } | Sort CreationTime -Descending | Select-Object Name).Name

Import csv and process new data to match old data:

# Import CSV data
$data = Import-CSV "$csv_file"

# Processing New Folders to Match
foreach ($update in $latest) {
    $update -match '(.+)(\d{5}\.\d+\s\d+$|\d{5}\.\d+)' | Out-Null
    $entry = ($data.osd_name | Where-Object {$_ -like "$($matches[1])*"}) 
    $index = $data.osd_name.IndexOf($entry)
    $data[$index].osd_name = $data[$index].osd_name -replace '(\d{5}\.\d+)',$matches[2]
}

And the awaited new values are exported successfully to the CSV file:

# Export new values to CSV
$data | Export-CSV "$csv_file" -Force -NoTypeInformation -Encoding ASCII

I removed the prompt for the number of folders to filter as @krzydoug suggested, and I didn’t need to re-sort the $latest and export them to a text file considering they can now be updated with the script.

Many thanks to @matt-bloomfield and @krzydoug for your expert help and patience with working this out.

Now I can get back to my books: :+1:

  • Learn Windows PowerShell in a Month of Lunches
  • PowerShell for Sysadmins