Click here to Skip to main content
14,838,597 members
Please Sign up or sign in to vote.
2.89/5 (9 votes)
See more:
Dear Experts Team,

I need to export large(more than 50 lakhs) datatable to a .csv file

I am using the below code, but its taking long time.

C#
string strFilePath= @"C:\myCSVfile.csv";


C#
public void CreateCSVFile(DataTable dtDataTablesList, string strFilePath)

    {
        // Create the CSV file to which grid data will be exported.

        StreamWriter sw = new StreamWriter(strFilePath, false);

        //First we will write the headers.

        int iColCount = dtDataTablesList.Columns.Count;

        for (int i = 0; i < iColCount; i++)
        {
            sw.Write(dtDataTablesList.Columns[i]);
            if (i < iColCount - 1)
            {
                sw.Write(",");
            }
        }
        sw.Write(sw.NewLine);

        // Now write all the rows.

        foreach (DataRow dr in dtDataTablesList.Rows)
        {
            for (int i = 0; i < iColCount; i++)
            {
                if (!Convert.IsDBNull(dr[i]))
                {
                    sw.Write(dr[i].ToString());
                }
                if (i < iColCount - 1)

                {
                    sw.Write(",");
                }
            }
            sw.Write(sw.NewLine);
        }
        sw.Close();
    }


Kindly let me know any another way of doing quickly.

Give me the solution please.

Regards
Posted
Updated 3-Aug-18 5:46am
v11
Comments
Santhosh Kumar Jayaraman 7-Sep-12 0:27am
   
If you are fetching datatable from database, then you can do it in Storedproc rather than bringing it to UI.
D-Kishore 7-Sep-12 0:30am
   
did you mean that i need to export data to .csv file using storedproc
sahabiswarup 7-Sep-12 0:51am
   
Good Question!

Hi Experts,

This is my final solution for this.

with this code we can export 50 lakhs records to csv file in lessthan 2 minutes.
use sqldatareader instead of datatable
C#
private void button1_Click(object sender, EventArgs e)
        {
 
            Stopwatch swra = new Stopwatch();
            swra.Start();
            string NewconnectionString = "myCoonectionString";
            StreamWriter CsvfileWriter = new StreamWriter(@"D:\testfile.csv");
            string sqlselectQuery = "select * from Mytable";
            SqlCommand sqlcmd = new SqlCommand();
 
            SqlConnection spContentConn = new SqlConnection(NewconnectionString);
            sqlcmd.Connection = spContentConn;
            sqlcmd.CommandTimeout = 0;
            sqlcmd.CommandType = CommandType.Text;
            sqlcmd.CommandText = sqlselectQuery;
            spContentConn.Open();
            using (spContentConn)
            {
                using (SqlDataReader sdr = sqlcmd.ExecuteReader())
                using (CsvfileWriter)
                {
                    //This Block of code for getting the Table Headers
                    DataTable Tablecolumns = new DataTable();
 
                    for (int i = 0; i < sdr.FieldCount; i++)
                    {
                        Tablecolumns.Columns.Add(sdr.GetName(i));
                    }
                    CsvfileWriter.WriteLine(string.Join(",", Tablecolumns.Columns.Cast<datacolumn>().Select(csvfile => csvfile.ColumnName)));
                    //This block of code for getting the Table Headers

                    while (sdr.Read())
                    //based on your Table columns you can increase and decrese columns
                        YourWriter.WriteLine(sdr[0].ToString() + "," + sdr[1].ToString() + "," + sdr[2].ToString() + "," + sdr[3].ToString() + "," + sdr[4].ToString() + "," + sdr[5].ToString() + "," + sdr[6].ToString() + "," + sdr[7].ToString() + "," + sdr[8].ToString() + "," + sdr[9].ToString() + "," + sdr[10].ToString() + "," + sdr[11].ToString() + ",");
                       
                }
            }
           swra.Stop();
Console.WriteLine(swra.ElapsedMilliseconds);
}</datacolumn>


Thanks for all your help, especially to lina.
   
v3
Comments
zahid_4b1 18-Feb-15 9:54am
   
this really helped thank you man
Count the number of processors. Create a thread for each, and divide the rows over the threads. Have each export to their own file, combine the files at the end. Add a nice progress-display and you're done.

FWIW; a "lac" is not an international recognized amount.
   
Easy way is to save it in XML and open it in EXCEL.
MSExcel can generate the schema for xml automatically.
Once a file is opened in Excel we can save it in csv.
It may be a crap for you.
But it's really time saving and easy coding.

C#
//code under export button_Click
        var ds = obj.Export();
        dset.WriteXml(@"D:\ExportExcelFiles\sample.xml");
        Label1.Text = @"File Downloaded to D:\ExportExcelFiles\sample.xml";


//Business logic code which is defined under some class..
SqlDataAdapter da = null;
    DataSet ds;
    public DataSet Export()
    {
        string qryString = "Select Category.CategoryName, Category.CategoryID, Item.ItemName, Item.Description, Item.TaxID, Item.HKU From Category Left Join Item ON Category.CategoryID=Item.CategoryID";
        con = new SqlConnection(conString);
        if (con.State == ConnectionState.Closed)
        { con.Open(); }
        cmd = new SqlCommand(qryString, con);
        ds= new DataSet();
        da = new SqlDataAdapter(cmd);
        da.Fill(ds);
        return ds;

    }


Thanks,
Rohith.
   
Hmmm? Not sure what is going on with the above code and why its posted that way but its creating a Datatable for no reason as it uses this messy joined concatenation of columns from the reader itself later. Also most of the syntax needs correcting.

Not good to paste defective code that doesn't work it distresses others and wastes their time.

A far simpler way is to simply create a Streamreader object and simply iterate through a filled Datatable itself.


using System;
using System.Linq;
using System.Data;
using System.IO;
using System.Data.SqlClient;
using System.Configuration;


namespace ExportSQLTableToExcel
{
    class Program
    {
        static void Main(string[] args)
        {
           
        string ExcelFileName = ConfigurationManager.AppSettings["ExcelFileName"];
        string FolderPath = ConfigurationManager.AppSettings["FolderPath"];
        string ConnectionString = ConfigurationManager.AppSettings["ConnectionString"];
            
            // create the reader 
            SqlConnection sqlConnection1 = new SqlConnection(ConnectionString);
            SqlCommand cmd = new SqlCommand();
            SqlDataReader reader;

            cmd.CommandText = " select *  FROM [dbo].[YourTable]";
            cmd.CommandType = CommandType.Text;
            cmd.Connection = sqlConnection1;

            sqlConnection1.Open();
            reader = cmd.ExecuteReader();

            //Create the datatable

            using (DataTable dt = new DataTable())
            {
                dt.Load(reader);
                Console.WriteLine(dt.Rows.Count);  //check its filled 

                //Create the Streamwriter

                StreamWriter CsvfileWriter = new StreamWriter(@"C:\testfile.csv");

                using (CsvfileWriter)
                {

                    //Use sting join methods to attach and write the columns 
                    CsvfileWriter.WriteLine(string.Join(",", 
                    dt.Columns.Cast<DataColumn>().Select(csvfile => 
                    csvfile.ColumnName)));

                    foreach (DataRow row in dt.Rows)
                    {
                        //Use sting join methods to attach and write and iterate 
                        //through the rows of the datatable
                        CsvfileWriter.WriteLine(string.Join(",", row.ItemArray));
                    }
                }
            }
           
            sqlConnection1.Close();

        }

    }
    
}
   
Comments
Richard Deeming 3-Aug-18 13:02pm
   
SIX YEARS too late, and the question already has an accepted answer.

Stick to answering recent questions.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900