2

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 enter image description here

  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

enter image description here

  1. 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:-

enter image description here

  1. 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 ?

John John
  • 1
  • 72
  • 238
  • 501
  • Have you looked at the solutions offered here? - [https://stackoverflow.com/questions/9943065/the-microsoft-ace-oledb-12-0-provider-is-not-registered-on-the-local-machine](https://stackoverflow.com/questions/9943065/the-microsoft-ace-oledb-12-0-provider-is-not-registered-on-the-local-machine) – Padwan Jul 24 '17 at 16:36
  • Is SSMS installed on the same computer as SQL Server? And do you have the driver installed there? – Jacob H Jul 24 '17 at 17:05
  • @JacobH yes the SSMS is installed on the same computer as the SQL server and it is a windows server 2008 R2 – John John Jul 24 '17 at 19:45
  • @Padwan now i trying to avoid installing any things as this is a database server used by many live applications, and our customer does not allow us to install any tools inside the database server, and if they will allow this then i have to wait for atleast one month to get the confirmation.. so i am trying to find a way to export the .xlsx file inside the DB without having to install any additional tools... is this possible? – John John Jul 24 '17 at 19:48
  • Install SSMS on another machine (that is not a server), then install provider there. (In my experience installing this provider on SQL Server database requires a machine restart, even though it is not supposed to) – Alex Jul 24 '17 at 19:55
  • SAve as a a CSV and import it. – Namphibian Jul 25 '17 at 00:10
  • @Namphibian now i tried to save my .xlsx file as .csv file using MS Excel program. and then i used the Import/Export wizard >> and i select the Data Source inside the wizard as flat file. this have imported the data , but i found many problems inside the imported data. the problem is related to the fact that my original .xlsx file contain `,` inside its cells data, and this will cause the .csv file to get wrong mapping.. as to the Import/Export wizard if it find a `,` inside the .csv file it will consider it as a new cell even if this `,` is part of the cell data. – John John Jul 25 '17 at 12:52
  • @Namphibian .. now i was thinking of this workaround. if that to find all the `,` inside my .xlsx file , and replace them with a phrase such as "TestTest1234". then to save the .xlsx file as .csv,, then import the .csv inside sql server. then to write some sql statements on my newly generated table to replace all the `TestTest1234` with `,`!!! – John John Jul 25 '17 at 12:52
  • a Comma in a CSV is easy to handle. Make sure you export strings surrounded by quotes. So dont replace the , with anything. Just export it correctly. – Namphibian Jul 25 '17 at 21:20
  • @Namphibian now i am not doing any manual conversion or export. i am saving the .xlsx file as .csv using MS Excel program. then i am using the Import/export wizard which comes with the SQL server management studio to import the .csv... so now when i save my .xlsx file as .csv file using excel program ,, it will not add any quotes to the generated .csv file.. – John John Jul 26 '17 at 15:46

3 Answers3

1

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.

Drgnkght
  • 121
  • 4
0

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.

Choose Data Source - Excel version

tgolisch
  • 6,549
  • 3
  • 24
  • 42
0

You have to install 64x import export wizard. I searched then found this;

Note To use the 64-bit version of the SQL Server Import and Export Wizard, you have to install SQL Server. SQL Server Data Tools (SSDT) and SQL Server Management Studio (SSMS) are 32-bit applications and only install 32-bit files, including the 32-bit version of the wizard.

If you install just SQL Tool Connectivity from SQL Server İnstallation wizard now able to import your excel to sql

[You can read in this page1

Caner Arslandoğmuş
  • 36
  • 4