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
- If Excel File does not exists, then create the Excel file and load the data
- 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