none
Trying to Import Excel Data from query RRS feed

  • Question

  • I am trying to import Excel data from query but getting the error message and not able to import data in my SQL table.

    SQL table (Test) and Excel tab (Test$) has same columns (EmployeeID and Name).

    Note: I also converted xlsx file to .xls file from File > SaveAS > Excel 97 - 2003 Workbook option but still not able to import data from query.

    Here's the query which I am using in Query Analyzer:

    INSERT INTO Test (EmployeeID,Name)
    SELECT EmployeeID,Name FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=C:\Brother, Department.xlsx', 'SELECT * FROM [Test$]')

    Error:

    OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Cannot update. Database or object is read-only.".
    Msg 7303, Level 16, State 1, Line 3
    Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".


    • Edited by Kenny_Gua Wednesday, August 14, 2019 4:21 AM
    Wednesday, August 14, 2019 4:00 AM

All replies

  • Hi Kenny,

    First, let's check what Microsoft ACE OLEDB providers are installed and their settings.

    Please run the following in SSMS and share the result.

    EXEC master.dbo.sp_MSset_oledb_prop;

    Additional recommendations:

    • Run SSMS as administrator.
    • Make sure that the *.xslx file is NOT opened in Excel.

    Also, are you sure that the fully qualified path to the Excel file is correct?

    C:\Brother, Department.xlsx


    Wednesday, August 14, 2019 4:28 AM
  • Hi Kenny_Gua,

     

    The Database Engine service must have permission of the Windows file system to access the file folder where database files are stored. Permission to the default location is configured during setup. If you place your database files in a different location, you might need to follow these steps to grant the Database Engine the full control permission to that location. Please check it.

     

    For more information , please refer to Configure File System Permissions for Database Engine Access.

     

    Hope it will help you.

     

    Best Regards

    Rachel


    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, August 14, 2019 9:28 AM
  • Hi, I have run the EXEC master.dbo.sp_MSset_oledb_prop and result is the following. What is the next step so that I can use my INSERT command.

    SQLOLEDB 0 0 0 0 0 0 0 0
    MSOLAP 1 0 0 0 0 0 0 0
    SQLNCLI11 1 0 0 0 0 0 0 0
    Microsoft.ACE.OLEDB.12.0 0 0 1 0 0 0 0 0
    Microsoft.ACE.OLEDB.16.0 0 0 0 0 0 0 0 0
    ADsDSOObject 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
    MSDAOSP 0 0 0 0 0 0 0 0
    MSIDXS 1 0 0 0 0 0 0 0

    My file name is Department.xlsx and it is located at C:\Brother
    • Edited by Kenny_Gua Thursday, August 15, 2019 4:37 AM
    Thursday, August 15, 2019 4:35 AM
  • Hi Kenny_Gua,

    Good, you have two ACE OLEDB providers installed, v 12.0 and 16.0

    Please launch your SSMS as administrator.

    Make sure that the *.xslx file is NOT opened in Excel.

    Please try following statements in SSMS:

    -- just once

    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
    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.16.0', N'AllowInProcess', 1 
    GO 
    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.16.0', N'DynamicParameters', 1 
    GO

    SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0 Xml; Database=C:\Brother\Department.xlsx', [Test$]);

    Thursday, August 15, 2019 5:04 AM
  • Hi, I have run the following script but gets the error.

    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
    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.16.0', N'AllowInProcess', 1 
    GO 
    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.16.0', N'DynamicParameters', 1 
    GO

    SELECT *
    FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0 Xml;
    Database=C:\Brother\Department.xlsx'
    ,
    [Test$]);

    Error:

    OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error".

    Msg 7303, Level 16, State 1, Line 10

    Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".


    Saturday, August 17, 2019 5:11 AM
  • Hi Kenny_Gua,

    1. Run SSMS as administrator.
    2. Give Everyone full permission on this folder:
      C:\Windows\ServiceProfiles\<SQL Server Service account name>\AppData\Local\Temp

    On my machine SQL Server OS Service is running as NetworkService
    So the folder is C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp

    Sunday, August 18, 2019 4:23 AM
  • Hi, How I can give permission to this following folder because I don't have 'appData' folder in NetworkService. So how I can found '\AppData\Local\Temp' and give the permission.

    C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp

    9 hours 43 minutes ago
  • Hi Kenny_Gua,

    First, please try to run SSMS as administrator. And try SELECT ...

    Just if it won't work, start dealing with the seconds bullet. Any local admin should be able to see that folder and change permissions.


    9 hours 29 minutes ago