CSVHelper - using two mapping classes and choosing the mapping based on the value in a field of each row of the csv file

Question

Using the CSVHelper .NET library with .NET Core 2.2 to parse large csv file (over 1 million rows) and write it to a SQL Server table.

I have two mapping classes: we need to loop through each row, and if the first value of the row is 1, we need to use class map 1, and if the value is 2, we need to use class map 2. Because CSVHelper is geared toward doing this activity in bulk, I'm having trouble conceptualizing how to use the if statement and for each loop to accomplish this task.

This what I have so far:

SQL Entity Class

public class TaskEntity
{
    public int Id { get; set; }
    public string SqlTableColumn1 { get; set; }
    public string SqlTableColumn2 { get; set; }
}

CSVHelper Mapping Class 1

public sealed class TaskEntityMap1 : ClassMap<TaskEntity>
{
    public TaskEntityMap1()
    {
        Map(m => m.SqlTableColumn1).Name("CsvColumn1");
        Map(m => m.SqlTableColumn2).ConvertUsing(row => row.GetField<string>("CsvColumn2") + " " + row.GetField<string>("CsvColumn3"));
    }
}

CSVHelper Mapping Class 2

public sealed class TaskEntityMap2 : ClassMap<TaskEntity>
{
    public TaskEntityMap2()
    {
        Map(m => m.SqlTableColumn1).Name("CsvColumn4");
        Map(m => m.SqlTableColumn2).ConvertUsing(row => row.GetField<string>("CsvColumn5") + " " + row.GetField<string>("CsvColumn6"));
    }
}

Program.cs (this is what we had before being given the requirement for the conditional mapping)

public static void Main(string[] args)
{
    using (var reader = new StreamReader(@"C:\Users\me\Documents\file.csv"))
    using (var csv = new CsvReader(reader))          
    {
        csv.Configuration.PrepareHeaderForMatch = (string header, int index) =>
            header.Replace(" ", "_").Replace("(", "").Replace(")", "").Replace(".", "");

        csv.Configuration.RegisterClassMap<TaskEntityMap>();

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

As the above example of the Program.cs code demonstrates the typical ease-of-use scenario which CSVHelper seems to be designed for, I'm having difficulty conceptualizing how to use two class maps, and how to loop through each row of the csv file and choose the class map depending on the value in a column in a given row.

David Specht David Specht · Accepted Answer · 2019-10-01T19:53:03

When I original heard your requirements, I thought you just need to check for the ClassMap once per document. It now sounds like it will change per row. Unfortunately, I have found once you register and start using a ClassMap it appears to get cached and you can't switch it half way through, so I don't think the two ClassMaps will work. I'll give you a couple of ways to solve it and you can let me know if either satisfies your requirements.

public static void Main(string[] args)
{
    using (var reader = new StreamReader(@"C:\Users\me\Documents\file.csv"))
    using (var csv = new CsvReader(reader))          
    {
        csv.Configuration.PrepareHeaderForMatch = (string header, int index) =>
            header.Replace(" ", "_").Replace("(", "").Replace(")", "").Replace(".", "");

        csv.Configuration.RegisterClassMap<TaskEntityMap>();

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

public sealed class TaskEntityMap : ClassMap<TaskEntity>
{
    public TaskEntityMap()
    {
        Map(m => m.SqlTableColumn1).ConvertUsing(row => row.GetField<int>(0) == 1 ? 
            row.GetField<string>("CsvColumn1") : 
            row.GetField<string>("CsvColumn4")
            );
        Map(m => m.SqlTableColumn2).ConvertUsing(row => row.GetField<int>(0) == 1 ? 
            row.GetField<string>("CsvColumn2") + " " + row.GetField<string>("CsvColumn3") :
            row.GetField<string>("CsvColumn5") + " " + row.GetField<string>("CsvColumn6")
            );
    }
}

Another option would be to manually construct the TaskEntity objects.

public static void Main(string[] args)
{
    using (var reader = new StreamReader(@"C:\Users\me\Documents\file.csv"))
    using (var csv = new CsvReader(reader))          
    {
        csv.Configuration.PrepareHeaderForMatch = (string header, int index) =>
            header.Replace(" ", "_").Replace("(", "").Replace(")", "").Replace(".", "");

        var records = new List<TaskEntity>();

        csv.Read();
        csv.ReadHeader();

        while (csv.Read())
        {                    
            if (csv.GetField<int>(0) == 1)
            {
                var record = new TaskEntity
                {
                    SqlTableColumn1 = csv.GetField<string>("CsvColumn1"),
                    SqlTableColumn2 = csv.GetField<string>("CsvColumn2") + " " + csv.GetField<string>("CsvColumn3")
                };
                records.Add(record);
            }
            else
            {
                var record = new TaskEntity
                {
                    SqlTableColumn1 = csv.GetField<string>("CsvColumn4"),
                    SqlTableColumn2 = csv.GetField<string>("CsvColumn5") + " " + csv.GetField<string>("CsvColumn6")
                };
                records.Add(record);
            }                    
        }
    }  // Break here.
}

CSVHelper - using two mapping classes and choosing the mapping based on the value in a field of each row of the csv file

1 Answers