C# - Importing Files to SQL Server Table with Dynamic Columns by using C Sharp

Scenario : Download Script

You are working as C# developer, you get multiple text of csv files from different clients those you need to load to dbo.Customer table. But there is little problem. Each client will not send exact number of columns what we have for you table.

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



Think about that client A sends always file with id, name and dob columns.
Client B can send file with Id and Name. Also Client B can send some files with three columns id,name and dob.
Client C can also send the file with only column Name and Dob or any other combination.

One thing to noticed here, the files can have same columns or less but will never have more columns than SQL Server table.

The below script can be used to load multiple files to SQL server table with same or less columns what we have in our SQL Server Table. By changing values for variables such as file delimiter you can use this to load pipe delimiter files. By changing value for fileExtension variable to .csv , you can load csv files.

sample files

How to Load Flat files to SQL Server Table with Dynamic Columns in File by using C Sharp




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)
        {

            string datetime = DateTime.Now.ToString("yyyyMMddHHmmss");
            string LogFolder = @"C:\Log\";
            try
            {

                //Declare Variables and provide values
                string SourceFolderPath = @"C:\Source\";
                string FileExtension = ".txt";
                string FileDelimiter = ",";
                string TableName = "dbo.Customer";
                string ArchiveFolder = @"C:\Archive\";
                

                //Get files from folder
          string[] fileEntries = Directory.GetFiles(SourceFolderPath, "*" + FileExtension);
                foreach (string fileName in fileEntries)
                {

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

                    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());
                   
                }

            }

        }
    }
}

I execute above script , it read the reads from multiple files, Loaded them to SQL Server
table and then moved the files to archive folder successfully.



Here are records loaded by package in dbo.Customer table from my sample files.

1 comment:

  1. hai i want to load text file that delimiter and quotation mark dynamic.Delimiter's(",","-","|")

    ReplyDelete