CsvHelper write mapper one property to multiple columns

Question

I'm using csvHelper (version 2.8.4) to write a class to csv. My class looks like this:

public class classA
{
   public int Amount { get; set; }
   public Dictionary<string, string> Dict{ get; set; }
}

Is it possible to write a mapper that maps Dict property to multiple columns? using some sort of converter?

for example if the class has the values:
Amount = 15
Dict = new Dictionary<string,string>{["a1"] = "a2",["b1"] = "b2"}

I want the resulting csv to be:

Amount,a1,b1
15,a2,b2

Thanks!

Maybe this will help you - https://stackoverflow.com/questions/21311423/mapping-to-a-dictionary — Preslav Tsenov, Jan 27 '22 at 11:51

score 2 · Answer 1 · answered Jan 27 '22 at 13:07

As mentioned in linked question, you may use ExpandoObject to serialize dictionary.

The following code will work for writing to CSV only, it's converting classA objects to ExpandoObject during serialization, including Amount property which is added manually.

public static List<dynamic> ToExpandoObjects(IReadOnlyList<classA> aObjects)
{
    var allKeys = aObjects
        .SelectMany(a => a.Dict.Keys)
        .Distinct()
        .ToHashSet();

    var result = new List<dynamic>();

    foreach (var a in aObjects)
    {
        var asExpando = new ExpandoObject();
        var asDictionary = (IDictionary<string, object>)asExpando;

        asDictionary[nameof(classA.Amount)] = a.Amount;
        foreach (var key in allKeys)
        {
            if(a.Dict.TryGetValue(key, out var value))
                asDictionary[key] = value;
            else
                asDictionary[key] = null;
        }

        result.Add(asExpando);
    }

    return result;
}

...
    using (var csv = new CsvWriter(writer, CultureInfo.InvariantCulture))
    {
        csv.WriteRecords(ToExpandoObjects(records));
    }

E.g. called as:

var records = new[] {
    new classA
    {
        Amount = 15,
        Dict = new Dictionary<string,string>{["a1"] = "a2",["b1"] = "b2"}
    },
    new classA
    {
        Amount = 15,
        Dict = new Dictionary<string,string>{["c1"] = "c2",["b1"] = "b2"}
    }
};

StringBuilder sb = new StringBuilder();
using (var writer = new StringWriter(sb))
using (var csv = new CsvWriter(writer, CultureInfo.InvariantCulture))
{
    csv.WriteRecords(ToExpandoObjects(records));
}

Console.WriteLine(sb.ToString());

produces

Amount	a1	b1	c1
15	a2	b2
15		b2	c2

David Specht · Accepted Answer · 2022-01-27T16:22:23.073

Possibly the easiest way is going to be to manually write out the dictionary part.

*** Update to work with CsvHelper Version 2.8.4 ***

void Main()
{
    var records = new List<classA>
    {
        new classA {
            Amount = 15,
            Dict = new Dictionary<string,string>{["a1"] = "a2",["b1"] = "b2"}
        }
    };
    
    using (var csv = new CsvWriter(Console.Out))
    {
        var dict = records.First().Dict;

        var properties = typeof(classA).GetProperties();
        
        foreach (PropertyInfo property in properties)
        {
            if (property.Name != "Dict")
            {
                csv.WriteField(property.Name);
            }
        }

        foreach (var item in dict)
        {
            csv.WriteField(item.Key);
        }

        csv.NextRecord();

        foreach (var record in records)
        {
            foreach (PropertyInfo property in properties)
            {
                if (property.Name != "Dict")
                {
                    csv.WriteField(property.GetValue(record));
                }
            }

            foreach (var item in record.Dict)
            {
                csv.WriteField(item.Value);
            }

            csv.NextRecord();
        }
    }
}

// You can define other methods, fields, classes and namespaces here

public class classA
{
    public int Amount { get; set; }
    public Dictionary<string, string> Dict { get; set; }
}

*** Works for current Version 27.2.1 ***

void Main()
{
    var records = new List<classA>
    {
        new classA { Amount = 15, Dict = new Dictionary<string,string>{["a1"] = "a2",["b1"] = "b2"} },
    };

    using (var csv = new CsvWriter(Console.Out, CultureInfo.InvariantCulture))
    {
        var dict = records.First().Dict;
        
        csv.WriteHeader<classA>();
        
        foreach (var item in dict)
        {
            csv.WriteField(item.Key);   
        }
        
        csv.NextRecord();
        
        foreach (var record in records)
        {
            csv.WriteRecord(record);

            foreach (var item in record.Dict)
            {
                csv.WriteField(item.Value);
            }
            
            csv.NextRecord();
        }       
    }
}

public class classA
{
    public int Amount { get; set; }
    public Dictionary<string, string> Dict { get; set; }
}

Which version of csvHelper did you use? doing as you suggested causes whatever I write in csv.WriteField be in a new line — sgch, Jan 27 '22 at 13:47
The newest version. Which version are you using? I don't remember a time when `csv.WriteField` would write a newline. — David Specht, Jan 27 '22 at 15:49
Using 2.8.4, it looks like `csv.WriteHeader()` and `csv.WriteRecord(record)` both cause the writing of a `newline` character. Not writing a `newline` character for both of those methods must have been an upgrade at some point. I'll update my answer to work for Version 2.8.4. — David Specht, Jan 27 '22 at 16:20

CsvHelper write mapper one property to multiple columns

2 Answers2