Exporting SQL Query to Excel

PowerShell Help
1

Hey! I am new to this but trying to get it worked out, hoping to find some advice here.

My setup:
Excel 2016 64 Bit
SQL Server 2014 Import and Export Data (64-Bit)
Windows 7 64 Bit
Windows PowerShell ISE (x86)
Microsoft OLE DB Provider for SQL Server

I can export my SQL database perfectly into excel, now I’m just trying to automate it. Here is the code I am currently using:

$serverName = "E2\E2SQL";
$databaseName = "*****";
$uid ="*****"
$pwd = "*****"
#the save location for the new Excel file
$filepath = "C:\Users\Bob\Desktop\TEST.xls";

#create a Dataset to store the DataTable 
$dataSet = new-object "System.Data.DataSet" "TEST"

#create a Connection to the SQL Server database
$cn = new-object System.Data.SqlClient.SqlConnection "server=$serverName;database=$databaseName;Integrated Security=false; User ID = $uid; Password = $pwd;"
$query= "SELECT JobNo FROM Online;"
#Create a SQL Data Adapter to place the resultset into the DataSet
$dataAdapter = new-object "System.Data.SqlClient.SqlDataAdapter" ($query, $cn)
$dataAdapter.Fill($dataSet) | Out-Null
#close the connection
$cn.Close()

I get an error on
$dataSet = new-object “System.Data.DataSet” “TEST”

I don’t really understand that line of code… Anyway any thoughts on how to get this working are greatly appreciated!

2

I don’t understand it either. I’m not sure why the class name is in quotes - that’s unusual.

The class is documented at https://msdn.microsoft.com/en-us/library/system.data.dataset(v=vs.110).aspx. The docs show a constructor that accepts a single string, which is what you’re providing. That string defines the name of the data set.

I’ll mention briefly that you’re not really “doing PowerShell,” here. This is foundational .NET. I bring it up only because this might not be the very best venue for questions, and someplace like StackOverflow.com might end up netting you better answers. That’s a downside to digging into .NET - it’s really C# programming, and the usual PowerShell usage patterns don’t apply.

You’re also going to have a devil of a time turning this directly into an XLS, as there’s no core .NET functionality to do so. You’ll end up using the ancient Excel Automation stuff. However, you should be able to dump a DataTable directly to Export-CSV to create a CSV file, which Excel will happily read.

As a former SQL guy, I’ll also mention that SQL Server Integration Services (SSIS) would be happy to do all of this export-to-XLS for you, all from a wonderful drag-and-drop interface that won’t require any coding and can be run on a schedule. I’m not sure PowerShell is the best tool for the job, in other words.