Scenario : Download Script
You are working as C# developer, You get tons of files in a folder. All the files has the same definition.You need to load all the files to SQL server Table.
Let's say here is SQL Server Table in which we need to load the files
The below program will load all the files from a folder to SQL Server table.
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using System.IO; using System.Data.SqlClient; using System.Data; namespace TechBrothersIT.com_CSharp_Tutorial { class Program { static void Main(string[] args) { string datetime = DateTime.Now.ToString("yyyyMMddHHmmss"); string LogFolder = @"C:\Log\"; try { //Declare Variables and provide values string SourceFolderPath = @"C:\Source\"; //Provide the Source Folder where files are present string FileExtension = ".txt"; //Provide the extension of files you need to load, can be .txt or .csv string FileDelimiter = ","; // provide the file delimiter such as comma or pipe string ArchiveFolder = @"C:\Archive\"; //Provide the archive folder path where files will be moved string TableName = "dbo.Customer"; //Provide the table name in which you would like to load the files. //Create Connection to SQL Server in which you like to load files SqlConnection SQLConnection = new SqlConnection(); SQLConnection.ConnectionString = "Data Source = (local); Initial Catalog =TechBrothersIT; " + "Integrated Security=true;"; //Reading file names one by one string[] fileEntries = Directory.GetFiles(SourceFolderPath, "*" + FileExtension); foreach (string fileName in fileEntries) { //Writing Data of File Into Table int counter = 0; string line; string ColumnList = ""; System.IO.StreamReader SourceFile = new System.IO.StreamReader(fileName); SQLConnection.Open(); while ((line = SourceFile.ReadLine()) != null) { if (counter == 0) { //By using Header Row, Build Column List ColumnList = "[" + line.Replace(FileDelimiter, "],[") + "]"; } else { //Build and Execute Insert Statement to insert record string query = "Insert into " + TableName + " (" + ColumnList + ") "; query += "VALUES('" + line.Replace(FileDelimiter, "','") + "')"; SqlCommand SQLCmd = new SqlCommand(query, SQLConnection); SQLCmd.ExecuteNonQuery(); } counter++; } SourceFile.Close(); SQLConnection.Close(); //move the file to archive folder after adding datetime to it File.Move(fileName, ArchiveFolder + "\\" + (fileName.Replace(SourceFolderPath, "")).Replace(FileExtension, "") + "_" + datetime + FileExtension); } } catch (Exception exception) { // Create Log File for Errors using (StreamWriter sw = File.CreateText(LogFolder + "\\" + "ErrorLog_" + datetime + ".log")) { sw.WriteLine(exception.ToString()); } } } } }
Go ahead and execute your script. It should load all the files from a folder to SQL Server table that name you have provided in variable.
How to load all the files from a folder to SQL Server Table in C# |
When a numeric field doesn't have a value for the numeric column, you get a conversion error from varchar to numeric. Can this be fixed?
ReplyDeletehi gvila have you solve this issue?
DeleteHi, can you convert this code to import multiple excel files with the same sheet name to sql database?
ReplyDeleteBest Regards