I recently had cause to create a PS (Powershell) script in order to query a SQL table. This in itself was easy enough, the only thing that was bothering me was that once I had read the data from the table I needed to read the data from the extract and loop this through my script to continue another process.
The issue I had was exporting the data in such a way that I could import it easily into a PS script. After much searching I found some really helpful sites, but none which appeared to do exactly what I wanted, all were close though and so I put a few together to create one that performed exactly as I wanted. So I’m sharing it in case anyone has this problem again.
Please note I’m only a very average script writer, with limited knowledge in this area, however I can perform the basics, and do most of what I need to. You may always find better script writers out there (would be crazy not to mention Scripting Guy amongst others) and better methods of performing what I needed – if you do please share! Otherwise please feel free to use as follows:
#SQL connection details
$SQLServer = "server" #use Server\Instance for named SQL instances!
$SQLDBName = "dbname"
$SqlQuery = "SELECT * FROM table"
#location of extract file
$extractFile = @"
c:\scripts\csv\list.csv
"@
#create SQL connection and extract dataset to file
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
clear
$DataSet.Tables[0] | Export-Csv -Force -Delimiter "," $extractFile
Once I’d completed this the data was extracted in the columns I wanted from the SQL table (my select statement was actually fairly more complex to account for codes and required texts; 0s!) I could loop my data through a foreach command.
Just replace the SQL Server name, database, Select statement and file locations with yours and you will be ready to go.
Happy days.
itsMonkeyBoy 