Import excel data to sql server script

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

output

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:

Difference between SQL and NoSQL (SQL vs NoSQL)

SQL Comments (Comment in SQL Query)

SQL Server Management Studio (SSMS) Versions

Free SQL Server Reporting Tools

Best Free SQL Server database hosting

How to check sql server version? (Various ways explained)

Validate Email address in SQL Server

Uniqueidentifier in SQL Server

Best SQL Server Tools for SQL Developers.

How to Create database in sql server management studio (Create table and Insert data)

How do I automatically import data from Excel to SQL Server?

Can Excel write to a SQL database?

How do I import a script into SQL Server?