I have some SQL output I'm trying to manipulate via PowerShell's foreach cmdlet, but I'm running into issues with getting it to iterate through each line of the output.
Here's the PowerShell code I have:
$result = @(D:\sql_select.bat --file D:\SQL.sql)
$output = $result -split "`r`n"
foreach ($l in $output) {
$name = [Regex]::Matches($output, ".+?(?=~)")
$size = [Regex]::Matches($output, "[^~]*$")
$tables += @{$name = $size}
}
Here's a bit of the SQL output I'm working with:
Table1~9.00
Table2~5.61
Table3~1.13
Table4~0.93
Table5~0.72
Table6~0.57
It doesn't appear that the 'foreach $l in $output' is working correctly since it's not putting the key/value pairs into the hashtable. I'd like to make it work so that Table1 will be a key with value 9.00, Table2 will be paired with 5.61, etc. How can I make this work?
(Note: I understand Invoke-SQLCmd is available in PowerShell and is quite useful, but I'm unfortunately not able to utilize it here. So, please don't bother suggesting that as a solution.)
So, I'm using your SQL output as input for my test, this should work fine:
$output='Table1~9.00
Table2~5.61
Table3~1.13
Table4~0.93
Table5~0.72
Table6~0.57' -split '\s+'
foreach ($l in $output) {
$name = ([Regex]::Match($l, ".+?(?=~)")).Value
$size = ([Regex]::Match($l, "[^~]*$")).Value
$tables += @{$name = $size}
}
PS C:\> $tables
Name Value
---- -----
Table1 9.00
Table2 5.61
Table3 1.13
Table4 0.93
Table5 0.72
Table6 0.57
If you use [regex]::Matches your match will be an array and the result would look like this:
PS C:\> $tables
Name Value
---- -----
Table6 {0.57, }
Table5 {0.72, }
Table4 {0.93, }
Table3 {1.13, }
Table2 {5.61, }
Table1 {9.00, }
You might also need to use $tables += [ordered]@{$name = $size} in case you need an ordered hashtable.