C# - How to Create Table per Excel File and Import all Sheet's Data Dynamically in C#

Scenario: Download Script

You are working as C# or dot Net Developer, you need to write a program that should create a Table if not exists per Excel File and load all sheet's data to it. As we are going to load all the sheets from single Excel file to a table, sheets should have same columns on each Excel file.

Here are our sample files.
How to create Table for each Excel file and load data from all sheets in C#

Customer_TechBrothersIT1.xlsx has two sheets in our case with columns Id, name and dob.
Create Table Dynamically for each Excel File in C# and Import All Sheets to Table


Customer_TechBrothersIT2.xlsx has two sheets with columns id and name.
How to load Multiple Sheets to SQL Server Table from a Excel File in C#

I have created Console Application by using below C# Code, it will create new table if does not exists for a excel file and load all the excel sheet's data to it.

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


                            // Create Table if does not exists                
                            string tableDDL = "";
                            tableDDL += "IF Not EXISTS (SELECT * FROM sys.objects WHERE object_id = ";
                            tableDDL += "OBJECT_ID(N'[dbo].[" + filename + "]') AND type in (N'U'))";
                            tableDDL += "Create table [" + filename + "]";
                            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 myCommand = new SqlCommand(tableDDL, SQLConnection);
                            myCommand.ExecuteNonQuery();

                            //Load the data from DataTable to SQL Server Table.
                            SqlBulkCopy blk = new SqlBulkCopy(SQLConnection);
                            blk.DestinationTableName = "[" + filename + "]";
                            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 above C# code and it created tables from sample files as shown below and load the data from multiple sheet's to them.
Create SQL Table Dynamically from Excel file and load data in C#

Lets run query on created tables and take a look if data is loaded from Excel files correctly.
Table created form Customer_TechBrothersIT.xlsx Excel Sheet and Data Loaded from Sheets.


Data loaded to Table from Customer_TechBrothersIT2 Excel File in C# and data loaded successfully.

No comments:

Post a Comment