CsvHelper - Read multiple related columns into a list of objects

Question

I have a CSV like this: (note: actual csv has 5 of each of the repeating columns instead of just 2)

Id Name Diagnosis 1 Diagnosis 2 Drug 1 Drug 2 How Taken 1 How Taken 2
== ==== =========== =========== ====== ====== =========== ===========
 1 One  F23         F25         D1     D55    Oral        Inject
 2 Two  F30                     D5     D7     Inject      Inhale

I'm using CsvHelper to map this into my POCO object that looks like this:

public class Episode {
    public int Id {get; set;}
    public string Name {get; set;}
    public IList<string> Diagnoses {get; set;}
    public IList<Drug> Drugs { get; set;}
}

public class Drug {
    public int Id {get; set;}
    public string Name {get; set;}
    public string AdministrationMethod {get; set;}
}

I've seen a couple references of mapping to IEnumerable with CsvHelper but there is no formal documentation on it.

Is there any way to:

1. Map Diagnosis 1, Diagnosis 2, etc. to the IList<string> Diagnoses property?
2. Map [Drug x, How Taken x] pairs to the IList<Drug> Drugs property?

The following is as close as I know how to come (definitely doesn't work but trying to help give the concept of what I'm after).

void Main()
{
    using (var reader = new StreamReader("path\\to\\file.csv"))
    using (var csv = new CsvReader(reader))
    {
        csv.Configuration.RegisterClassMap<EpisodeMap>();
        var records = csv.GetRecords<EpisodeMap>();
    }
}

public EpisodeMap : ClassMap<Episode>{
    public EpisodeMap(){
      Map(m => m.Name).Name("Name");
      Map(m => m.Diagnoses).Name("Diagnosis").Index(5);  //Unsure how to indicate appropriate name "Diagnosis {index}"
      References<DrugMap>(m => m.Drugs).Index(5); //?? Not sure if something like this is possible
    }
}

public DrugMap : ClassMap<Drug> {
    public DrugMap() {
       Map(m => m.Name).Name("Drug");
       Map(m => m.AdministrationMethod).Name("How Taken");
}

Answer 1

Unfortunately Index() only works with simple IEnumerables like IEnumerable<string>. You are going to need to use ConvertUsing() for more complex objects, either utilizing the name or index of the column. Name() should have worked for Diagnosis, but for some reason I can only get it to work if the heading columns are named the same as the property Diagnoses.

You could also use ConvertUsing() to get Diagnosis headings to work.

public class Program
{
    public static void Main(string[] args)
    {
        using (MemoryStream stream = new MemoryStream())
        using (StreamWriter writer = new StreamWriter(stream))
        using (StreamReader reader = new StreamReader(stream))
        using (CsvReader csv = new CsvReader(reader, new Configuration()))
        {
            writer.WriteLine("Id,Name,Diagnoses 1,Diagnoses 2,Drug 1,Drug 2,How Taken 1,How Taken 2");
            writer.WriteLine("1,One,F23,F25,D1,D55,Oral,Inject");
            writer.WriteLine("2,Two,F30,,D5,D7,Inject,Inhale");
            writer.Flush();
            stream.Position = 0;
            reader.BaseStream.Position = 0;

            csv.Configuration.RegisterClassMap<EpisodeMap>();
            csv.Configuration.PrepareHeaderForMatch = (header, index) => Regex.Replace(header, " ", string.Empty);

            var records = csv.GetRecords<Episode>().ToList();
        }

        Console.ReadKey();
    }
}

public class EpisodeMap : ClassMap<Episode>
{
    public EpisodeMap()
    {
        Map(m => m.Id);
        Map(m => m.Name);
        Map(m => m.Diagnoses).Index(2,3);  //Unsure how to indicate appropriate name "Diagnosis {index}"
        Map(m => m.Drugs).ConvertUsing(row =>
        {
            return new List<Drug>
            {
                new Drug { Name = row["Drug1"], AdministrationMethod = row["HowTaken1"] },
                new Drug { Name = row["Drug2"], AdministrationMethod = row["HowTaken2"]}
            };
        });
    }
}