I'm using this (simplified) chunk of code to extract a set of tables from SQL Server with BCP.
$OutputDirectory = "c:\junk\"
$ServerOption = "-SServerName"
$TargetDatabase = "Content.dbo."
$ExtractTables = @(
"Page"
, "ChecklistItemCategory"
, "ChecklistItem"
)
for ($i=0; $i -le $ExtractTables.Length – 1; $i++) {
$InputFullTableName = "$TargetDatabase$($ExtractTables[$i])"
$OutputFullFileName = "$OutputDirectory$($ExtractTables[$i])"
bcp $InputFullTableName out $OutputFullFileName -T -c $ServerOption
}
It works great, but now some of the tables need to be extracted via views, and some don't. So I need a data structure something like this:
"Page" "vExtractPage"
, "ChecklistItemCategory" "ChecklistItemCategory"
, "ChecklistItem" "vExtractChecklistItem"
I was looking at hashes, but I'm not finding anything on how to loop through a hash. What would be the right thing to do here? Perhaps just use an array, but with both values, separated by space?
Or am I missing something obvious?
This question is related to
powershell
dictionary
hashtable
Here is another quick way, just using the key as an index into the hash table to get the value:
$hash = @{
'a' = 1;
'b' = 2;
'c' = 3
};
foreach($key in $hash.keys) {
Write-Host ("Key = " + $key + " and Value = " + $hash[$key]);
}
I prefer this variant on the enumerator method with a pipeline, because you don't have to refer to the hash table in the foreach (tested in PowerShell 5):
$hash = @{
'a' = 3
'b' = 2
'c' = 1
}
$hash.getEnumerator() | foreach {
Write-Host ("Key = " + $_.key + " and Value = " + $_.value);
}
Output:
Key = c and Value = 1
Key = b and Value = 2
Key = a and Value = 3
Now, this has not been deliberately sorted on value, the enumerator simply returns the objects in reverse order.
But since this is a pipeline, I now can sort the objects received from the enumerator on value:
$hash.getEnumerator() | sort-object -Property value -Desc | foreach {
Write-Host ("Key = " + $_.key + " and Value = " + $_.value);
}
Output:
Key = a and Value = 3
Key = b and Value = 2
Key = c and Value = 1
About looping through a hash:
$Q = @{"ONE"="1";"TWO"="2";"THREE"="3"}
$Q.GETENUMERATOR() | % { $_.VALUE }
1
3
2
$Q.GETENUMERATOR() | % { $_.key }
ONE
THREE
TWO
A short traverse could be given too using the sub-expression operator $( ), which returns the result of one or more statements.
$hash = @{ a = 1; b = 2; c = 3}
forEach($y in $hash.Keys){
Write-Host "$y -> $($hash[$y])"
}
Result:
a -> 1
b -> 2
c -> 3
Shorthand is not preferred for scripts; it is less readable. The %{} operator is considered shorthand. Here's how it should be done in a script for readability and reusability:
PS> $hash = @{
a = 1
b = 2
c = 3
}
PS> $hash
Name Value
---- -----
c 3
b 2
a 1
Note: personal preference; syntax is easier to read
The GetEnumerator() method would be done as shown:
foreach ($h in $hash.GetEnumerator()) {
Write-Host "$($h.Name): $($h.Value)"
}
Output:
c: 3
b: 2
a: 1
The Keys method would be done as shown:
foreach ($h in $hash.Keys) {
Write-Host "${h}: $($hash.Item($h))"
}
Output:
c: 3
b: 2
a: 1
Be careful sorting your hashtable...
Sort-Object may change it to an array:
PS> $hash.GetType()
IsPublic IsSerial Name BaseType
-------- -------- ---- --------
True True Hashtable System.Object
PS> $hash = $hash.GetEnumerator() | Sort-Object Name
PS> $hash.GetType()
IsPublic IsSerial Name BaseType
-------- -------- ---- --------
True True Object[] System.Array
You can also do this without a variable
@{
'foo' = 222
'bar' = 333
'baz' = 444
'qux' = 555
} | % getEnumerator | % {
$_.key
$_.value
}
If you're using PowerShell v3, you can use JSON instead of a hashtable, and convert it to an object with Convert-FromJson:
@'
[
{
FileName = "Page";
ObjectName = "vExtractPage";
},
{
ObjectName = "ChecklistItemCategory";
},
{
ObjectName = "ChecklistItem";
},
]
'@ |
Convert-FromJson |
ForEach-Object {
$InputFullTableName = '{0}{1}' -f $TargetDatabase,$_.ObjectName
# In strict mode, you can't reference a property that doesn't exist,
#so check if it has an explicit filename firest.
$outputFileName = $_.ObjectName
if( $_ | Get-Member FileName )
{
$outputFileName = $_.FileName
}
$OutputFullFileName = Join-Path $OutputDirectory $outputFileName
bcp $InputFullTableName out $OutputFullFileName -T -c $ServerOption
}
Source: Stackoverflow.com