Extract data from System.Data.DataRow in PowerShell

Question

I have a PowerShell script that executes a SQL command and returns a list of ID numbers.

When I iterate through the list, this is what it returns.

System.Data.DataRow
System.Data.DataRow
System.Data.DataRow
System.Data.DataRow
System.Data.DataRow
System.Data.DataRow

I tried adding Out-String to my list,

$q_result = $db.ExecuteWithResults($int_cmd2)
$table = $q_result.Tables[0] | Out-String

foreach ($user_info in $table)
{
    write-host $user_info 
}

but that returns a poorly formatted list of numbers, everything is tabbed to the far right:

                                                                                                      GroupID
                                                                                                      -------------
                                                                                                                381
                                                                                                                382
                                                                                                                383
                                                                                                                384
                                                                                                                385
                                                                                                                386

I tried using, $user_info.Item[0] in the loop, but that returns nothing.

How can I extract just the numbers from the list?

zstefanova · Accepted Answer · 2019-02-28 07:47:47Z

36

You can also use ItemArray. Prints the entire row, while Item(index) prints the values from the indexed cell.

answered Feb 28, 2019 at 7:47

zstefanova

1,8312 gold badges28 silver badges42 bronze badges

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

Comments

Mathias R. Jessen · Accepted Answer · 2018-07-13 13:30:58Z

25

Item is a parameterized property, not a list you can index into:

foreach($row in $table)
{
    $row.Item("GroupID")
}

or (assuming that "GroupID" is the first column):

foreach($row in $table)
{
    $row.Item(0)
}

edited Jul 13, 2018 at 13:30

answered Aug 30, 2016 at 10:30

Mathias R. Jessen

178k13 gold badges175 silver badges234 bronze badges

1 Comment

cyberbemon Over a year ago

Thank you, I got the Item Mixed up, it's listed as a property in the MSDN page.

Dominic Flynn · Accepted Answer · 2019-09-19 00:06:52Z

4

Assuming the column name is GroupID

$sql = "SELECT [GroupID] FROM theTable"
$table = Invoke-SQLCmd -Query $sql -ServerInstance $sqlServerInstance -Database $database -OutputAs DataRows

foreach($user_info in $table)
{
    $user_info.GroupID
}

or

foreach($user_info in $table.GroupID)
{
    $user_info
}

edited Sep 19, 2019 at 0:06

answered Sep 18, 2019 at 23:59

Dominic Flynn

712 bronze badges

Collectives™ on Stack Overflow

Extract data from System.Data.DataRow in PowerShell

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related