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# |
what if there is a text qualifier
ReplyDelete