CODEWITHSUNDEEP
.netcsvdatatable
paparazzo
paparazzo

Reputation: 45106

DataTable To CSV

This question is NOT about parsing a CSV.

Using the following code to create a CSV from a DataTable
But it is slow
100 rows by 14 columns is 4 seconds
Is there a faster way?

StringBuilder sb = new StringBuilder();
bool first = true;
int colCount = 0;
foreach (DataColumn dc in DT.Columns)
{
    if (first) first = false; else sb.Append(",");
    sb.Append("\"" + dc.ColumnName +  "\"");
    colCount++;
}
sb.AppendLine();
foreach (DataRow dr in DT.Rows)
{  
    first = true;
    for (int i = 0; i < colCount; i++)
    {
        if (first) first = false; else sb.Append(",");
        sb.Append("\"" + dr[i].ToString().Trim() + "\"");
    }
    sb.AppendLine();
}
return sb.ToString();

StringBuilder is not the problem here.
Load i from 0 to 1 million runs in 300 milliseconds

StringBuilder sb = new StringBuilder();
Stopwatch sw = new Stopwatch();
sw.Start();
for (int i = 0; i < 1000000; i++)
{
    sb.Append(i.ToString());
}
sw.Stop();
Debug.Write(sw.ElapsedMilliseconds.ToString());

Upvotes: 5

Views: 6057

Answers (2)

leon22
leon22

Reputation: 5669

Here is a complete version of an extension method that converts it to a CSV string instead of a file:

public static string ToCsv(this DataTable dt)
{
    using var writer = new StringWriter();
    using (var csv = new CsvWriter(writer, CultureInfo.InvariantCulture))
    {
        // write column names
        foreach( DataColumn column in dt.Columns )
        {
            csv.WriteField( column.ColumnName );
        }
        csv.NextRecord();
        
        foreach (DataRow row in dt.Rows)
        {
            for (var i = 0; i < dt.Columns.Count; i++)
            {
                csv.WriteField(row[i]);
            }
            csv.NextRecord();
        }
    }
    
    return writer.ToString();
}

Upvotes: 0

Josh Close
Josh Close

Reputation: 23403

There are faster ways of doing string concatenation and some of the other logic. Using the string builder to build up the whole thing may be part of the slowdown too. A library would have some of these things thought out already and would probably perform faster, as some have suggested.

Here is some code using CsvHelper (which I wrote).

using( var dt = new DataTable() )
{
    dt.Load( dataReader );
    foreach( DataColumn column in dt.Columns )
    {
        csv.WriteField( column.ColumnName );
    }
    csv.NextRecord();

    foreach( DataRow row in dt.Rows )
    {
        for( var i = 0; i < dt.Columns.Count; i++ )
        {
            csv.WriteField( row[i] );
        }
        csv.NextRecord();
    }
}

If you have the DataReader you don't even have to use the DataTable then, which should speed it up some too.

var hasHeaderBeenWritten = false;
while( dataReader.Read() )
{
    if( !hasHeaderBeenWritten )
    {
        for( var i = 0; i < dataReader.FieldCount; i++ )
        {
            csv.WriteField( dataReader.GetName( i ) );
        }
        csv.NextRecord();
        hasHeaderBeenWritten = true;
    }

    for( var i = 0; i < dataReader.FieldCount; i++ )
    {
        csv.WriteField( dataReader[i] );
    }
    csv.NextRecord();
}

Upvotes: 12

Related Questions