I have a script that does various things and the end result is one large table. I was wondering how I could export this final table to a new Excel file (with column headers as well).
I would need to do this within the script.
This question is tagged with
~ Asked on 2012-01-31 21:00:48
This is by far the best post for exporting to excel from SQL:
To quote from user
Apart from using DTS and Export wizard, we can also use this query to export data from SQL Server2000 to Excel
Create an Excel file named testing having the headers same as that of table columns and use these queries
1 Export data to existing EXCEL file from SQL Server table
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\testing.xls;', 'SELECT * FROM [SheetName$]') select * from SQLServerTable
2 Export data from Excel to new SQL Server table
select * into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\testing.xls;HDR=YES', 'SELECT * FROM [Sheet1$]')
3 Export data from Excel to existing SQL Server table (edited)
Insert into SQLServerTable Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\testing.xls;HDR=YES', 'SELECT * FROM [SheetName$]')
4 If you dont want to create an EXCEL file in advance and want to export data to it, use
EXEC sp_makewebtask @outputfile = 'd:\testing.xls', @query = 'Select * from Database_name..SQLServerTable', @colheaders =1, @FixedFont=0,@lastupdated=0,@resultstitle='Testing details'
(Now you can find the file with data in tabular format)
5 To export data to new EXCEL file with heading(column names), create the following procedure
create procedure proc_generate_excel_with_columns ( @db_name varchar(100), @table_name varchar(100), @file_name varchar(100) ) as --Generate column names as a recordset declare @columns varchar(8000), @sql varchar(8000), @data_file varchar(100) select @columns=coalesce(@columns+',','')+column_name+' as '+column_name from information_schema.columns where [email protected]_name select @columns=''''''+replace(replace(@columns,' as ',''''' as '),',',',''''') --Create a dummy file to have actual data select @data_file=substring(@file_name,1,len(@file_name)-charindex('\',reverse(@file_name)))+'\data_file.xls' --Generate column names in the passed EXCEL file set @sql='exec master..xp_cmdshell ''bcp " select * from (select '[email protected]+') as t" queryout "'[email protected]_name+'" -c''' exec(@sql) --Generate data in the dummy file set @sql='exec master..xp_cmdshell ''bcp "select * from '[email protected]_name+'..'[email protected]_name+'" queryout "'[email protected]_file+'" -c''' exec(@sql) --Copy dummy file to passed EXCEL file set @sql= 'exec master..xp_cmdshell ''type '[email protected]_file+' >> "'[email protected]_name+'"''' exec(@sql) --Delete dummy file set @sql= 'exec master..xp_cmdshell ''del '[email protected]_file+'''' exec(@sql)
After creating the procedure, execute it by supplying database name, table name and file path:
EXEC proc_generate_excel_with_columns 'your dbname', 'your table name','your file path'
Its a whomping 29 pages but that is because others show various other ways as well as people asking questions just like this one on how to do it.
Follow that thread entirely and look at the various questions people have asked and how they are solved. I picked up quite a bit of knowledge just skimming it and have used portions of it to get expected results.
To update single cells
A member also there Peter Larson posts the following: I think one thing is missing here. It is great to be able to Export and Import to Excel files, but how about updating single cells? Or a range of cells?
This is the principle of how you do manage that
update OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\test.xls;hdr=no', 'SELECT * FROM [Sheet1$b7:b7]') set f1 = -99
You can also add formulas to Excel using this:
update OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\test.xls;hdr=no', 'SELECT * FROM [Sheet1$b7:b7]') set f1 = '=a7+c7'
Exporting with column names using T-SQL
Member Mladen Prajdic also has a blog entry on how to do this here
If you get the following error:
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries
Then run this:
sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'Ad Hoc Distributed Queries', 1; GO RECONFIGURE; GO
~ Answered on 2012-01-31 21:01:25
$Server = "TestServer" $Database = "TestDatabase" $Query = "select * from TestTable" $FilePath = "C:\OutputFile.csv" # This will overwrite the file if it already exists. Invoke-Sqlcmd -Query $Query -Database $Database -ServerInstance $Server | Export-Csv $FilePath
In my usual cases, all I really need is a CSV file that can be read by Excel. However, if you need an actual Excel file, then tack on some code to convert the CSV file to an Excel file. This answer gives a solution for this, but I've not tested it.
~ Answered on 2017-05-09 14:25:56