Powershell Load DataTable to Multi Dimensional Array

Question

Powershell Load DataTable to Multi Dimensional Array

Jaeden Ruiner 131

Okay,

THis should be simple, but thus far i have not been able to do this without annoying over-typed for loops and a lot of other hooplah that shouldn't be needed.

        $conn = New-Object System.Data.SqlClient.SqlConnection  
        $conn.ConnectionString = "Server=$($serverName);Integrated Security=true";  
        $conn.Open();  
  
        $cmd = $conn.CreateCommand()  
        $cmd.CommandText = "SELECT Name, Value FROM mydb.dbo.Table";  
        $table = New-Object System.Data.DataTable  
        $table.Load($cmd.ExecuteReader())  
  
        $conn.Close();  
          
        $column = $table.Columns[0].ColumnName  
  
        $data = @($table | select -ExpandProperty $column)

That above gives me an array of the first column, but i want it to get me an array of the row data. so each row has an "array of values" and i want an array of those arrays.
The C# equivalent would be:

object[][] data = table.OfType<DataRow>().Select(r => r.ItemArray)

or to be more type specific:

string[][] data = table.OfType<DataRow>().Select(r => r.ItemArray.Select(c => c.ToString()).ToArray())

Thus far, i cannot seem to do this is a simple statement or pair of statements in PowerShell, i seem to require doing some form of loop, or create custom objects, etc.
i just want the data formatted into arrays.

Thanks
Jaeden "Sifo-Dyas" al'Raec Ruiner

3 answers

Your answer

Answer 1

Rich Matheisen 47,901

Try Add-Type to execute C# in PowerShell: executing-c-code-in-powershell

Jaeden Ruiner 131 Reputation points

2020-11-16T18:05:43.23+00:00

Though this is a really powerful concept, and something i will investigate in the future, as I was hoping that if i can reference .Net base assemblies that i'd be able to reference my own too, i am already executing the .Net assemblies to retrieve the data I'm just trying to figure out the Powershell way to manipulate the data I'm getting back.
thanks
J"SD"a'RR

Answer 2

Anonymous

Hi,

In powershell you can create a two dimensional array in this way

[string[,]]$data = [string[,]]::new(5,5)

https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.core/about/about_arrays?view=powershell-5.1#properties-of-arrays

Best Regards，
Ian

============================================

If the Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Jaeden Ruiner 131 Reputation points

2020-11-16T18:19:53.907+00:00
That's how to "create" a two dimensional array as a variable.
I'm looking for a way to grab an existing array property and shove that into an array of arrays.

like I tried this:

#1 $data = @($table.Select() | SELECT {$_.ItemArray}) #or 2 $data = $table.Select() | SELECT {$_.ItemArray} #or 3 $data = @($table | SELECT { $_.ItemArray }) #or 4 $data = @($table | SELECT -ExpandProperty ItemArray) #followed by Write-Output $data

1-3 all gave me this output:

$.ItemArray : {Bob, 25}
$.ItemArray : {Dave, 42}

where as #4 gave me:

Bob
25
Dave
42
Jaeden Ruiner 131 Reputation points

2020-11-16T18:20:12.803+00:00
ItemArray is an "object[]" array property on the DataRow object. So why can't i simply "create an array of arrays" from it?
as it is, i'm not trying to create a "2-dimensional array" i'm trying to create an array of arrays.
so the code for accessing it would be:

$rowData= $table | Select Array of arrays here Write-Output "Name\t\t\t| Value" Write-Output "---------------------------------" foreach ($r in $rowData) { Write-Output "$($r[0])\t\t\t| $($r[1])"; } # or if i wanted to: Write-Output $rowData[4][0] #which would grab row 5 and write out the name column

Answer 3

Anonymous

Hi,

Sorry for the misunderstanding. You can create an array of arrays like this

[array[]]$data = [array[]]::new(5)

Best Regards，
Ian

============================================

If the Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Share via

Powershell Load DataTable to Multi Dimensional Array

3 answers

Your answer