How to use Opendatasoure can not work if i want to retrieve data from XLS files

How to use Opendatasoure can not work if i want to retrieve data from XLS files

Post by Luke Shen » Tue, 07 Oct 2003 16:24:10


I want to use the Transact-SQL function Opendatasoure to get external data
in XLS sheet. From the SQL 2000 online help file, i found one example as
follows:

SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended
properties=Excel 5.0')...xactions

But it can not work under my computer. Does anyone have the experience on
how to use this function in SQL query analyser tools?

Thanks for helps
 
 
 

How to use Opendatasoure can not work if i want to retrieve data from XLS files

Post by Sue Hoegem » Tue, 07 Oct 2003 21:59:51

Could you give more details on what you mean by it can not
work under your computer? Post details of any errors and
problems you are having with this?

-Sue

On Mon, 6 Oct 2003 15:24:10 +0800, "Luke Sheng"

 
 
 

How to use Opendatasoure can not work if i want to retrieve data from XLS files

Post by v-binya » Tue, 07 Oct 2003 22:05:04

Hi Luke,

Thank you for using MSDN Newsgroup! It's my pleasure to assist you on your issue.

From your description, I notice you want to use OpenDataSource to fetch the data in Excel
files. However, the Sheet Name was not provided in the T-SQL statement.

Assume you have one Excel file named "book1.xls" which locates in "c:\test" folder and there
are three sheets in the book1.xls: sheet1, sheet2 and sheet3 (Just create a new Excel file in the
test folder).

To use OpenDataSource, you should provide Provider Name, Connection String and the
Sheet Name you'd like to visit. All you have provided were correct except for the Sheer Name
at the end of the T-SQL Statement. Please substitute "xactions" for "Sheet Name$"
(ATTENTION: add the $ after the Sheet Name).

Here I provide you a sample for your reference

SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\test\book1.xls";
User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]


Please apply the suggestion and let me know if it works. If there is anything more I can assist
you, please feel free to let me know.


Regards,
Billy Yao
Microsoft Online Partner Support

----------------------------------------------------
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.
 
 
 

How to use Opendatasoure can not work if i want to retrieve data from XLS files

Post by Luke Shen » Wed, 08 Oct 2003 10:13:50

Dear Billy,

Thanks for your help. Your comments are correct. Yes, i should add the "$"
at the end of the sheet name.

Thanks again.

Luke


"Billy Yao [MSFT]" < XXXX@XXXXX.COM >
: XXXX@XXXXX.COM ...
issue.
the data in Excel
"c:\test" folder and there
a new Excel file in the
and the
for the Sheer Name
Name$"
anything more I can assist