convert string csv to List of objects

Question

I have the following sting

const string csv = "Foo1,Foo2,Foo3,Foo4,Foo5,Foo6,Ping Pong\n" +
                   "2016-02-29,1437.530029,1445.839966,1433.77002,1436.930054,34016300,1436.930054\n" +
                   "2016-02-25,1431.439941,1431.439941,1421.280029,1426.97998,78076500,1426.97998\n" +
                   "2016-02-24,1430.459961,1432.430054,1417.449951,1419.790039,29049900,1419.790039\n";

How I can convert it to List<Model> where

public class Model
{
    public string Foo1 { get; set; }
    public string Foo2 { get; set; }
    public string Foo3 { get; set; }
    public string Foo4 { get; set; }
    public string Foo5 { get; set; }
    public string Foo6 { get; set; }
    public string Ping_Pong { get; set; }
}

Note of the Ping Pong header in my orig csv

I tried to use CsvHelper but with no success as it is taking a stream rather then string and I failed trying to convert a parse

EDIT It does not matter for me if to use CsvHelper or not, in the end I want to convert the csv to List<Model>

How can I do this?

Or you could convert the string to a stream easy enough: var ms = new MemoryStream(new UTF8Encoding.GetBytes(csv)); — Kevin, Mar 09 '16 at 19:38
There are ways to convert a `string` to a `Stream` http://stackoverflow.com/questions/1879395/how-to-generate-a-stream-from-a-string — juharr, Mar 09 '16 at 19:39
I would recommend that you don't give up with csvhelper -- I've had a lot of success with it. It can take a TextReader, and you can surface your string with a reader by using a StringReader: var myReader = new StringReader(thecsvString): — JMarsch, Mar 09 '16 at 19:42

score 16 · Accepted Answer · edited Apr 30 '18 at 08:58

16

You can create an StringReader using csv variable:

var textReader = new StringReader(csv);

var csvr = new CsvReader(textReader);
var records = csvr.GetRecords<Model>();

If you want your own parser:

var lines = csv.Split(new char[] {'\n'}, StringSplitOptions.RemoveEmptyEntries).Skip(1);
List<Model> models = new List<Model>();

foreach (var item in lines)
{
    var values = item.Split(',');
    var model = new Model
    {
        Foo1 = values[0],
        Foo2 = values[1],
        Foo3 = values[2],
        Foo4 = values[3],
        Foo5 = values[4],
        Foo6 = values[5],
        Ping_Pong = values[6],
    };

    models.Add(model);
}

EDIT:

To resolve the header problem using CsvHelper you need create a map configuration class specifying the mappings between headers and properties:

public class ModelMap : CsvClassMap<Model>
{
    public ModelMap()
    {
        Map(m => m.Foo1);
        Map(m => m.Foo2);
        Map(m => m.Foo3);
        Map(m => m.Foo4);
        Map(m => m.Foo5);
        Map(m => m.Foo6);
        Map(m => m.Ping_Pong).Name("Ping Pong");
    }
}

Using like this:

var textReader = new StringReader(csv);

var csvr = new CsvReader(textReader);
csvr.Configuration.RegisterClassMap<ModelMap>();

var records = csvr.GetRecords<Model>();

edited Apr 30 '18 at 08:58

Markus Bergkvist

13
4

answered Mar 09 '16 at 19:48

Arturo Menchaca

15,783
1
29
53

This is working perfectly, my question is how can I assign the header to the correct property in class Model? I prefer instead of reading the [x] place – user829174 Mar 09 '16 at 19:54
You could just do `csv.Split('\n')` using the `Split(params char[] separators)` overload since you don't really need to remove empty entries for the OP's example. – juharr Mar 09 '16 at 19:59
@juharr: was my first try but `csv` has a `\n` at the end. params overload returned an empty line at the end. – Arturo Menchaca Mar 09 '16 at 20:03
1

Somehow when testing your second answer is not working for me. var records = csvr.GetRecords(); gives me null – user829174 Mar 09 '16 at 20:08
@user829174: StockModel is the same as Model? if not note you need create the map inheriting from CsvClassMap – Arturo Menchaca Mar 09 '16 at 20:10
yes, StockModel is Model, sorry for confusing you, I copy paste.. still not working – user829174 Mar 09 '16 at 20:11
@user829174: returns null or object properties are null? – Arturo Menchaca Mar 09 '16 at 20:17
when hover records with debugger I get records.System.Collections.IEnumerator.Current = null. I want to return List – user829174 Mar 09 '16 at 20:19
@user829174: try with this `var records = csvr.GetRecords().ToList();` to convert explicitly to a list – Arturo Menchaca Mar 09 '16 at 20:20
awesome, appreciate your help! – user829174 Mar 09 '16 at 20:23

score 0 · Answer 2 · answered Mar 09 '16 at 19:53

0

The problem is that your data set (the csv string) is missing a column (you specify 7 columns, but the Ping Pong column is missing from the csv). The default behavior will be to throw, but you can set a config option to ignore missing columns:

Here's working code:

var config = new CsvConfiguration();
// setting this will cause the missing Ping Pong field to be ignored
config.WillThrowOnMissingField = false;

// we wrap your string in a StringReader to make it accessible to CsvReader
var reader = new CsvReader(new StringReader(csv), config);
var records = reader.GetRecords<Model>().ToList();
records.Dump();

answered Mar 09 '16 at 19:53

JMarsch

21,484
15
77
125

I do have 'Ping Pong' data, I doubled checked in my question. I have 7 values in header (where ping pong has space in between) and 7 values in data – user829174 Mar 09 '16 at 19:59
@user829174 You're right, I miscounted. I renamed your header from "Ping Pong" to "Ping_Pong", so it matches your model, and now all 7 columns parse – JMarsch Mar 09 '16 at 20:01

Gordon Allocman · Answer 3 · 2016-03-09T20:06:40.507

-1

To do exactly what you asked you could use this:

var modelStrings = csv.Split('\n').Skip(1);
var models = new List<Model>();
foreach(string s in modelStrings)
{
    var props = s.Split(',');
    models.Add(new Model(props[0],props[1],props[2],props[3],props[4],props[5],props[6]));

}

but a word of warning: this is probably slow, and you might need to add more extensive logic for instantiating new models to account for possible changes in csv format.

Edit:

To clarify what this does, it first splits the csv string by new lines skipping the first one. Then it uses each string in that list and splits them by commas to get a list of values (model properties) to instantiate the Model class with

edited Mar 09 '16 at 20:06

answered Mar 09 '16 at 19:47

Gordon Allocman

768
7
23

not sure it will work, what is 'foreach(string s in models)'? – user829174 Mar 09 '16 at 19:49
whoops i changed a variable name, let me edit my answer – Gordon Allocman Mar 09 '16 at 19:49
Your first split would also split on the space in "Ping Pong". – juharr Mar 09 '16 at 19:57
One reason I like using a library like csvreader instead of parsing by hand, is that the reader will handle data that is in Excel's csv format. That may or may not matter for the OP's use, but here's an example: if one of the data values is a comma (so you want that comma, it's not a separator), Excel would put that value in quotes, and you would ignore any commas inside the quotes. CsvHelper has all that parsing logic built in. Just something to know -- could be overkill for the OP's need, but might not be. – JMarsch Mar 09 '16 at 19:58
@juharr ah ok I believe I misinterpreted the information on the docs on Split, I updated it so it shouldn't be an issue anymore – Gordon Allocman Mar 09 '16 at 19:59
Since the `Split` overload you are using is `Split(params char[] seperators)` you don't have to create an array, so `csv.Split('\n')` would do the same thing. – juharr Mar 09 '16 at 20:02

convert string csv to List of objects

3 Answers3