by gladguy at 2012-09-26 19:25:35
Hi,by cmille19 at 2012-09-27 19:16:47
I am trying to generate a report for the database freespace and export that to a .xlsx file using powershell. I am new to Powershell.
I am not sure how to get the ouput from a dataset table into a worksheet. The code i used creates the file but with no data in it.
[code2=powershell]# Get Free space for the listed servers in different worksheets
#Create Excel Com Object
$msexcel = new-object -com excel.application
#make Excel application visible
$msexcel.Visible = $True
#put a new workbook
$workbook = $msexcel.workbooks.Add()
#go to the first worksheet in the work book
$worksheet1 = $workbook.worksheets.Item(1)
#Name the worksheet
$worksheet1.Name = 'Freespace'
#delete all the remaining worksheets (depends on the number of sheets excel is configured to 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 as Date
, InstanceName as [Instance Name]
, DatabaseName as [Database Name]
, CONVERT(DECIMAL(10,2), REPLACE(db_size, ' MB', '')) as [Size in MB]
, Status
INTO #db_attributes
FROM tblDatabaseAttributes WHERE DT_Inserted = @runDate AND dbid > 4
SELECT * FROM #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 the output from the dataset dt to worksheet
# ??? I am not able to get this bit working
$worksheet1 = $dt
# Any help on this please
#save spreadsheet
$workbook.saveas("C:\DBA\freespace.xlsx")
#Close Excel
$msexcel.Quit()
}[/code2]
I see several problems. First you’re trying to set your worksheet variable which is a Com-object to an ADO.NET datatable–this will not work. Second you’re overwriting your dt and worksheet on each iteration through our foreach loop and lastly the query you provided does not run.by gladguy at 2012-10-02 20:58:00
Rather try to fix everything you had I took slightly different approach and used an Excel Powershell module http://blogs.technet.com/b/heyscriptingguy/archive/2011/07/21/use-a-powershell-module-to-easily-export-excel-data-to-csv.aspx
Here’s what I came up with:
#mkdir c:\dba
#echo "$env:computername\sql1" > c:\dba\serverlist.txt
#echo "$env:computername\sqlexpress" >> C:\dba\serverlist.txt
$data = new-object System.Data.DataTable
foreach ($svr in get-content "C:\DBA\serverlist.txt")
{
$con = "server=$svr;database=master;Integrated Security=sspi"
$cmd = @"
CREATE TABLE #output(
server_name varchar(128),
dbname varchar(128),
physical_name varchar(260),
dt datetime,
file_group_name varchar(128),
size_mb int,
free_mb int)
exec sp_MSforeachdb @command1=
‘USE [?]; INSERT #output
SELECT CAST(SERVERPROPERTY(’‘ServerName’‘) AS varchar(128)) AS server_name,
’‘?’’ AS dbname,
f.filename AS physical_name,
CAST(FLOOR(CAST(getdate() AS float)) AS datetime) AS dt,
g.groupname,
CAST (size*8.0/1024.0 AS int) AS ‘‘size_mb’’,
CAST((size - FILEPROPERTY(f.name,‘‘SpaceUsed’’))*8.0/1024.0 AS int) AS ‘‘free_mb’’
FROM sysfiles f
JOIN sysfilegroups g
ON f.groupid = g.groupid’
SELECT * FROM #output
"@
$da = new-object System.Data.SqlClient.SqlDataAdapter ($cmd, $con)
$dt = new-object System.Data.DataTable
$da.fill($dt) | out-null
$data.Merge($dt)
}
import-module importexportexcel
$data = $data | select server_name,dbname,physical_name,dt,file_group_name,size_mb,free_mb
Export-Excel -Path .\Example.xlsx -InputObject $data
ii .\Example.xlsx
Thank you for the reply and suggestions. I used some of your suggestions and the reference links provided and managed to get the output into an Excel Spreadsheet using the below code.by cmille19 at 2012-10-03 08:02:38# set the query
$query = " DECLARE @runDate datetime;
SET @runDate = '2012-01-01'
SELECT
DT as Date
, InstanceName as [Instance Name]
, DatabaseName as [Database Name]
, CONVERT(DECIMAL(10,2), REPLACE(db_size, ' MB', '')) as [Size in MB]
, Status
INTO #db_attributes
FROM tblDatabaseAttributes WHERE DT_Inserted = @runDate AND dbid > 4
SELECT * FROM #disk_attributes
DROP table #db_attributes
"
# Opent excel com objcts
$excel = New-Object -ComObject excel.application
$excel.visible = $False
$excel.displayalerts=$False
# output file path
$csvoutputfile = "C:\DBA\A.csv"
$excelfile = "C:\DBA\report.xls"
foreach ($svr in get-content "C:\DBA\servers.txt")
{
$results = invoke-sqlcmd -query $query -serverinstance $svr -database db1
Write-host $svr
$results |export-csv $csvoutputfile -notypeinformation
$workbook = $excel.Workbooks.Open($csvoutputfile)
$workSheet = $workbook.worksheets.Item(1)
$resize = $workSheet.UsedRange
$resize.EntireColumn.AutoFit() | Out-Null
$xlExcel8 = 56
$workbook.SaveAs($excelFile,$xlExcel8)
$workbook.Close()
}
$excel.quit()
$excel = $null
Note: I had to use sqlps.exe for the Invoke-SQLCMD to work.
The output from this script overwrites the same sheet. Is there a way to get the output written to different sheets for every server in the servers.txt file?
Someone has already done this and posted the code on PoshCode. See http://poshcode.org/2123
What you should do is simply generate a CSV file per server, then source and call the script to convert CSVs to Excel:
. .\ConvertCSV-ToExcel.ps1
get-ChildItem C:\DBA*.csv | ConvertCSV-ToExcel -output ‘report.xlsx’