C# - How to create table for each of Excel Sheet Dynamically and Import Data in C#

Scenario: Download Script

You are working as C# or dot net Developer, you have tons of Excel files sitting in source folder. You got this requirement in which you need to write a program that should create table for each Excel sheet and load data.
The table name should be Excel File name_ Sheet name, I am going to use NVARCHAR(4000) as data type for all the fields, you can change as you like.

here are my excel files with multiple sheets.
Excel files with multiple sheets- How to create new table for each sheet and load data in C#

File Customer_TechBrothersIT1.xlsx has two sheets MySheet and Sheet1


Customer_TechBrothersIT2.xlsx has also two sheets. 4 Tables should be created for these two files and 2 sheets.


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 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)";


                //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)
                {
                    string filename = "";
                    fileFullPath = FolderPath + "\\" + file.Name;
                    filename = file.Name.Replace(".xlsx", "");

                    //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=1\"";
                    OleDbConnection cnn = new OleDbConnection(ConStr);


                    //Get Sheet Name
                    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
                            OleDbCommand oconn = new OleDbCommand("select * from [" + sheetname + "]", cnn);
                            OleDbDataAdapter adp = new OleDbDataAdapter(oconn);
                            DataTable dt = new DataTable();
                            adp.Fill(dt);

                            //remove "$" from sheet name
                            sheetname = sheetname.Replace("$", "");

                            // Generate Create Table Script by using Header Column,
                            //It will drop the table if Exists and Recreate                  
                            string tableDDL = "";
                            tableDDL += "IF EXISTS (SELECT * FROM sys.objects WHERE object_id = ";
                            tableDDL += "OBJECT_ID(N'[dbo].[" + filename + "_" + sheetname + "]') AND type in (N'U'))";
                            tableDDL += "Drop Table [dbo].[" + filename + "_" + sheetname + "]";
                            tableDDL += "Create table [" + filename + "_" + sheetname + "]";
                            tableDDL += "(";
                            for (int i = 0; i < dt.Columns.Count; i++)
                            {
                                if (i != dt.Columns.Count - 1)
                                    tableDDL += "[" + dt.Columns[i].ColumnName + "] " + "NVarchar(max)" + ",";
                                else
                                    tableDDL += "[" + dt.Columns[i].ColumnName + "] " + "NVarchar(max)";
                            }
                            tableDDL += ")";

                            SQLConnection.Open();
                            SqlCommand SQLCmd = new SqlCommand(tableDDL, SQLConnection);
                            SQLCmd.ExecuteNonQuery();

                            //Load the data from DataTable to SQL Server Table.
                            SqlBulkCopy blk = new SqlBulkCopy(SQLConnection);
                            blk.DestinationTableName = "[" + filename + "_" + sheetname + "]";
                            blk.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 which I created by using above C# code and it created the table for each sheet from multiple Excel files and loaded the data.



New Table created for each Excel sheet and data loaded to it from Excel in C#

Let's take a look on data loaded to these Tables from Excel sheets

Table created for each Excel sheet and data import successfully in C# dynamically

2nd Sheet data loaded after creating new table by using C# code

4 comments:

  1. You can try ZetExcel.com If you need Excel generation functionality for your .net application.

    ReplyDelete
  2. Thanks for an interesting blog. What else may I get that sort of info written in such a perfect approach? I have an undertaking that I am just now operating on, and I have been on the lookout for such info. how to take dianabol

    ReplyDelete
  3. This will only work with One Word Columns.

    ReplyDelete
  4. Love it! such flawless and well-structured code :) Was a great help for me in my project.

    ReplyDelete