Import external excel data in SQL Server

In SQL Server, we can upload external data from excel or text files by two ways:

Ø  Import and Export wizard

Ø  SSIS (SQL Server Integration System)

Here, first we will see the step-by-step process to upload excel data into a table of SQL Server database server.

Below is the excel data, which we have for Sales Order:

 

For import data into database table, there is an option available for importing/exporting external data as:

I have a database TestDB on my system, where I have created a table which is having same number of columns:

USE TestDB

CREATE table SalesOrder

(

OrderDate Date,  

Region      varchar(40),

Rep   varchar(40),

Item varchar(40),

Units int, 

UnitCost float,  

Total float

)

Do right click on database in SQL Server Management studio, where we have to upload data, as on TestDB database, I did right click to go on Import option under the Task option:

 

SQL Import and Export wizard will be appeared on the screen, click on Next button to start process:

 

New Dialog box will be opened for asking to choose a source, so here we have excel as source:

I have selected my excel workbook, which I need to use for uploading data:

 

On the next page, we need to provide destination source information so here I have provided my DB server and database information, where I have my table to upload data:

On the next page, there are two options available:

Ø  Copy data from one or more tables or views: When we want to copy of source data for importing into destination.

Ø  Write a query to specify the data to transfer: When we have to write a query to get source data for importing.

So here we have to copy excel data, so I have selected first option as:

 

On the next page, wizard is providing source information so on first sheet of excel workbook, we have data so I have point it to my database table SalesOrder and then I have clicked on Edit Mappings option to check mapping of source and destination columns as:

 

Now Click on Next button to move on next page, where we can validate the source data type with destination data type to avoid the data type mismatch issue.

 

On the next page “Save and Run Package”, we can select “Save SSIS package” if we want to save a SSIS package for further use.

On the “Save SSIS package” page, we need to provide a package name and click on Next to finish this process.

 

After successfully completion of import processing, dialog box will look like as:

Now we can check the table SalesOrder in database for data:

If you have any thoughts or suggestion, feel free to post in the below comment section.

Thanks!