CSV to Dictionary, matching up column titles and row values?

Question

I feel quite embarrassed to ask this, but I simply cannot get my desired results.

I have a CSV file which looks like this:

Col_Title_1|Col_Title_2|Col_Title_3|Col_Title_4|Col_Title_5
  Value_1  |  Value_2  |  Value_3  |  Value_4  |  Value_5

I want to read this data and manipulate it such as (pseudo code):

var test = array.Column("Col_Title_3").Value;

I just can't seem to pair the column and value up accordingly.

Is a dictionary or KeyValuePair best to use?

Here is 1 good test I did but of course does not work 100%:

Dictionary<string, string> dict = File.ReadLines(e.FullPath).Select(line =>
line.Split('|')).ToDictionary(line => line[0], line => line[1]);

I know I can do a hacky/hard code way like so:

string lineValues = File.ReadLines(e.FullPath).ElementAt(1);
string row3 = lineValues.Split('|')[2];
string row5 = lineValues.Split('|')[4];

But for obvious reasons I do not want to do it like this.

Any help will be greatly appreciated! Thanks all!

Answer 1

You could do something like this.

var lines = File.ReadLines("test.txt");
var header  = lines.First().Split('|');
var data = lines.Skip(1).First().Split('|');
var dict = new Dictionary<string, string>();
for (int i = 0; i < header.Length; i++)
{
    dict.Add(header[i], data[i]);
}

Console.WriteLine("Taking Col_Title_3 -> {0}", dict["Col_Title_3"]);
foreach (var i in dict.Keys)
{
    Console.WriteLine("key: {0} value: {1}",i,dict[i]);
}

Or the dictionary could alternatively be populated like this

var lines = File.ReadLines("test.txt");
var header = lines.First().Split('|');
var data = lines.Skip(1).First().Split('|');            
var dict = header.Select((a, i) => new { key = a, data = data[i] })
              .ToDictionary(b => b.key, c => c.data);

Answer 2

If you can use a third party library I highly recommend you take a look at CSVHelper (you can use nuget to get it into your project). You can just create a POCO (Plain Old CLR Object) that defines the columns you want to map and it does the rest. Will make this far easier.