CSV to object model mapping

Question

I have a csv file that I want to read in to a List. Here's an example file:

Plant,Material,"Density, Lb/ft3",Storage Location
FRED,10000477,64.3008,3300
FRED,10000479,62.612,3275
FRED,10000517,90,3550
FRED,10000517,72,3550
FRED,10000532,90,3550
FRED,10000532,72,3550
FRED,10000550,97,3050

I know I can manually read the csv file in and build the list using a normal StreamReader. But I was wondering if there was a better way perhaps using LINQ?

Answer 1

You can check out this article on codeproject.com:

LINQ to CSV library

I haven't tried it myself yet but it looks great.

Answer 2

There's a codeproject for that:

http://www.codeproject.com/KB/linq/LINQtoCSV.aspx

However, you may want to look here:

http://stackoverflow.com/questions/2320122/linq-and-streamreader-getting-lines

Answer 3

For the specific data shown in your question...

var yourData = File.ReadAllLines("yourFile.csv")
                   .Skip(1)
                   .Select(x => x.Split(','))
                   .Select(x => new
                                {
                                    Plant = x[0],
                                    Material = x[1],
                                    Density = double.Parse(x[2]),
                                    StorageLocation = int.Parse(x[3])
                                });

If you already have a type declared for your data then you can use that rather than the anonymous type.

Note that this code isn't robust at all. It won't correctly handle values containing commas/newlines etc, quoted string values, or any of the other esoteric stuff that is often found in CSV files.

Answer 4

You can use a simple code like this, which ignores the header and doesn't work with quotes, but may be sufficient for your needs.

from line in File.ReadAllLines(fileName).Skip(1)
let columns = line.Split(',')
select new
{
  Plant = columns[0],
  Material = int.Parse(columns[1]),
  Density = float.Parse(columns[2]),
  StorageLocation = int.Parse(columns[3])
}

Or you can use a library, like others suggested.

Answer 5

I wrote a simple library to allow developers to use LINQ on CSV files. Here is my blog post about it: http://procbits.com/2010/10/11/using-linq-with-csv-files/

In your case, you would have to change your header string to look like this:

Plant,Material,DensityLbft3,StorageLocation

And then you could parse the file like this:

var linqCSV = new CsvToXml("csvfile", true);
linqCsv.TextQualifier = null;

linqCsv.ColumnTypes.Add("Plant", typeof(string));
linqCsv.ColumnTypes.Add("Material", typeof(int));
linqCsv.ColumnTypes.Add("DensityLbft3", typeof(double));
linqCsv.ColumnTypes.Add("StorageLocation", typeof(int));

linqCsv.Convert();

You could then use LINQ like this:

var items = from item in linqCsv.DynamicRecords
            where item.Plant == "Fred" && item.DensityLbft3 >= 62.6
            orderby item.StorageLocation
            select item;

Hope that helps or works for you.