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#
 

5 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. Hello, it appears there is a syntax error, where, the closing quote in the code line below is actually escaped and not ending the string literal correctly, causing the script to be thrown off.

    string LogFolder = @"C:\Log\";

    ReplyDelete
  3. I want to import data from all the folders and subfolders in the specified path what changes i can do in this .. please help

    ReplyDelete
    Replies
    1. while traversing the sub-folder, it is able to pick up the file name from the sub folder but while opening the file it is taking the parent folder path

      example: file path - d:\test
      sub directory : d:\test\test2

      inside subdirectory my file is present but when iterating it is reading the path of sub directory file as : d:\test\file_name

      Delete
  4. string SheetName = "Customer";

    what is customer ?

    ReplyDelete