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

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

假定表如下：

USE testDb2

GO

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 Procedure CPP_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_name

from

information_schema.columns

where

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

调用示例：

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

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

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

0)表T1结构

a int

b int

x char

1)开启Web Assistant Procedures

exec sp_configure 'show advanced options', 1

RECONFIGURE

exec sp_configure 'Web Assistant Procedures', 1

RECONFIGURE

2)执行如下语句

EXEC sp_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.263

a b x

0 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 proc p_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',@obj out

if @err<>0 goto lberr

exec @err=sp_oamethod @obj,'open',null,@constr

if @err<>0 goto lberr

--创建表的SQL

declare @tbname sysname

set @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.xusertype

where 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 goto lberr

exec @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)

return

lberr:

exec sp_oageterrorinfo 0,@src out,@desc out

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, string sheetName, 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;

}

return sheet;

}

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

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

方法六，使用SSIS

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

@ECHO OFF

REM -------------------------------------------------------------------------------

REM Generic script for exporting data to file from SQL Server using a SQL query.

REM The resulting file will be tab separated with newline as the row delimiter.

REM A log file is generated and kept in case of an error or when in debug mode.

REM See command syntax for details.

REM

REM History:

REM 20120327 Lars Rönnbäck CREATED

REM -------------------------------------------------------------------------------

: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%.header

SET myDataFile=%theFile%.%aRandomNumber%.data

SET myLogFile=%theFile%.%myStartTime%_%aRandomNumber%.log

:checks

IF "%theQuery%"=="" (

GOTO syntax

)

IF "%theFile%"=="" (

GOTO syntax

)

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

:export

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

IF ERRORLEVEL 1 GOTO error

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

IF ERRORLEVEL 1 GOTO error

ECHO. >> "%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 GOTO error

:cleanup

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

IF ERRORLEVEL 1 GOTO error

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

IF ERRORLEVEL 1 GOTO error

IF /I NOT [%theDebug%]==[Y] (

DEL "%myLogFile%"

)

IF ERRORLEVEL 1 GOTO error

GOTO end

:error

ECHO

ECHO ERROR: An export error has occured!

IF NOT [%myLogFile: =%]==[] (

ECHO Details can be found in:

ECHO %myLogFile%

)

ECHO

EXIT /B 1

:syntax

ECHO.

ECHO SYNTAX: %0 "sql query" "output file" [server] [Y]

ECHO -------------------------------------------------------------------------------

ECHO You must specify an SQL query and an output file name in which the results of

ECHO the query will be stored. Specifying a server is optional and defaults to the

ECHO server you are executing on. If a fourth argument is given as Y a log file of

ECHO the command outputs will be saved in the same folder as the output file.

ECHO -------------------------------------------------------------------------------

:end

REM This is the end.

小结：

1、导出带有列名的Excel，可以用BCP，语句最少，但导出的不是真正的Excel文件；

2、使用OpenRowset，可以导出真正的Excel;

3、使用NPOI，可以最大化地满足编程人员的需求，另外也可在导出时再做适当的逻辑处理，另外也不需要xp_cmdshell等额外的权限；

4、最简单的是使用SSIS的导出向导，界面直观，可以直接导出为Excel 2003/2007格式。