.NET writing a delimited text file

Question

I am writing a framework for writing out collections into different formats for a project at my employer. One of the output formats is delimited text files (commonly known as the CSV -- even though CSVs aren't always delimited by a comma).

I am using the Microsoft.Jet.OLEDB.4.0 provider via OleDbConnection in ADO.net. For reading this files, its very quick. However, for writing, its extremely slow.

In one case, I have a file with 160 records, with each record having about 250 fields. It takes approximately 30 seconds to create this file, seemingly CPU bound.

I have done the following, which provided significant performance boosts, but I can't think of anything else:

1. Preparing the statement once
2. Using unnamed parameters

Any other suggestions to speed this up some?

Answer 1

I have written a small and simple set of classes at my employer to do just that (write and read CSV files or other flat files with a fixed field length). I have just used the StreamWriter & StreamReader classes, and it is quite fast actually.

Answer 2

How about "don't use OleDbConnection"... writing delimited files with TextWriter is pretty simple (escaping aside). For reading, CsvReader.

Answer 3

Try using the System.Configuration.CommaDelimitedStringCollection, like this code here to print a list of objects to a TextWriter.

public void CommaSeperatedWriteLine(TextWriter sw, params Object[] list)
{
    if (list.Length > 0)
    {
        System.Configuration.CommaDelimitedStringCollection commaStr = new System.Configuration.CommaDelimitedStringCollection();

        foreach (Object obj in list)
        {
            commaStr.Add(obj.ToString());
        }

        sw.WriteLine(commaStr.ToString());
    }
}

Answer 4

Take a look at this LINQ to CSV library from code project: http://www.codeproject.com/KB/linq/LINQtoCSV.aspx

I have not used this yet but I have had it in my reference file for about a year now.

"This library makes it easy to use CSV files with LINQ queries."