How to Read CSV Into C# Class

Question

I have the following class

    public class Department
    {
        public string id { get; set; }
        public string name { get; set; }
    }

I have a csv file that contains a list of ids, names. I want to read the ids and create a url and the name and create a json body to post to the url. I have the following code but I'm not sure how to split the csv lines into id and name.

        try
        {
            //Open File and Read
            var csv = new List<Department>();
            var lines = System.IO.File.ReadAllLines(@"C:\file.csv");
            foreach (string line in lines)
                // Add Code Here
                string url = "https://www.test.com/department/" + id;
                try
                {
                    HttpWebRequest request = (HttpWebRequest)HttpWebRequest.Create(url);
                    request.Headers.Add("Authorization", "77ba42a2d0511e109068bcb9857d035da27a4eae");
                    request.Method = "PUT";
                    HttpWebResponse response = (HttpWebResponse)request.GetResponse();
                    // Add Code for Json Body
                }
         }

Answer 1

I ran into CSV parsing problems, even with TextFieldParser. Your CSV probably doesn't have embedded newline characters, but whenever you have a CSV with arbitrary text in some columns, you can have new lines in the middle of a row. Using ReadAllLines will never work properly.

Rather than import some rather large library, I wrote a function to read one row from a CSV stream. This reads every cell as a String, and it strips quotes. That might mean you cannot tell "2" from 2 in the file, but usually that's not necessary. However, it handles commas and newlines in quoted cells, which are what generally screw up the simple approaches. It also handles quotes inside quotes, so "So she said, ""Hi!""" will be parsed correctly as one cell as the text So she said, "Hi!"

    private static String[] ReadCSVRow( TextReader csv )
    {
        int ch;
        Boolean bInQuote = false;
        StringBuilder builder = new StringBuilder(128);
        List<String> columns = new List<String>();

        while ((ch = csv.Read()) != -1)
        {
            if (builder.Length == 0 && !bInQuote)
            {
                if (ch == '\"')
                    bInQuote = true;
                else if (ch == ',')
                {
                    // add an empty string
                    columns.Add("");
                }
                else if (ch == '\n')
                {
                    // return current columns
                    columns.Add("");
                    return columns.ToArray();
                }
                else if (ch != '\r')
                {
                    // start a new column
                    builder.Append((char)ch);
                }
            }
            else if (bInQuote)
            {
                // we have started our column with a quote
                if (ch == '\"')
                {
                    // peek to see if this is double.
                    int next = csv.Peek();
                    if (next == '\"')
                    {
                        // read it.
                        csv.Read();
                        // add it
                        builder.Append('\"');
                    }
                    else 
                    {
                        // no longer in quotes. next SHOULD be a comma or end of file.
                        bInQuote = false;
                    }
                }
                else
                {
                    builder.Append((char)ch);
                }
            }
            else
            {
                if (ch == ',')
                {
                    // add our column and go to next
                    columns.Add(builder.ToString());
                    builder.Clear();
                }
                else if (ch == '\n')
                {
                    // add column and return.
                    columns.Add(builder.ToString());
                    builder.Clear();
                    return columns.ToArray();
                }
                else if (ch != '\r')   // skip line feeds if not in quotes.
                {
                    builder.Append((char)ch);
                }
            }
        }

        //
        // we hit end of file without a new line. If we have data, return it, otherwise null.
        if (builder.Length > 0)
            columns.Add(builder.ToString());
        if (columns.Count == 0)
            return null;

        return columns.ToArray();
    }

Now, to use it, you'd do something like this:

using( TextReader reader = new StreamReader("C:\\file.csv")) {
    String[] row;
    while( (row = ReadCSVRow(reader)) != null ) {
         ...
         // first column in row[0], 2nd in row[1], etc...
    }
}

Answer 2

I usually wouldn't recommend parsing a csv with string.Split.

However, if this is a simple enough csv file, you could do something like this.

var lines = File.ReadAllLines(@"C:\file.csv");

var csv = lines.Select(x =>{
                              var parts = x.Split(',');
                              return new Department()
                                       {
                                          id = parts[0].Trim(),
                                          name = parts[1].Trim(),
                                       };
                           }).ToList();

Note, i'm not sure if these are quote strings, but if they are you could just trim the quotes as well

Dislaimer, it is better to use a dedicated CSV lib :) the reason why is csv fields can be quoted and contain commas and all sorts of shenanigans.

Full Demo Here

Answer 3

public class CurrentAlertStatus
{
    public string message_type { get; set; }
    public string seconds { get; set; }
    public string deviceid { get; set; }
    public string vin { get; set; }
    public string esn { get; set; }
    public string iccid { get; set; }

}

.....

public void ReadCsvIntoListOfObjects{
        var statuses = new List<CurrentAlertStatus>();
        var lines = File.ReadAllLines("path.csv").Skip(1);

        foreach (var line in lines)
        {
            var values = line.Split(";");
            var st = new CurrentAlertStatus();
            var props = typeof(CurrentAlertStatus).GetProperties();

            for (int i = 0; i < values.Length-1; i++)
            {
                props[i].SetValue(st, values[i]);
            }
            statuses.Add(st);
        }

}