Pull separate columns from .csv into separate arrays in c#

Question

Background on this project. It started as a simple homework assignment that required me to store 5 zip codes and their corresponding cities. When a user puts a Zip code in a textbox, a corresponding city is returned, and likewise the opposite can be done. I wrote the code to return these values, but then I decided I wanted to store ALL zip codes and their corresponding Cities in an external .csv, and store those values in arrays and run the code off that because if its worth doing, its worth overdoing! To clarify, this is no longer for homework, just to learn more about using external files in C#.

In the following code, I have called to open the file successfully, now I just need help in figuring out how to pull the data that is stored in two separate columns (one for city, one for zip code) and store them in two arrays to be acted upon by the for loop. Here is the code I have now. You can see how I have previously stored the other values in arrays and pulled them out:

public partial class Form1 : Form
{
    public Form1()
    {
        InitializeComponent();
    }

    private void btnConvert2City_Click(object sender, EventArgs e)
    {
        try
        {
            string dir = System.IO.Path.GetDirectoryName(
                System.Reflection.Assembly.GetExecutingAssembly().Location);

            string path = dir + @"\zip_code_database_edited.csv";
            var open = new StreamReader(File.OpenRead(path));

            int EnteredZipcode = Convert.ToInt32(txtZipcode.Text.Trim());
            string result = "No Cities Found";

            string[] Cities = new String[5] { "FLINTSTONE", "JAMAICA", "SCHENECTADY", "COTTONDALE", "CINCINNATI" };
            int[] Zipcode = new int[5] { 30725, 11432, 12345, 35453, 45263 };

            for (int i = 0; i <= Zipcode.Length - 1; i++)
            {
                if (Zipcode[i] == EnteredZipcode)
                {
                    result = Cities[i];
                    break;
                }
            }
            string DisplayState = result;
            txtCity.Text = DisplayState;
        }
        catch (FormatException)
        {
            MessageBox.Show("Input must be numeric value.");
        }
        catch (OverflowException)
        {
            MessageBox.Show("Zipcode to long. Please Re-enter");
        }
    }

    private void btnConvert2Zipcode_Click(object sender, EventArgs e)
    {
        string dir = System.IO.Path.GetDirectoryName(
                System.Reflection.Assembly.GetExecutingAssembly().Location);

        string path = dir + @"\zip_code_database_edited.csv";
        var open = new StreamReader(File.OpenRead(path));

        String EnteredCity = txtCity.Text.ToUpper();
        string result = "No Zipcode Found";

        string[] Cities = new String[5] { "FLINTSTONE", "JAMAICA", "SCHENECTADY", "COTTONDALE", "CINCINNATI" };
        int[] Zipcode = new int[5] { 30725, 11432, 12345, 35453, 45263 };

        for (int i = 0; i <= Cities.Length - 1; i++)
        {
            if (Cities[i] == EnteredCity)
            {
                result = Convert.ToString(Zipcode[i]);
                break;
            }
        }           
        string DisplayZip = result;
        txtZipcode.Text = DisplayZip;
    }       
}

The following data is a snippet of what the data in my excel .csv looks like:

zip,primary_city
44273,Seville
44274,Sharon Center
44275,Spencer
44276,Sterling
44278,Tallmadge
44280,Valley City
44281,Wadsworth
44282,Wadsworth
44285,Wayland

And so on for about 46,000 rows.

How can I pull the zip and the primary_city into two separate arrays (I'm guessing with some ".Split "," "line) that my for-loop can operate on?

Also, if there are better ways to go about this, please let me know (but be sure to leave an explanation as I want to understand where you are coming from).

Answer 1

Don't create two separate array.Create a separate class for city

class City
{
    public string Name{get;set;}
    public int ZipCode{get;set;}
}

---

Now to read the data from that csv file

List<City> cities=File.ReadAllLines(path)
                      .Select(x=>new City
                         {
                              ZipCode=int.Parse(x.Split(',')[0]),
                              Name=x.Split(',')[1]
                         }).ToList<City>();

Or you can do this

   List<City> cities=new List<City>();
   foreach(String s in File.ReadAllLines(path))
   {
       City temp=new City();
       temp.ZipCode=int.Parse(s.Split(',')[0]);
        temp.Name=s.Split(',')[1];
       cities.Add(temp);
   }

Answer 2

You can try this:

    string dir = System.IO.Path.GetDirectoryName(
            System.Reflection.Assembly.GetExecutingAssembly().Location);

    string path = dir + @"\zip_code_database_edited.csv";
    var open = new StreamReader(File.OpenRead(path));
    var cities = new HashList<string>();
    var zipCodes = new HashList<int>();
    var zipAndCity = new string[2];
    string line = string.Empty;
    using (open)
{
        while ((line = reader.ReadLine()) != null)
        {
            zipAndCity = line.Split(",");
                zipCodes.Add(int.Parse(zipAndCity[0]));
                cities.Add(zipAndCity[1]);   
        }

}

Answer 3

I am posting this answer having learned much more about C# since I posted this question. When reading a CSV, there are better options than String.Split().

The .NET Framework already has a built-in dedicated CSV parser called TextFieldParser.

It's located in the Microsoft.VisualBasic.FileIO namespace.

Not only are there many edge cases that String.Split() is not properly equipped to handle, but it's also much slower to use StreamReader.