Reading different CSV with CSVHelper

Question

Basically what I'm trying to do is to read CSV file using CSVHelper. The trick is that there is more than one file(each time user picks different one) and each of them has different structure.

Only thing they share is first col named "Id", then every of them has different number of cols(from 2 additional up to 5) with different data types.

I tried to do this like that:

public class Country
{
    public int Id { get; set; }
    public IList<string> Attributes { get; set; }
}

public sealed class CountryMap : CsvClassMap<Country>
{
    private List<string> attributeColumns = 
        new List<string> { "Attribute1", "Attribute2", "Attribute3", "Attribute4", "Attribute5" };

    public override void CreateMap()
    {
        Map(m => m.Id).Name("Id").Index(0);
        Map(m => m.Attributes).ConvertUsing(row =>
            attributeColumns
                .Select(column => row.GetField<string>(column))
                .Where(value => String.IsNullOrWhiteSpace(value) == false)
            );
    }
}

And then with:

using (var reader = new CsvReader(new StreamReader(FilePath,encoding.UTF8)))
{
    reader.Configuration.RegisterClassMap<CountryMap>();
    while (reader.Read())
    {
        var card = reader.GetRecord<Country>();
    }
}

But only what I get is car = null.

I would be really thankful for any tips and answers.

Answer 1

A different approach would be to simply use the .Net OleDB provide to create a DataTable form the CSV file for you. Since the DataTable will already contain the necessary schema data it should simplify the mapping of the file columns to your internal classes.

    using System.Data;
    using System.Data.OleDb;
    using System.IO;

    namespace App.Data
    {
        public class CsvFileHelper
        {
            public static DataTable ReadAsDataTable(string fileFullName)
            {
                DataTable tableCSV;

                using (OleDbConnection connection = new OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0; Data Source = " + Path.GetDirectoryName(fileFullName) + "; Extended Properties = \"Text;HDR=YES;FMT=Delimited\""))
                {
                    connection.Open();

                    using (OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM " + Path.GetFileName(fileFullName), connection))
                    {
                        DataSet ds = new DataSet("CSVDataSet");
                        adapter.Fill(ds);

                        tableCSV = ds.Tables[0];
                    }
                }

                return tableCSV;
            }
        }
    }