Import xlsx file to 2008 r2 express

  • hi folks,

    i am tired to breaking my head in this simple task, trying to import an xlsx file to 2008 r2,

    after doing lot of googling i have done these following:

    1. UNINSTALL all 32-bit Microsoft Office applications and instances (Access 2007 install, Office 10 32-bit, etc.).

    2. Install the access and other engines from following site

    http://www.microsoft.com/downloads/en/details.aspx?FamilyID=c06b8369-60dd-4b64-a44b-84b371ede16d&displaylang=en

    3. Ran the following query in sql server

    sp_configure ‘show advanced options’, 1;

    GO

    RECONFIGURE;

    GO

    sp_configure ‘Ad Hoc Distributed Queries’, 1;

    GO

    RECONFIGURE;

    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

    4. I am using this query

    SELECT * FROM OPENROWSET(‘Microsoft.ACE.OLEDB.12.0′, ‘Excel 12.0;Database=c:\PATH_TO_YOUR_EXEXCEL_FILE.xls’,'select * from [sheet1$]‘)

    after doing all this i was getting 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)"."

    after doing some google search i got a link (http://www.aspsnippets.com/Articles/The-OLE-DB-provider-Microsoft.Ace.OLEDB.12.0-for-linked-server-null.aspx) and follow the instruction

    but no luck still getting the same error.

    can any one came across this situation, please help.

  • hello,

    On SQL 2k8 express,

    I followed your steps of

    sp_configure 'show advanced options', 1;

    GO

    RECONFIGURE;

    GO

    sp_configure 'Ad Hoc Distributed Queries', 1;

    GO

    RECONFIGURE;

    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

    then used this SQL which worked perfectly,

    SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',

    'Excel 12.0;Database=C:\temp\book1.xlsx;HDR=no', 'Select * from [Sheet1$]')

    only difference is that I had HDR=no

    or yes if you have header lines in your excel file.

    Dave

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply