OPENDATASOURCE and EXCEL

• Mat Elkington-143276

  SSC Veteran

  Points: 277

  More actions

  September 3, 2004 at 4:58 am

  #66107

  Hi all,

  When using the following statement:

  SELECT *

  INTO test_import

  FROM OpenDataSource

  ('Microsoft.Jet.OLEDB.4.0','Data Source="c:\xyz.xls";User ID=;Password=;Extended properties=Excel 5.0')...[adCOI_TA-DAL328416$]

  I get the following error:

  OLE DB provider 'Microsoft.Jet.OLEDB.4.0' does not contain table 'adCOI_TA-DAL328416$'.

  If I remove the hyphen in the sheet name in the Excel Spreadsheet in question and rerun the command with the hyphen removed, it works - the same thing happens with spaces.

  Am I right in thinking that the OpenDataSource function does not work with spaces and/or hyphens in the catalog name? Or have I got the syntax wrong? - (I've tried double quotes as well) - Is there a work arround...?

  Thanks.

  ---

  Yes I would like a banana,

  Mr Shouty

• Bob Monahon

  Ten Centuries

  Points: 1282

  More actions

  September 4, 2004 at 9:42 am

  #521765

  Books on-line indicates that you can use QUOTES or brackets, and that the hyphen is allowed in a quoted identifier:

  http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_03_89rn.asp

  You might want to set Quoted Identifier on, and then execute the command:

  SET QUOTED_IDENTIFIER ON

  SELECT *

  INTO test_import

  FROM OpenDataSource

  ('Microsoft.Jet.OLEDB.4.0','Data Source="c:\xyz.xls";User ID=;Password=;Extended properties=Excel 5.0')..."adCOI_TA-DAL328416$"

  That's the best information I could find.

  ---

  Regards,

  Bob Monahon

• meglecz

  SSC Eights!

  Points: 826

  More actions

  October 6, 2004 at 1:40 pm

  #525640

  Hi,

  It should be working:

  SELECT *

  INTO test_import

  FROM OpenDataSource

  ('Microsoft.Jet.OLEDB.4.0','Data Source="c:\xyz.xls";User ID=;Password=;Extended properties=Excel 5.0')...['adCOI_TA-DAL328416$']

  Regards,

  Attila Meglécz