C# - How to Convert CSV or Text files to Excel file in C#

You are working as C# developer, you need to write a program that should read text or csv files from source folder and convert them to an Excel file/s in destination folder. 

Each text or csv file will be converted to separate excel file. The sheet name will be text or file name. Each input file can have different columns as we are going to create separate excel for each text or csv file.

The below script can be used to convert multiple files from a folder to excel files. You can control the functionality with variable values. Let's say if you want to read only .csv files and convert them, Change the variable value =.csv.

Here is C# code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
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 source folder path where csv or text files exists
                string SourceFolderPath = @"C:\Source\";
                //Provide the path where you like to have Excel files
                string DestinationFolderPath = @"C:\Destination\";
                //Provide the extension of input text files such as .txt or .csv 
                string FileExtension = ".txt";
                // provide the file delimiter such as comma or pipe.
                string FileDelimiter = ","; 
                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 + "\\" + filenameonly
                        + ";" + "Extended Properties=\"Excel 12.0 Xml;HDR=YES;\"";
                    OleDbConnection Excel_OLE_Con = new OleDbConnection();
                    OleDbCommand Excel_OLE_Cmd = new OleDbCommand();

                    //drop Excel file if exists
                    File.Delete(DestinationFolderPath + "\\" + filenameonly + ".xlsx");
                    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());

                }

            }

        }
    }
}