OpenRowset Excel problem
-
I've been reading for hours now and I can't get the solution to my problem.
I want to dump a table into an excel datasheet, but I need to recreate the sheet everytime. I tried to insert nulls but leaves white cells, there is no delete operation, and the most close solution is the next one
INSERT OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;HDR=NO;IMEX=1;Database=D:\testing.xls;',
'SELECT * FROM [Hoja1$]') select * from Table
But it gives me this error
Msg 7399, Level 16, State 1, Line 6
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider indicates that the user did not have the permission to perform the operation.
Msg 7343, Level 16, State 2, Line 6
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" could not INSERT INTO table "[Microsoft.Jet.OLEDB.4.0]".
However what I really wanted en the begining was to create an excel datasheet (not to fill an existing one) and be able to dump several tables requested from a web, in a way that I could always select the same file from a temporary directory.
Thx all
-
Why don't you just inbuild query to your DB into the spreadsheet?
Check out in Excel: Data -> Get External Data -> New Database Query.
_____________
Code for TallyGenerator -
If you want to delete an xls file before you start, try this: (You'll need to set your variable accordingly.) I use this code snippet in a proc that runs every day, deleting the existing xls file and replacing it with a new one, under the same name.
--Create temporary table for xp_fileexists result set- Delete code courtesy of Susan Knowles - mssql maillist
CREATE
TABLE #FileExists
(
DoesExist smallint,
FileInDir
smallint,
DirExist
smallint)
DECLARE
@DelCmd varchar(128)
SET
@DelCmd = 'DEL ' + @path + @Excel_Name + '.xls'
BEGIN
INSERT INTO #FileExists EXEC Master..xp_FileExist @FilePath
IF EXISTS (SELECT DoesExist FROM #FileExists fe WHERE fe.DoesExist = 1)
BEGIN
EXEC Master..xp_cmdshell @DelCmd, no_output
END
END
DROP
TABLE #FileExists
-
thx all, it seems that I lost a reply
However I find another solution
create proc usp_write2Excel (@fileName varchar(100),@NumOfColumns tinyint,@query varchar(200))
as
begin
declare @dosStmt varchar(200)
declare @tsqlStmt varchar(500)
declare @colList varchar(200)
declare @charInd tinyint
set nocount on
--
-- COLUMNS LIST CREATION
--
set @charInd=0
set @colList = '[A]'
while @charInd < @NumOfColumns - 1
begin
set @charInd = @charInd + 1
set @colList = @colList + ',[' + char(65+ @charInd) +']'
end
-- CREATE MY EXCEL FILE BY COPING EXCEL TEMPLATE
set @dosStmt = ' copy d:\empty.xls ' + @fileName
exec master..xp_cmdshell @dosStmt
-- Create a "temporary" linked server to that file in order to "Export" Data
EXEC sp_addlinkedserver 'ExcelSource','Jet 4.0','Microsoft.Jet.OLEDB.4.0',@fileName,NULL,'Excel 5.0'
-- construct a T-SQL statement that will actually export the query results-- to the Table in the target linked server
set @tsqlStmt = 'Insert ExcelSource...[Sheet1$] ' + ' ( ' + @colList + ' ) '+ @query
-- execute dynamically the TSQL statement
exec (@tsqlStmt)
print @tsqlStmt
-- drop the linked server
EXEC sp_dropserver 'ExcelSource'
set nocount off
end
GO
exec usp_write2Excel 'd:\Customers.xls',6,'select * from R_Precio'
It does exactly what I want, it creates a new file from a given empty sheet. I just lost the names of the columns but It could be fixed. The empty sheet must have the leters A, B... in the first row to do the matching, and it could be used for any numbers of columns
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply