Extract values into variables from filename in Powershell

Question

I have a Powershell script to read .sql files from a specific folder and run them against a database depending on the name of the filename. The filenames are always the same: myDatabase.script.SomeRandomCharacters.csv

There can be many files which is why the script has a foreach loop.

[CmdletBinding()]
param (
    [parameter(Mandatory = $true)][ValidateSet('dev')][String]$serverName, 
    [parameter(Mandatory = $true)][String]$databaseName,
)

$dir = Split-Path $MyInvocation.MyCommand.Path
$scripts = Get-ChildItem $dir | Where-Object { $_.Extension -eq ".sql" } | Where-Object { $_.Name -like "$databaseName*" }

foreach ($s in $scripts) {
    $script = $s.FullName
    Invoke-Sqlcmd -ServerInstance $serverName -Database $databaseName -InputFile $script
}

The issue here is that if I would have 2 databases "myDatabase" and "myDatabase2", running the script with the former input would run the latter as well since the Where-Object filtering uses an asterisk.

I can't figure out how to modify the script so that I get the absolute value of whatever is before the first fullstop in the filename. What I would also what to do is to validate the value between the first and second fullstops, in the example filename it is script.

Any help is appreciated!

You mean you only want to check if what's before the first full stop is your database name? Like | Where-Object { ($_.Name -split '\.')[0] -eq $databaseName } — Shaneis
– Shaneis, Commented Jul 7, 2021 at 9:00
Using this twice to form the $scripts variable allows me to use this for both the first and second part of the filename so if someone adds a file with the correct database name but an incorrect second part, it will not execute. — Karri
– Karri, Commented Jul 7, 2021 at 9:59

Mathias R. Jessen · Accepted Answer · 2021-07-07 09:14:25Z

3

Use the database names to construct a regex pattern that will match either:

param(
    [Parameter(Mandatory = $true)][ValidateSet('dev')][String]$ServerName, 
    [Parameter(Mandatory = $true)][String[]]$DatabaseNames,
)

# Construct alternation pattern like `db1|db2|db3`
$dbNamesEscaped = @($DatabaseNames |ForEach-Object {
  [regex]::Escape($_)
}) -join '|'

# Construct pattern with `^` (start-of-string anchor)
$dbNamePattern = '^{0}' -f $dbNamesEscaped

# Fetch scripts associated with either of the database names
$scripts = Get-ChildItem $dir | Where-Object { $_.Extension -eq ".sql" -and $_.Name -match $dbNamePattern }

# ...

answered Jul 7, 2021 at 9:14

Mathias R. Jessen

178k13 gold badges175 silver badges234 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Martin Brandl · Accepted Answer · 2021-07-07 09:03:22Z

2

You can use the StartsWith function to fix your filter:

$scripts = Get-ChildItem $dir | Where-Object { $_.Extension -eq ".sql" } | Where-Object { $_.Name.StartsWith("$($databaseName).") }

answered Jul 7, 2021 at 9:03

Martin Brandl

59.4k16 gold badges151 silver badges190 bronze badges

Collectives™ on Stack Overflow

Extract values into variables from filename in Powershell

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related