Accessing Excel File

Question

Accessing Excel File

DaveO

Say Hey Kid

Points: 673
More actions
September 27, 2004 at 1:15 pm

#83776

Does anyone know how to get SQL Server to read, via opendatasource, an Excel file containing multiple worksheets? I have tried specifying the opendatasource table name as the name of the worksheet appearing on the tab at the bottom of the Excel file with and without a dollar sign, but I constantly receive a message indicating 'the table does not exist'. Thanks.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

I have never used opendatasource for Excel (always used Linked Server). Have you made sure that the number of octets is correct? i.e.

ExcelFile..Sheet$ ??? may have nothing to do with opendatasource but figured I would throw it out there

Good Hunting!

AJ Ahrens

webmaster@kritter.net

Jean-Pierre PONS SSC-Addicted Points: 497 More actions · Answer 2

You can do that :

EXEC sp_dropserver 'EXCELTEST', 'droplogins'

go

EXEC sp_addlinkedserver N'EXCELTEST', N'Jet 4.0',

N'Microsoft.Jet.OLEDB.4.0',

N'\\server\path\file.xls', NULL, N'Excel 5.0'

GO

EXEC sp_addlinkedsrvlogin N'EXCELTEST', false

GO

Then, you can :

select * from EXCELTEST..."'Sheet name$'"

BUT you have to format your Excel file with this rule :

The sheet must begin with a row containing Header's name (A1) and the data must be table formatted (beginning in A2) like this example :

Matricule	Nom	Prénom	Section
550008	BERTHOU	PAUL	0000004
550011	CAMILLE	XAVIER	0000004

...

keep rollin'...

bye

theBainster Old Hand Points: 343 More actions · Answer 3

Make sure you put an extra period before the Sheet name:

SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',

'Data Source=c:\book1.xls;Extended Properties=Excel 8.0')...Sheet1$

Bryan

DaveO Say Hey Kid Points: 673 More actions · Answer 4

Thanks everyone for your suggestions. I was avoiding linked servers only because the company I work for overuses excel files and it would have been a nightmare to try and administer all the linked servers I would have required.

After some trial and error, there is actually a way to do this. You must define a 'name' within excel which creates a psuedo-table. This name is a set of selected cells within the worksheet you are attempting to read. You assign these cells a 'name' by selecting the Insert option from the toolbar. Once you have defined this 'name', say we call it tableone, you can then reference it in your query like so:

SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',

'Data Source=c:\"excel file.xls";Extended Properties=Excel 8.0')...tableone

If your worksheet has column names include them in the 'name' you create within excel. This way you can select just the columns you are interested in:

SELECT column1,column2 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',

'Data Source=c:\"excel file.xls";Extended Properties=Excel 8.0')...Sheet1$

Steven Thebo SSCarpal Tunnel Points: 4235 More actions · Answer 5

Another VERY important note: Your columns must contain consistent data or you risk data loss. The driver that accesses Excel as a db determines data types for each column by examination of a number of rows (configurable Extended Property). However if you have an alphanumeric column in which the first n rows are numeric (where n is greter than or equal to the value of the configurable property), then it will determine that column to be numeric. All data rows that do not meet this auto-determination of datatype will fail when accessed (ie data will not be accessed)! depending on version of Excel and version of driver, the entire row may be inaccessable or you might end up with Null for each field that does not meet the data type criteria determined.

Solution: create a Schema.INI file within the directory of your Excel file that specifies explicitly each column's data type.

BTW: This can apply to ANY data file accessed using the Microsoft OLE DB Provider for Jet, including Text Files.

While this isn't indicated as as issue in this thread, thought I'd toss it in anyway in case it can save someone headaches down the line.

SET

Helical Johan Ten Centuries Points: 1033 More actions · Answer 6

Hi, I load a excel archive, but the sheet name is separated with a space.

How do i it?

INSERT INTO #FILE_EXCEL (CUSTOMER_ID, IDENTIFICACION SELECT ltrim(rtrim([CUSTOMER ID])), ltrim(rtrim(IDENTIFICACION)), FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0','Data Source= c:\CANCEL.xls;Extended properties=Excel 8.0')...[JULIO 23$];

Msg 7314, Level 16, State 1, Line 1

The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" does not contain the table "JULIO 23$". The table either does not exist or the current user does not have permissions on that table.

Thanks