January 4, 2017 at 12:22 am
Goal is to have one sql table for the 2016 excel file source. Am willing to try anything just to get the files uploaded to SQL Server.
Two ways have tried to import this excel 2016 file using Import Wizard in SSMS.
1. Excel:
->. chose Microsoft Excel source
->. navigated to file (have excel saved in variants Excel 97-2003 Worksheet and Microsoft Excel Worksheet, and tried both)
->. hit next
error:
The 'Microsoft.ACE.OLEDB.16.0' provider is not registered on the local machine. (System.Data)
2. CSV:
->. converted excel to csv file and chose Flat File source
->. navigated to csv file
->. hit next
error:
The ConnectionString property has not been initialized. (System.Data)
Installed AccessRuntime_x64_en-us and retried above. No success and I do not know how to solve for these errors. Kindly help.
--Quote me
January 4, 2017 at 2:47 am
Install the x86 drivers, and give it another try. Excel connections (at least 2014 prior), only worked with 32 bit drivers, not 64 bit. I don't know about 2016 (I haven't tested), but this tells me that this hasn't changed.
Edit: P.s. If you can, give the server a reboot after installation. If it can't and it doesn't work, do a restart so when you can and try again.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 4, 2017 at 8:30 am
I tried installing Access Runtime x86, as advised, but got error: 'We can't install the 32-bit version of Office because we found the following 64-bit programs on your PC: Microsoft Access Runtime 2013 Microsoft Office 32 bit Components 2013 Please uninstall all 64-bit Office programs, then retry installing 32-bit Office. If you want to install 64-bit Office instead, please run the 64-bit setup.
On my machine is installed 64 bit Office 365 which wouldn't install until I uninstalled all 32 bit components, and nothing in my Programs panel suggests I have anything office 32 bit. I can't find any 32 bit components to uninstall, so I don't trust this error message.
This 64 bit versus 32 bit is a gotcha I don't know how to get around. Please, if you have any other suggestions, offer them.
--Quote me
January 4, 2017 at 8:34 am
Just to double check, is this what you're trying to install? Microsoft Access Database Engine 2010 Redistributable
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 4, 2017 at 8:37 am
No. I used Microsoft Access 2013 Runtime
I'll try the Microsoft Access Database Engine 2010 Redistributable you shared.
--Quote me
January 4, 2017 at 9:00 am
Installation of Microsoft Access Database Engine 2010 Redistributable completed. Went back to trying to import excel file:
IMPORT WORKED!
Now, I found the below the best configuration for importing
1. select datasource Microsoft Excel
2. gave filepath to Excel 2016 version of the file to import,
2. for Excel Version selected Microsoft Excel 2007-2010
3. selected 'Microsoft OLEDB provider for sql server' for Destination
do you know why I need to use Excel Version 2007-2010 to import a Excel 2016 file? Thanks Thom.
--Quote me
January 4, 2017 at 9:06 am
polkadot (1/4/2017)
Installation of Microsoft Access Database Engine 2010 Redistributable completed. Went back to trying to import excel file:IMPORT WORKED!
Now, I found the below the best configuration for importing
1. select datasource Microsoft Excel
2. gave filepath to Excel 2016 version of the file to import,
2. selected Excel Version 2007-2010
3. selected 'Microsoft OLEDB provider for sql server' for Destination
do you know why I need to use Excel Version 2007-2010 to import a Excel 2016 file? Thanks Thom.
I wasn't actually aware that there had been any encoding changes to Excel documents since the 2007-2010 format. I just let the Import wizard choose for me, as it's one of the few things it assumes correctly (unlike the data types, where you're in for a whirlwind of wrong assumptions, truncations and failed conversions).
I'm hazarding a complete guess here, but it might be that Excel 2016 only saves in "2016" encoding if it uses 2016+ only functionality. Thus the file is back compatible for the users that haven't upgraded to the latest version of Office (some of us are still running with a 32bit version Office 2010 🙁 )
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 4, 2017 at 9:57 am
You saved my day.
--Quote me
June 27, 2018 at 5:10 pm
does it matter what version of SSMS you're using? I'm trying to import an excel 2016 sheet into SSMS 2017. 32bit office and 32bit access redistributable installed 🙁
August 13, 2019 at 8:16 pm
This also worked for me ...
We recently upgraded to Office 365 (64 bit) and I could no longer import Excel files in either SSMS 2014 or SSMS v17.9.1. Yet after installing the Microsoft Access Database Engine 2010 Redistributable (not a higher version and I installed the AccessDatabaseEngine.exe NOT the 64-bit version), I was able to import Excel files worked on both versions of SSMS, using Excel version of Microsoft Excel 2007-2010.
Thanks!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply