Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C#2.0 C#3.0 SQL
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.
 
string strFilePath= @"C:\myCSVfile.csv";
 
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 6-Sep-12 18:37pm
Edited 10-Sep-12 18:58pm
v11
Comments
Santhosh Kumar J at 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 at 7-Sep-12 0:30am
   
did you mean that i need to export data to .csv file using storedproc
biswarup88 at 7-Sep-12 0:51am
   
Good Question!
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

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
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.
  Permalink  
v3
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

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.
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

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.
 
//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.
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 5

HI D.Kishore
 
Thanks a lot u r rocking, U saved my time.
 
Thanks,
Dinesh.
  Permalink  

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

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 555
1 Kornfeld Eliyahu Peter 409
2 Maciej Los 359
3 DamithSL 196
4 OriginalGriff 188
0 OriginalGriff 6,353
1 DamithSL 4,854
2 Maciej Los 4,466
3 Kornfeld Eliyahu Peter 4,058
4 Sergey Alexandrovich Kryukov 3,897


Advertise | Privacy | Mobile
Web01 | 2.8.141220.1 | Last Updated 15 Nov 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100