CODEWITHSUNDEEP
sqlsql-serverpowershellvariables
Karri
Karri

Reputation: 186

Extract values into variables from filename in Powershell

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!

Upvotes: 2

Views: 452

Answers (2)

Mathias R. Jessen
Mathias R. Jessen

Reputation: 174465

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 }

# ...

Upvotes: 3

Martin Brandl
Martin Brandl

Reputation: 58931

You can use the StartsWith function to fix your filter:

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

Upvotes: 2

Related Questions