locked
How to write a large data (5lakhs) in CSV from dataset/datatable using ASP.NET FW 4.5 in optimised way RRS feed

  • Question

  • User593385765 posted
    Q1)how to save large data in CSV format from datatable/dataset using ASP.net 4.5 FW.
        
    i am using console application where i want to save or write large data from data table into csv in less time, around 5lakhs data should be save to csv.
    any kind of help will be appreciated.
     
    Q2)We will get the time out exception while doing the above process - Error might be raised as below.
    Timeout expired The timeout period elapsed prior to completion of the operation or the server is not responding
     
    Q3) While generating the CSV with specific column format, how to rearrange from datatable / dataset in ASP.NET 4.5 FW
    Your help really appreciated.....:)
    Tuesday, July 23, 2019 10:43 AM

All replies

  • User475983607 posted

    Use standard export features found in your DB server.  If you are using SQL server then you can do this in SSMS by writing a query and selecting export to file. 

    https://www.sqlservercentral.com/blogs/export-a-ssms-query-result-set-to-csv

    Tuesday, July 23, 2019 11:16 AM
  • User-821857111 posted

    Use BULK INSERT: https://docs.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql?view=sql-server-2017

    BULK INSERT YourTable FROM 'path-to-csv'

    Tuesday, July 23, 2019 12:35 PM
  • User2053451246 posted

    This is a simple tool that can be installed using NuGet.  Very easy to use and very fast.

    https://joshclose.github.io/CsvHelper/

    It can take a datatable, list, etc. and export to the format you want.

    Tuesday, July 23, 2019 2:53 PM
  • User665608656 posted

    Hi Veesam,

    According to your description, I'm not sure how complex the data you export is, but I simulated 100,000 pieces of data and successfully exported them to the CSV file in a very short time using the following methods:

    One method, you could refer to : c# datatable to csv

      StringBuilder sb = new StringBuilder();
                IEnumerable<string> columnNames = dt.Columns.Cast<DataColumn>().
                                                  Select(column => column.ColumnName);
                sb.AppendLine(string.Join(",", columnNames));
    
                foreach (DataRow row in dt.Rows)
                {
                    IEnumerable<string> fields = row.ItemArray.Select(field => field.ToString());
                    sb.AppendLine(string.Join(",", fields));
                }
    
                File.WriteAllText(@"C:\Users\yongqy\Desktop\test.csv", sb.ToString());

    Another method based on @ryanbesko advice, you can nuget CSVHelper, then use following code:

               using (var textWriter = File.CreateText(@"C:\Users\yongqy\Desktop\test1.csv"))
                using (var csv = new CsvWriter(textWriter))
                {
                    // Write columns
                    foreach (DataColumn column in dt.Columns)
                    {
                        csv.WriteField(column.ColumnName);
                    }
                    csv.NextRecord();
    
                    // Write row values
                    foreach (DataRow row in dt.Rows)
                    {
                        for (var i = 0; i < dt.Columns.Count; i++)
                        {
                            csv.WriteField(row[i]);
                        }
                        csv.NextRecord();
                    }
                }

    You could refer to this link : Export datatable to csv using csvhelper

    Best Regards,

    YongQing.

    Thursday, July 25, 2019 8:27 AM