How To Import Data From Excel Sheet Into A SQL Server Database using C#

Recently I was working on project where I was given huge set of master data in a excel file which i had to migrate to the database. Generally I used to prefer some migration tools for such task like Talend but unfortunately the software was not installed on my system because of recent formatting of the system.

In this post, I will explain how you can migrate/import the data from an excel sheet into a SQL Server database using C# language.

For the demo purpose, I will create an very simple C# console application to migrate an excel having employees data.

Excel Migration Console App

 

Now, Let’s Create a table in SQL Server to hold the data.

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE Employee
(
 
    ID INT PRIMARY KEY IDENTITY(1,1),
 
    FirstName VARCHAR(30),
 
    LastName VARCHAR(30),
 
    Department VARCHAR(30),
 
    Gender VARCHAR(10)
)

 
We have Employee schema ready. Now, we can write C# code to migrate data into Employee table.

OLE DB (Object Linking and Embedding, Database, sometimes written as OLEDB or OLE-DB) an API designed by Microsoft providing a set of interfaces that allows accessing data from a variety of sources that do not necessarily implement SQL.

So, Add these namespace in your c# class.

1
2
USING SYSTEM.DATA.OLEDB;
USING SYSTEM.DATA.SQLCLIENT;

See the below complete code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
using System;
using System.Data.OleDb;
using System.Data.SqlClient;
 
namespace ExcelMigration
{
    class Program
    {
        static void Main(string[] args)
        {
            // Excel File Path Location
            string excelfilepath = @"C:\Users\rakesh.kumar\Documents\employee.xls";
 
            // SQL Server TableName
            string TableName = "Sample";
 
            // Make Sure Your Sheet Name And Columns Names Are Correct, here sheet name is sheet4
            string exceldataquery = "select FirstName,LastName,Department,Gender from [Sheet1$]";
            try
            {
                // Excel Connection String and SQL Server Connection String
                string excelconnectionstring = @"provider=microsoft.jet.oledb.4.0;
                      data source=" + excelfilepath + 
                      ";extended properties=" + "\"excel 4.0;hdr=yes;\"";
                string sqlconnectionstring = @"server=(localdb)\ProjectsV13; 
                    database = TestDB; connection reset = false";
 
                //Execute A Query To Drase Any Previous Data From Employee Table
                string deletesqlquery = "delete from " + TableName;
                SqlConnection sqlconn = new SqlConnection(sqlconnectionstring);
                SqlCommand sqlcmd = new SqlCommand(deletesqlquery, sqlconn);
 
                sqlconn.Open();
                sqlcmd.ExecuteNonQuery();
                sqlconn.Close();
 
                // Build A Connection To Excel Data Source And Execute The Command
                OleDbConnection oledbconn = new OleDbConnection(excelconnectionstring);
                OleDbCommand oledbcmd = new OleDbCommand(exceldataquery, oledbconn);
                oledbconn.Open();
                OleDbDataReader dr = oledbcmd.ExecuteReader();
 
                // Connect To SQL Server DB And Perform a Bulk Copy Operation
                SqlBulkCopy bulkcopy = new SqlBulkCopy(sqlconnectionstring);
 
                // Provide Excel To Table Column Mapping If Any Difference In Name
                bulkcopy.ColumnMappings.Add("FirstName", "FirstName");
                bulkcopy.ColumnMappings.Add("LastName", "LastName");
                bulkcopy.ColumnMappings.Add("Department", "Department");
                bulkcopy.ColumnMappings.Add("Gender", "Gender");
 
                // Provide The Table Name For Bulk Copy
                bulkcopy.DestinationTableName = TableName;
 
                while (dr.Read())
                {
                    bulkcopy.WriteToServer(dr);
                }
 
                oledbconn.Close();
            }
            catch (Exception ex)
            {
                //handle exception
            }
        }
    }
}

Code Explanation:

1
2
3
4
// Excel Connection String and SQL Server Connection String
                string excelconnectionstring = @"provider=microsoft.jet.oledb.4.0;
                      data source=" + excelfilepath + 
                      ";extended properties=" + "\"excel 4.0;hdr=yes;\"";

“hdr=yes;” indicates that the first row contains column names, not data. “hdr=no;” indicates the opposite.

 

1
2
// Make Sure Your Sheet Name And Columns Names Are Correct, here sheet name is sheet4
string exceldataquery = "select FirstName,LastName,Department,Gender from [Sheet1$]";

FROM clause takes spreadsheet name followed by a “$” and wrapped in “[]” brackets.
“SELECT * FROM [Sheet1$a5:d]”, start picking the data as of row 5 and up to column D.

Download the source code here
 
If you have any query then please comment below and let us know. If you liked this article, then please follow us on Facebook to get notification for new posts.

Happy Learning 🙂

Rakesh is a programming Geek, technology enthusiast, a passionate writer, thinker with passion for computer programming.  He loves to explore technology and finds ultimate joy when writing about trending technology, geek stuff and web development.