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

最新推荐文章于 2023-01-16 15:47:38 发布

weixin_39611049

最新推荐文章于 2023-01-16 15:47:38 发布

阅读量1k

点赞数

文章标签： sql怎么导出带标题的csv格式

本文链接：https://blog.csdn.net/weixin_39611049/article/details/111723870

版权

本文介绍了从SQL Server导出带列名的CSV文件到Excel的多种方法，包括BCP命令、存储过程、OpenRowSet、NPOI组件和SSIS。详细讲解了每种方法的实现步骤和注意事项，如BCP命令的配置、存储过程的创建、OpenRowSet的使用以及SSIS导出向导的优势。这些方法涵盖了从简单的命令行操作到复杂的编程解决方案，以满足不同需求。

摘要由CSDN通过智能技术生成

前几天在项目中遇到一个问题，需要从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,'令狐冲'

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

--To allow advanced options to be changed.

EXEC sp_configure 'show advanced options', 1

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

RECONFIGURE

--To enable the feature.

EXEC sp_configure 'xp_cmdshell', 1

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

RECONFIGURE

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

方法一：使用BCP

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

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

2 /*Example*/

3 /*CPP_Export_To_Excel_With_Header 'Testdb2','Demo_A','服务器名\实例名','C:\TestExxelWithHeader.xls'*/

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

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

7 Create Procedure [dbo].[CPP_Export_To_Excel_With_Header]

8 (9 @db_name varchar(255),10 @table_name varchar(255),11 @server_name varchar(255),12 @file_path varchar(255)13 )14 as

16 ----Generate column names as a recordset

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

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

22 select

23 @columns=coalesce(@columns+',','')+column_name+'as'+column_name24 from

25 information_schema.columns26 where

27 table_name=@table_name

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

30 ----Generate column names file

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

32 print @sql

33 exec(@sql)34

35 ----Create a dummy file to have actual data

36 set @sql='exec master..xp_cmdshell''bcp "'+@db_name+'..'+@table_name+'" out "'+@TableDataWithoutHeaders+'" -c -T -S'+@server_name+''''

37 print @sql

38 exec(@sql)39

40 --Merge File into One Final Format

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

42 print @sql

43 exec(@sql)44

45 --Delete temp File

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

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

49 exec(@sql)

调用方法：

exec CPP_Export_To_Excel_With_Header 'testdb2','Demo_A','myshop\SQLExpress','c:\Demo_A2.xls'

注意，报错的话：

1、SQL Server是否以wndows登录方式或混合模式安装，参数中的“-c -T -S机器名\SQLExpress”，即机器名\实例，如果默认实例名与机器一致，用机器名即可。

2、也可以SA用户登录，请修改过程中的参数为BCP相应参数，见上面示例。

方法二：

1 ALTER procedure [dbo].[proc_generate_excel_with_header]

2 (3 @db_name varchar(100),4 @table_name varchar(100),5 @server_name varchar(255),6 @file_name varchar(100)7 )8 as

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

11 /*Example*/

12 /*proc_generate_excel_with_header 'Testdb','Demo_A','服务器名\实例名','c:\Demo_A.xls'*/

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

14 ---- proc_generate_excel_with_header 'Testdb','Demo_A','localhost\SQLExpress','C:\TestExcelWithHeader.xls'

17 --Generate column names as a recordset

18 DECLARE @raw_sql nvarchar(4000), @sql varchar(8000)19

20 DECLARE @columnHeader VARCHAR(8000)21 SELECT @columnHeader = COALESCE(@columnHeader+',' ,'')+ ''''''+column_name +'''''' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @table_name

23 DECLARE @ColumnList VARCHAR(8000)24 SELECT @ColumnList = COALESCE(@ColumnList+',' ,'')+ 'CAST('+column_name +'AS VARCHAR)' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @table_name

26 SELECT @raw_sql = 'SELECT'+ @columnHeader +'UNION ALL SELECT' + @ColumnList + 'FROM' + @db_name+'..'+@table_name

27 PRINT @raw_SQL

28 --EXECUTE sp_executesql @raw_sql

30 --filepath

31 ----select @file_name=substring(@file_name,1,len(@file_name)-charindex('\',reverse(@file_name)))+'\data_file.xls'

32 if isnull(@server_name,'')=''

33 set @server_name='localhost'

35 --Generate data in the dummy file

36 set @sql='exec master..xp_cmdshell''bcp "'+@raw_sql+'" queryout "'+@file_name+'" -c -T -S'+@server_name+''''

37 print @sql

38 exec(@sql)

调用示例：

exec proc_generate_excel_with_header 'testdb2','Demo_A','myshop\SQLExpress','c:\Demo_A.xls'

同样，报错的话：

1、SQL Server是否以wndows登录方式或混合模式安装，参数中的“-c -T -S机器名\SQLExpress”，即机器名\实例，如果默认实例名与机器一致，用机器名即可。

2、也可以SA用户登录，请修改过程中的参数为BCP相应参数，见上面示例。

如果，你的环境是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)=''--要创建的工作表名,默认为文件名

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

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

--To allow advanced options to be changed.

EXEC sp_configure 'show advanced options', 1

--To allow advanced options to be changed.

EXEC sp_configure 'Ole Automation Procedures', 1

EXEC sp_configure 'Ad Hoc Distributed Queries', 1

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

RECONFIGURE

调用示例：

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.

小结：

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

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

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

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