Reputation: 45096
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: 6046
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
Reputation: 23393
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