C# - How to get Excel file's information and import to SQL Server Table in C#

Scenario : Download Script

You are working as C# or Dot Net developer, You are asked to create a program that should collection the information for all the excel files in a folder such as

  • Folder Path
  • Excel File Name
  • Sheet Name/s from Each Excel file
  • Number of Records per Sheet
  • Last Date Modified
  • Last Access Date
  • File Size in KB
and insert into SQL Server table.
The information can be used for multiple purposes such as
  • Estimate the storage and do capacity planning
  • To find data in some file, you will be able to get Sheet Names so you don't have to open it
  • Data Growth can be estimated as you are going to log Row Count from each sheet
  • You can tell what time file was accessed  and if somebody made any change to file.
Let's create Table in SQL Server Database by using below DDL Statement so we can store Excel file information.


Create table dbo.ExcelFileInformation(
id int identity(1,1),
FolderPath VARCHAR(255),
FileName VARCHAR(255),
SheetName VARCHAR(255),
Rows Int,
LastDateModified DateTime,
LastAccessTime Datetime,
FileSizeinKB Int)


I used below C# code to create Console Application that can be used to get excel file's information and insert into dbo.ExcelFileInformation.

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 in which you want to load excel file's information
                String TableName = @"ExcelFileInformation";
                //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 = "";

                    //Get Row Count for each sheet
                    foreach (DataRow drSheet in dtSheet.Rows)
                    {
                        if (drSheet["TABLE_NAME"].ToString().Contains("$"))
                        {
                            sheetname = drSheet["TABLE_NAME"].ToString();
                            OleDbCommand oconn = new OleDbCommand("select count(*) AS RowCnt from [" + sheetname + "]", cnn);
                            OleDbDataAdapter adp = new OleDbDataAdapter(oconn);
                            DataTable dt = new DataTable();
                            adp.Fill(dt);
                            string RC = "";
                            foreach (DataRow row in dt.Rows)
                            {
                                object[] array = row.ItemArray;
                                RC = array[0].ToString();
                            }

                            cnn.Close();
                            //If sheets start with Numbers, when we read it add extra '(single quote), this will take //care of that part
                            sheetname = sheetname.Replace("'", "");


                            //Prepare Insert Query with file info to insert into table
                            string InsertQuery = "";
                            InsertQuery = " Insert into [" + SchemaName + "].[" + TableName + "] (";
                            InsertQuery += "[FolderPath],[FileName],[SheetName],[Rows],[LastDateModified],[LastAccessTime],[FileSizeinKB])";
                            InsertQuery += "Values('" + FolderPath + "','" + file.Name + "','" +
                                           sheetname + "','" + RC + "','" + file.LastWriteTime
                                           + "','" + file.LastAccessTime + "',"
                                           + file.Length / 1024 + ")";

                            //Run Insert Query to Insert Data into SQL Server Table 
                            SqlCommand cmd = SQLConnection.CreateCommand();
                            cmd.CommandText = InsertQuery;
                            SQLConnection.Open();
                            cmd.ExecuteNonQuery();
                            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 with above C# code.It was able to read multiple excel files with multiple sheets and inserted the information into sql server table.
How to read excel file's information in C# and load to SQL Server Table 


1 comment:

  1. I like this site very much, Its a very nice position to read and receive info . excel spreadsheet company

    ReplyDelete