T-SQL: export to a new Excel file

Question

I have a script that does different things, and the end result is one big table. I was wondering how I can export this final table to a new Excel file (also with column headers).

I need to do this in a script.

Answer 1

This is by far the best post to export to excel from SQL:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Quote from user madhivanan ,

Besides using the DTS wizard and export, we can also use this query to export data from SQL Server2000 to Excel

Create an Excel file called testing with headers similar to table column headers and use these queries

1 Export data to an existing EXCEL file from a 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 a 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 an 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 do not 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 data file in tabular format)

5 To export data to a new EXCEL file with a header (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 table_name=@table_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 '+@columns+') as t" queryout "'+@file_name+'" -c''' exec(@sql) --Generate data in the dummy file set @sql='exec master..xp_cmdshell ''bcp "select * from '+@db_name+'..'+@table_name+'" queryout "'+@data_file+'" -c''' exec(@sql) --Copy dummy file to passed EXCEL file set @sql= 'exec master..xp_cmdshell ''type '+@data_file+' >> "'+@file_name+'"''' exec(@sql) --Delete dummy file set @sql= 'exec master..xp_cmdshell ''del '+@data_file+'''' exec(@sql) 

After creating the procedure, execute it, specifying the database name, table name, and file path:

 EXEC proc_generate_excel_with_columns 'your dbname', 'your table name','your file path' 

His ad is 29 pages long, but this is because others show different ways, as well as people asking questions in exactly the same way as doing it.

Follow this topic fully and look at the various questions that people asked and how they are resolved. I gained quite a bit of knowledge just by looking at it and using parts of it to get the expected results.

Updating individual cells

The member also there Peter Larson reports the following: I think one thing is missing here. It's great to be able to export and import Excel files, but what about updating individual cells? Or a range of cells?

This is the principle of how you deal with it.

 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 in 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' 

Export with column names using T-SQL

Mladen Prajdic member also has a blog entry on how to do it here.

Links: www.sqlteam.com (by the way, this is a great blog / forum for those who want to get more from SQL Server). To refer to the error, I used this

Errors that may occur

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 

Answer 2

Use PowerShell:

 $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 

Answer 3

I would like to add an additional comment to what JonH did in step 5:

 @columns=coalesce(@columns+',','')+column_name+' as '+'[' + column_name + ']' 

Adding brackets allows columns with spaces to be included in the output.