Photo by Mika Baumeister

When working with C# and CSV files, I always use CSVHelper. It's a small library for reading and writing CSV files. Extremely fast, flexible, and easy to use. I couldn't agree more, it's simply awesome :).

Now, I ran into a rather delicate problem a couple of days ago.
Say that you a CSV file looking like this:

id,categories
1234,"food,pasta,meat"
4321,"food,beer,alcohol"

You then have a class that you want to map the data to:

public class GoodFoodCsvRow
{
    public string Id { get; set; } = null!;
    public List<string> Categories { get; set; } = new();
}

And your CSVHelper Map looks like this:

public class GoodFoodCsvRowMap : ClassMap<GoodFoodCsvRow>
{
    public GoodFoodCsvRowMap()
    {
        Map(x => x.Id).Index(0);
        Map(x => x.Categories).Index(1);
    }
}

If you now were to read said CSV file like this...

using (var response = await _httpClient.SendAsync(request, HttpCompletionOption.ResponseHeadersRead))
{
    response.EnsureSuccessStatusCode();
    using (var responseStream = await response.Content.ReadAsStreamAsync())
    {
        using (var reader = new StreamReader(responseStream))
        {
            using (var csv = new CsvReader(reader, CultureInfo.InvariantCulture))
            {
                csv.Configuration.HasHeaderRecord = true;
                csv.Configuration.RegisterClassMap<GoodFoodCsvRowMap>();
                var rows = csv.GetRecords<GoodFoodCsvRow>();
                return rows.ToDictionary(x => x.Id, x => x.Categories);
            }
        }
    }
}

...you would notice that the Categories list only contains one string looking like this:

That's not what we want, we want each category to be a separate item in the Categories list.

Luckily, it's really easy to achieve using CSVHelper, we just need to update our GoodFoodCsvRowMap so it looks like this:

public class GoodFoodCsvRowMap : ClassMap<GoodFoodCsvRow>
{
    public GoodFoodCsvRowMap()
    {
        Map(x => x.Id).Index(0);
        Map(x => x.Categories).Index(1).Convert(row =>
        {
            var columnValue = row.Row.GetField<string>("categories");
            return columnValue?.Split(',').ToList() ?? new List<string>();
        });
    }
}

Now when we run it again the problem will be solved:

[Fact]
public void Test1()
{
    var text = "id,categories\r\n" +
               "1234,\"food,pasta,meat\"\r\n" +
               "4321,\"food,beer,alcohol\"";
    var stream = new MemoryStream(Encoding.UTF8.GetBytes(text));
    using var reader = new StreamReader(stream);
    var config = new CsvConfiguration(CultureInfo.InvariantCulture)
    {
        HasHeaderRecord = true,
    };
    using var csv = new CsvReader(reader, config);
    csv.Context.RegisterClassMap<GoodFoodCsvRowMap>();
    var rows = csv.GetRecords<GoodFoodCsvRow>();
    var result = rows.ToDictionary(x => x.Id, x => x.Categories);

    result.Count.ShouldBe(2);
    result.ShouldContain(x => x.Key == "1234" && x.Value.Count == 3 && x.Value.Contains("food") && x.Value.Contains("pasta") && x.Value.Contains("meat"));
    result.ShouldContain(x => x.Key == "4321" && x.Value.Count == 3 && x.Value.Contains("food") && x.Value.Contains("beer") && x.Value.Contains("alcohol"));
}