sql server导出数据到excel表格

时间:2021-08-10 02:06:07
背景:sql server 能否实现不用先建好excel文件,就导出数据到一个新的excel表格。试了exec master..xp_cmdshell bcp,这个需要先建好表格并且设置好文件权限之后才能够导出数据。
问题:能否我直接导出数据到一个实际不存在的文件名,导出的时候,自动创建好这个文件,然后导出数据。 sql server导出数据到excel表格

11 个解决方案

#1


sql server导出数据到excel表格不用预先设置啊,我都是直接导出来的,不过是CSV,用的就是BCP,导出的时候才指定文件名

#2


--这是个例子,我假设要把test库的temtable表的applicationID,Category两列导出成CSV文件
if OBJECT_ID('temtable','u')is not null
drop table temtable

select * into temtable from (select 'applicationID'as [applicationID],'Category'  as [Category] --表头
union all 
select CONVERT(varchar(20),[applicationID]), [Category] from  #temtable)a
declare @filename nvarchar(30)
select @filename='Report'+right(replace(convert(date,GETDATE()),'-',''),2)+substring(replace(convert(date,GETDATE()),'-',''),5,2)+substring(replace(convert(date,GETDATE()),'-',''),1,4)+'.csv'
------------------------------------------
declare @strSQL nvarchar(1024)
set @strSQL='bcp "SELECT applicationID,Category FROM test.dbo.temtable" queryout C:\Temp\DBA_date\'+@filename+' -c -T -t","'
print @strSQL
EXEC master..xp_cmdshell @strSQL

#3


引用 2 楼 DBA_Huangzj 的回复:
--这是个例子,我假设要把test库的temtable表的applicationID,Category两列导出成CSV文件
if OBJECT_ID('temtable','u')is not null
drop table temtable

select * into temtable from (select 'applicationID'as [applicationID],'Category'  as [Category] --表头
union all 
select CONVERT(varchar(20),[applicationID]), [Category] from  #temtable)a
declare @filename nvarchar(30)
select @filename='Report'+right(replace(convert(date,GETDATE()),'-',''),2)+substring(replace(convert(date,GETDATE()),'-',''),5,2)+substring(replace(convert(date,GETDATE()),'-',''),1,4)+'.csv'
------------------------------------------
declare @strSQL nvarchar(1024)
set @strSQL='bcp "SELECT applicationID,Category FROM test.dbo.temtable" queryout C:\Temp\DBA_date\'+@filename+' -c -T -t","'
print @strSQL
EXEC master..xp_cmdshell @strSQL
没有事先建好文件,一直会报这个错误:Error = [Microsoft][SQL Server Native Client 11.0]无法打开 BCP 主数据文件

#4


那是别的问题,我这个代码是没有实现创建好的。一直在用。

#5


引用 4 楼 DBA_Huangzj 的回复:
那是别的问题,我这个代码是没有实现创建好的。一直在用。
太忧伤了

#6



--試試以下代碼:
EXEC master..xp_cmdshell 'bcp "SELECT * FROM 庫名.dbo.表名" queryout C:\temp.xls -c -S 服務器名 -U 用戶名 -P 密碼'

#7


引用 3 楼 journeysxy 的回复:
Quote: 引用 2 楼 DBA_Huangzj 的回复:

--这是个例子,我假设要把test库的temtable表的applicationID,Category两列导出成CSV文件
if OBJECT_ID('temtable','u')is not null
drop table temtable

select * into temtable from (select 'applicationID'as [applicationID],'Category'  as [Category] --表头
union all 
select CONVERT(varchar(20),[applicationID]), [Category] from  #temtable)a
declare @filename nvarchar(30)
select @filename='Report'+right(replace(convert(date,GETDATE()),'-',''),2)+substring(replace(convert(date,GETDATE()),'-',''),5,2)+substring(replace(convert(date,GETDATE()),'-',''),1,4)+'.csv'
------------------------------------------
declare @strSQL nvarchar(1024)
set @strSQL='bcp "SELECT applicationID,Category FROM test.dbo.temtable" queryout C:\Temp\DBA_date\'+@filename+' -c -T -t","'
print @strSQL
EXEC master..xp_cmdshell @strSQL
没有事先建好文件,一直会报这个错误:Error = [Microsoft][SQL Server Native Client 11.0]无法打开 BCP 主数据文件


这个报错是权限问题哦。 

#8



--如果已建立連線,也可以如下:
EXEC master..xp_cmdshell 'bcp "SELECT * FROM 庫名.dbo.表名" queryout C:\temp.xls -c -T'

#9


引用 7 楼 OrchidCat 的回复:
Quote: 引用 3 楼 journeysxy 的回复:

Quote: 引用 2 楼 DBA_Huangzj 的回复:

--这是个例子,我假设要把test库的temtable表的applicationID,Category两列导出成CSV文件
if OBJECT_ID('temtable','u')is not null
drop table temtable

select * into temtable from (select 'applicationID'as [applicationID],'Category'  as [Category] --表头
union all 
select CONVERT(varchar(20),[applicationID]), [Category] from  #temtable)a
declare @filename nvarchar(30)
select @filename='Report'+right(replace(convert(date,GETDATE()),'-',''),2)+substring(replace(convert(date,GETDATE()),'-',''),5,2)+substring(replace(convert(date,GETDATE()),'-',''),1,4)+'.csv'
------------------------------------------
declare @strSQL nvarchar(1024)
set @strSQL='bcp "SELECT applicationID,Category FROM test.dbo.temtable" queryout C:\Temp\DBA_date\'+@filename+' -c -T -t","'
print @strSQL
EXEC master..xp_cmdshell @strSQL
没有事先建好文件,一直会报这个错误:Error = [Microsoft][SQL Server Native Client 11.0]无法打开 BCP 主数据文件


这个报错是权限问题哦。 
是指sa用户没权建excel文件吗?!

#10


引用 9 楼 journeysxy 的回复:
Quote: 引用 7 楼 OrchidCat 的回复:

Quote: 引用 3 楼 journeysxy 的回复:

Quote: 引用 2 楼 DBA_Huangzj 的回复:

--这是个例子,我假设要把test库的temtable表的applicationID,Category两列导出成CSV文件
if OBJECT_ID('temtable','u')is not null
drop table temtable

select * into temtable from (select 'applicationID'as [applicationID],'Category'  as [Category] --表头
union all 
select CONVERT(varchar(20),[applicationID]), [Category] from  #temtable)a
declare @filename nvarchar(30)
select @filename='Report'+right(replace(convert(date,GETDATE()),'-',''),2)+substring(replace(convert(date,GETDATE()),'-',''),5,2)+substring(replace(convert(date,GETDATE()),'-',''),1,4)+'.csv'
------------------------------------------
declare @strSQL nvarchar(1024)
set @strSQL='bcp "SELECT applicationID,Category FROM test.dbo.temtable" queryout C:\Temp\DBA_date\'+@filename+' -c -T -t","'
print @strSQL
EXEC master..xp_cmdshell @strSQL
没有事先建好文件,一直会报这个错误:Error = [Microsoft][SQL Server Native Client 11.0]无法打开 BCP 主数据文件


这个报错是权限问题哦。 
是指sa用户没权建excel文件吗?!


参考:
EXEC xp_cmdshell 'ECHO %USERDOMAIN%\%USERNAME%'
看返回的是否为 :NT AUTHORITY\NETWORK SERVICE
配置管理器(configuration manager)看到登陆内置账号为Network service,改成local system问题解决。

执行创建excel的时候,是需要在windows端创建文件。network service是没有这个权限的。 需要提升权限才可以。 因此修改为local system 就是本地系统账户,即可创建文件了。 

#11


引用 10 楼 OrchidCat 的回复:
Quote: 引用 9 楼 journeysxy 的回复:

Quote: 引用 7 楼 OrchidCat 的回复:

Quote: 引用 3 楼 journeysxy 的回复:

Quote: 引用 2 楼 DBA_Huangzj 的回复:

--这是个例子,我假设要把test库的temtable表的applicationID,Category两列导出成CSV文件
if OBJECT_ID('temtable','u')is not null
drop table temtable

select * into temtable from (select 'applicationID'as [applicationID],'Category'  as [Category] --表头
union all 
select CONVERT(varchar(20),[applicationID]), [Category] from  #temtable)a
declare @filename nvarchar(30)
select @filename='Report'+right(replace(convert(date,GETDATE()),'-',''),2)+substring(replace(convert(date,GETDATE()),'-',''),5,2)+substring(replace(convert(date,GETDATE()),'-',''),1,4)+'.csv'
------------------------------------------
declare @strSQL nvarchar(1024)
set @strSQL='bcp "SELECT applicationID,Category FROM test.dbo.temtable" queryout C:\Temp\DBA_date\'+@filename+' -c -T -t","'
print @strSQL
EXEC master..xp_cmdshell @strSQL
没有事先建好文件,一直会报这个错误:Error = [Microsoft][SQL Server Native Client 11.0]无法打开 BCP 主数据文件


这个报错是权限问题哦。 
是指sa用户没权建excel文件吗?!


参考:
EXEC xp_cmdshell 'ECHO %USERDOMAIN%\%USERNAME%'
看返回的是否为 :NT AUTHORITY\NETWORK SERVICE
配置管理器(configuration manager)看到登陆内置账号为Network service,改成local system问题解决。

执行创建excel的时候,是需要在windows端创建文件。network service是没有这个权限的。 需要提升权限才可以。 因此修改为local system 就是本地系统账户,即可创建文件了。 
更改问本地系统帐号,就可以导出的时候自己建文件了。 sql server导出数据到excel表格

#1


sql server导出数据到excel表格不用预先设置啊,我都是直接导出来的,不过是CSV,用的就是BCP,导出的时候才指定文件名

#2


--这是个例子,我假设要把test库的temtable表的applicationID,Category两列导出成CSV文件
if OBJECT_ID('temtable','u')is not null
drop table temtable

select * into temtable from (select 'applicationID'as [applicationID],'Category'  as [Category] --表头
union all 
select CONVERT(varchar(20),[applicationID]), [Category] from  #temtable)a
declare @filename nvarchar(30)
select @filename='Report'+right(replace(convert(date,GETDATE()),'-',''),2)+substring(replace(convert(date,GETDATE()),'-',''),5,2)+substring(replace(convert(date,GETDATE()),'-',''),1,4)+'.csv'
------------------------------------------
declare @strSQL nvarchar(1024)
set @strSQL='bcp "SELECT applicationID,Category FROM test.dbo.temtable" queryout C:\Temp\DBA_date\'+@filename+' -c -T -t","'
print @strSQL
EXEC master..xp_cmdshell @strSQL

#3


引用 2 楼 DBA_Huangzj 的回复:
--这是个例子,我假设要把test库的temtable表的applicationID,Category两列导出成CSV文件
if OBJECT_ID('temtable','u')is not null
drop table temtable

select * into temtable from (select 'applicationID'as [applicationID],'Category'  as [Category] --表头
union all 
select CONVERT(varchar(20),[applicationID]), [Category] from  #temtable)a
declare @filename nvarchar(30)
select @filename='Report'+right(replace(convert(date,GETDATE()),'-',''),2)+substring(replace(convert(date,GETDATE()),'-',''),5,2)+substring(replace(convert(date,GETDATE()),'-',''),1,4)+'.csv'
------------------------------------------
declare @strSQL nvarchar(1024)
set @strSQL='bcp "SELECT applicationID,Category FROM test.dbo.temtable" queryout C:\Temp\DBA_date\'+@filename+' -c -T -t","'
print @strSQL
EXEC master..xp_cmdshell @strSQL
没有事先建好文件,一直会报这个错误:Error = [Microsoft][SQL Server Native Client 11.0]无法打开 BCP 主数据文件

#4


那是别的问题,我这个代码是没有实现创建好的。一直在用。

#5


引用 4 楼 DBA_Huangzj 的回复:
那是别的问题,我这个代码是没有实现创建好的。一直在用。
太忧伤了

#6



--試試以下代碼:
EXEC master..xp_cmdshell 'bcp "SELECT * FROM 庫名.dbo.表名" queryout C:\temp.xls -c -S 服務器名 -U 用戶名 -P 密碼'

#7


引用 3 楼 journeysxy 的回复:
Quote: 引用 2 楼 DBA_Huangzj 的回复:

--这是个例子,我假设要把test库的temtable表的applicationID,Category两列导出成CSV文件
if OBJECT_ID('temtable','u')is not null
drop table temtable

select * into temtable from (select 'applicationID'as [applicationID],'Category'  as [Category] --表头
union all 
select CONVERT(varchar(20),[applicationID]), [Category] from  #temtable)a
declare @filename nvarchar(30)
select @filename='Report'+right(replace(convert(date,GETDATE()),'-',''),2)+substring(replace(convert(date,GETDATE()),'-',''),5,2)+substring(replace(convert(date,GETDATE()),'-',''),1,4)+'.csv'
------------------------------------------
declare @strSQL nvarchar(1024)
set @strSQL='bcp "SELECT applicationID,Category FROM test.dbo.temtable" queryout C:\Temp\DBA_date\'+@filename+' -c -T -t","'
print @strSQL
EXEC master..xp_cmdshell @strSQL
没有事先建好文件,一直会报这个错误:Error = [Microsoft][SQL Server Native Client 11.0]无法打开 BCP 主数据文件


这个报错是权限问题哦。 

#8



--如果已建立連線,也可以如下:
EXEC master..xp_cmdshell 'bcp "SELECT * FROM 庫名.dbo.表名" queryout C:\temp.xls -c -T'

#9


引用 7 楼 OrchidCat 的回复:
Quote: 引用 3 楼 journeysxy 的回复:

Quote: 引用 2 楼 DBA_Huangzj 的回复:

--这是个例子,我假设要把test库的temtable表的applicationID,Category两列导出成CSV文件
if OBJECT_ID('temtable','u')is not null
drop table temtable

select * into temtable from (select 'applicationID'as [applicationID],'Category'  as [Category] --表头
union all 
select CONVERT(varchar(20),[applicationID]), [Category] from  #temtable)a
declare @filename nvarchar(30)
select @filename='Report'+right(replace(convert(date,GETDATE()),'-',''),2)+substring(replace(convert(date,GETDATE()),'-',''),5,2)+substring(replace(convert(date,GETDATE()),'-',''),1,4)+'.csv'
------------------------------------------
declare @strSQL nvarchar(1024)
set @strSQL='bcp "SELECT applicationID,Category FROM test.dbo.temtable" queryout C:\Temp\DBA_date\'+@filename+' -c -T -t","'
print @strSQL
EXEC master..xp_cmdshell @strSQL
没有事先建好文件,一直会报这个错误:Error = [Microsoft][SQL Server Native Client 11.0]无法打开 BCP 主数据文件


这个报错是权限问题哦。 
是指sa用户没权建excel文件吗?!

#10


引用 9 楼 journeysxy 的回复:
Quote: 引用 7 楼 OrchidCat 的回复:

Quote: 引用 3 楼 journeysxy 的回复:

Quote: 引用 2 楼 DBA_Huangzj 的回复:

--这是个例子,我假设要把test库的temtable表的applicationID,Category两列导出成CSV文件
if OBJECT_ID('temtable','u')is not null
drop table temtable

select * into temtable from (select 'applicationID'as [applicationID],'Category'  as [Category] --表头
union all 
select CONVERT(varchar(20),[applicationID]), [Category] from  #temtable)a
declare @filename nvarchar(30)
select @filename='Report'+right(replace(convert(date,GETDATE()),'-',''),2)+substring(replace(convert(date,GETDATE()),'-',''),5,2)+substring(replace(convert(date,GETDATE()),'-',''),1,4)+'.csv'
------------------------------------------
declare @strSQL nvarchar(1024)
set @strSQL='bcp "SELECT applicationID,Category FROM test.dbo.temtable" queryout C:\Temp\DBA_date\'+@filename+' -c -T -t","'
print @strSQL
EXEC master..xp_cmdshell @strSQL
没有事先建好文件,一直会报这个错误:Error = [Microsoft][SQL Server Native Client 11.0]无法打开 BCP 主数据文件


这个报错是权限问题哦。 
是指sa用户没权建excel文件吗?!


参考:
EXEC xp_cmdshell 'ECHO %USERDOMAIN%\%USERNAME%'
看返回的是否为 :NT AUTHORITY\NETWORK SERVICE
配置管理器(configuration manager)看到登陆内置账号为Network service,改成local system问题解决。

执行创建excel的时候,是需要在windows端创建文件。network service是没有这个权限的。 需要提升权限才可以。 因此修改为local system 就是本地系统账户,即可创建文件了。 

#11


引用 10 楼 OrchidCat 的回复:
Quote: 引用 9 楼 journeysxy 的回复:

Quote: 引用 7 楼 OrchidCat 的回复:

Quote: 引用 3 楼 journeysxy 的回复:

Quote: 引用 2 楼 DBA_Huangzj 的回复:

--这是个例子,我假设要把test库的temtable表的applicationID,Category两列导出成CSV文件
if OBJECT_ID('temtable','u')is not null
drop table temtable

select * into temtable from (select 'applicationID'as [applicationID],'Category'  as [Category] --表头
union all 
select CONVERT(varchar(20),[applicationID]), [Category] from  #temtable)a
declare @filename nvarchar(30)
select @filename='Report'+right(replace(convert(date,GETDATE()),'-',''),2)+substring(replace(convert(date,GETDATE()),'-',''),5,2)+substring(replace(convert(date,GETDATE()),'-',''),1,4)+'.csv'
------------------------------------------
declare @strSQL nvarchar(1024)
set @strSQL='bcp "SELECT applicationID,Category FROM test.dbo.temtable" queryout C:\Temp\DBA_date\'+@filename+' -c -T -t","'
print @strSQL
EXEC master..xp_cmdshell @strSQL
没有事先建好文件,一直会报这个错误:Error = [Microsoft][SQL Server Native Client 11.0]无法打开 BCP 主数据文件


这个报错是权限问题哦。 
是指sa用户没权建excel文件吗?!


参考:
EXEC xp_cmdshell 'ECHO %USERDOMAIN%\%USERNAME%'
看返回的是否为 :NT AUTHORITY\NETWORK SERVICE
配置管理器(configuration manager)看到登陆内置账号为Network service,改成local system问题解决。

执行创建excel的时候,是需要在windows端创建文件。network service是没有这个权限的。 需要提升权限才可以。 因此修改为local system 就是本地系统账户,即可创建文件了。 
更改问本地系统帐号,就可以导出的时候自己建文件了。 sql server导出数据到excel表格