Unable to export my .xlsx to my Sql server 2008 da

Question

I have an excel sheet .xlsx extension with around 500,000 row and 16 columns. and i want to import it inside our sql server database which runs under windows server 2008 R2. so i tried the following steps inside my SQL Server Management studio 2008 R2, but they did not work:-

1. i created a new database which have the same 16 columns + i add a new ID column and i set it as the Primary key.

2. then i right click on the database > Tasks >> Import Data

3. i select excel sheet 2007 >> browse for the file >> click next >> i got this error:-

TITLE: SQL Server Import and Export Wizard

The operation could not be completed.

------------------------------ ADDITIONAL INFORMATION:

The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. (System.Data)

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

OK

4. now inside this link the correct answer mentioned the following:-

The problem you are likely having is the Import/Export Wizard is being launched as the 32-bit version. This is probably due to you right clicking on a database and clicking on import and since SSMS is a 32-bit program it will launch 32-bit processes. Try explicitly running the Import/Export Wizard (64-bit) by clicking on Start->Program Files->Microsoft SQL Server 2012->Import and Export Data (64-bit) to import your data from a 64-bit datasource.

so i run the Import/Export Wizard (64-bit) , but inside the data source drop-down i can not find excel sheet as follow:-

5. final step i tried running this command :-

INSERT INTO [SalesDataDemo].[dbo].[SalesData] select * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=c:*****.xlsx;HDR=YES', 'SELECT * FROM [Sheet1$]')

but i got this error:-

Msg 7403, Level 16, State 1, Line 1 The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered.

so can anyone adivce how i can import my .xlsx file inside my sql server 2008 r2 database ?

Final note, now the SQl server i am trying to do the import inside it , does not have excel sheet or office installed.. so could this be the problem ?

Answer 1

After you install ACE, if you still get this error, try picking different Excel version from the dropdown. It sounds silly, but really, try each one. The import wizard doesn't seem to guess very well about which drivers you have installed.

Answer 2

Go to Microsoft's website and download Microsoft Access Database Engine 2010 Redistributable. Install that and import the following settings into the registry.

Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\Providers\Microsoft.ACE.OLEDB.12.0]
    "AllowInProcess"=dword:00000001
    "DynamicParameters"=dword:00000001
    "DisallowAdhocAccess"=dword:00000000

I believe that should allow you to import your spreadsheet.