none
Error while loading data from Excel to SQL SERVER table RRS feed

  • Question

  • Hi Experts,

    I've a requirement to load data from excel sheet to sql server table. I've to load data from .xlsx extension file to sql server 2012 table.

    I've written below code for this...

    -- Code start
    USE [master]
    GO
    sp_configure 'show advanced options', 1
    GO
    RECONFIGURE WITH OverRide
    GO
    sp_configure 'Ad Hoc Distributed Queries', 1
    GO
    RECONFIGURE WITH OverRide
    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
    -- code end
    
    -- test query 1: change the path to your excel file
    select * FROM OPENROWSET(
    'Microsoft.ACE.OLEDB.12.0'
    , 'Excel 12.0;Database=E:\Excel.xlsx'
    , [Sheet1$]
    )
    
    -- test query 2: change the path to your excel file
    select * FROM OPENROWSET(
    'Microsoft.ACE.OLEDB.12.0'
    , 'Excel 12.0;Database=E:\Excel.xlsx'
    , 'select top 2 * from [Sheet1$]'
    )

    when am running the above query getting the below error...

    OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error".
    Msg 7303, Level 16, State 1, Line 1
    Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

    I've searched how to resolve this but after trying many options not able to fix this error.

    Could you please help me in resolving this.

    Thanks a lot.

    Regards,

    NoorBi


    • Edited by SQL2012BI Wednesday, March 20, 2013 12:25 PM update
    Wednesday, March 20, 2013 12:23 PM

Answers

  • Hi NoorBi,

    Try creating a folder in E:\ and place your file in the new folder. Make sure that you have proper access to that folder. Sometimes local admin have issues in accessing the drive directly.

    Regards,

    Brindha.

    • Marked as answer by SQL2012BI Thursday, March 21, 2013 5:18 AM
    Wednesday, March 20, 2013 1:54 PM

All replies

  • Hi NoorBi,

    Are you trying to run this query using Windows Authentication? Do you have access to the 'E:\' where you placed your file?

    Regards,

    Brindha.

    Wednesday, March 20, 2013 12:32 PM
  • Yes, I am using my local machine for this. I am admin on my machine.
    Wednesday, March 20, 2013 12:35 PM
  • Is E: a local disk or a mapped network share?

    Have you considered using SSIS instead? Alternatively write a program or a Excel macro that reads the Excel file and loads it into SQL Server?

    You could also save the Excel file as a comma-separate file and load it with BCP, but this is really only worthwhile if it's a one-off.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, March 20, 2013 1:38 PM
  • Hi NoorBi,

    Try creating a folder in E:\ and place your file in the new folder. Make sure that you have proper access to that folder. Sometimes local admin have issues in accessing the drive directly.

    Regards,

    Brindha.

    • Marked as answer by SQL2012BI Thursday, March 21, 2013 5:18 AM
    Wednesday, March 20, 2013 1:54 PM
  • If you don't have a specific business requirement for using openrowset, then I'd use SSIS to import this excel sheet.  MS documentation says openrowset "...is an alternative to accessing tables in a linked server and is a one-time, ad hoc method of connecting and accessing remote data by using OLE DB."  http://msdn.microsoft.com/en-us/library/ms190312.aspx

    You can easily import this in Microsoft SQL Server Management Studio by right clicking on the database-->Tasks-->Import Data.

    If you want to repeat this process many times, then you can save the SSIS package you created and set it as a step in a SQL Agent job to import on a schedule.

    Wednesday, March 20, 2013 2:31 PM
  • It looks like you have a linked server setup.

    1.  Make sure you've downloaded and installed the ACE on the computer with SQL Server installed on it.

    http://www.microsoft.com/en-us/download/details.aspx?id=13255

    2.  Delete your linked server and recreate it.

    EXEC sp_addlinkedserver @server = N'ExcelDataSource',
    @srvproduct=N'ExcelData', @provider=N'Microsoft.ACE.OLEDB.12.0',
    @datasrc=N'E:\test.xlsx',
    @provstr=N'EXCEL 12.0' ;

    Make sure you put the excel file on the server in the correct location. 

    3.  After you have created the linked server add your account to it.  This can be done through the SMS GUI too.

    exec sp_addlinkedsrvlogin
        @rmtsrvname = 'ExcelDataSource',
        @useself = 'False',
        @locallogin = YourAccount,
        @rmtuser = 'Admin',
        @rmtpassword = null

    4.  Open a new query and type the following

    select * from ExcelDataSource...[Sheet1$]

    Wednesday, March 20, 2013 9:46 PM
  • Thank you Brindha it worked.

    I created new folder and place my file inside that folder and executed query..it worked.

    But strange behavior isn't it?

    Thanks a lot.

    --NoorBi

    Thursday, March 21, 2013 5:20 AM