Using FileHelpers to read a dynamic CSV to DataTable

Question

I find FileHelpers very convenient to handle "rich" CSV, with custom separators, quoted identifiers, empty items filtering, etc. But it mainly seem to be intended to load files with format known in advance to create a strongly typed object list, with properties decorated with attributes that are supposed match the headers.

My purpose is a little bit different : I would like to be able to load custom CSV files, with format and column count not known in advance, in a flexible format like DataTable, or some kind of string array. Side remark : my csv are coming from Stream, not from physical files.

I made some attempts, using methods like ReadStreamAsDT in FileHelperEngine (full implementation below) but I encounter some problems.

If headers are containing spaces or other characters that can not appear in C# properties, it throws exception "The string 'My field' not is a valid .NET identifier". My headers can contain all type of characters.
It requires to manually parse the header line separately

My implementation that exhibits problems above :

using (var streamReader = new StreamReader(stream, true))
{   
    var cb = new DelimitedClassBuilder("temp", ";")
    {
        IgnoreFirstLines = 0,
        IgnoreEmptyLines = true,
        Delimiter = ";"
    };
    var headerArray = streamReader.ReadLine().Split(';');
    foreach (var header in headerArray)
    {
        cb.AddField(header, typeof(string));
        cb.LastField.FieldQuoted = true;
        cb.LastField.QuoteChar = '"';
    }

    var engineType = cb.CreateRecordClass();
    var engine = new FileHelperEngine(engineType);
    var datatable = engine.ReadStreamAsDT(streamReader);
}

I would like to avoid to import another CSV package than FileHelpers, and as we also need to generate some CSV later using the same logic, I would like to avoid to write my own code for this.

The FileHelpers documentation didn't helped me much with DataTable usage.

I have also noticed one-liner methods like CommonEngine.CsvToDataTable() or CsvEngine.CsvToDataTable() but it's intended to work from physical files.

Is there a way to do this with FileHelpers to take profit of some of is advanced features (safe quote handling, trimming, CSV generation, etc) ?

You don't need to use any third-party libraries for reading CSV files into a `DataTable` object - you can use OLE-DB to read the CSV file directly: https://stackoverflow.com/questions/1050112/how-to-read-a-csv-file-into-a-net-datatable — Dai, May 29 '19 at 14:10
@Dai that *does* require an extra component, the Jet driver that has to match the application's bitness. It can't handle weird CSVs either, eg those with headers, or invalid rows. I'm not sure it can handle text fields with newlines, which *are* allowed in CSVs — Panagiotis Kanavos, May 29 '19 at 14:25
Thanks @Dai but using OLE/Jet is definitely not the way I want to go. I want a "managed" solution, with customizable behavior. — AFract, May 29 '19 at 14:31

score 2 · Accepted Answer · answered Dec 26 '19 at 09:08

At the time I wrote the question, I've just wrote my own code to load CSV file to datatable with all features I needed.

However, there's another library than FileHelpers that does a much better job in this field, "CsvHelper" : https://joshclose.github.io/CsvHelper/ : https://joshclose.github.io/CsvHelper/examples/data-table/

using (var reader = new StreamReader("path\\to\\file.csv"))
using (var csv = new CsvReader(reader))
{
    // Do any configuration to `CsvReader` before creating CsvDataReader.
    using (var dr = new CsvDataReader(csv))
    {        
        var dt = new DataTable();
        dt.Load(dr);
    }
}

(just set your separator if you need to customize it, etc)

Or you can do it manually if you prefer : https://github.com/JoshClose/CsvHelper/issues/1142#issuecomment-432910368 if you prefer.

Using FileHelpers to read a dynamic CSV to DataTable

1 Answers1

Linked