C# - How to Import Multiple CSV files into Excel file to separate Excel Sheets in C#

Scenario: Download Script

You are working as C# developer, You need to write a program in C# that should read all CSV files from a folder and create a new Excel file with Date time and load CSV files to it. Each CSV file should be loaded to new excel sheet.


The below program can be used to load all the csv or text files to Excel file , each file to separate Excel sheet from a folder.

Here are couple of sample CSV files those I am using for testing the program.
How to Import multiple CSV files to Excel file dynamically in C#


C# Script to load multiple CSV files to Excel File
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.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
            {
                //Declare Variables
                //Provide the source folder path
                string SourceFolderPath = @"C:\Source\";
                //Provide the Destination folder path
                string DestinationFolderPath = @"C:\Destination\";
                //Provide the extension of input files such as .csv or .txt
                string FileExtension = ".csv";
                //Provide the file delimiter such as comma or pipe
                string FileDelimiter = ",";
                //Provide the Excel file name that you want to create
                string ExcelFileName = "TechBrothersIT";
                string CreateTableStatement = "";
                string ColumnList = "";

                //Reading file names one by one
                string SourceDirectory = SourceFolderPath;
                string[] fileEntries = Directory.GetFiles(SourceDirectory, "*" + FileExtension);
                foreach (string fileName in fileEntries)
                {


                    //Read first line(Header) and prepare Create Statement for Excel Sheet
                    System.IO.StreamReader file = new System.IO.StreamReader(fileName);
                    string filenameonly = (((fileName.Replace(SourceDirectory, "")).Replace(FileExtension, "")).Replace("\\", ""));
                    CreateTableStatement = (" Create Table [" + filenameonly + "] ([" 
                        + file.ReadLine().Replace(FileDelimiter, "] Text,[")) 
                        + "] Text)";
                    file.Close();

                    //Construct ConnectionString for Excel
                    string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + DestinationFolderPath + "\\" + ExcelFileName + "_" + datetime
                        + ";" + "Extended Properties=\"Excel 12.0 Xml;HDR=YES;\"";
                    OleDbConnection Excel_OLE_Con = new OleDbConnection();
                    OleDbCommand Excel_OLE_Cmd = new OleDbCommand();

                    Excel_OLE_Con.ConnectionString = connstring;
                    Excel_OLE_Con.Open();
                    Excel_OLE_Cmd.Connection = Excel_OLE_Con;

                    //Use OLE DB Connection and Create Excel Sheet
                    Excel_OLE_Cmd.CommandText = CreateTableStatement;
                    Excel_OLE_Cmd.ExecuteNonQuery();

                    //Writing Data of File to Excel Sheet in Excel File
                    int counter = 0;
                    string line;

                    System.IO.StreamReader SourceFile =
                    new System.IO.StreamReader(fileName);
                    while ((line = SourceFile.ReadLine()) != null)
                    {
                        if (counter == 0)
                        {
                            ColumnList = "[" + line.Replace(FileDelimiter, "],[") + "]";

                        }
                        else
                        {
                            string query = "Insert into [" + filenameonly + "] (" 
                                + ColumnList + ") VALUES('" 
                                + line.Replace(FileDelimiter, "','") 
                                + "')";
                            var command = query;
                            Excel_OLE_Cmd.CommandText = command;
                            Excel_OLE_Cmd.ExecuteNonQuery();

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

                }

            }

        }
    }
}


I executed the above program with my sample csv files and it was able to create Excel file with multiple sheets and loaded the data from input files.

How to Import each CVS File to separate sheet in Excel file by using C#
 

1 comment: