C# - How to Export Data from SQL Server Table or View to Text File in C Sharp

Scenario: Download Script

You are working as C# developer. You are asked to write a program that should read the data from a table, view or function and write the results to flat file. Each time you run the program it should get the data from table or view and create flat file with date-time.


The below program will create flat file on each execution with results extracted from a table or view.
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 FileNamePart = "Customer";//Datetime will be added to it
                string DestinationFolder = @"C:\Destination\";
                string TableName = "Dbo.Customer";
                string FileDelimiter = ","; //You can provide comma or pipe or whatever you like
                string FileExtension = ".txt"; //Provide the extension you like such as .txt or .csv


                //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;";

                //Read data from table or view to data table
                string query = "Select * From " + TableName;
                SqlCommand cmd = new SqlCommand(query, SQLConnection);
                SQLConnection.Open();
                DataTable d_table = new DataTable();
                d_table.Load(cmd.ExecuteReader());
                SQLConnection.Close();

                //Prepare the file path 
                string FileFullPath = DestinationFolder + "\\" + FileNamePart + "_" + datetime + FileExtension;

                StreamWriter sw = null;
                sw = new StreamWriter(FileFullPath, false);

                // Write the Header Row to File
                int ColumnCount = d_table.Columns.Count;
                for (int ic = 0; ic < ColumnCount; ic++)
                {
                    sw.Write(d_table.Columns[ic]);
                    if (ic < ColumnCount - 1)
                    {
                        sw.Write(FileDelimiter);
                    }
                }
                sw.Write(sw.NewLine);

                // Write All Rows to the File
                foreach (DataRow dr in d_table.Rows)
                {
                    for (int ir = 0; ir < ColumnCount; ir++)
                    {
                        if (!Convert.IsDBNull(dr[ir]))
                        {
                            sw.Write(dr[ir].ToString());
                        }
                        if (ir < ColumnCount - 1)
                        {
                            sw.Write(FileDelimiter);
                        }
                    }
                    sw.Write(sw.NewLine);

                }

                sw.Close();
             
            }
            catch (Exception exception)
            {
                // Create Log File for Errors
                using (StreamWriter sw = File.CreateText(LogFolder
                    + "\\" + "ErrorLog_" + datetime + ".log"))
                {
                    sw.WriteLine(exception.ToString());
                   
                }

            }

        }
    }
}

I executed the program couple of times and Customer file was created as shown below.
How to create Text file from SQL Server Table or View in C#


6 comments:

  1. This code perfectly working , Thank you for that. But I am facing issue with datetime field, can you please help for this.

    in Text files date format changed, how we can get same date format whatever we have in table.
    Eg: Table contains datetime as yyyy-mm-dd hh:mm:ss
    IN file populating as dd/mm/yyyy hh:mm:ss AM

    ReplyDelete
    Replies
    1. Hi Bro
      help fix error
      Additional information: Could not find a part of the path 'C:\Log\ErrorLog_20190911110140.log'.

      Delete
  2. Excellent script but if I want the script to generate the flat file without the header. What would I need to change?

    ReplyDelete
  3. I have a question ?
    I have table & 10000 rows are affected In this table And I want to export table data to text file. But one condition.
    when 1 to 2000 rows export in one text file, next file create with 2001 to 4000 row, again next file create 4001 to 6000 rows... Etc.
    Basically I want to export data in 5 text file each file store 2000 rows only.

    ReplyDelete
  4. Perfectly works fine, I want destination file field data type to match with sql columns, Will this be possible? , If yes can you suggest please.

    ReplyDelete