DSN-Less Export To XLSX

Question

I have seen a few examples on how to export to .xlsx but they all require a DSN. I need a DSNLess example of how to export Sql Server 2008 query results to a .xlsx file.

How I am connecting to sql server and running a select query

$server = "Server1"
$database = "database1"
$query = "SELECT * from master"
$filepath = "C:\projects\excel1.xls";

$excel = New-Object -ComObject Excel.Application
$workbook = $excel.Workbooks.add()
$worksheetA = $workbook.Worksheets.Add()
$sheet1 = $workbook.worksheets.Item(1)
$sheet1.name = "Sheet1 - Test"

$connectionTemplate = "Data Source={0};Integrated Security=SSPI;Initial Catalog={1};"
$connectionString = [string]::Format($connectionTemplate, $server,   $database)
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString

$command = New-Object System.Data.SqlClient.SqlCommand
$command.CommandText = $query
$command.Connection = $connection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $command
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$dataTable = new-object "System.Data.DataTable"
$dataTable = $dataSet.Tables[0]

$x=1
$dataTable | FOREACH-OBJECT{
#Here is where the actual export to .xlsx would go
$x++}

$excel.ActiveWorkbook.SaveAs("$filepath ")
$excel.quit()

Answer 1

You don't need a DSN. You just need a properly-formatted ConnectionString.

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";

Then use an OleDbConnection/Command to execute your queries.

Or, export to CSV and then convert to XLSX within Excel.