none
unable to connect to excel data source RRS feed

  • Question

  • Good day guys, i have been struggling for days to connect to excel as a data source, i kept getting this error message while trying to select  sheet name. At first i found a solution that told me to install the 32bit ace oledb drive which i did and it worked, but then i tried running another project and it started to give  the same error message, i have tried almost every fix i have come across on the internet but still yet it keeps failing, i even uninstalled my ssdt to see if it would help but still same error message, i have changed run64bitruntime to false still not working. i need help guys, thanks.

    "TITLE: Microsoft Visual Studio

     ------------------------------

    Could not retrieve the table information for the connection manager 'Excel Connection Manager'.
    Object reference not set to an instance of an object.

    ------------------------------
    BUTTONS:

    OK
    ------------------------------

    Tuesday, September 17, 2019 1:58 PM

Answers

  • The Shwag Don,

    Great. The Microsoft.ACE.OLEDB.12.0 is properly installed, though you need to adjust two properties:

    USE [master] 
    GO 
    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1 
    GO 
    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1 
    GO

    After that please try to query the Excel file  directly in SSMS along the following:

    SELECT *
     FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
       'Excel 12.0 Xml; HDR=NO;
        Database=c:\Users\...\dada.xlsx',
        [Sheet1$]);

    • Marked as answer by The Shwag Don Thursday, September 19, 2019 8:20 PM
    Wednesday, September 18, 2019 9:52 PM

All replies

  • Hi there,

    This is not enough insight into being able to help you efficiently.

    Please list the software versions used, and how to reproduce your issue.


    Arthur

    MyBlog


    Twitter

    Tuesday, September 17, 2019 2:32 PM
    Moderator
  • The Shwag Don,

    Let's check few things on your dev. machine.

    Please execute the SQL below in SSMS and share its outcome.

    -- 0. Check what 64-bit OLEDB providers are installed
    -- Microsoft.ACE.OLEDB.12.0
    -- Microsoft.ACE.OLEDB.15.0 or 16.0
    EXEC master.sys.sp_MSset_oledb_prop;


    Tuesday, September 17, 2019 5:33 PM
  • Hi The Shwag Don,

    May I know which excel version do you choose in excel connection manager?

    1.Microsoft Excel 97-2003,     Microsoft.Jet.OLEDB.4.0

    2.Microsoft Excel 2007-2010Microsoft.ACE.OLEDB.12.0

    3.Microsoft Excel 2013,          Microsoft.ACE.OLEDB.15.0

    4.Microsoft Excel 2016,          Microsoft.ACE.OLEDB.16.0

    This link will be useful:Installing the Microsoft.ACE.OLEDB.12.0 Provider for Both 64-bit and 32-bit Processing.

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, September 18, 2019 1:47 AM
  • Good day i would have liked to post images but i am getting a restriction message about verification of account but i am using a Visual studio 2017 (SSDT) and i have Microsoft.ACE.OLEDB.12.0 installed, the 64bit and i recently just installed the 32bit in quiet mode. I am running a windows 10.
    Wednesday, September 18, 2019 6:53 AM
  • just like i said earlier i have permission issues with uploading of images on here so i can only leave a text. i have a Microsoft.ACE.OLEDB.12.O 64bit and 32bit installed in quiet mode
    Wednesday, September 18, 2019 7:02 AM
  • i have a Microsoft.ACE.OLEDB.12.0 installed, you know when you select excel file it automatically chooses the  excel version in the connection manager, i am just getting to find out the excel file am working with was created 2015 but i tried to save it again from the XLSX format to XLS(97-2003) but it still didn't work.
    Wednesday, September 18, 2019 7:11 AM
  • i have a Microsoft.ACE.OLEDB.12.0 installed, you know when you select excel file it automatically chooses the  excel version in the connection manager, i am just getting to find out the excel file am working with was created 2015 but i tried to save it again from the XLSX format to XLS(97-2003) but it still didn't work.

    Hi,

    May I know how do you change the excel file from XLSX to XLS?

    • If you change the excel file extension directly, excel source/destination will show the following error message as the picture shown.
    • If you open .xlsx file , then click File->Save As->Browse and save as .xls file, excel source/destination will read it successfully.

           

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com


    Wednesday, September 18, 2019 9:56 AM
  • Good day Mona i really appreciate your time and effort, with regards to how i changed the file, i used just the same procedure as the above picture, i opened in excel and saved as new extension, at first it worked but when i tried to load another project it failed and since then it has been failing i even tried reconnecting the previous excel file which worked at first through the conversion, but it now failed again. This issue has really been stressing me out, because i have tried almost all the fix i could find on the internet but still yet no success, even tried using an oledb source then changing the provider to access drive and editing the extended properties but that still failed. The main problem is that it fails to read the sheet.
    Wednesday, September 18, 2019 12:59 PM
  • Good day i would have liked to post images but i am getting a restriction message about verification of account but i am using a Visual studio 2017 (SSDT) and i have Microsoft.ACE.OLEDB.12.0 installed, the 64bit and i recently just installed the 32bit in quiet mode. I am running a windows 10.

    The Shwag Don,

    Please execute the SQL below in SSMS and share its outcome. You can do it in a text mode. No need for a screen shot.

    -- 0. Check what 64-bit OLEDB providers are installed
    -- Microsoft.ACE.OLEDB.12.0
    -- Microsoft.ACE.OLEDB.15.0 or 16.0
    EXEC master.sys.sp_MSset_oledb_prop;

    Please find me on LinkedIn or Skype.
    Wednesday, September 18, 2019 2:09 PM
  • This is the result of the SQL Query 

    provider_name allow_in_process disallow_adhoc_access dynamic_parameters index_as_access_path level_zero_only nested_queries non_transacted_updates sql_server_like
    SQLOLEDB 0 0 0 0 0 0 0 0
    SSISOLEDB 0 0 0 0 0 0 0 0
    SQLNCLI11 1 0 0 0 0 0 0 0
    Microsoft.ACE.OLEDB.12.0 0 0 0 0 0 0 0 0
    ADsDSOObject 1 0 0 0 0 0 0 0
    MSOLEDBSQL 1 0 0 0 0 0 0 0
    Search.CollatorDSO 0 0 0 0 0 0 0 0
    MSDASQL 1 0 0 0 0 0 0 0
    MSOLAP 1 0 0 0 0 0 0 0
    MSDAOSP 0 0 0 0 0 0 0 0

    Wednesday, September 18, 2019 9:36 PM
  • The Shwag Don,

    Great. The Microsoft.ACE.OLEDB.12.0 is properly installed, though you need to adjust two properties:

    USE [master] 
    GO 
    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1 
    GO 
    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1 
    GO

    After that please try to query the Excel file  directly in SSMS along the following:

    SELECT *
     FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
       'Excel 12.0 Xml; HDR=NO;
        Database=c:\Users\...\dada.xlsx',
        [Sheet1$]);

    • Marked as answer by The Shwag Don Thursday, September 19, 2019 8:20 PM
    Wednesday, September 18, 2019 9:52 PM
  • when i ran the query for the excel file on SSMS i got an error saying 

    Msg 15281, Level 16, State 1, Line 9
    SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', search for 'Ad Hoc Distributed Queries' in SQL Server Books Online.

    Completion time: 2019-09-18T21:26:34.8542374-04:00

    Thursday, September 19, 2019 1:28 AM
  • hi Yitzhak,

    My issue was resolved. Thank you so much.

    FIX:

    1. I changed the entire project to 32-bit.
    2. In the excel source, i used explicit sql statement instead of views or sheet name.

    RECOMMENDATION:

    Always test sql statement querying excel in SSMS:

    SELECT *
     FROM OPENROWSET('Microsoft.ACE.OLEDB.16.0',
       'Excel 12.0 Xml;
        Database=C:\project msbi\OLTP\shaddy outlaw.xlsx',
        'SELECT * FROM [Sales$A1:D2]');
    
    	SELECT *
     FROM OPENROWSET('Microsoft.ACE.OLEDB.16.0',
       'Excel 12.0 Xml;
        Database=C:\project msbi\OLTP\shaddy outlaw.xlsx',
        'SELECT * FROM [Sales$]');

    Thursday, September 19, 2019 8:19 PM