locked
SQL Server Express 2012 import from xlsx (stored procedure) RRS feed

  • Question

  • Hello Everyone,

    How is the day going? Hope it's better then myne :)

    I've recently started using MS SQL Server Express 2012 (migrated from MS Access) and bumped into huge problem:

    When I was using access I used to import excel files by using saved imports (file name is always the same. When I get new source file - I override and run saved import) - pretty easy.
    Now when using MS SQL Server Express 2012 I noticed that it's impossible to have such an easy import from xlsx files due to some incompatibilities... I spend several hours trying various solutions and tips from this forum and a lot of other forums with no success....

    So - I am puttin my all hopes in your guys - this is my last resort....

    Setup:
    Windows 7 Enterprice 32bit
    CPU i5, 8GB RAM
    SQL Server Express 2012 32bit  +  AccessDatabaseEngine 32bit  +  Ad Hoc queries = enabled.

    I have the following code for impot:

    EXEC Test.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
    SELECT * FROM
    OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0; Database=C:\Users\mkrusa\Desktop\NonCat Ongoing.xlsx;',
    'SELECT * FROM [data$]')
    Errors I get: 
    Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)"

    Besides - when I run the import through menu and do everything manually - it works.... The reason for this is that I will have to import same file (updated daily) again and again and doing all manually would be back to medieval times

    Huge THANK YOU to everyone!

    Tuesday, January 15, 2013 7:12 AM

All replies

  • Hello,

    Please verify that you had installed the needed driver for Excel: open up the ODBC Data Source Administrator window (Start > Run > type ODBCAD32.EXE and enter) and have a look in the Drivers tab.
    The drivers can be downloaded from the Microsoft site:
    Excel 2007 ACE driver – 12.00.6423.1000


    Regards,
    Fanny Liu


    Fanny Liu
    TechNet Community Support

    • Proposed as answer by Fanny Liu Wednesday, January 23, 2013 8:25 AM
    Tuesday, January 22, 2013 8:11 AM