T-SQL：导出到新的Excel文件 [英] T-SQL: Export to new Excel file

问题描述

我有一个脚本，做各种各样的事情，最终的结果是一个大桌子。我想知道如何将这个决赛桌面导出到一个新的Excel文件（列标题）。

我需要在脚本中执行此操作。 p>

解决方案

这是迄今为止从SQL导出为ex​​cel的最佳帖子：

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

从用户引用 madhivanan ，

除了使用DTS和导出向导，我们还可以使用此查询将数据从SQL Server2000导出到Excel

创建一个名为testing的Excel文件，其标题与从SQL Server表中导出数据到现有EXCEL文件

表格列，并使用这些查询
 
 
 
  insert into OPENROWSET（'Microsoft.Jet.OLEDB.4.0'，
'Excel 8.0; Database = D：\testing.xls;'，
'SELECT * FROM [SheetName $]'）选择*从SQLServerTable 
  

2将数据从Excel导出到新的SQL Server表

  select * 
 into SQLServerTable FROM OPENROWSET（'Microsoft.Jet.OLEDB.4.0'，
'Excel 8.0; Database = D：\\ \\ testing.xls; HDR = YES'，
'SELECT * FROM [Sheet1 $]'）
  

将数据从Excel导出到现有SQL Server表（编辑）

 插入到SQLServerTable中选择* FROM OPENROWSET .Jet.OLEDB.4.0'，
'Excel 8.0; Database = D：\testing.xls; HDR = YES'，
'SELECT * FROM [SheetName $]'）
  

4如果您不想提前创建一个EXCEL文件，并希望导出数据，请使用

  EXEC sp_makewebtask 
 @outputfile ='d：\testing.xls'，
 @query ='从* ..SQLServerTable'，
 @colheaders = 1，
 @ FixedFont = 0，@ lastupdated = 0，@ resultsti tle ='测试细节'
  

（现在您可以用表格格式查找包含数据的文件） p>

5要使用标题（列名）将数据导出到新的EXCEL文件，请创建以下过程

 创建过程proc_generate_excel_with_columns 
（
 @db_name varchar（100），
 @table_name varchar（100），
 @file_name varchar（100）
 ）
 as 
 
  - 将列名作为记录集生成
 declare @columns varchar（8000），@sql varchar（8000），@data_file varchar（100）
选择
 @ columns = coalesce（@columns +'，'，''）+ column_name +'as'+ column_name 
 from 
 information_schema.columns 
其中
 table_name = @table_name 
选择@columns =''''''+替换（替换（@columns，'as'，'''''as'），'，'，'，'''''' b 
 $ b  - 创建一个虚拟文件以具有实际数据
 select @ data_file = substring（@ file_name，1，len（@file_name） -charindex（'\'，reverse（@file_name）））+'\data_file.xls'
 
  - 在传递的EXCEL文件中生成列名称
 set @ sql =' exec master..xp_cmdshell''bcpselect * from（select'+ @ columns +'）as tqueryout'+ @ file_name +'-c'''
 exec（@sql）
 
  - 在虚拟文件中生成数据
 set @ sql ='exec master..xp_cmdshell''bcpselect * from'+ @ db_name +'..'+ @ table_name +'queryout'+ @ data_file +'-c'''
 exec（@sql）
 
  - 复制虚拟文件以传递EXCEL文件
 set @ sql ='exec master..xp_cmdshell'键入'+ @ data_file +'>> '+ @ file_name +''''
 exec（@sql）
 
  - 删除虚拟文件
 set @ sql ='exec master..xp_cmdshell''del' + @ data_file +''''
 exec（@sql）
  

创建该过程后，通过提供数据库名称，表名和文件路径来执行它：

  EXEC proc_generate_excel_with_columns'你的dbname'，'你的表名' '你的文件路径'
  

它是一个谁打29页，但这是因为别人显示各种其他方式当人们提出问题就像这样就如何做到这一点。

完全遵循这个线程，看看人们提出的各种问题，以及如何解决问题。我拿起了相当多的知识，只是撇去它，并使用了其中的一部分来获得预期的结果。

更新单元格

还有一个成员Peter Larson发布了以下内容：
我觉得有一件事在这里缺少。能够导出和导入到Excel文件是非常好的，但是如何更新单个单元格？还是一系列单元格？

这是您如何管理

  update OPENROWSET（'Microsoft.Jet.OLEDB.4.0'，
'Excel 8.0; Database = c：\test.xls; hdr = no'，
'SELECT * FROM [ Sheet1 $ b7：b7]'）设置f1 = -99 
  

您还可以使用这个：

 更新OPENROWSET（'Microsoft.Jet.OLEDB.4.0'，
'Excel 8.0; Database = c ：\test.xls; hdr = no'，
'SELECT * FROM [Sheet1 $ b7：b7]'）设置f1 ='= a7 + c7'
  / pre> 
 
 
 使用T-SQL导出列名称 
 
 
 
会员Mladen Prajdic还有一个关于如何执行此操作的博客条目此处 
 
 
 
参考文献： www.sqlteam.com （btw这是一个很好的博客/论坛，获得更多的SQL Server）。对于错误引用我使用这个 
 
 
 
错误可能会发生
 
 
 
如果您收到以下错误：
 
 
 OLE DB提供程序' Microsoft.Jet.OLEDB.4.0'不能用于
分发查询
 
 
然后运行这个：
  sp_configure'show advanced options'，1; 
 GO 
 RECONFIGURE; 
 GO 
 sp_configure'Ad Hoc Distributed Queries'，1; 
 GO 
 RECONFIGURE; 
 GO 
  
 
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 is by far the best post for exporting to excel from SQL:

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

To quote from user madhivanan,

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

References: www.sqlteam.com (btw this is an excellent blog / forum for anyone looking to get more out of SQL Server).  For error referencing 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


                        
这篇关于T-SQL：导出到新的Excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！