Import data to SQL Server table from Excel

sql-server import-from-excel
prabs picture prabs · Apr 23, 2015 · Viewed 20.4k times · Source

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.

Answer

prabs picture prabs · Apr 24, 2015

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.

Related questions

How does one change the default varchar 255 of a column when importing data from Excel to Sql Server using Import Export Wizard?

I am trying to import data from excel to Sql Server using Import wizard. Everything seems fine in the first steps. The wizard is able to read the excel file and properly identify columns. But in the final step when …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More