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.

How are you importing it? Through the wizard? Have you selected 'Excel source'? We need more information. — Kris Gruttemeyer, Apr 23 '15 at 20:50
How did Access get in the middle of this? I thought you importing data to sql from Excel? — Sean Lange, Apr 23 '15 at 21:14
http://stackoverflow.com/questions/375991/import-excel-spreadsheet-columns-into-sql-server-database. Not enough info if you are using the wizard make sure you have the datatypes, lengths, and proper Jet engine. — bumble_bee_tuna, Apr 23 '15 at 21:38

score 5 · Accepted Answer · answered Apr 24 '15 at 16:19

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.

three years later this saved me hours (more) of frustration :-) — Chris, Jan 16 '19 at 14:35
7 years, & 7 months after, this saves me after an hour of spinning around in circles — Tanaka Mawere, Nov 26 '22 at 12:09

score 5 · Answer 2 · answered Jun 27 '18 at 08:56

5

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.

answered Jun 27 '18 at 08:56

Dgloria

161
1
12

1

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 – Ali Mirzaie Mar 02 '21 at 08:29

score 2 · Answer 3 · answered Nov 25 '20 at 23:14

2

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.

answered Nov 25 '20 at 23:14

alekks

76
7

1

In My Case: Renaming the extension from .xlsx to. xls, and then keep opening the file worked like magic. – Rao Farhan Dec 16 '21 at 10:18

score 1 · Answer 4 · answered Mar 10 '16 at 17:15

1

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.

answered Mar 10 '16 at 17:15

Rich

11
1

score 1 · Answer 5 · answered Jan 31 '18 at 09:14

1

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

answered Jan 31 '18 at 09:14

Gene Pel

11
1

score 0 · Answer 6 · answered Mar 16 '16 at 06:28

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.

score 0 · Answer 7 · answered May 13 '16 at 07:16

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!

score 0 · Answer 8 · answered Aug 06 '16 at 16:23

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.

score 0 · Answer 9 · answered Dec 16 '16 at 18:12

0

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.

answered Dec 16 '16 at 18:12

wrangler2004

1

score 0 · Answer 10 · answered Aug 13 '20 at 00:28

0

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.

answered Aug 13 '20 at 00:28

reasonet

145
12

Import data to SQL Server table from Excel

10 Answers10