Powershell - Parse System.Data.DataRow to String

string powershell datarow
infinity picture infinity · Oct 18, 2012 · Viewed 27.8k times · Source

I need your inputs on how to convert the output from a SQL query into a string in powershell. I am currently doing the following

function ExecuteFromScriptRowset($connectionParams, $file)
{
     return (invoke-sqlcmd @connectionParams 
             -inputFile $file -MaxCharLength 8000000)
}

I use the above function to call a .sql file that does something like 

SELECT Names FROM tblXXX

Here is the actual powershell code that I use to invoke the SQL stored proc

$output = ExecuteFromScriptRowset $someDB (Resolve-Path '.\selectXXX.sql')

The output is an array of System.Data.DataRow which I need to convert to a string array. I am currently trying out the following which is not working as intended

$formatOut = @()

for ($i=0; $i -le $output.Length; $i++)
{
    $formatOut = $formatOut + [string]$output[$i]
}

Here is the output of $formatOut is after the for loop and this is not what I was looking for

$formatOut[0] = System.Data.DataRow
$formatOut[1] = System.Data.DataRow
$formatOut[2] = System.Data.DataRow

Could you please let me know what I need to do so that I create a string array of the contents of the $output object

Answer

Neolisk picture Neolisk · Oct 18, 2012

What you are seeing is completely normal, because by default ToString will output object type. This is per .NET specifications.

You can convert a DataRow to array of object using ItemArray, and then join via "," or whichever join method you prefer, like this:

$formatOut = $formatOut + ($output[$i].ItemArray -join ",")

Related questions

How can I check if a string is null or empty in PowerShell?

Is there a built-in IsNullOrEmpty-like function in order to check if a string is null or empty, in PowerShell? I could not find it so far and if there is a built-in way, I do not want to write a …

Read More
How to search a string in multiple files and return the names of files in Powershell?

I have started learning powershell a couple of days ago, and I couldn't find anything on google that does what I need so please bear with my question. I have been asked to replace some text strings into multiple files. …

Read More
Splitting a string into separate variables

I have a string, which I have split using the code $CreateDT.Split(" "). I now want to manipulate two separate strings in different ways. How can I separate these into two variables?

Read More