Click here to Skip to main content
12,406,342 members (70,305 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: C#2.0 C#3.0 C# 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 17:37pm
D-Kishore4.2K
Updated 10-Sep-12 17:58pm
v11
Comments
Santhosh Kumar J 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
biswarup88 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
Comments
zahid_4b1 18-Feb-15 9:54am
   
this really helped thank you man
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
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160730.1 | Last Updated 15 Nov 2013
Copyright © CodeProject, 1999-2016
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