Extremely short post about importing Excel data into SQL.
There is a gotcha that I just lost half an hour to. I’m writing this to save you that half hour.
You have data in Excel 2016 and you want to get it into SQL.
You open the Import/Export wizard and click on Excel:
You press Next and get this error:
You google it and end up somewhere like Stack Overflow where you see an answer something like this:
This download will install a set of components that facilitate the transfer of data between existing Microsoft Office files such as Microsoft Office Access 2007 (*.mdb and .accdb) files and Microsoft Office Excel 2007 (.xls, *.xlsx, and *.xlsb) files to other data sources such as Microsoft SQL Server.
You dutifully install the drivers – and fail again.
There are two solutions:
- Roll your Excel file back to 2003 by making an .xls file (seen solely through the prism of importing this should make no operational difference)
- Install the old drivers and as specificed on Stack Overflow, import again, but – here’s the gotcha – select Excel 2007-2010
Thank you for reading. Hopefully I saved you the time I lost.
Databackwriter Blog 