CsvHelper - read in multiple columns to a single list

Question

I'm using CSVHelper to read in lots of data

I'm wondering if it's possible to read the last n columns in and transpose them to a list

"Name","LastName","Attribute1","Attribute2","Attribute3"

And mould the data into something like this

public class Person
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public IList<string> Attributes { get; set; }
}

I'm looking to do this in one step, I'm sure I could have an intermediate step where I put into an object with matching attribute properties but it would be nice to do it on a one-er

score 23 · Accepted Answer · answered Jun 28 '13 at 11:32

This does the trick as a mapper.

public sealed class PersonMap : CsvClassMap<Person>
{
    private List<string> attributeColumns = 
        new List<string> { "Attribute1", "Attribute2", "Attribute3" };

    public override void CreateMap()
    {
        Map(m => m.FirstName).Name("FirstName").Index(0);
        Map(m => m.LastName).Name("LastName").Index(1);
        Map(m => m.Attributes).ConvertUsing(row =>
            attributeColumns
                .Select(column => row.GetField<string>(column))
                .Where(value => String.IsNullOrWhiteSpace(value) == false)
            );
    }
}

Then you just need something like this

using (var reader = new CsvReader(new StreamReader(filePath)))
{
    reader.Configuration.RegisterClassMap<PersonMap>();
    while (reader.Read())
    {
        var card = reader.GetRecord<Person>();
    }
}

How am I supposed to have 'attributeColumns' dynamically from the 'Person' object instead of hardcoding random strings? — George Findulov, Feb 22 '16 at 10:57

score 3 · Answer 2 · answered Aug 25 '16 at 04:29

3

Version 3 has support for reading and writing IEnumerable properties. You can use an IList<T> property just like you have. You just need to specify the start index of the field.

Map( m => m.Attributes ).Index( 2 );

answered Aug 25 '16 at 04:29

Josh Close

22,935
13
92
140

I have seen this Version 3 in other posts of yours too. I need this feature, but I do not find this version... Nuget only provides up to version 2.16.3 now. How can I get it? – cnom Mar 01 '17 at 13:43
1

In NuGet manager, check the box to enable pre-release versions. If you're doing it via the console, you can do `Install-Package CsvHelper -Pre` – Josh Close Mar 01 '17 at 19:04
Thank you for this, I did it, but now I could use some help or documentation on how to map IEnumerable properties.. In my case I try to concatenate the multiple values of the enumerable to one value for export. – cnom Mar 02 '17 at 13:17
1

The documentation for 3.0 isn't done yet. Check out the unit tests for examples for the time being. – Josh Close Mar 02 '17 at 18:19
@JoshClose Since I don't know the starting index, Is there any way to Map this property like the StartsWith('Attribute'). – Farshan Dec 31 '20 at 14:01

Tim Schmelter · Answer 3 · 2013-06-27T22:50:05.153

I don't know this library, so following might be helpful or not.

If you already have an IEnumerable<IEnumerable<string>> which represents all records with all columns you could use this Linq query to get your List<Person> with the IList<string> Attributes:

IEnumerable<IEnumerable<string>> allRecords = .....;
IEnumerable<Person> allPersons = allRecords
.Select(rec => 
{
    var person = new Person();
    person.FirstName = rec.ElementAt(0);
    person.LastName = rec.ElementAtOrDefault(1);
    person.Attributes = rec.Skip(2).ToList();
    return person;
}).ToList();

Edit: Downloaded the library, following at least compiles, could not really test it:

IList<Person> allPersons = new List<Person>();
using (var reader = new CsvHelper.CsvReader(yourTextReader))
{
    while (reader.Read())
    {
        var person = new Person();
        person.FirstName = reader.CurrentRecord.ElementAt(0);
        person.LastName = reader.CurrentRecord.ElementAtOrDefault(1);
        person.Attributes = reader.CurrentRecord.Skip(2).ToList();
        allPersons.Add(person);
    }
}

I like the use of linq here, I ended up doing something similar within the confines of the library — Neil, Jun 27 '13 at 22:23
@Neil: I have downloaded the lib and edited my answer to provide something that compiles and might give you an idea. However, i could not test it and must go to bed now ;) — Tim Schmelter, Jun 27 '13 at 22:45

score 2 · Answer 4 · edited May 20 '20 at 21:20

2

You don't actually need to define the column names beforehand, you can get them from the FieldHeaders property. So in a more dynamic version of Neil's answer you could define your Mapper as such:

public sealed class PersonMap : CsvClassMap<Person> {
    public override void CreateMap() {
        Map(m => m.FirstName).Name("FirstName").Index(0);
        Map(m => m.LastName).Name("LastName").Index(1);
        Map(m => m.Attributes).ConvertUsing(row =>
            (row as CsvReader)?.FieldHeaders
                 .Where(header => header.StartsWith("Attribute"))
                 .Select(header => row.GetField<string>(header))
                 .Where(value => !string.IsNullOrWhiteSpace(value))
                 .ToList()
        );
    }
}

edited May 20 '20 at 21:20

d219

2,707
5
31
36

answered Apr 26 '16 at 14:19

Dimitri Troncquo

354
2
11

It doesn't work. I got compile error, Cannot convert type 'Person' to 'CsvHelper.CsvReader' via a reference conversion, boxing conversion, unboxing conversion, wrapping conversion, or null type conversion – sky91 Apr 25 '18 at 05:19
Doesn't work for me, I get that `FieldHeaders` does not exist on the property `CsvReader` – Sigex Apr 08 '19 at 16:31
@Sigex, which version of CSVHelper are you using? I wrote this answer some time ago, it's possible some of the properties have changed – Dimitri Troncquo Nov 25 '19 at 19:18

CsvHelper - read in multiple columns to a single list

4 Answers4

Linked