Ignoring empty fields in a CSV using LINQ

Question

I have a csv that looks like:

Column1,Column2,Column3,Column4,Column5,Column6,Column7,Column7,Column8,Column9,
45.50334645,5640192,3915776,52633600,351924224,12354,90505216,78790656,247287808, 
39.23091283,5640192,3915776,52633600,349986816,4562,90505216,78790656,247287808, 
25.26042,5640192,3915776,52633600,349986816,   ,90505216,78790656,247287808,

I need to get the MIN, MAX, and Average from each column. I am using LINQ to do this since the CSV's can be quite large.

Here is the current code I have.

var lines = System.IO.File.ReadAllLines(csvPath);
var columns = lines[0].Split(',');
for (int i = 1; i < columns.Count(); i++)
{
    var columnQuery = from line in lines
                        let elements = line.Split(',')
                        select Convert.ToDouble(elements[i]);

    var results = columnQuery.ToList();
    var min = results.Min();
    var max = results.Max();
    var avg = results.Average();
}

This will error out on the highlighted value in the csv since it is considered a Datetime.

The error I get is "Input string was not in a correct format."

Thanks for any help.

I don't get it. There's a DateTime in there? Why not use DateTime.TryParse than? — Steven
– Steven, Commented Aug 18, 2014 at 20:00
And if the file is large, use File.ReadLines instead. That will return an enumerator and will not load the whole file at once (what File.ReadAllLines does do). — Steven
– Steven, Commented Aug 18, 2014 at 20:00
It doesn't have a datetime. It just thinks it is because it is a blank field. — user1241394
– user1241394, Commented Aug 18, 2014 at 20:04

MarcinJuraszek · Accepted Answer · 2014-08-18 20:03:17Z

1

You can use ternary operator:

var columnQuery = from line in lines
                  let elements = line.Split(',')
                  select string.IsNullOrWhiteSpace(elements[i]) ? 0. : Convert.ToDouble(elements[i]);

answered Aug 18, 2014 at 20:03

MarcinJuraszek

126k15 gold badges200 silver badges267 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1241394 Over a year ago

This works. I was trying to mimic excel functions of MIN, MAX, and Average. And I just looked and saw they also use 0 if the data is missing. Thanks.

Krizz · Accepted Answer · 2014-08-18 20:03:45Z

0

Have you tried using Double.TryParse instead of Convert.ToDouble to avoid exception or to explicitly filter out empty rows with where elements[i].Trim() != "" before select?

answered Aug 18, 2014 at 20:03

Krizz

11.6k1 gold badge34 silver badges45 bronze badges

Collectives™ on Stack Overflow

Ignoring empty fields in a CSV using LINQ

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related