C# - How to create CSV file per Excel File and Load All Sheets from Excel File to it in C#

Scenario : Download Script

You are working as C# or Dot Net Developer. You got this requirement where you get single or multiple Excel files in Source or Input folder. Each File Can have single Sheet or Multiple Sheets.  If there are multiple Sheets they will have same metadata( Same Columns).

You need to create csv file per Excel file and load all sheets from it to newly created csv fil by using C#.

Here are sample files with multiple Sheet that I am going to use for this post. The Sheets on each Excel file has same number or columns.

How to create single CSV file per Excel File in C# 

Customer_TechBrothersIT1.xlsx has two sheets in our case with columns Id, name and dob.

Create CSV files dynamically from Excel Files and Sheets in C#

Customer_TechBrothersIT2.xlsx has two sheets with columns id and name.

How to Create Single CSV File per Excel Sheet and load data from all sheets in C#

I have used the below code to create Console Application. 

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;

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 SourceFolderPath = @"C:\Source\";
                //Provide destination folder where you like to create CSV files from Excel Sheets
                string DestinationFolderPath = @"C:\Destination\";
                //Provide the file delimiter such as comma or pipe
                string FileDelimited = @",";


                var directory = new DirectoryInfo(SourceFolderPath);
                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 = SourceFolderPath + "\\" + 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=0\"";
                    OleDbConnection cnn = new OleDbConnection(ConStr);


                    //Get Sheet Names
                    cnn.Open();
                    DataTable dtSheet = cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    string sheetname;
                    sheetname = "";
                    Int16 sheetcnt = 0;

                    //loop through each sheet
                    foreach (DataRow drSheet in dtSheet.Rows)
                    {
                        sheetcnt += 1;
                        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("$", "");

                            //Create CSV File and load data to it from Sheet
                            StreamWriter sw = new StreamWriter(DestinationFolderPath + "\\" + filename + ".csv", true);
                            int ColumnCount = dt.Columns.Count;
                            //we are checking SheetCnt=1, so put header in csv for only one time for first sheet
                            if (sheetcnt == 1)
                            {
                                // Write the Header Row to File
                                for (int i = 0; i < ColumnCount; i++)
                                {
                                    sw.Write(dt.Columns[i]);
                                    if (i < ColumnCount - 1)
                                    {
                                        sw.Write(FileDelimited);
                                    }
                                }
                                sw.Write(sw.NewLine);
                            }


                            // Write All Rows to the File
                            foreach (DataRow dr in dt.Rows)
                            {
                                for (int i = 0; i < ColumnCount; i++)
                                {
                                    if (!Convert.IsDBNull(dr[i]))
                                    {
                                        sw.Write(dr[i].ToString());
                                    }
                                    if (i < ColumnCount - 1)
                                    {
                                        sw.Write(FileDelimited);
                                    }
                                }
                                sw.Write(sw.NewLine);
                            }
                            sw.Close();
                        }
                    }
                }
            }

            catch (Exception exception)
            {
                // Create Log File for Errors
                using (StreamWriter sw = File.CreateText(LogFolder
                    + "\\" + "ErrorLog_" + datetime + ".log"))
                {
                    sw.WriteLine(exception.ToString());

                }

            }

        }
    }
}

As I have two sample files with two sheets per file,If I will run the C# Console Application, It should create two csv files and load data from 4 sheets.

How to create csv file per Excel File and load data to it in C#