C# - How to Read Excel Sheet Data after Skipping Rows and Load to SQL Server Table in C#

Scenario : Download Script

You are working as C# developer or Dot Net Developer. You need to write a program that should be able to skip some rows from an Excel Sheet and load the remaining records to SQL Server Table. The program should be able to handle to load multiple Excel files.


Here is my sample excel sheet with some data.
How to Skip Rows in Excel Sheet and Export actual records to SQL Server Table in C#

As you can see that I have first 5 rows as company name. In your case you might have logo or any information. In my case, I need to start reading from A7 and load the data to dbo.Customer table.

We also validate the columns against our Table columns in database, let's say if there is a column in excel and it does not exists in database table, the program is going to ignore that column and insert the data for the columns which are present in excel and database table.

Let's create customer table first and then write C# code to skip the rows and load the data to table.

CREATE TABLE [dbo].[Customer](
    [Id] [int] NULL,
    [Name] [varchar](100) NULL,
    [Dob] [date] NULL
)


I created Console Application by using below C# code.
 

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 path where Excel files exists
                string FolderPath = @"C:\Source\";
                //Provide the table name in which you want to load the data
                string TableName = "Customer";
                //Provide the schema of table 
                string SchemaName = "dbo";
                //Provide the starting column for read actul records
                string StartingColumn = "A";
                //Provide the end column till which you want to read
                string EndingColumn = "C";
                //Provide the row number from which you like to start reading
                string StartReadingFromRow = "7";
                //Provide the Database Name in which table or view exists
                string DatabaseName = "TechbrothersIT";
                //Provide the SQL Server Name 
                string SQLServerName = "(local)";
                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 Name, it can handle multiple sheets
                    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 + StartingColumn + StartReadingFromRow + ":" + EndingColumn + "]", 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.
                            //If columns does not exists in table, it will ignore and load only matching columns data
                            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";


                            //Create Connection to SQL Server Database from which you like to export tables to Excel
                            SqlConnection SQLConnection = new SqlConnection();
                            SQLConnection.ConnectionString = "Data Source = " 
                                + SQLServerName + "; Initial Catalog =" 
                                + DatabaseName + "; " 
                                + "Integrated Security=true;";

                            //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 + StartingColumn 
                                + StartReadingFromRow + ":" + EndingColumn + "]", 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 above program and it was able to skip the rows in excel sheet and loaded the actual data according as by variable values I have provided at start of program.

How to skip rows in Excel and import actual data rows to SQL Server table in C#

15 comments:

  1. HOW TO DO THIS IF I HAVE MULTIPLE SHEETS HAVING DIFFERENT STARTING FIELD IN EXCEL

    ReplyDelete
  2. Both these plants originally belong to America. They were brought to Egypt only in the 19th century by Mohammed Ali Pasha, the reigning king of Egypt. best sheets

    ReplyDelete
  3. What a thrilling post, you have pointed out some excellent points, I as well believe this is a superb website. I have planned to visit it again and again. 2ms office 2016 product key

    ReplyDelete
  4. Hi! This is my first comment here so I just wanted to give a quick shout out and say I genuinely enjoy reading your blog posts. Can you recommend any other Beauty Guest Post blogs that go over the same topics? Thanks a ton!

    ReplyDelete
  5. 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. Big thanks for the useful info. budget tracking template

    ReplyDelete
  6. how to extract the document present in excel cell and insert into db?

    ReplyDelete
  7. Thank you because you have been willing to share information with us. we will always appreciate all you have done here because I know you are very concerned with our. email database

    ReplyDelete
  8. Wow, this is fascinating reading. I am glad I found this and got to read it. Great job on this content. I liked it a lot. Thanks for the great and unique info.this will help

    ReplyDelete
  9. I was just looking for this info for some time. After six hours of continuous Googleing, finally I got it in your website. I wonder what’s the Google’s issue that does not rank this type of informative web sites closer to the top. Usually the top websites are full of garbage. Help with Excel

    ReplyDelete
  10. Microsoft Excel accompanies some fundamental usefulness to assist clients with exploring a spreadsheets recipes. excel spreadsheet consultant

    ReplyDelete
  11. i am always looking for some free stuffs over the internet. there are also some companies which gives free samples. CSGO 1000 Hours Accounts

    ReplyDelete
  12. This is actually the kind of information I have been trying to find. Thank you for writing this information. valorant account free

    ReplyDelete
  13. PUBG employs a physics engine that can make your car react in a realistic way to bumpy, unstable terrain, so head's up: you will end up going airborne. If you're not great with bumps, stay between the lines and stick to the road for a (generally) smoother ride. buy rust account

    ReplyDelete
  14. GMT News24
    started its journey in 10th April, 2021. It is fully powered & sponsored by PAP International LTD. Our slogan is “সর্বত্র সমাচার প্রায়শই”. We are here to give users all the latest news as fast and as reliably as possible. We want to prosper through our work. We want to win every user’s heart through our true & authentic news as well as with our unique design, layout, view & ease of access. We want to cover as much territory as possible with both our printed and digital offerings.

    Our Ethics:
    We are getting popular day by day because we have some unique values & principles we follow:

    We at GMT News24 practice transparent, objective & professional journalism.
    We are here to represent people & stand by their side.
    We are full of positivity. Everyday we speak of positive change.
    We welcome innovation. Innovation is progress.
    We believe in group work. Unity is power.
    Our Exclusivity:
    We are here to serve the people. We respect the democracy, the community and the spirit of liberation war. We are not bias toward any specific group or party. We are eager to serve you unbiased, truthful & authentic news. We are equally respectful to all the races & all the people as we believe in “We all are human” motto.

    We make sure that all our journalists follow the journalism rules & ethics strictly. Our expert & well-trained journalists are scattered all around to give you all the latest news as fast as possible. They make sure all news get covered. They are also trained to maintain moderate and soothing language in our news.

    We are inspired by our readers. Our readers keep us going and give us the push to improve. GMT News24 will always be accountable to its user/readers.

    GMT News24 Online News Portal:
    We started our journey as an online news portal. Now is the era of digital platforms. It is very convenient to get access to news from anywhere anytime. We are here to give you that convenience. Any news that is important, rest assure. We will be sure to cover it. We are also open to suggestions. If you think we are lacking some features or not covering the type of news; feel free to contact us. We will take your suggestion into count.

    GMT News24 Printed:
    This is a work in progress. Stay with us. We will be launching in this platform soon……

    GMT News24 IP TV:
    This is a work in progress. Stay with us. We will be launching in this platform soon……


    GMT News24 English

    CSGO medal Accounts

    ReplyDelete