How to get the first row from a PowerShell datatable

Question

In PowerShell, if $dt is a datatable, I am used to using foreach() to do row-by-row operations. For example...

foreach ($tmpRow in $dt) {
   Write-Host $tmpRow.blabla
}

I just want to get the first row (of n columns). I could introduce a counter $i and just break the foreach loop on the first iteration, but that seems clunky. Is there a more direct way of achieving this?

Answer 1

• For a collection (array) that is already in memory, use indexing, namely [0]:

  • Note: Normally, $dt[0] should suffice, but in this case the index must be applied to the .Rows property, as Theo advises:

    • $dt.Rows[0].blabla

  • Given that PowerShell automatically enumerates a System.Data.DataTable by enumerating the System.Data.DataRow instances stored in its .Rows property - both in the pipeline and in a foreach loop, as in your code - the need to specify .Rows explicitly for indexing is surprising.

    • With $dt containing a System.Data.DataTable instance, $dt[0] is actually the same as just $dt itself, because PowerShell in this context considers $dt a single object, and generally supports indexing even into such single objects, in the interest of unified treatment of scalars and arrays - see this answer for background information.

• For command output, use Select-Object -First 1. Using the example of Invoke-SqlCmd

  • (Invoke-SqlCommand ... | Select-Object -First 1).blabla

  • Note: Since Invoke-SqlCommand by default outputs individual System.Data.DataRow instances (one by one), you can directly access property .blabla on the result.

  • The advantage of using Select-Object -First 1 is that short-circuits the pipeline and returns once the first output object has been received, obviating the need to retrieve further objects.

Answer 2

PowerShell automatically enumerates all rows when you pipe a DataTable, so you could pipe it to Select-Object -First 1:

# set up sample table
$dt = [System.Data.DataTable]::new()
[void]$dt.Columns.Add('ID', [int])
[void]$dt.Columns.Add('Name', [string])

# initialize with 2 rows
[void]$dt.Rows.Add(1, "Clive")
[void]$dt.Rows.Add(2, "Mathias")

# enumerate only 1 row
foreach ($tmpRow in $dt |Select-Object -First 1) {
   Write-Host "Row with ID '$($tmpRow.ID)' has name '$($tmpRow.Name)'"
}

Expected screen buffer output:

Row with ID '1' has name 'Clive'