C# - How to Import Multiple CSV Files to SQL Server from a Folder in C Sharp

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

Create table dbo.Customer(
Id INT,
Name VARCHAR(100),
Dob Date)



Sample files for this demo shown below
How to load multiple files to SQL Server table in C#

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#


3 comments:

  1. 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?

    ReplyDelete
  2. Hi, can you convert this code to import multiple excel files with the same sheet name to sql database?
    Best Regards

    ReplyDelete