sql怎么导出带标题的csv格式_SQL Server带列名导出到Excel(Export to CSV with headers)的几个思路...

前几天在项目中遇到一个问题，需要从SQL Server导出表到Excel，但需要带列名。晚上尝试了几种方法，并作个小结。

假定表如下：

USEtestDb2GO

IF NOT OBJECT_ID('Demo_A') IS NULL

DROP TABLE [Demo_A]

/****** Object: Table [dbo].[Demo_A] downmoon:3w@live.cn ******/

CREATE TABLE [dbo].[Demo_A]([ID] int not null,[Name] [Nvarchar](20) NOT NULL)GO

INSERT [dbo].[Demo_A]

SELECT 1,'郭靖'

union ALL SELECT 2,'胡一刀'

union ALL SELECT 3,'令狐冲'

GO

如果通常的思路，我们可以用BCP，命令如下：

--To allow advanced options to be changed.

EXEC sp_configure 'show advanced options', 1

GO

--To update the currently configured value for advanced options.

RECONFIGURE

GO

--To enable the feature.

EXEC sp_configure 'xp_cmdshell', 1

GO

--To update the currently configured value for this feature.

RECONFIGURE

GO

EXEC master..xp_cmdshell 'bcp Testdb2.dbo.Demo_A out c:\Temp.xls -c -q -S"ap4\Net2012" -U"sa" -P"sA"'

方法一：使用BCP

为了方便，我创建了一个存储过程：

/****** SQL Export to xls ***************/

/*Example*/

/*CPP_Export_To_Excel_With_Header 'Testdb2','Demo_A','C:\TestExxelWithHeader.xls'*/

/*2012.5.4 BY tony,邀月, 3w@live.cn*/

---- CPP_Export_To_Excel_With_Header 'Testdb2','Demo_A','C:\TestExcelWithHeader.xls'

Create ProcedureCPP_Export_To_Excel_With_Header

(@db_name varchar(255),@table_name varchar(255),@file_path varchar(255)

)as

----Generate column names as a recordset

declare @columns varchar(8000), @sql varchar(8000)declare @HeadersOnlyFile varchar(255),@TableDataWithoutHeaders varchar(255)set @HeadersOnlyFile=replace(cast(newid() as VARCHAR(40)),'-','')+'1.xls'

set @TableDataWithoutHeaders=replace(cast(newid() as VARCHAR(40)),'-','')+'2.xls'

select

@columns=coalesce(@columns+',','')+column_name+'as'+column_namefrominformation_schema.columnswheretable_name=@table_name

select @columns=''''''+replace(replace(@columns,'as','''''as'),',',',''''')print @columns

----Generate column names file

set @sql='exec master..xp_cmdshell''bcp " select * from (select'+@columns+') as t" queryout "'+@HeadersOnlyFile+'" -c'''

print @sql

exec(@sql)----Create a dummy file to have actual data

set @sql='exec master..xp_cmdshell''bcp "'+@db_name+'.dbo.'+@table_name+'" out "'+@TableDataWithoutHeaders+'" -c -t -T'''

print @sql

exec(@sql)--Merge File into One Final Format

set @sql='exec master..xp_cmdshell''copy /b'+@HeadersOnlyFile+'+'+@TableDataWithoutHeaders+' '+@file_path+''''

print @sql

exec(@sql)--Delete temp File

set @sql='exec master..xp_cmdshell''del'+@HeadersOnlyFile+''''

exec(@sql)set @sql='exec master..xp_cmdshell''del'+@TableDataWithoutHeaders+''''

exec(@sql)

调用方法：

CPP_Export_To_Excel_With_Header 'Testdb2','Demo_A','C:\TestExcelWithHeader.xls'

方法二：

create procedureproc_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_namefrominformation_schema.columnswheretable_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)

调用示例：

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

如果，你的环境是SQL Server 2005,那么可以有：

方法三，使用sp_makewebtask,仅适用于SQL Server 2005

0)表T1结构

aintbintxchar

1)开启Web Assistant Proceduresexec sp_configure 'show advanced options', 1

RECONFIGURE

exec sp_configure 'Web Assistant Procedures', 1

RECONFIGURE

2)执行如下语句EXECsp_makewebtask@outputfile = 'd:\testing.xls',@query = 'Select TOP 10 * from shenliang1985..T1',@colheaders =1,@FixedFont=0,@lastupdated=0,@resultstitle='Querying details'

3)查看生成的EXCEl的

Querying details

Last updated:2010-03-03 01:02:59.263a b x0 0 0

2 5 1

4 10 2

6 15 3

8 20 4

10 25 5

12 30 6

14 35 7

16 40 8

18 45 9

可惜SQL Server 2008以后sp_makewebtask 这个存储过程取消了，后续版本也不再启用。

方法一和方法二其实生成的文件都不上真正的Excel文件，虽然后缀名为xls，为此，找到邹建写的一个存储过程。

方法四,使用OpenRowSet：

/*--数据导出EXCEL

导出查询中的数据到Excel,包含字段名,文件为真正的Excel文件

如果文件不存在,将自动创建文件

如果表不存在,将自动创建表

基于通用性考虑,仅支持导出标准数据类型

--邹建 2003.10(引用请保留此信息)--*/

/*--调用示例

p_exporttb @sqlstr='select * from 地区资料'

,@path='c:\',@fname='aa.xls',@sheetname='地区资料'

--*/

create procp_exporttb@sqlstr varchar(8000),--查询语句,如果查询语句中使用了order by ,请加上top 100 percent

@path nvarchar(1000),--文件存放目录

@fname nvarchar(250),--文件名

@sheetname varchar(250)=''--要创建的工作表名,默认为文件名

as

declare @err int,@src nvarchar(255),@desc nvarchar(255),@out int

declare @obj int,@constr nvarchar(1000),@sql varchar(8000),@fdlist varchar(8000)--参数检测

if isnull(@fname,'')=''set @fname='temp2012.xls'

if isnull(@sheetname,'')='' set @sheetname=replace(@fname,'.','#')--检查文件是否已经存在

if right(@path,1)<>'\' set @path=@path+'\'

create table #tb(a bit,b bit,c bit)set @sql=@path+@fname

insert into #tb exec master..xp_fileexist @sql

--数据库创建语句

set @sql=@path+@fname

if exists(select 1 from #tb where a=1)set @constr='DRIVER={Microsoft Excel Driver (*.xls)};DSN='''';READONLY=FALSE'

+';CREATE_DB="'+@sql+'";DBQ='+@sql

else

set @constr='Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties="Excel 8.0;HDR=YES'

+';DATABASE='+@sql+'"'

--连接数据库

exec @err=sp_oacreate 'adodb.connection',@objoutif @err<>0 gotolberrexec @err=sp_oamethod @obj,'open',null,@constr

if @err<>0 gotolberr--创建表的SQL

declare @tbnamesysnameset @tbname='##tmp_'+convert(varchar(38),newid())set @sql='select * into ['+@tbname+'] from('+@sqlstr+') a'

exec(@sql)select @sql='',@fdlist=''

select @fdlist=@fdlist+',['+a.name+']',@sql=@sql+',['+a.name+']'

+case

when b.name like '%char'

then case when a.length>255 then 'memo'

else 'text('+cast(a.length as varchar)+')' end

when b.name like '%int' or b.name='bit' then 'int'

when b.name like '%datetime' then 'datetime'

when b.name like '%money' then 'money'

when b.name like '%text' then 'memo'

else b.name end

FROM tempdb..syscolumns a left join tempdb..systypes b on a.xtype=b.xusertypewhere b.name not in('image','uniqueidentifier','sql_variant','varbinary','binary','timestamp')and a.id=(select id from tempdb..sysobjects where name=@tbname)if @@rowcount=0 return

select @sql='create table ['+@sheetname

+']('+substring(@sql,2,8000)+')',@fdlist=substring(@fdlist,2,8000)exec @err=sp_oamethod @obj,'execute',@out out,@sql

if @err<>0 gotolberrexec @err=sp_oadestroy @obj

--导入数据

set @sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel 8.0;HDR=YES

;DATABASE='+@path+@fname+''',['+@sheetname+'$])'

exec('insert into'+@sql+'('+@fdlist+') select'+@fdlist+'from ['+@tbname+']')set @sql='drop table ['+@tbname+']'

exec(@sql)returnlberr:exec sp_oageterrorinfo 0,@src out,@descout

lbexit:select cast(@err as varbinary(4)) as错误号

,@src as 错误源,@desc as错误描述select @sql,@constr,@fdlist

go

为了执行这个存储过程，你得先打开以下开关：

--To allow advanced options to be changed.

EXEC sp_configure 'show advanced options', 1

GO

--To allow advanced options to be changed.

EXEC sp_configure 'Ole Automation Procedures', 1

GO

EXEC sp_configure 'Ad Hoc Distributed Queries', 1

GO

--To update the currently configured value for advanced options.

RECONFIGURE

GO

调用示例：

p_exporttb @sqlstr='select * from [Demo_A]',@path='c:\',@fname='Export2xls_ByProc.xls',@sheetname='员工名称'

结果确实是正宗的xls文件。

如果你是兼写程序的DBA，那么NPOI是你理想的选择，因为它是纯原生的不依赖于Office组件的开源第三方组件，它提供了一个“CreateExportDataTableSheetAndHeaderRow”方法可以让你方便的生成纯正的Excel，遗憾的是，目前好像只支持到Excel 2003。示例请看这儿(http://scottonwriting.net/sowblog/archive/2011/06/08/export-an-ado-net-datatable-to-excel-using-npoi.aspx),该组件的源码：http://npoi.codeplex.com/

方法五：

protected Sheet CreateExportDataTableSheetAndHeaderRow(DataTable exportData, stringsheetName, CellStyle headerRowStyle)

{var sheet = this.Workbook.CreateSheet(EscapeSheetName(sheetName));//Create the header row

var row = sheet.CreateRow(0);for (var colIndex = 0; colIndex < exportData.Columns.Count; colIndex++)

{var cell =row.CreateCell(colIndex);

cell.SetCellValue(exportData.Columns[colIndex].ColumnName);if (headerRowStyle != null)

cell.CellStyle=headerRowStyle;

}returnsheet;

}

当然，如果你觉得以上方法门槛有点高，那么SSIS可能是你的首选，它的优势在于简单直观，并且可以导出为Excel2007格式。

只要在导出时选择第一行包含列名，即可。

方法六，使用SSIS

因为有人觉得界面过于繁琐，于是仿照导出向导的思路写了一个批处理，你可以修改为自己适合的内容：

@ECHO OFF

REM-------------------------------------------------------------------------------REMGeneric script for exporting data to file from SQL Server using a SQL query.REMThe resulting file will be tab separated with newline as the row delimiter.REMA log file is generated and kept in case of an error or when in debug mode.REMSee command syntax for details.REM

REMHistory:REM20120327 Lars Rönnbäck CREATEDREM-------------------------------------------------------------------------------

:constants

SET myCodePage=ACP:variables

SET theQuery=%~1

SET theFile=%~2

SET theServer=%~3

SET theDebug=%~4

SET /a aRandomNumber=%random%%%1000

FOR /F "usebackq tokens=1-7* delims=.:/,- " %%a IN (`ECHO %DATE%_%TIME%`) DO (SET myStartTime=%%a%%b%%c%%d%%e%%f%%g)

SET myColumnQuery="select top 0 * into [#columns_%myStartTime%_%aRandomNumber%] from (%theQuery%) q; select stuff((select char(9) + c.name from tempdb.sys.columns c where c.object_id = t.object_id order by c.column_id for XML path(''), type).value('.', 'varchar(max)'), 1,1,'') AS Header from tempdb.sys.tables t where t.name like '#columns_%myStartTime%_%aRandomNumber%%%'"

SET myHeaderFile=%theFile%.%aRandomNumber%.headerSET myDataFile=%theFile%.%aRandomNumber%.dataSET myLogFile=%theFile%.%myStartTime%_%aRandomNumber%.log:checks

IF "%theQuery%"=="" (

GOTOsyntax)

IF "%theFile%"=="" (

GOTOsyntax)

IF "%theServer%"=="" (

SET theServer=%COMPUTERNAME%)

:information

ECHO Start Time: %myStartTime% >> "%myLogFile%" 2>&1

ECHO Random Number: %aRandomNumber% >> "%myLogFile%" 2>&1

ECHO File: %theFile% >> "%myLogFile%" 2>&1

ECHO Server Name: %theServer% >> "%myLogFile%" 2>&1

ECHO Query: >> "%myLogFile%" 2>&1

ECHO. >> "%myLogFile%" 2>&1

ECHO %theQuery% >> "%myLogFile%" 2>&1

:exportBCP %myColumnQuery% queryout"%myHeaderFile%" -T -S "%theServer%" -a 65535 -c -C %myCodePage% -q >> "%myLogFile%" 2>&1

IF ERRORLEVEL 1 GOTOerror

BCP"%theQuery%" queryout "%myDataFile%" -T -S "%theServer%" -a 65535 -c -C %myCodePage% -q >> "%myLogFile%" 2>&1

IF ERRORLEVEL 1 GOTOerrorECHO. >> "%myLogFile%" 2>&1

ECHO Merging files... >> "%myLogFile%" 2>&1

ECHO. >> "%myLogFile%" 2>&1

COPY /A "%myHeaderFile%" + "%myDataFile%" "%theFile%" /B /Y >> "%myLogFile%" 2>&1

IF ERRORLEVEL 1 GOTOerror:cleanup

DEL "%myHeaderFile%" >NUL 2>&1

IF ERRORLEVEL 1 GOTOerrorDEL "%myDataFile%" >NUL 2>&1

IF ERRORLEVEL 1 GOTOerrorIF /I NOT [%theDebug%]==[Y] (

DEL "%myLogFile%"

)

IF ERRORLEVEL 1 GOTOerrorGOTO end

:error

ECHOECHOERROR: An export error has occured!IF NOT [%myLogFile: =%]==[] (

ECHODetails can be found in:ECHO%myLogFile%)

ECHOEXIT /B 1

:syntax

ECHO.

ECHO SYNTAX: %0 "sql query" "output file"[server] [Y]ECHO-------------------------------------------------------------------------------ECHOYou must specify an SQL query and an output file name in which the results ofECHO the query will be stored.Specifying a server is optional and defaults to theECHO server you are executing on. Ifa fourth argument is given as Y a log file ofECHO the command outputs will be saved in the same folder as the output file.

ECHO-------------------------------------------------------------------------------:end

REMThis is the end.