March 2, 2015 at 10:13 am
I need to create .xlsx file exporting data from sql database and file name would be 'FileName' and current yyyymmdd.
I'm using following code.
declare @filepath nvarchar(4000);
set @filepath = '\\file1\Shared\Buying Report\NewFile' + CAST(YEAR(CURRENT_TIMESTAMP) AS VARCHAR)+ RIGHT('00'+CAST(MONTH(CURRENT_TIMESTAMP) AS VARCHAR),2)+ RIGHT('00'+CAST(DAY(CURRENT_TIMESTAMP) AS VARCHAR),2)+'.xlsx'
EXEC p_CreateExcel @db_name='st01', @table_name = 'Austomate_report', @file_name = @filepath
I enabled xP_cmdshell on my server also give right permission to destination folder and File got created at provided path with YYYYMMDD but not getting data copy on the file. Let me show you what's it shows me.
1,output
NULL
Starting copy...
NULL
1 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1 Average : (1000.00 rows per sec.)
NULL
2,output
NULL
Starting copy...
1000 rows successfully bulk-copied to host-file. Total received: 1000
1000 rows successfully bulk-copied to host-file. Total received: 2000
1000 rows successfully bulk-copied to host-file. Total received: 3000
NULL
3741 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 94 Average : (39797.87 rows per sec.)
NULL
3,
output
The system cannot find the path specified.
NULL
March 2, 2015 at 10:26 am
The problem is that we don't know what's inside of p_CreateExcel. The code creates what I suppose is a valid path with a file that includes the date. I'm not sure if you need to add quotes or if it's something else in the code of the procedure.
March 2, 2015 at 10:28 am
Thanks for your reply.
Below codes is in P_createExcel
USE [st01]
GO
/****** Object: StoredProcedure [dbo].[p_CreateExcel] Script Date: 02/26/2015 11:27:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[p_CreateExcel]
(
@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 "set fmtonly off select * from (select '+@columns+') as t" queryout "'+@file_name+'" -c -t, -T -S'''
exec(@sql)
--Generate data in the dummy file
set @sql='exec master..xp_cmdshell ''bcp "set fmtonly off select * from XeProgst01.dbo.'+@table_name+'" queryout "'+@data_file+'" -c -t, -T -S'''
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)
GO
March 2, 2015 at 1:21 pm
Your first problem which generates an error is that you're missing quotes in your last 2 steps. This will throw an error when your path has spaces in it.
ALTER procedure [dbo].[p_CreateExcel]
(
@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 "set fmtonly off select * from (select '+@columns+') as t" queryout "'+@file_name+'" -c -t, -T -S'''
exec(@sql)
--Generate data in the dummy file
set @sql='exec master..xp_cmdshell ''bcp "set fmtonly off select * from XeProgst01.dbo.'+@table_name+'" queryout "'+@data_file+'" -c -t, -T -S'''
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)
GO
The other problem is that you're creating a csv with xlsx extension which is basically unusable as it is. I found the code posted in here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926
but I'm not sure why they claim is an Excel file.
Right now, I don't have the time to test other options as most of the time I do this using SSIS.
March 2, 2015 at 1:46 pm
Note that BCP can generate files with the .xlsx extension, but they'll still just be text files. BCP cannot generate a true spreadsheet file... period. At best, this will serve only to confuse the users and EXCEL itself.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply