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!

I think @Rene147's solution is the best one. Create a POCO that matches what you want and then use the helper class to give you a list of the POCOs based on the sheet. Neither a KeyValuePair or a Dictionary are good options, the Dictionary in particular because you're going to overwrite values in the dictionary for each new line in the spreadsheet. — Maurice Reeves, Apr 08 '14 at 13:59

Kunukn · Accepted Answer · 2014-04-08T14:14:40.823

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);

Thank you very much Kunukn! Funnily enough I solved my own question a few minutes before you posted but I tried your solution and the performance was better - thanks again :). — user2439970, Apr 08 '14 at 14:17

score 1 · Answer 2 · answered Apr 08 '14 at 13:49

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.

score 0 · Answer 3 · answered Apr 08 '14 at 14:27

Example of implementation

class Program
{
    static void Main(string[] args)
    {
        string[] lines = new string[] {
            "Col_Title_1|Col_Title_2|Col_Title_3|Col_Title_4|Col_Title_5", //Line 0 is Header/Columns
            "Value_1|Value_2|Value_3|Value_4|Value_5" //Line 1 and so on is data records
        };
        string fileContent = "Col_Title_1|Col_Title_2|Col_Title_3|Col_Title_4|Col_Title_5" + Environment.NewLine +
                      "Value_1|Value_2|Value_3|Value_4|Value_5";
        CSVTable table = new CSVTable(lines);
        string firstColumnvalue = table[0]["Col_Title_1"];
        Console.WriteLine(firstColumnvalue);
        table = new CSVTable(fileContent);
        firstColumnvalue = table[0]["Col_Title_1"];
        Console.WriteLine(firstColumnvalue);

    }
}

public class CSVTable
{
    public CSVTable(string table)
        : this(table.Split(new string[] { Environment.NewLine }, StringSplitOptions.None))
    {
    }

    public CSVTable(string[] lines)
    {
        Columns = lines[0].Split('|');
        Records = lines.ToList().GetRange(1, lines.Length - 1).Select(line => new CSVRecord(line, Columns)).ToList();
    }
    public string[] Columns { get; private set; }
    List<CSVRecord> Records { get; set; }

    public CSVRecord this[int index]
    {
        get { return Records[index]; }
    }
}

public class CSVRecord : Dictionary<string, string>
{
    public CSVRecord(string line, string[] keys)
        : base()
    {
        var lista = line.Split('|');
        for (int i = 0; i < lista.Length; i++)
        {
            Add(keys[i], lista[i]);
        }
    }
}

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

3 Answers3