C# - How to Import multiple text files to SQL Server Table by using C Sharp

Scenario: Download Script

You are working as C# developer and you need to write a program that should be able to read text files from a folder and load to SQL Server Table. 

How to load multiple text files to SQL Server table by using C#

The below code can be used to load multiple flat files or text files to load to SQL Server table. You can change the variable values to handle different situations. Let's say if you need to load .CSV files, you can change the FileExtension value =".CSV".

If you are interested to load pipe delimited files instead of comma, you can change value for filedelimiter="|" and rest of the program should work fine to load pipe delimited files.


Create Table statement.

CREATE TABLE dbo.TechBrothersITCourse (
    id INT
    ,CourseName VARCHAR(100)
    )

I created Console Application by using below C# code.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.IO;
using System.Data.SqlClient;

namespace TechBrothersIT.com_CSharp_Tutorial
{
    class Program
    {
        static void Main(string[] args)
        {

            try
            {

                //Declare Variable and set values
                //provide input folder
                string SourceFolder = @"C:\Source\";
                //provide the extension of files you would like to read
                string FileExtension = ".txt";
                //provide the table name in which you would like to load data
                string TableName = "dbo.TechBrothersITCourse";
                //provide the file delimiter such as comma,pipe
                string filedelimiter = ",";
                //provide the Archive folder where you would like to move file
                string ArchiveFodler = @"C:\Archive\";

                //Create Connection to SQL Server
                SqlConnection SQLConnection = new SqlConnection();
                SQLConnection.ConnectionString = "Data Source = (local); Initial Catalog =TechBrothersIT; "
                   + "Integrated Security=true;";

                // get files from folder according to file extension 
                string[] fileEntries = Directory.GetFiles(SourceFolder, "*" + FileExtension);
                foreach (string fileName in fileEntries)
                {
                    System.IO.StreamReader SourceFile =
                new System.IO.StreamReader(fileName);

                    string line = "";
                    Int32 counter = 0;

                    SQLConnection.Open();
                    while ((line = SourceFile.ReadLine()) != null)
                    {
                        //skip the header row
                        if (counter > 0)
                        {
                            //prepare insert query
                            string query = "Insert into " + TableName +
                                   " Values ('" + line.Replace(filedelimiter, "','") + "')";

                            //execute sqlcommand to insert record
                            SqlCommand myCommand = new SqlCommand(query, SQLConnection);
                            myCommand.ExecuteNonQuery();
                        }
                        counter++;
                    }

                    SourceFile.Close();
                    SQLConnection.Close();

                    //Move the file to Archive folder, fileName variable contains complete path for input file.
                    //Path.GetFileName() to get only the file name from full path
                    File.Move(fileName, ArchiveFodler + Path.GetFileName(fileName));

                }
            }
            catch (IOException Exception)
            {
                Console.Write(Exception);
            }
            }

    }
}


Save the script and run the program.It should read the files from source folder, load to sql server table and move to archive folder. 
C# - How to Import multiple Text files to SQL Server Table by using C Sharp



2 comments:

  1. When I need to convert documents from one format to another, I use an online converter https://anyconv.com/ https://anyconv.com/

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

    ReplyDelete