Import data to SQL Server table from Excel

Question

I am trying to import data from excel file to SQL Server table. I am getting this error "External table is not in the expected format (Microsoft Office Access Database Engine)" I am using Microsoft SQL Server 2012 and Excel 2007. I tried Microsoft SQL Server Import and Export wizard. Then I chose Microsoft Excel as Data source, chose the file name and chose the version of excel. Then i got the above error message.

Beneath

How are you importing it? Through the wizard? Have you selected 'Excel source'? We need more information. – Little

How did Access get in the middle of this? I thought you importing data to sql from Excel? – Ingenerate

#376491. Not enough info if you are using the wizard make sure you have the datatypes, lengths, and proper Jet engine. – Vedda

Answer 1

Found out that it was to do with the excel file. Before opening the excel file i got this error message "The file you are trying to open .xls is in a different format than specified by the file extension. Verify that file is not corrupted and is from a trusted source before opening the file. " Opened it and copied its contents and created a new excel file from it. Then I used this file to import in the wizard. I specified Microsoft Excel as source with file name and Microsoft excel 2007 as version. For the destination I chose SQL Server Native client 11.0, Server name and database. I chose edit mappings to map the fields between excel sheet and target table. After running the wizard it transferred the data to the target table in sql server.

Beneath

three years later this saved me hours (more) of frustration :-) – Evangelinaevangeline

7 years, & 7 months after, this saves me after an hour of spinning around in circles – Discounter

Answer 2

Save it with a different name as the latest xlsx version. Nevermind if you think you changed nothing, it actually did. Then try to import the new file.

Harridan

when you save the html as excel this problem will happen and by save as action the file will save as excel real format. thanks worked for me – Puffball

Answer 3

Another answer to this question could be (as it was for me) that it only works when I have the same Excel file, that I'm importing to the database, open. The reason for this being my Companys encryption policies, using a software called NASCA. When I close the file, it gets encrypted and gets unreadable by e.g. SQL Server Management Studio.

So, try to keep the file open during the import and it might work.

Czarevna

In My Case: Renaming the extension from .xlsx to. xls, and then keep opening the file worked like magic. – Rhearheba

Answer 4

Check to make sure the Excel workbook doesn't have a password on it (and if it does remove it). I was getting same error and this fixed it.

Rolland

Answer 5

You should save your Excel file to a lower version is can be a 2003 version of excel. Once save the file you can now do the Data Transfer of SQL.

Gene

Method

Answer 6

I had this issue, with an xsls file (Excel 2007). The wizard identified the version correctly, but failed with the "not in expected format" error.

I am not sure what the source of the error was - I suspect there were links or references in the spreadsheet that were invalid.

I copied the contents of the sheet I was interested in, and pasted into a new blank worksheet. I saved it, and then opened it in the import wizard, and had no issues importing it.

Abutting

Answer 7

I found out that the issue was that the Excel Sheet was in .xls format and i was using Excel 2010 and SQL Server 2014 so the solution was to simply convert the spreadsheet to .xlsx and then run the wizard again by picking Excel as the Data Source which automatically chose Exel 2007 as the Excel Version for the data source and that solved the issue as the wizard run smoothly.

So there is no need to copy the contents of the sheet into a new spreadsheet.

hope that helps anybody!

Chan

Answer 8

In my case I was getting this error when importing data from 3 sheets at once. I then choose one sheet only and it worked. I choose the other two sheets next and they worked too so sometime you may just want to import sheets separately or give it a second chance. It might help somebody.

Hyo

Answer 9

My issue was that my excel table did not have a named range defined. Once I created a named range in my excel file, I was able to import it.

Watercourse

Answer 10

I ran into this problem today, and I determined it was due to a graphic at the beginning of the first sheet. Since I only cared about the 3rd sheet, I deleted the first two sheets, and then the error went away.

Jewess

Answer 11

I fixed it by close one drive...

Paraffinic

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center. – Parvis