Importing sheet names & IDs from Excel 2007 into S

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Importing sheet names & IDs from Excel 2007 into S

Author

Topic

davsarg
Starting Member

2 Posts

Posted - 2011-10-20 : 08:52:03

Hi everyone,

I'm trying to find a way of getting the sheet names and, more importantly the sheet IDs from an Excel 2007 (xlsx) Workbook into Sql Server 2008. I do have some code (not written by me) that will work for Excel 2003 but not for Excel 2007. Can anyone offer any suggestions as to how I could accomplish this please . It's worth pointing out that, being something of a newbie to this aspect of SQL, I don't understand how the existing code works.

Here's the code as it stands:

ALTER PROCEDURE [dbo].[spSelUploadXlsWorkSheets](
@xlsWorkbook VARCHAR(255)
)
AS
BEGIN

SET NOCOUNT ON

DECLARE @hr int
DECLARE @adoCatalogue int
DECLARE @adoConn int

DECLARE @numTables int -- Number of tables/sheets found in workbook
DECLARE @curTable int -- current table/sheet processed
DECLARE @tableName VARCHAR(200)

SET @curTable = 0
-- Table variable to hold all sheetnames in workbook
DECLARE @result TABLE (sheetNo SMALLINT, sheetname varchar(255))

-- Create an ADOX-Catalog object
EXEC @hr = master.dbo.sp_OACreate 'ADOX.Catalog', @adoCatalogue OUTPUT
PRINT 'Create Catalog : ' + CAST (@hr AS VARCHAR)

-- Create an ADODB.Connection Object
EXEC @hr = master.dbo.sp_OACreate 'ADODB.Connection', @adoConn OUTPUT
PRINT 'Create Connection : ' + CAST (@hr AS VARCHAR)

-- Set Properties to open an Excel Workbook
EXEC @hr = master.dbo.sp_OASetProperty @adoConn, 'Provider', 'Microsoft.Jet.OLEDB.4.0'
PRINT 'Add Provider : ' + CAST (@hr AS VARCHAR)

EXEC @hr = master.dbo.sp_OASetProperty @adoConn, 'Properties("Extended Properties").Value', 'Excel 8.0'
PRINT 'Add extended properties : ' + CAST (@hr AS VARCHAR)

PRINT @xlsWorkbook

EXEC @hr = master.dbo.sp_OASetProperty @adoConn, 'Properties("Data Source").Value', @xlsWorkbook
PRINT 'Create Data Source : ' + CAST (@hr AS VARCHAR)

-- Open Connection to Workbook
EXEC @hr = master.dbo.sp_OAMethod @adoConn, 'Open'
PRINT 'Open Connection : ' + CAST (@hr AS VARCHAR)

-- Return OLE error
DECLARE @src VARCHAR(200)
DECLARE @desc VARCHAR(200)

EXEC sp_OAGetErrorInfo @adoConn, @src OUT, @desc OUT
PRINT convert(varbinary(4),@hr)
PRINT @src
PRINT @desc

-- Get ActiveConnection Reference
EXEC @hr = master.dbo.sp_OASetProperty @adoCatalogue, 'ActiveConnection', @adoConn
PRINT 'Retrieve Active Connection: ' + CAST (@hr AS VARCHAR)

-- Get total count of worksheets and named ranges in workbook
EXEC @hr = master.dbo.sp_OAGetProperty @adoCatalogue, 'Tables.count', @numTables OUTPUT
PRINT 'Table Count : ' + CAST (@hr AS VARCHAR)

-- Loop through all the worksheets to retrieve the name
WHILE @curTable <= (@numTables - 1)
BEGIN
DECLARE @command VARCHAR(200)
SET @command = 'Tables(' + CAST(@curTable AS VARCHAR) + ').name'
EXEC @hr = master.dbo.sp_OAGetProperty @adoCatalogue, @command, @tableName OUTPUT
PRINT 'Table Names : ' + CAST (@hr AS VARCHAR)
PRINT 'Table Names : ' + CAST (@hr AS VARCHAR)
INSERT INTO @result SELECT @curTable, @tableName
SET @curTable = @curTable + 1
END

EXEC @hr = master.dbo.sp_OAMethod @adoConn, 'Close'

EXEC @hr = master.dbo.sp_OADestroy @adoConn
SELECT sheetNo, sheetname FROM @result
END

when I execute the procedure and pass an appropriate parameter to it, the query executes successfully but with no results and I get the following messages displayed:

<<\\ACTUAL SERVER NAME, PATH & FILE NAME WITH XLSX EXTENSION IS DISPLAYED HERE>>>
Create Catalog : 0
ADO Catalogue : 16711422
Create Connection : 0
ADODB.Connection : 33488638
Add Provider : 0
Add extended properties : 0
<<\\ACTUAL SERVER NAME, PATH & FILE NAME WITH XLSX EXTENSION IS DISPLAYED HERE>>>
Create Data Source : 0
Open Connection : -2147467259
0x80004005
Microsoft JET Database Engine
External table is not in the expected format.
Retrieve Active Connection: -2146824579
Table Count : 0

(1 row(s) affected)

If anyone could help me with this then I'd be very grateful.

Many thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-11-16 : 05:35:06

EXCEL 2007 version needs a different JET engine. Make sure that the Server has Jet 12.0 engine

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources