How to add data from SQL server to hashtable using PowerShell?

Question

Basically, I want the data to show up in an excel file like it shows in the SQL database. This is much more simplified version of the work that I need to do but in essence this what it is.

I retrieve the data from SQL and for each item retrieved(which is the primary key) I want the data corresponding to it to be added in the hashtable. I then export this hashtable as a CSV

The CSV file is generated but with some weird data

I am not sure what exactly is wrong because when I Write-host $hashObject I can see the data is in there.

Code

$server = "DESKTOP\SQLEXPRESS"
$database = "AdventureWorks2019"
$hashTable = @{}
$hashObject = @([PSCustomObject]$hashTable)
$query = "SELECT[DepartmentID] FROM [AdventureWorks2019].[HumanResources].[Department]"
$invokeSql = Invoke-Sqlcmd -ServerInstance $server -Database $database -Query $query
$departmentResult = $invokeSql.DepartmentID

ForEach($department in $departmentResult){
    $queryAll = "SELECT [Name],[GroupName],[ModifiedDate]FROM [AdventureWorks2019].[HumanResources].[Department] Where DepartmentID=$department"
    $invokeSql = Invoke-Sqlcmd -ServerInstance $server -Database $database -Query $queryAll
    $name = $invokeSql.Name
    $groupName = $invokeSql.GroupName
    $modifiedDate = $invokeSql.ModifiedDate
    $hashObject+=("Department",$department, "Name",$name,"GroupName",$groupName,"ModifiedDate",$modifiedDate)
}
ConvertTo-Csv $hashObject| Export-Csv -Path "C:\Users\Desktop\PowerShell\HashTable_OutputFiles\HashOutput.csv"

At first glance you could do an inner join on your sql query of the 2 tables and save yourself the overhead — Santiago Squarzon
– Santiago Squarzon, Commented Jun 12, 2022 at 0:52
@SantiagoSquarzon there is only one table involved here[HumanResources].[Department] the columns are: [DepartmentID],[Name],[GroupName],[ModifiedDate] — Brute
– Brute, Commented Jun 12, 2022 at 0:57
Why is the first query needed? If you're querying the same table, DepartmentID=$department is not clear — Santiago Squarzon
– Santiago Squarzon, Commented Jun 12, 2022 at 1:01
@SantiagoSquarzon This is much simpliefied version of the real problem. In the real problem the data from the first query is matched against something else and if it is true only then I need the rest of the data. Also, I need the primary key to get data from other tables too(that is after the condition is true). — Brute
– Brute, Commented Jun 12, 2022 at 1:13

Santiago Squarzon · Accepted Answer · 2022-06-12 02:14:24Z

1

This is a simplified version of what you're attempting to do, in this case you should be able to use the SQL IN Operator in your second query instead of querying your Database on each loop iteration.

As aside, is unclear what you wanted to do when declaring a hash table to then convert it to a PSCustomObject instance and then wrap it in an array:

$hashTable  = @{}
$hashObject = @([PSCustomObject] $hashTable)

It's also worth noting that ConvertTo-Csv and Import-Csv are coded in such a way that they are intended to receive objects from the pipeline. This answer might help clarifying the Why. It's also unclear why are you attempting to first convert the objects to Csv and then exporting them when Import-Csv can (and in this case, must) receive the objects, convert them to a Csv string and then export them to a file.

$server     = "DESKTOP\SQLEXPRESS"
$database   = "AdventureWorks2019"
$query      = "SELECT [DepartmentID] FROM [AdventureWorks2019].[HumanResources].[Department]"
$invokeSql  = Invoke-Sqlcmd -ServerInstance $server -Database $database -Query $query
$department = "'{0}'" -f ($invokeSql.DepartmentID -join "','")
$query      = @"
SELECT [Name],
       [GroupName],
       [ModifiedDate]
FROM [AdventureWorks2019].[HumanResources].[Department]
WHERE DepartmentID IN ($department);
"@

Invoke-Sqlcmd -ServerInstance $server -Database $database -Query $query |
    Export-Csv -Path "C:\Users\Desktop\PowerShell\HashTable_OutputFiles\HashOutput.csv"

If you want to query the database per ID from the first query, you could do it this way (note this is similar to what you where looking to accomplish, merge the ID with the second results from the second query):

$invokeSql = Invoke-Sqlcmd -ServerInstance $server -Database $database -Query $query
$query     = @"
SELECT [Name],
       [GroupName],
       [ModifiedDate]
FROM [AdventureWorks2019].[HumanResources].[Department]
WHERE DepartmentID = '{0}';
"@

& {
    foreach($id in $invokeSql.DepartmentID) {
        $queryID = $query -f $id
        Invoke-Sqlcmd -ServerInstance $server -Database $database -Query $queryID |
            Select-Object @{ N='DepartmentID'; E={ $id }}, *
    }
} | Export-Csv -Path "C:\Users\Desktop\PowerShell\HashTable_OutputFiles\HashOutput.csv"

edited Jun 12, 2022 at 2:14

answered Jun 12, 2022 at 1:31

Santiago Squarzon

65.5k5 gold badges26 silver badges59 bronze badges

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

2 Comments

Brute Over a year ago

Thanks for answering. The above code returned only one row. Moving the $query in a loop as in ForEach($item in $department){} rectified the issue. But I don't understand is that where is the data from the For loop is being stored because it does show up correctly in the CSV file. Secondly, as great this answer is what I really wanted was to add values in an array\hastable, as they were returned,under certain columns, then exporting all that data to CSV file

Santiago Squarzon Over a year ago

@Brute I have added a second example which is more similar to what you where doing before, that one merges the ID with the second query and exports it directly to Csv.

Collectives™ on Stack Overflow

How to add data from SQL server to hashtable using PowerShell?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related