Gotcha: Importing from Excel to SQL Server

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:

  1. 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)
  2. 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s