0

A vendor is providing us a csv file nightly. We need to take that csv, pull out some of the columns, then import them into our in-house application. The csv, as we receive it, looks a bit like this:

StudentId, GradYear, 2014 Thing1, 2014 Thing2, 2015 Thing1, 2015 Thing2

and so on, adding columns seemingly to perpetuity. When I first saw this csv, my first inclination was to transpose it to 2 columns: thus:

StudentId, 123456
GradYear, 2016
2014 Thing1, overdue
2015 Thing1, completed

My inclination is use csvHelper to read this file in, and transpose it by constructing an ILookup. So I would have a Lookup that would look like:

var theLookup = ILookup<string, KeyValuePair<string, string>>

The key being the StudentId, and the KeyValuePair being the column header : cell value. I can almost work it out using a Dictionary, with a counter for the dictionary key, but I can't make the leap to Lookup with the StudentId as the key. Here is how I'm constructing the Dictionary:

Dictionary<int, List<KeyValuePair<string, string>>> vendorDictionary = new     Dictionary<int, List<KeyValuePair<string, string>>>();

using (var fileReader = File.OpenText(sourcePath))
using (var reader = new CsvHelper.CsvReader(fileReader))
{
     while (reader.Read())
    {
        var dynamicVendor = reader.GetRecord<dynamic>() as IDictionary<string, object>;

        foreach (var rec in dynamicVendor)
        {
            var recordDictionary = dynamicVendor.ToDictionary(k => k.Key, k => k.Value.ToString());
            vendorDictionary.Add(counter, recordDictionary.ToList());
            ++counter;
        }
    }

I can view the records with the following code. Do I need to do a version of this foreach to make into a Lookup? Or, should I just use a Dictionary with some "if it's 'StudentId' then make it the key?

foreach (var rec in vendorDictionary)
{
    Console.WriteLine("Vendor Dictionary record # " + rec.Key + " values are:");
    foreach (var value in rec.Value)
    {
       Console.WriteLine("Key: '" + value.Key + "' Value: '" + value.Value + "'");
    }                        
}

Mostly I want to know if I'm even on the right track here. When I have whatever object of vendor data I end up with, I will need to loop through it, compare student with our student record and make decisions about updating records in our app.

Jazzy
  • 519
  • 9
  • 31
  • I think you're approaching this the wrong way. Why do you feel you need to transpose the data? What does that accomplish? – Jeff Mercado Nov 13 '15 at 05:48
  • I need to isolate each students record, and read the data into a dynamic object. Maybe transpose is the wrong term, what I'm attempting to do is put each student in their own object, while also dealing with the possibility the vendor will add columns (additional school years) to the csv. – Jazzy Nov 13 '15 at 16:45

0 Answers0