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?

Or: `$dt | Select -First 1 | Foreach { ...` – iRon Dec 07 '20 at 15:14 — iRon, Dec 07 '20 at 15:14

mklement0 · Answer 1 · 2020-12-07T17:30:42.907

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.

score 4 · Answer 2 · answered Dec 07 '20 at 15:16

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'

How to get the first row from a PowerShell datatable

2 Answers2