How to map 2d array in csv file to poco collection or dictionary of dictionary using FileHelpers?

Question

I have the following data structure in my csv file:

I want to either parse it into the following data structure :

[DelimitedRecord(","), IgnoreFirst(1)]
public class FxConversionRate
{
    [FieldConverter(ConverterKind.Date, "d/M/yyyy")]
    public DateTime Date;
    public string Currency;
    public double Rate;
}

Or else want to parse it into a Dictionary<string, Dictionary<DateTime, double>>

How can I accomplish either way? I do not want to modify the source csv table layout and believe I need to customize the import and mapping.

Thanks

EDIT

The following code snippet both, reads data from csv into a 2D array and also into a data structure (Dictionary of Dictionary in this case but could as well be the above proposed data structure FxConversionRate):

public class FxConversionTable
{
    public Dictionary<Currency, Dictionary<DateTime, double>> FxConversionRates{ get; set; } //key1 = Currency, key2 = DateTime, value = double
    public string[,] String2DArray{ get; set; }

    public FxConversionTable()
    {
        FxConversionRates = new Dictionary<Currency, Dictionary<DateTime, double>>();
    }

    public void ReadFxConversionRatesFromCsvFile(string pathFileName)
    {
        var strings = new List<List<string>>();

        using (var reader = new StreamReader(File.OpenRead(pathFileName)))
        {
            //read symbol rows and parse
            while (!reader.EndOfStream)
            {
                var line = reader.ReadLine();

                if (string.IsNullOrEmpty(line))
                    continue;

                var values = line.Split(',');

                //populate string array
                strings.Add(values.ToList());

                //header
                if (strings.Count == 1)
                {
                    foreach (var currencyString in values.Skip(1))
                    {
                        Currency ccy = (Currency) Enum.Parse(typeof (Currency), currencyString);
                        FxConversionRates.Add(ccy, new Dictionary<DateTime, double>());
                    }

                    continue;
                }

                //populate data collection
                var date = DateTime.ParseExact(values[0], "d/M/yyyy", CultureInfo.InvariantCulture);

                for (int i = 1; i < values.Count(); i++)
                {
                    var ccy = (Currency) Enum.Parse(typeof (Currency), strings[0][i]);
                    FxConversionRates[ccy].Add(date, Convert.ToDouble(values[i]));
                }
            }
        }

        String2DArray = FileIO.ListOfListTo2DArray<string>(strings);
    }
}

I am, however, still looking for a more generic solution via FileHelpers...

Answer 1

This should do the trick for you. It's not the most elegant solution but it works. You will need to add plenty of error checking for things like missing columns or data or source file corruption etc.

    private static void Main(string[] args)
    {
        var fileData = File.ReadAllBytes("Data.csv");
        var tableData = CreateDataTableFromFile(fileData);

        DataColumn dateColumn = tableData.Columns["Date"];
        Dictionary<string, List<FxConversionRate>> rates = new Dictionary<string, List<FxConversionRate>>();
        foreach (DataColumn column in tableData.Columns)
        {
            if (column != dateColumn)
            {
                foreach (DataRow row in tableData.Rows)
                {
                    FxConversionRate rate = new FxConversionRate();
                    rate.Currency = column.ColumnName;
                    rate.Date = DateTime.Parse(row[dateColumn].ToString());
                    rate.Rate = double.Parse(row[column].ToString());
                    if (!rates.ContainsKey(column.ColumnName))
                        rates.Add(column.ColumnName, new List<FxConversionRate>());
                    rates[column.ColumnName].Add(rate);
                }
            }
        }

        foreach (var key in rates.Keys)
        {
            Console.WriteLine($"Found currency: {key}");
            foreach (var rate in rates[key])
            {
                Console.WriteLine($"   {rate.Date.ToShortDateString()} : {rate.Rate:###,###,##0.00}");
            }
        }

        Console.WriteLine("Press any key");
        Console.ReadKey();
    }

    private static DataTable CreateDataTableFromFile(byte[] importFile)
    {
        var cb = new DelimitedClassBuilder("temp", ",") { IgnoreFirstLines = 0, IgnoreEmptyLines = true, Delimiter = "," };
        var ms = new MemoryStream(importFile);
        var sr = new StreamReader(ms);
        var headerArray = sr.ReadLine().Split(',');
        foreach (var header in headerArray)
        {
            cb.AddField(header, typeof(string));
            cb.LastField.FieldQuoted = true;
            cb.LastField.QuoteChar = '"';
        }
        var engine = new FileHelperEngine(cb.CreateRecordClass());
        return engine.ReadStreamAsDT(sr);
    }

Note that the CreateDataTableFromFile routine was taken from https://stackoverflow.com/a/6045923/697159