C# - How to create Excel Sheet From CSV File and Keep appending data in Excel Sheet in C#

Scenario: Download Script

You are working as C# Developer, you need to create C# program that should read a CSV file from a folder and then create Excel File. The new file with same name will be dropped to folder every day. Next time when you run the program it should append the data to existing Excel. So we are covering two scenarios

  1. If Excel File does not exists, then create the Excel file and load the data
  2. If Excel file already exists then just load the data ( append the data to existing Excel sheet)
The below code can be used to achieve above requirement.
here is my sample file.
How to create and Load CVS Data to Excel file and if Excel exist, then append data  in C#

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
                    // Source folder path
                    string SourceFolder = @"C:\Source\";
                    // Provide Input file Name
                    string fileName = @"TechbrothersIT-2015-01-02.txt";
                    //Provide the path where you like to have Excel files
                    string ExcelOutputfilePath = @"C:\Destination\TechBrothersIT.xlsx";
                    // provide the file delimiter such as comma or pipe for input file.
                    string FileDelimiter = ",";
                    string CreateTableStatement = "";
                    string ColumnList = "";

                        //Read first line(Header) and prepare Create Statement for Excel Sheet
                        //In case Excel file does not exists and we need to create
                        System.IO.StreamReader file = new System.IO.StreamReader(SourceFolder+fileName);
                        CreateTableStatement = (" Create Table [" + fileName + "] ([" 
                             + file.ReadLine().Replace(FileDelimiter, "] Text,[")) + "] Text)";
                        file.Close();

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

                //If file exists , open it and load the data
                if (File.Exists(ExcelOutputfilePath))
                                 {
                    Excel_OLE_Con.ConnectionString = connstring;
                    Excel_OLE_Con.Open();
                    Excel_OLE_Cmd.Connection = Excel_OLE_Con;
                    
                }
                //If file does not exists, create it and load the data
                else
                    {
                        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(SourceFolder+fileName);
                        while ((line = SourceFile.ReadLine()) != null)
                        {
                            if (counter == 0)
                            {
                        //read header and build Column List for insert query
                                ColumnList = "[" + line.Replace(FileDelimiter, "],[") + "]";

                            }
                            else
                            {
                        //prepare inset query and execute to insert record in excel sheet
                                string query = "Insert into [" + fileName + "] (" + 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());

                }

            }

        }
    }
}

When I executed the program first time, the Excel file was not there. Excel file was created and 3 records from input files were loaded as shown below.
How to append data to Excel Sheet from CSV file in C#


I went back and remove 3 records and inserted the below records 
4,Windows 10,AppendTest

Executed the program again, and it appended the new records to existing Excel file as shown below.

How to Append records in Excel sheet by loading from CSV File in C#



No comments:

Post a Comment