0

I have a csv data like this

Header1 Header2 Header3 ... ValueN
Key1 Value11 Value12 Value13 ... Value1N
Key2 Value21 Value22 Value23 ... Value2N
Key3 Value31 Value32 Value33 ... Value3N
... ... ... ... ... ...
KeyN ValueN1 ValueN2 ValueN3 ... ValueNN

which have dynamic size of columns.
I want to load it to a lookup table

dictionary<string, dictionary<string, string>> lookup_table

so I can get data by

data = lookup_table[key_name][header_name]

Furthermore, I have to write back to csv if data got changed. How should I create my class and map to read/write it?
csvhelper version = 28.0.1

sdrest
  • 35
  • 5
  • I would suggest either of two ways: (1) Create a model class reflecting the columns in your CSV. Read the CSV records as a collection of your model class, then populate your dictionary-of-dictionaries from that colletion. Or, (2) read the CSV "manually" with CsvHelper's CsvReader and populate your dictionary-of-dictionaries directly with the data read from the CSV with CsvReader. For writing, it would be the reverse process: either creating a collection of the model class and then serializing the collection to CSV, or writing the dictionary-of-dictionaries "manually" using CsvWriter. –  Sep 06 '22 at 09:32
  • For the first approach (i.e. using a model class that reflects the CSV structure), you might need to at least explicitly map the first column/field of the CSV to the respective model class property (https://joshclose.github.io/CsvHelper/examples/configuration/). For my 2nd suggested approach, the CsvHelper documentation offers a handy example of how to consume CSV without using a model class: https://joshclose.github.io/CsvHelper/examples/reading/reading-by-hand/ –  Sep 06 '22 at 09:46
  • 1.May you give more detail of making a model class with unknown size of column? I find an answer in https://stackoverflow.com/questions/42134421/dynamic-creation-of-columns-using-csvhelper. Do you have another suggest? 2. Does manually method comsume more time than model class method? – sdrest Sep 06 '22 at 10:29
  • 1
    If you don't know in advance how many columns a CSV file has, then it is in my opinion better to directly read the CSV records with the help of CsvReader without defining a model class. In my previous comment, i gave a link of an example in the CsvHelper doc that illustrates how to read records from a CSV. –  Sep 06 '22 at 11:22
  • Is the header for the first, key, column actually missing, or just empty? Might you [edit] your question to share a sample of your CSV as **text** rather than as a screen shot? On stack overflow images should not be used for textual content, see [*Discourage screenshots of code and/or errors*](https://meta.stackoverflow.com/a/307500) and [*Why not upload images of code on SO when asking a question*](https://meta.stackoverflow.com/a/285557) for why. For instructions on formatting see *[How do I format my code blocks?](https://meta.stackexchange.com/q/22186)* which also applies to CSV text. – dbc Sep 07 '22 at 15:56
  • One thing to keep in mind: `Dictionary – dbc Sep 07 '22 at 16:02

1 Answers1

1

Except for @dbc comment that the order of the items may change due to the unordered nature of Dictionary<TKey, TValue>, this should work.

void Main()
{
    var lookup_table = new Dictionary<string, Dictionary<string, string>>();
    
    using (var reader = new StringReader(",Header1,Header2,Header3\nKey1,value11,value12,value13\nKey2,value21,value22,value23"))
    using (var csv = new CsvReader(reader, CultureInfo.InvariantCulture))
    {       
        csv.Read();
        csv.ReadHeader();
        
        var headerLength = csv.Context.Reader.HeaderRecord.Length;
        var header = csv.Context.Reader.HeaderRecord;
        
        while (csv.Read())
        {
            var key = csv.GetField(0);
            lookup_table.Add(key, new Dictionary<string, string>());
            
            for (int i = 1; i < headerLength; i++)
            {
                lookup_table[key][header[i]] = csv.GetField(i);
            }
        }
    }
    
    using (var csv = new CsvWriter(Console.Out, CultureInfo.InvariantCulture))
    {
        var headers = lookup_table.First().Value.Keys.ToList();
        
        csv.WriteField(string.Empty);
        
        foreach (var header in headers)
        {
            csv.WriteField(header);
        }
        
        csv.NextRecord();
        
        foreach (KeyValuePair<string, Dictionary<string, string>> entry in lookup_table)
        {
            csv.WriteField(entry.Key);
            
            for (int i = 0; i < headers.Count; i++)
            {
                csv.WriteField(entry.Value[headers[i]]);
            }
            
            csv.NextRecord();
        }
    }
}
David Specht
  • 7,784
  • 1
  • 22
  • 30