C# - How to Import or Load Text file to SQL Server Table by using C Sharp

Scenario: Download Script

You are working as C# developer, You need to write a program that should read the text file (CSV) file and load the data to SQL Server table. Once the file data is loaded to SQL server table, the file need to be moved to Archive Folder.

Step 1: 
Let's create table in database as per our file metadata. I have the file with id and CourseName as shown below.

Load or Import Text file to SQL Server Table by using C Sharp


Create Table statement.

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



Step 2: Create New project and choose Console Application.

Below code can be used to load the text file to SQL Server Table and move the file to Archive folder once loaded.

I have create variable so you can change the values as per your requirement. Let's say if you have the file with pipe ( | ) delimited. You can change filedelimiter value to "|";

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 file name
                string SourceFileName = "TechBrothersIT.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;";

                System.IO.StreamReader SourceFile =
                new System.IO.StreamReader(SourceFolder+SourceFileName);

                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
                File.Move(SourceFolder+SourceFileName, ArchiveFodler + SourceFileName);              

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

    }
}

Step 3: Save the script and Execute
Once you execute the program, it should read the file and load the data to table as shown below.
C# - Import Text file to SQL Server Table by using C Sharp




1 comment:

  1. hello sir,i am doing this with my database and with button.is that like this way.i have done..but its not working corectly

    ReplyDelete