C#- How to convert each excel sheet to CSV file from Excel Files 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. 
You need to create CSV file for each of the excel sheet in Destination Folder. The csv file name should be ExcelFileName_SheetName.csv.

Here are sample files with multiple Sheet that I am going to use for this post.

How to convert Excel Sheets to CVS File in C#


Customer_TechBrothersIT1.xlsx has two sheets in our case with columns Id, name and dob.
How to create CSV files from Excel Sheet in C#



Customer_TechBrothersIT2.xlsx has two sheets with columns id and name.
Create CSV file for each of the Excel Sheet from Excel Files in C#



I created Console Application in C# by using below code. The application will convert Excel Sheets to CSV files. The number of excel sheets can be less or more,program creates csv files dynamically.

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

                    //Loop through each sheet
                    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);
                            //cnn.Open();
                            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 + "_" + sheetname + ".csv", false);
                            int ColumnCount = dt.Columns.Count;

                            // 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());

                }

            }

        }
    }
}


Save the code and build. I executed the application with my sample excel files and it generate the CSV files for each of the sheet as shown below.
How to Convert Excel Sheets to CSV files for Single or Multiple Excel Files in C#

3 comments:

  1. Hi, i would like to write the data in above two excel files(including all the sheets data) onto a single CSV file, Please help me achieve this

    ReplyDelete
  2. ZetExcel is an excellent excel SDK for.Net framework. This is very useful for developing high-performance applications to create, edit, convert or print excel spreadsheet file formats without requiring Microsoft. I have used this Excel Spreadsheet Programming API from (https://zetexcel.com/). you can try.

    ReplyDelete
  3. The demand is consistently increasing in all countries across the globe, but North America is the center of this growth where the demand of face detection OpenCV C++ programmer, coder, or engineer will remain very high during the next few years. if anyone wants to learn programming then you can also checkout these best courses to learn open cv

    ReplyDelete