In previous article, I have mentioned Import csv into SQL server (with query OR without query using SSMS) but sometimes we have data in Excel format so in this article, I have provided step by step procedure to import excel data in SQL Server database using SQL Server Management Studio (SSMS) and with using SQL Query also.
Let's take a look at sample excel file which we will use to import in SQL Server database.
Import Excel to SQL server using SSMS Wizard
In this process, we will use SQL server management studio GUI to import Excel file in sql server database table, this GUI based option is good and easy for large excel files, since it allows you to import data, step by step and more easily.
Step 1: Open SQL Server Management Studio (SSMS) and then select database in which you want to excel file then right-click on it -> "Tasks"->Select "Import data"
Step 2: Now, a Wizard will open, click "Next" on the wizard to configure settings for importing excel data in sql server
and in next step, we will have to choose excel file version as data-source, excel file version and browse location of excel file which we need to import
Since, I am importing Excel file of version 2007-10, I have selected this excel version, and have already installed 'Microsoft.ACE.OLEDB.12.0', if you are importing excel of version 2016, then you must select that version from dropdown and you must have installed 'Microsoft.ACE.OLEDB.16.0' in your local machine, otherwise you may get error 'The 'Microsoft.ACE.OLEDB.16.0' provider is not registered on the local machine. (System.Data)'
Step 3: Select destination source
Now, we will need to select Destination as "SQL Server Native Client 11.0", and Server Name which has destination database and table
You might also have to add SQL Server Authentication details, in the above process we are using Windows Authentication, so I have selected that.
And you also need to select database, for above case it is "OrderDetails"
Step 4: In the next screen, you can select "Copy data from one or more tables or views" as option, so simply save excel rows in existing table.
Step 5: Select correct Excel sheet and destination table, and you can also review or edit mappings of source/destination table columns
In the next step you can still review mappings
Stpe 6: Once done, you can click on "Next" and review and verify input/output of all the details
Step 7: Click finish and if you see everything in green, means excel was imported in SQL Server database table successfully
You can also review output in SQL Server table, by executing query
Use OrderDetails Select * from Orders
Import Excel to SQL server using query
So, we can use OPENROWSET to import an Excel file in SQL Server.
INSERT INTO Orders ( Country, Price, OrderQuantity) SELECT A.Country, A.Price, A.OrderQuantity FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:\excel\orders.xlsx', 'SELECT * FROM [Sheet1$]') as A
Once above query is output, I was able to save excel file data into SQl Server Table
In Above query
[Sheet1$] = Sheet name
A.Country, A.Price, A.OrderQuantity = Name of the columns, which I had in Excel file (we are skipping "Id" column, since it was Identity column in Orders table)
To execute above query you must have ACE provider for Microsoft.ACE.OLEDB.12.0 plus Ad hoc distributed queries must be enabled, you can enable Ad Hoc distributed queries by executing below query
EXEC sp_configure 'show advanced options', 1 RECONFIGURE GO EXEC sp_configure 'ad hoc distributed queries', 1 RECONFIGURE GO
Also, to run above query, I had to use my SQL Server Management Studio in Administrator mode, otherwise I ran into problem
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error". Msg 7303, Level 16, State 1, Line 2 Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
You may also like to read: