C# - Import Only Matching Column's Data to SQL Server Table from Excel Files Dynamically in C#

Scenario: Download Script

You are working as C# or Dot Net Developer.  You get Excel files in source folder with single or multiple sheets that you need to load into SQL Server Table called dbo.Customer in TechbrothersIT Database.
Here is the definition of dbo.Customer Table.

CREATE TABLE dbo.Customer (
    id INT
    ,NAME VARCHAR(50)
    ,dob DATE
    )

There are some problems with Excel Sheet data
1) Sometime you get exact number of columns in Sheet
2) Sometime you get extra columns in Excel file Sheets
3) Sometime you get less columns than your table definition.
4) Excel File can be provided with any name in given folder
5) Sheet Name can change as well in each Excel file

You need to write C# program that should be able to handle above situation and load all data for columns which match with our SQL Server Table Definition( dbo.Customer). 

It should ignore extra columns in Excel sheets and load the matching columns data without error. If we receive less columns in Excel sheet, It should go ahead and load that into table. 

This is how our excel files and sheets looks like
How to load data from Excel to SQL server Table for Matching Columns only in C#

Sheet Data for Customer_TechBrothersIT1.xlsx

Load Data from Excel Sheet Dynamically in SQL Server Table  by using C#


Sheet Data for Customer_TechBrothersIT2.xlsx
Import data from Excel files dynamically to SQL Server for matching columns dynamically in C#


Sheet Data for Customer_TechBrothersIT3.xlsx
Less Columns provided as compared to table in Excel Sheet , need to loaded to SQL table by using C#


The below C# code can be used to import data from multiple excel files with single or multiple excel sheets for matching columns.

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 source folder path where excel files are present
                string FolderPath = @"C:\Source\";
                //Provide the Database Name 
                string DatabaseName = "TechbrothersIT";
                //Provide the SQL Server Name 
                string SQLServerName = "(local)";
                //Provide the table name 
                String TableName = @"Customer";
                //Provide the schema of table
                String SchemaName = @"dbo";

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

                var directory = new DirectoryInfo(FolderPath);
                FileInfo[] files = directory.GetFiles();

                //Declare and initilize variables
                string fileFullPath = "";

                //Get one Book(Excel file at a time)
                foreach (FileInfo file in files)
                {
                    fileFullPath = FolderPath + "\\" + file.Name;

                    //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 Sheet Names
                    cnn.Open();
                    DataTable dtSheet = cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    string sheetname;
                    sheetname = "";
                    foreach (DataRow drSheet in dtSheet.Rows)
                    {
                        if (drSheet["TABLE_NAME"].ToString().Contains("$"))
                        {
                            sheetname = drSheet["TABLE_NAME"].ToString();

                            //Load the DataTable with Sheet Data so we can get the column header
                            OleDbCommand oconn = new OleDbCommand("select top 1 * from [" + sheetname + "]", cnn);
                            OleDbDataAdapter adp = new OleDbDataAdapter(oconn);
                            DataTable dt = new DataTable();
                            adp.Fill(dt);
                            cnn.Close();

                            //Prepare Header columns list so we can run against Database to get matching columns for a table.
                            string ExcelHeaderColumn = "";
                            string SQLQueryToGetMatchingColumn = "";
                            for (int i = 0; i < dt.Columns.Count; i++)
                            {
                                if (i != dt.Columns.Count - 1)
                                    ExcelHeaderColumn += "'" + dt.Columns[i].ColumnName + "'" + ",";
                                else
                                    ExcelHeaderColumn += "'" + dt.Columns[i].ColumnName + "'";
                            }

                            SQLQueryToGetMatchingColumn = "select STUFF((Select  ',['+Column_Name+']' from Information_schema.Columns where Table_Name='" +
                                                         TableName + "' and Table_SChema='" + SchemaName + "'" +
                                                          "and Column_Name in (" + @ExcelHeaderColumn 
                                                          + ") for xml path('')),1,1,'') AS ColumnList";


                            //Get Matching Column List from SQL Server
                            string SQLColumnList = "";
                            SqlCommand cmd = SQLConnection.CreateCommand();
                            cmd.CommandText = SQLQueryToGetMatchingColumn;
                            SQLConnection.Open();
                            SQLColumnList = (string)cmd.ExecuteScalar();
                            SQLConnection.Close();

                            //Use Actual Matching Columns to get data from Excel Sheet
                            OleDbConnection cnn1 = new OleDbConnection(ConStr);
                            cnn1.Open();
                            OleDbCommand oconn1 = new OleDbCommand("select " + SQLColumnList
                                + " from [" + sheetname + "]", cnn1);
                            OleDbDataAdapter adp1 = new OleDbDataAdapter(oconn1);
                            DataTable dt1 = new DataTable();
                            adp1.Fill(dt1);
                            cnn1.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 dt1.Columns)
                                    BC.ColumnMappings.Add(column.ToString(), column.ToString());
                                BC.WriteToServer(dt1);
                            }
                            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 the C# Console application that I created by using above C# code. It loaded the data from sample Excel files from multiple excel sheets for matching columns. Also I noticed that BulkCopy is case sensitive when it comes to match the columns. Make sure you always have the Column Name in same case as you have in SQL Server Table.

How to import data from multiple excel files to sql server table for matching columns only in C#



12 comments:

  1. There is lots of Writer but your writing way is so good and different. It’s really helpful for us and knowledgeable so thanks for sharing...
    Advanced Excel Training in Delhi
    Advanced Excel Training in Noida
    Advanced Excel Training in Gurgaon

    ReplyDelete
  2. There are no aeronautical organizations visiting our school and there is zero percent chance that I would find up an occupation in aeronautical. data science course in pune

    ReplyDelete

  3. Attend The Digital Marketing Courses in Bangalore From ExcelR. Practical Digital Marketing Courses in Bangalore Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The Digital Marketing Courses in Bangalore.
    Digital Marketing training in Bangalore

    ReplyDelete
  4. Attend The Data Analytics Courses From ExcelR. Practical Data Analytics Courses Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The Data Analytics Courses.
    ExcelR Data Analytics Courses

    ReplyDelete
  5. Such a very useful article. I have learn some new information.thanks for sharing.
    data scientist course in mumbai

    ReplyDelete
  6. Well, The information which you posted here is very helpful & it is very useful for the needy like me.., Wonderful information you posted here. Thank you so much for helping me out to find the Data science course in mumbai Organisations and introducing reputed stalwarts in the industry dealing with data analyzing & assorting it in a structured and precise manner. Keep up the good work. Looking forward to view more from you.

    ReplyDelete
  7. Well, the most on top staying topic is Data Science.Out of all, Data science course in mumbai is making a huge difference all across the country. Thank you so much for showing your work and thank you so much for this wonderful article.

    ReplyDelete

  8. Excelr is providing emerging & trending technology training, such as for data science, Machine learning, Artificial Intelligence, AWS, Tableau, Digital Marketing. Excelr is standing as a leader in providing quality training on top demanding technologies in 2019. Excelr`s versatile training is making a huge difference all across the globe. Enable ​business analytics​ skills in you, and the trainers who were delivering training on these are industry stalwarts. Get certification on " data science in hyderabad" and get trained with Excelr.

    ReplyDelete
  9. Thanks for sharing your valuable information to us, it is very useful.
    data science course

    ReplyDelete
  10. I feel very grateful that I read this. It is very helpful and very informative and I really learned a lot from it.
    data analytics

    ReplyDelete