Read CSV with Linq

Question

I have the following lines (more, but this sample is fine) in a CSV file.

Date,Open,High,Low,Close,Volume,Adj Close
2012-11-01,77.60,78.12,77.37,78.05,186200,78.05
2012-10-31,76.96,77.75,76.96,77.47,290700,77.47
2012-10-26,77.30,77.62,76.86,77.36,195100,77.36

I need to perform several different functions on the data, each only needing certain fields from a row. i.e. function 1 will need the 2nd and 3rd sets of data from each line, function 2 will need the 4th set of data. How would I do that with LINQ (skipping the first line)?

Solution

You can try this:-

            var lines = File.ReadAllLines(@"Linq.csv").Select(x => x.Split(','));
            //Considering each line contains same no. of elements
            int lineLength = lines.First().Count();  
            var CSV = lines.Skip(1)
                       .SelectMany(x => x)
                       .Select((v, i) => new { Value = v, Index = i % lineLength })
                       .Where(x => x.Index == 2 || x.Index == 3)
                       .Select(x => x.Value);
            foreach (var data in CSV)
            {
                Console.WriteLine(data);
            }

Steps:-

Step 1 - Read all lines from CSV file and split them by Comma(,) which will result in an array os strings with every value.

Step 2 - Skip the first array (which is holding the headers), then use SelectMany to flatten the list into one, Next you need to set the index for each set(in the flatten list)similar, which I am doing with Select opertaor, the last thing left is filtering & selecting the item.