PowerShell: I cannot get $DataSet.Tables[0].rows into an array of strings, with no headers

Question

I have this to query SQL from powershell:

PARAM([String] $env_code, [String] $db_server)

$connectionString = "ConnectTheShizz"
$connection = new-object system.data.SqlClient.SQLConnection($connectionString)
$sqlCommand = "SELECT Yada FROM YadaYada"

$command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)
$connection.Open()

$adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
$dataset = New-Object System.Data.DataSet
$adapter.Fill($dataSet) | Out-Null

$connection.Close()

$strStuff = $DataSet.Tables[0].rows| ft -HideTableHeaders

I am not sure what $strStuff is (what DataType it is or whatever). If I just print it out to the console, I get what looks like an array of ints (though in the DB, these are strings, so I am not sure if that is what they are coming back as):

But if I attempt to make them strings:

$strStuff | ForEach-Object {$_.ToString()}
Microsoft.PowerShell.Commands.Internal.Format.FormatStartData
Microsoft.PowerShell.Commands.Internal.Format.GroupStartData
Microsoft.PowerShell.Commands.Internal.Format.FormatEntryData
Microsoft.PowerShell.Commands.Internal.Format.FormatEntryData
...

If I just assume they are strings, and try to roll on down the road, I get this:

$strCusips -join ','
Microsoft.PowerShell.Commands.Internal.Format.FormatStartData,Microsoft.PowerShell.Commands.Internal.Format.GroupStartData,Microsoft.PowerShell.Commands.Internal.Format.FormatEntryData,Microsoft.PowerShell.Commands.Internal.Format.FormatEntryData,...

EDIT:

To be clearer, this is what I have:

This is what I want (a single, comma-separated string):

0003128,0005588,0016308,0021216,...

EDIT 2:

Using .GetType() I get this:

IsPublic IsSerial Name                                     BaseType            
-------- -------- ----                                     --------            
True     True     Object[]                                 System.Array

| Get-Member does this:

   TypeName: Microsoft.PowerShell.Commands.Internal.Format.FormatStartData
Name                                    MemberType Definition                  
----                                    ---------- ----------                  
Equals                                  Method     bool Equals(System.Object...
GetHashCode                             Method     int GetHashCode()           
GetType                                 Method     type GetType()              
ToString                                Method     string ToString()           
autosizeInfo                            Property   Microsoft.PowerShell.Comm...
ClassId2e4f51ef21dd47e99d3c952918aff9cd Property   string ClassId2e4f51ef21d...
groupingEntry                           Property   Microsoft.PowerShell.Comm...
pageFooterEntry                         Property   Microsoft.PowerShell.Comm...
pageHeaderEntry                         Property   Microsoft.PowerShell.Comm...
shapeInfo                               Property   Microsoft.PowerShell.Comm...
   TypeName: Microsoft.PowerShell.Commands.Internal.Format.GroupStartData
Name                                    MemberType Definition                  
----                                    ---------- ----------                  
Equals                                  Method     bool Equals(System.Object...
GetHashCode                             Method     int GetHashCode()           
GetType                                 Method     type GetType()              
ToString                                Method     string ToString()           
ClassId2e4f51ef21dd47e99d3c952918aff9cd Property   string ClassId2e4f51ef21d...
groupingEntry                           Property   Microsoft.PowerShell.Comm...
shapeInfo                               Property   Microsoft.PowerShell.Comm...
   TypeName: Microsoft.PowerShell.Commands.Internal.Format.FormatEntryData
Name                                    MemberType Definition                  
----                                    ---------- ----------                  
Equals                                  Method     bool Equals(System.Object...
GetHashCode                             Method     int GetHashCode()           
GetType                                 Method     type GetType()              
ToString                                Method     string ToString()           
ClassId2e4f51ef21dd47e99d3c952918aff9cd Property   string ClassId2e4f51ef21d...
formatEntryInfo                         Property   Microsoft.PowerShell.Comm...
outOfBand                               Property   bool outOfBand {get;set;}   
writeStream                             Property   Microsoft.PowerShell.Comm...
   TypeName: Microsoft.PowerShell.Commands.Internal.Format.GroupEndData
Name                                    MemberType Definition                  
----                                    ---------- ----------                  
Equals                                  Method     bool Equals(System.Object...
GetHashCode                             Method     int GetHashCode()           
GetType                                 Method     type GetType()              
ToString                                Method     string ToString()           
ClassId2e4f51ef21dd47e99d3c952918aff9cd Property   string ClassId2e4f51ef21d...
groupingEntry                           Property   Microsoft.PowerShell.Comm...
   TypeName: Microsoft.PowerShell.Commands.Internal.Format.FormatEndData
Name                                    MemberType Definition                  
----                                    ---------- ----------                  
Equals                                  Method     bool Equals(System.Object...
GetHashCode                             Method     int GetHashCode()           
GetType                                 Method     type GetType()              
ToString                                Method     string ToString()           
ClassId2e4f51ef21dd47e99d3c952918aff9cd Property   string ClassId2e4f51ef21d...
groupingEntry                           Property   Microsoft.PowerShell.Comm...

Edited above. I dont know how to get what I think is an array of strings into a single comma separated string. `-join` does not seem to work. There seems to be something screwy about the datastructure that I am unaware of. — lukehawk, Aug 09 '17 at 17:40
Use `.GetType()` and `| Get-Member` to see what data type you're working with and if it has accessors for the data you want. — Maximilian Burszley, Aug 09 '17 at 18:04
@Bill - ?? because i have to pass it to an API expecting a comma separated list... ?? — lukehawk, Aug 09 '17 at 18:07
1) Don't use `| ft`. That coerces the output to a string. 2) Determine the property for each row that contains the value you want. 3) Use `| Select-Object -ExpandProperty` with the property name determined in step 2. — Bill_Stewart, Aug 09 '17 at 18:11
@Bill - Thanks, that worked. But what was it before? Using `.GetType()` looks like it is an array of strings, but I could not join them. What data structure is it? — lukehawk, Aug 09 '17 at 18:17
Call the `GetType()` method for one of the objects to see its type, and pipe the individual object to `Get-Member` to see its methods and/or properties. — Bill_Stewart, Aug 09 '17 at 18:19
Dude. WTH is with the duplicate tag? Is there a way to challenge that? — lukehawk, Aug 09 '17 at 18:26

PowerShell: I cannot get $DataSet.Tables[0].rows into an array of strings, with no headers

0 Answers0