将大型数据表数据导出到c＃windows应用程序中的.csv文件 [英] export large datatable data to .csv file in c# windows applications

问题描述

亲爱的专家团队，



我需要将大型（超过50万卢比）数据表导出到.csv文件



我使用下面的代码，但需要很长时间。

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

推荐答案

嗨专家，



这是我的最终解决方案。



使用此代码我们可以将50万条记录导出到csv文件在小于2分钟。

使用sqldatareader而不是datatable

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>

感谢您的帮助，特别是对lina。

Thanks for all your help, especially to lina.

计算号码处理器。为每个创建一个线程，并在线程上划分行。将每个导出到自己的文件，最后组合文件。添加一个不错的进度显示，你就完成了。



FWIW; lac不是国际公认的金额。

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.

简单的方法是将其保存为XML并在EXCEL中打开。

MSExcel可以生成架构xml自动。

一旦在Excel中打开文件，我们就可以将它保存在csv中。

这可能是你的垃圾。

但它确实节省了时间并且编码简单。

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;

    }

谢谢，

Rohith。

Thanks,
Rohith.

这篇关于将大型数据表数据导出到c＃windows应用程序中的.csv文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！