OpenDataSource fails from worksheet name starts with a number in xls file

[sayedul]

Hi,

I can access data from xls file using OpenDataSource. But if the worksheet name starts with numeric character it gives error. Scripts are as follows:

If the worksheet name is 'Sheet1' it works fine with script below:

select * from OpenDataSource 
	( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\test.xls";Extended properties=Excel 8.0')...[Sheet1$]

But if the worksheet name is '2007data' the script below gives error:

select * from OpenDataSource
	( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\test.xls";Extended properties=Excel 8.0')...[2007data$]

Can anyone give me a solution? I need to keep the worksheet name starting with numeric character.

Thanks.

[sayedul]

Hi,

I got a solution for worksheet namely '2007data' below and works fine:

select * from OpenDataSource
	( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\test.xls";Extended properties=Excel 8.0')...['2007data$']

But strange again!! Script below does not work for worksheet namely 'Sheet1' in the same way:

select * from OpenDataSource
	( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\test.xls";Extended properties=Excel 8.0')...['Sheet1$']

Please come anyone and give a unique solution for both cases.

Thanks.

Hi,

I can access data from xls file using OpenDataSource. But if the worksheet name starts with numeric character it gives error. Scripts are as follows:

If the worksheet name is 'Sheet1' it works fine with script below:

select * from OpenDataSource 
	( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\test.xls";Extended properties=Excel 8.0')...[Sheet1$]

But if the worksheet name is '2007data' the script below gives error:

select * from OpenDataSource
	( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\test.xls";Extended properties=Excel 8.0')...[2007data$]

Can anyone give me a solution? I need to keep the worksheet name starting with numeric character.

Thanks.