How could I convert excel file columns in a dictionary?

Question

I have an Excel file (csv separated with ";") with n columns, right now it doesn't matter the number of columns, just let's say that n is 6.

There are two out of these six columns, let's call them Column_A and Column_B that I want to convert into a dictionary (Dictionary<string, List<string>>) where the key will be the column name and the values are a list formed by the values in the cell, which will be text.

As instance, if I had the following Excel file:

I would like to get a dictionary with two keys, Column_A and Column_B where its values are a list and each item on the list is its correspondent value:

desired_dictionary = {"column_A": ["a1", "a2", "a3. a31", "a4"], "column_B" = ["b1", "b2", "b3", "b4. b41"]}

Is there a way to do this?

I was using StreamReader (bad done) but I was not getting the desired results, so asking I figured out that I should use another thing — Marisa, Mar 04 '19 at 14:35

score 0 · Answer 1 · answered Mar 04 '19 at 15:49

0

I hope it helps: The Dictionary must be composed by key and value. The key can´t repeat, and in value you can save an array for example to save the values foreach column. Key will represents a number of column where A=1, B=2 , c=3...

 Dictionary<int,Array> dict = new Dictionary<int, Array>();
            string[] Row = new string[4];
            Row[0] = "Data1";
            Row[1] = "Data2";
            Row[2] = "Data3";
            Row[3] = "Data4";
            dict.Add(1, Row);

            foreach (KeyValuePair<int, Array> item in dict)
            {
                Console.WriteLine("Key: {0}, Value: {1} {2} {3} {4}", item.Key, item.Value.GetValue(0), item.Value.GetValue(1), item.Value.GetValue(2), item.Value.GetValue(3));
            }

answered Mar 04 '19 at 15:49

Aaron Pan Vega

174
7

Hi Aaron Pan Vega. What I don't see is how can I do this programmatically with a CSV file. – Marisa Mar 04 '19 at 17:09
Hi, you can use interop to create and excel object and load your csv inside. In this related thread you have information about it: https://stackoverflow.com/questions/7244971/how-do-i-import-from-excel-to-a-dataset-using-microsoft-office-interop-excel , and officially: https://learn.microsoft.com/es-es/dotnet/csharp/programming-guide/interop/how-to-access-office-onterop-objects – Aaron Pan Vega Mar 05 '19 at 07:58

How could I convert excel file columns in a dictionary?

1 Answers1