question

ChristopherJack-1763 avatar image
0 Votes"
ChristopherJack-1763 asked IanXue-MSFT answered

Read SQL into a Array powershell

Hi,

I have PS code that reads csv file into an array using


 $file = Import-Csv $edifile
 $OrderInfo += $file | Where-Object {$_."Issue Date" -eq $Datearray[$i]} | Select-Object "Order Number"

Which means I can then reference elements of the array based on their column headings.

I am trying to get to the same result using a SQL query

 ## - Connect to SQL Server using non-SMO class 'System.Data':
 $SqlConnection = New-Object System.Data.SqlClient.SqlConnection;
 $SqlConnection.ConnectionString = `
 "Server = $SQLServer; Database = $Database; Integrated Security = True";
    
 $SqlCmd = New-Object System.Data.SqlClient.SqlCommand;
 $SqlCmd.CommandText = $("EXEC [schema].[ReboundHeader]");
 $SqlCmd.Connection = $SqlConnection;
 $SqlCmd.CommandTimeout = 0;
    
 ## - Extract and build the SQL data object '$DataSetTable':
 $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter;
 $SqlAdapter.SelectCommand = $SqlCmd;
 $DataSet = New-Object System.Data.DataSet;
 $SqlAdapter.Fill($DataSet);
 $DataSetTable = $DataSet.Tables["Table"];

The output from sql in VS does not look the same as the CSV file import.

How do I output the SQL into an array which I can then reference the same way as the CSV import?

TFH



windows-server-powershell
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @ChristopherJack-1763 ,

at least for me it's hard to say what needs to be done because I don't know how the CSV file looks like nor how the SQL Data result-set looks like.

Or maybe I didn't understand your question?

Basically you have your SQL query, my guess it's in $SqlCmd?
This SQL query should contain the same columns in the same order like your CSV file.
This way the $DataSetTable should contain the same columns and data.


(If the reply was helpful please don't forget to upvote and/or accept as answer, thank you)

Regards
Andreas Baumgarten

0 Votes 0 ·

Hi Andreas,

its more how to reference the data. When I read it in from a CSV FILE into an array I can reference it via

$array[0]."Column Name"

but when I read in the SQL Ive tried to find similar but cant seem to see how reference columns.

No specific column names.

0 Votes 0 ·

What happens if you try this $DataSet.Rows[0] after the line 15 in your script ( $SqlAdapter.Fill($DataSet); )
Do you see the column names?

What is the content of $DataSet? It contains all the data you need?


(If the reply was helpful please don't forget to upvote and/or accept as answer, thank you)

Regards
Andreas Baumgarten

0 Votes 0 ·

1 Answer

IanXue-MSFT avatar image
0 Votes"
IanXue-MSFT answered

Hi,

Please check the value of $DataSet.Tables which is retrieved by the stored procedure "[schema].[ReboundHeader]".

Best Regards,
Ian Xue
============================================
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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.