• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 267
  • Last Modified:

Use sql wizard to import excel table - sql server express 2016 to Excel 2015 (e3)

I am trying to use the sql management studio import wizard to import some tables from excel.

I have SQL server express 2016 installed and Excel 2016 (e3) (see image of version below).

Everytime I try to import from excel I get the following error msg (see image below).

So far I have tried installing Microsoft Access Database Engine 2010 Redistributable after reading suggestions in blogs etc. (link to download below). However I am still getting the same error msg.

https://www.microsoft.com/en-gb/download/details.aspx?id=13255


Note: I am on a 64 bit system and I am using 64 bit verions of excel and sql.

sql error
0
mikes6058
Asked:
mikes6058
  • 6
  • 5
1 Solution
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
The DTExecUI is a 32-bit only utility. So, it will look for the 32-bit version of the driver, not 64-bit. Can you install the 32-bit version of the driver and try running your SSIS package again?

As an alternate, you can try running the DTExec (command-line) utility.
0
 
mikes6058Author Commented:
Sorry I'm not sure what you mean my the DTexecUi? Also what is the driver bit you want me to install.

Sorry for being an idiot - this is my first day using SQL

Thanks
Mike
0
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
That's quite okay. No apologies necessary. The DTExecUI is the GUI-based
utility that you are using to execute your SSIS package to import Excel
data to SQL Server.

This utility is a 32-bit only utility. Hence, all components that it
uses have to be 32-bit. In your case, since your Office is 64-bit, it
does not have the necessary 32-bit components to read from your Excel file.

One sure shot way is to use a 32-bit version of Office. But since you
already have the 64-bit version of Office, your step to install
"Microsoft Access Database Engine 2010 Redistributable" was correct.
However, you need to use the 32-bit redistributable.

On the download page, you must have seen 2 executables:
1. AccessDatabaseEngine.exe
2. AccessDatabaseEngine_X64.exe

The "AccessDatabaseEngine.exe" (without the _x64 in the file name) is
the 32-bit version - which is what you need to download and install.

Once installed, try to import the data once again - hopefully, you
should be all set.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
mikes6058Author Commented:
Hi

Thank-you for elaborating but unfortunately this has not worked. I am getting the following error message when attempting to download the 32 bit option.

error.PNG
Could it have anything to do with the fact that the Microsoft Access Database Engine is a 2010 version and not 2016?

On the download page their are a set of instructions which specifically mentions earlier versions of excel but not 2016. See below.

instructions
0
 
mikes6058Author Commented:
Just so you are aware I cannot reinstall a 32 bit version of office as it does not perform as well as the 64
0
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
Hmmm...okay. You must have two versions of "Import and Export Data" wizard on your machine. One of them must have the text "(64-bit)". Can you try to use that version of the Import and Export Data wizard?
0
 
mikes6058Author Commented:
You are right, there does appear to be a 32 and 64 bit "import export data" wizard. However I have done what you suggested and I am getting the same error msg as before (see below).

error-3.PNG
Mike
0
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
Thank-you for your patience. Office documents follow a common standard across various versions. Can you try using the 2007 Office System Driver? You can get it from here: http://www.microsoft.com/en-us/download/confirmation.aspx?id=23734

Our systems use 32-bit Office on 64-bit versions, so I am unable to test your scenario, however from the community it does look like the 64-bit Office drivers do not help in a lot of cases. The 2007 Office drivers have helped a lot of people.
0
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
Oh, and when you select your source, do try to see if selecting an older Office version helps (e.g. Excel 2007 or 2013).
0
 
mikes6058Author Commented:
Bingo, it worked using 2007. Thanks for your perseverance Nakul!

Mike
0
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
You're welcome! Glad I could be of help.
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now