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#



47 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
  11. Writing with style and getting good compliments on the article is quite hard, to be honest.But you've done it so calmly and with so cool feeling and you've nailed the job. This article is possessed with style and I am giving good compliment. Best!
    data scientist courses

    ReplyDelete
  12. Nice Article...Very interesting to read this article. I have learn some new information.thanks for sharing.
    Click here

    ReplyDelete
  13. I finally found great post here.I will get back here. I just added your blog to my bookmark sites. thanks.Quality posts is the crucial to invite the visitors to visit the web page, that's what this web page is providing.
    ExcelR data analytics

    ReplyDelete
  14. I feel very useful that I read this post. It is very informative and I really learned a lot from it.So thanks for sharing this information with us.
    redbus ticket online booking
    best matrimonial site
    Coupondunia
    Digital Marketing Institute

    ReplyDelete
  15. Easily, the article is actually the best topic on this registry related issue. I fit in with your conclusions and will eagerly look forward to your next updates.
    ExcelR Business Analytics Course

    ReplyDelete
  16. After reading your article I was amazed. I know that you explain it very well. And I hope that other readers will also experience how I feel after reading your article. CLICK HERE

    ReplyDelete
  17. Great post i must say and thanks for the information. Education is definitely a sticky subject. However, is still among the leading topics of our time. I appreciate your post and look forward to more.
    ExcelR Data Analytics courses

    ReplyDelete
  18. You might comment on the order system of the blog. You should chat it's splendid. Your blog audit would swell up your visitors. I was very pleased to find this site. I wanted to thank you for this great read!!
    Please check this Data Science Certification

    ReplyDelete
  19. Excellent Blog! I would like to thank for the efforts you have made in writing this post. I am hoping the same best work from you in the future as well. I wanted to thank you for this websites! Thanks for sharing. Great websites!
    ExcelR data science course in mumbai

    ReplyDelete
  20. Attend The PMP Certification From ExcelR. Practical PMP Certification Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The PMP Certification.
    ExcelR PMP Certification

    ReplyDelete
  21. Awesome..I read this post so nice and very imformative information...thanks for sharing
    Click here for data science course

    ReplyDelete
  22. They're produced by the very best degree developers who will be distinguished for your polo dress creating. You'll find polo Ron Lauren inside exclusive array which include particular classes for men, women.
    Please check ExcelR data science course in pune with placements

    ReplyDelete
  23. A good blog always comes-up with new and exciting information and while reading I have feel that this blog is really have all those quality that qualify a blog to be a one.
    best data analytics courses in hyderabad

    ReplyDelete
  24. After reading your article I was amazed. I know that you explain it very well. And I hope that other readers will also experience how I feel after reading your article.
    data analytics courses

    ReplyDelete
  25. Excellent Blog! I would like to thank for the efforts you have made in writing this post. I am hoping the same best work from you in the future as well. I wanted to thank you for this websites! Thanks for sharing. Great websites!
    ExcelR Data Analytics Course

    ReplyDelete
  26. Excellent effort to make this blog more wonderful and attractive. ExcelR Data Science Training Pune

    ReplyDelete
  27. What a really awesome post this is. Truly, one of the best posts I've ever witnessed to see in my whole life. Wow, just keep it up.

    360digitmg Internet of Things Training

    ReplyDelete
  28. I am really enjoying reading your well written articles. It looks like you spend a lot of effort and time on your blog. I have bookmarked it and I am looking forward to reading new articles. Keep up the good work.
    ExcelR Data Analytics Course

    ReplyDelete
  29. Excellent effort to make this blog more wonderful and attractive. ExcelR Data Science Class in Pune

    ReplyDelete
  30. Nice information, valuable and excellent work, as share good stuff with good ideas and concepts, lots of great information and inspiration, both of which I need, thanks to offer such a helpful information here. data science course

    ReplyDelete
  31. This post is very simple to read and appreciate without leaving any details out. Great work!
    Please check ExcelR Data Science Courses in Pune

    ReplyDelete
  32. Nice information, valuable and excellent work, as share good stuff with good ideas and concepts, lots of great information and inspiration, both of which I need, thanks to offer such a helpful information here. data science course

    ReplyDelete

  33. I see some amazingly important and kept up to length of your strength searching for in your on the site

    https://360digitmg.com/course/certification-program-in-data-science/

    ReplyDelete
  34. Excellent Blog! I would like to thank for the efforts you have made in writing this post. I am hoping the same best work from you in the future as well. I wanted to thank you for this websites! Thanks for sharing. Great websites!
    Please check ExcelR Data Science Certification

    ReplyDelete
  35. Pretty good post. I just stumbled upon your blog and wanted to say that I have really enjoyed reading your blog posts. Any way I’ll be subscribing to your feed and I hope you post again soon.
    ExcelR Data Analytics Course

    ReplyDelete
  36. This post is very simple to read and appreciate without leaving any details out. Great work!
    Please check ExcelR Data Science Courses

    ReplyDelete