C# - How to Import data from multiple CSV or Text files to an Excel sheet in C#

Scenario : Download Script

You are working as C# developer, you need to write a program that should read all the csv or text files from input folder and create a new Excel file with date-time and load the data to single sheet from all those input files.

As we are going to load the data from all the CSV or text files to single Excel Sheet. The input files should have same columns.

I am inserting the CSV file names into sheet as well so I would know that from which CSV data came, you can remove if you don't need that. I have put the comments for removing.

How to load multiple CSV Files to Excel Sheet in C#


The below code can be used to load all the CSV or Text files to Excel Sheet. By using different values for variable you can control the functionality of program. Let's say if you have the files with pipe delimiter, you can change the FileDelimiter variable ="|".

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 the Source folder path where input files exist
                string SourceFolderPath = @"C:\Source\";
                //Provide the destination folder path where you like to create Excel file
                string DestinationFolderPath = @"C:\Destination\";
                // Provide the extension of input files such as .txt or .csv
                string FileExtension = ".txt";
                // Provide the file delimiter such as comma or pipe
                string FileDelimiter = ",";
                // Provide the name of Excel file you like to create
                string ExcelFileName = "TechBrothersITCourses";
                string CreateTableStatement = "";
                string ColumnList = "";
                Int16 FileCnt = 0;

                //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 [" + ExcelFileName + "] ([" + file.ReadLine().Replace(FileDelimiter, "] Text,[")) + "] Text,[CSVFileName] Text)";
                    file.Close();

                    //Construct ConnectionString for Excel, also adding date-time
                    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;

                    //Create Sheet if does not exists
                    if (FileCnt == 0)
                    {
                        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)
                        {
                            //I am also inserting CSV file name, if you don't like remove [CSVFileName]
                            ColumnList = "[" + line.Replace(FileDelimiter, "],[") + "],[CSVFileName]";

                        }
                        else
                        {
                            string query = "Insert into [" + ExcelFileName + "] (" +
                                ColumnList + ") VALUES('" +
                                line.Replace(FileDelimiter, "','") 
                                //if you don't want to insert file name , then remove the filename+FileExtension part
                                + "','" + filenameonly + FileExtension + "')";
                            
                            var command = query;
                            Excel_OLE_Cmd.CommandText = command;
                            Excel_OLE_Cmd.ExecuteNonQuery();

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

                }

            }

        }
    }
}


I executed above program and it created an Excel file with date-time and loaded all the input files from source folder to Excel sheet.

How to import or load multiple CSV files to an Excel File/ Excel Sheet in C#
 

4 comments:

  1. import records Thanks for a very interesting blog. What else may I get that kind of info written in such a perfect approach? I’ve a undertaking that I am simply now operating on, and I have been at the look out for such info.

    ReplyDelete
  2. You have performed a great job on this article. It’s very precise and highly qualitative. You have even managed to make it readable and easy to read. You have some real writing talent. Thank you so much. Used Shipping Containers For Sale

    ReplyDelete
  3. Even in case you aren't rich, you may come up with the money for to mutually very own a cruise deliver. But then comes the subsequent logical question: Why might you or all and sundry need to stay on a cruise deliver? Who might this be appropriate for?Shipping from china

    ReplyDelete
  4. Even in case you aren't rich, you may come up with the money for to mutually very own a cruise deliver. But then comes the subsequent logical question: Why might you or all and sundry need to stay on a cruise deliver? Who might this be appropriate for?

    ReplyDelete