C# - How to Import Excel Sheet to SQL Server Table in C#

Scenario: Download Script

You are working as C# or dot net developer in insurance company. You need to write a C# code that should read the data from Excel Sheet and load to SQL Server Table.

How to Import Excel Sheet Data to SQL Server Table in C#

Let's create the table in database first after studying our data for Customer Sheet.

CREATE TABLE [dbo].[tblCustomer](
    [id] int NULL,
    [name] varchar(100) NULL,
    [dob] date NULL
) 

I created Console Application by using below C# code that will be able to import data from Customer Excel sheet to our dbo.tblCustomer table. 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
//added below name spaces
using System.IO;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;

namespace TechBrothersIT.com_CSharp_Tutorial
{
    class Program
    {
        static void Main(string[] args)
        {
            //the datetime and Log folder will be used for error log file in case error occured
            string datetime = DateTime.Now.ToString("yyyyMMddHHmmss");
            string LogFolder = @"C:\Log\";
            try
            {

                //Provide the Database Name 
                string DatabaseName = "TechbrothersIT";
                //Provide the SQL Server Name 
                string SQLServerName = "(local)";
                //Provide the table name in which you want to load excel sheet's data
                String TableName = @"tblCustomer";
                //Provide the schema of table
                String SchemaName = @"dbo";
                //Provide Excel file path
                string fileFullPath = @"C:\Source\Customer_1.xlsx";
                //Provide Sheet Name you like to read
                string SheetName = "Customer";


                //Create Connection to SQL Server Database 
                SqlConnection SQLConnection = new SqlConnection();
                SQLConnection.ConnectionString = "Data Source = "
                    + SQLServerName + "; Initial Catalog ="
                    + DatabaseName + "; "
                    + "Integrated Security=true;";


                    //Create Excel Connection
                    string ConStr;
                    string HDR;
                    HDR = "YES";
                    ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" 
                        + fileFullPath + ";Extended Properties=\"Excel 12.0;HDR=" + HDR + ";IMEX=0\"";
                    OleDbConnection cnn = new OleDbConnection(ConStr);        


                            //Get data from Excel Sheet to DataTable
                            OleDbConnection Conn = new OleDbConnection(ConStr);
                            Conn.Open();
                            OleDbCommand oconn = new OleDbCommand("select * from [" + SheetName + "$]", Conn);
                            OleDbDataAdapter adp = new OleDbDataAdapter(oconn);
                            DataTable dt = new DataTable();
                            adp.Fill(dt);
                            Conn.Close();

                            SQLConnection.Open();
                            //Load Data from DataTable to SQL Server Table.
                            using (SqlBulkCopy BC = new SqlBulkCopy(SQLConnection))
                            {
                                BC.DestinationTableName = SchemaName + "." + TableName;
                                foreach (var column in dt.Columns)
                                    BC.ColumnMappings.Add(column.ToString(), column.ToString());
                                BC.WriteToServer(dt);
                            }
                            SQLConnection.Close();   
                
            }
            catch (Exception exception)
            {
                // Create Log File for Errors
                using (StreamWriter sw = File.CreateText(LogFolder
                    + "\\" + "ErrorLog_" + datetime + ".log"))
                {
                    sw.WriteLine(exception.ToString());

                }

            }

        }
    }
}

I executed Console application and it was able to read the data from Customer sheet from Excel file and imported into SQL server Table.

How to import data from Excel Sheet to SQL Server table in C#