Export SQL Query results to .xlsx file

hi,

i trying generate report database freespace , export .xlsx file using powershell. new powershell.

i not sure how ouput dataset table worksheet. code used creates file no data in it.

# free space listed servers in different worksheets  #create excel com object  $msexcel = new-object -com excel.application  #make excel application visible  $msexcel.visible = $true  #put new workbook  $workbook = $msexcel.workbooks.add()  #go first worksheet in work book  $worksheet1 = $workbook.worksheets.item(1)  #name worksheet  $worksheet1.name = 'freespace'  #delete remaining worksheets (depends on number of sheets excel configured open with)  $workbook.worksheets.item(2).delete()  $workbook.worksheets.item(2).delete()  foreach ($svr in get-content "c:\dba\serverlist.txt")  {    $con = "server=$svr;database=master;integrated security=sspi"     $cmd = " use db1      declare @rundate datetime;      set @rundate = '2012-01-01'   select   	  dt date  	, instancename [instance name]  	, databasename [database name]  	, convert(decimal(10,2), replace(db_size, ' mb', '')) [size in mb]  	, status  #db_attributes  tbldatabaseattributes dt_inserted = @rundate , dbid > 4  select * #disk_attributes  drop table #db_attributes  "  $da = new-object system.data.sqlclient.sqldataadapter ($cmd, $con)  $dt = new-object system.data.datatable  $da.fill($dt) | out-null   # populate output dataset dt worksheet  # ??????  $worksheet1 = $dt  #save spreadsheet  $workbook.saveas("c:\dba\freespace.xlsx")  #close excel  $msexcel.quit()   }

any appreciated.

cheers,

hi,

1. make sure in sqlps console, or have community sqlps (or sqlpsv2) module loaded ( ie. import-module sqlps ) in either powershell console or ise.

2. have query ready, then save powershell variable:

$sql = “select * [adventureworks].[production].[location]“

3. next one-liner will build powershell object , exported *csv file: (execute in localhost only. use -serverinstance -database parameter if needed t0 execute query)

invoke-sqlcmd -query $sql | export-csv -path c:\temp\excelfile.csv -notypeinformation

4. last line open file in excel:

ii c:\temp\excelfile.csv

this surely beats going ssis quick results!

for more details:

export query excel sheet automatically

http://sethusrinivasan.com/2012/04/06/export-the-query-to-excel-sheet-automatically/

http://sqlserverpedia.com/blog/sql-server-bloggers/powershell-and-sql/

hope helps.

regards,

yan li

technet subscriber support

if are technet subscription user , have feedback on our support quality, please send feedback here.

---

yan li

technet community support

Windows Server  >  Windows PowerShell