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#

12 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. 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
  5. how to extract the document present in excel cell and insert into db?

    ReplyDelete
  6. 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
  7. 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
  8. 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
  9. Microsoft Excel accompanies some fundamental usefulness to assist clients with exploring a spreadsheets recipes. excel spreadsheet consultant

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

    ReplyDelete
  11. Python is commonly used for developing websites and software, task automation, data analysis, and data visualization. Since it's relatively easy to learn, Python has been adopted by many non-programmers such as accountants and scientists, for a variety of everyday tasks, like organizing finances. if anyone wants to learn programming then you can also checkout these python questions answers for interview

    ReplyDelete