Read CSV file to nested objects

user_5421434 0
Viewed 734

I have CSV file like

Title|Column|Value
A|Z1|1
A|Z1|2
A|Z1|3
A|Z2|1
A|Z2|5
B|Z3|4
B|Z3|6
....

I want to read this csv file into the following class hierarchy: (I want to end up with a List of MyClass)

class MyClass
{
    public string Title { get; set; }
    public List<Column> Columns { get; set; }
}
class Column
{
    public string Column { get; set; }
    public List<Value> Values { get; set; }
}
class Value
{
    public string Value { get; set; }
}

I am not able to comprehend how to achieve this. What I tried up till now is:

class DTO
{
    public string Title { get; set; }
    public string Column { get; set; }
    public string Value { get; set; }
}

List<DTO> records = new List<DTO>();
using (var reader = new StreamReader(model.Path))
using (var csv = new CsvReader(reader))
{
    records = csv.GetRecords<DTO>().ToList();
}

var temp = records
    .GroupBy(x => x.Title)
    .Select(y => new
    {
        Title = y.Key,
        Columns = y.SelectMany(x => new { x.Column, x.Value }) //I am not sure how to proceed from here
    });

The issue I am facing is how can I further group by on the columns and get the values, or if I should be using another approach?

Some more explanation on the csv structure: There can be multiple Values for each column, and there can be multiple columns for each Title.

csvhelper csv group-by linq c#
1 Answers

There can be multiple Values for each column, and there can be multiple columns for each Title.

That means that you have to repeat the first step and GroupBy again by columns. You also should use your types that you already implemented for this. Don't use anonymous types:

IEnumerable<MyClass> temp = records
    .GroupBy(x => x.Title)
    .Select(y => new MyClass
    {
        Title = y.Key,
        Columns = y.GroupBy(x => x.Column)
        .Select( c => new Column
        {
            Column_ = c.Key,
            Values = c.Select(v => new Value
            {
                Value_ = v.Value
            }).ToList()
        }).ToList()
    });

    temp.Dump();

Result (from the LINQPad Dump):

enter image description here

PS. I changed the names of the properties, because the compiler does not allow them to be named exactly as the class is.

class Column
{
    public string Column_ { get; set; }
    public List<Value> Values { get; set; }
}
class Value
{
    public string Value_ { get; set; }
}
user_51744690
Related