How could I build a dictionary in C# from a CSV file where the keys are in one column and the values in other?

Question

I have an excel file (separated with with commas) two columns, City and Country. Column A has countries and column B has cities. Each row therefore has a country and a city located in this country.

City  Country 

Madrid    Spain    

Barcelona Spain    

Paris     France   

Valencia  Spain    

Rome      Italy    

Marseille  France   

Florence   Italy    

I am wondering a way to read this excel in C# in a Dictionary> type where the key will be my country and the values the city, so after reading it I will have the following:

{
 "Spain":  ["Madrid", "Barcelona", "Valencia"], 
 "France": ["Paris", "Marseille"], 
 "Italy":  ["Rome", "Florence"]
}

What I have tried so far is creating this class:

class ReadCountryCityFile
{
    Dictionary<string, List<string>> countrycitydict{ get; }
    // constructor
    public ReadCountryCityFile()
    {
        countrycitydict= new Dictionary<string, List<string>>();
    }
    public Dictionary<string, List<string>> ReadFile(string path)
    {
        using (var reader = new StreamReader(path))
        {
            List<string> listcountry = new List<string>();
            List<string> listcity = new List<string>();
            while (!reader.EndOfStream)
                {
                    var line = reader.ReadLine();
                    if (line != "Country;City")
                    {
                        List<string> citieslist = new List<string>();
                        var values = line.Split(';');
                        citieslist .Add(values[0]);
                        string country= values[1];
                        countrycitydict[intents] = citieslist ;
                    }
                }
                return countrycitydict;
        }
   }

But countrydict is not as expected. How could I do it?

How could I solved it if intead of

City Country

Madrid Spain

I had

City   Country

Madrid    Spain
Valencia   

Answer 1

Providing that you use a simple CSV (with no quotations) you can try Linq:

 Dictionary<string, string[]> result = File
   .ReadLines(@"c:\MyFile.csv")
   .Where(line => !string.IsNullOrWhiteSpace(line)) // To be on the safe side
   .Skip(1)  // If we want to skip the header (the very 1st line)
   .Select(line => line.Split(';')) //TODO: put the right separator here
   .GroupBy(items => items[0].Trim(), 
            items => items[1])
   .ToDictionary(chunk => chunk.Key, 
                 chunk => chunk.ToArray());

Edit: If you want (see comments below) Dictionary<string, string> (not Dictionary<string, string[]>) e.g. you want

   ...
  {"Spain",  "Madrid\r\nBarcelona\r\nValencia"}, 
   ...

instead of ... {"Spain", ["Madrid", "Barcelona", "Valencia"]} ...

you can modify the last .ToDictionary into:

   .ToDictionary(chunk => chunk.Key, 
                 chunk => string.Join(Environment.NewLine, chunk));