How to import data from .csv text files with different column headings into memory

Question

I am trying to read in data from a CSV file one line at a time. The first line, column headings, determines the data type in each row. I already know all the data belongs to a class I have in my project called UserModel, but I am trying to do this with out hard coding the data to UserModel fields. I am able to read the first couple of lines, and create the first instance of the user model correctly. But things fall apart when I try to read the third line. I am using a Dictionary, and I am trying to use the instance of the created Dictionary when I read the first couple of lines. Meaning use the order of keys in the Dictionary instance, and just update the values with the next set of data, but I can not figure out why the set of data just does not line up with the right keys.

I got some great code here and used it. DictionaryToObject() method is one of them. I think I am using more loops than I need to, and do not know enough to use some other way of accessing some of the data structures like string array, list, and the Dictionary that I have. I am a novice, and may have used the wrong data structure and or even some unnecessary code in my attempt to get the result I want.

public class MyData 
{
    BindingList<UserModel> users = new BindingList<UserModel>();
    UserModel currentUser = new UserModel();

    public BindingList<UserModel> GetUsers()
    {

        string[] lines = File.ReadAllLines("StandardDataSet.csv");
        // Add error handing to make sure all lines have four enteris 
        foreach (string line in lines.Skip(1))
        {
            //I am not sure how to handle the first line that is a header
            string [] values = line.Split(',').Select(x => x.Trim()).ToArray();
            currentUser.FirstName = values[0];
            currentUser.LastName = values[1];
            int.TryParse(values[2], out int age);
            currentUser.Age = age;
            bool.TryParse(values[3], out bool alive);
            currentUser.IsAlive = alive;
            users.Add(currentUser);
        }
        return users;
    }
private static T DictionaryToObject<T>(IDictionary<string, object> dict) where T : new()
    {
        T t = new T();
        PropertyInfo[] properties = t.GetType().GetProperties();

        foreach (PropertyInfo property in properties)
        {
            if (!dict.Any(x => x.Key.Equals(property.Name, StringComparison.InvariantCultureIgnoreCase)))
                continue;
            KeyValuePair<string, object> item = dict.First(x => x.Key.Equals(property.Name, StringComparison.InvariantCultureIgnoreCase));
            Type tPropertyType = t.GetType().GetProperty(property.Name).PropertyType;
            Type newT = Nullable.GetUnderlyingType(tPropertyType) ?? tPropertyType;
            //if (item.Value is '0') Convert.ToBoolean(Convert.ToInt32(item.Value));
            object newA = Convert.ChangeType(item.Value, newT);
            t.GetType().GetProperty(property.Name).SetValue(t, newA, null);
        }
        return t;
    }
public BindingList<UserModel> GetUsersAdvanced()
    {
        users = new BindingList<UserModel>();
        var dict= new Dictionary<string,object>();
        string[] lines = File.ReadAllLines("AdvancedDataSet.csv");
        var header = lines.First().Split(',');
        var data = lines.Skip(1).First().Split(',');


        for (int i = 0; i < header.Length; i++)
            {
                if (data[i] == "0" || data[i] == "1")
                    {
                        Boolean IsAlive = Extensions.ToBoolean(data[i]);
                        dict.Add(header[i], IsAlive);
                    }
                else
                //if (data[i]!= "0" || data[i]!="1")
                dict.Add(header[i], data[i]);
        }   
        currentUser = DictionaryToObject<UserModel>(dict);
        var dictCopy = new Dictionary<string, object>(dict);
        var dictConcurrent = new ConcurrentDictionary<string, object>(dict);

    foreach (var line in lines.Skip(1))
    {
    if (line != null)  data = line.Split(',');

                foreach (var key in dict.Keys)
                {
                int i = 0;
                if (data[i] == "0" || data[i] == "1")
                    {
                        Boolean IsAlive = Extensions.ToBoolean(data[i]);
                        dictCopy[key] = IsAlive;
                        i++;
                        continue;
                    }
                    else
                    dictCopy[key] = data[i];
                    i++;  
                 }
                 currentUser = DictionaryToObject<UserModel>(dictCopy);
                 users.Add(currentUser);
                 currentUser = new UserModel();

    }
 return users;
}

There are many tools and libraries which will do all that for you...and do it quite well — Ňɏssa Pøngjǣrdenlarp, May 30 '19 at 23:27
[Stop writing your own CSV parser](http://www.secretgeek.net/csv_trouble); the format is more complicated than you realize. There are plenty of [working CSV parsers](https://stackoverflow.com/questions/2081418/) ready for you to drop in to your application. — Dour High Arch, May 30 '19 at 23:46

score 0 · Accepted Answer · answered May 31 '19 at 00:28

As the comments from people says, I also recommend to use existing libraries. They typically also support such data mapping to object and data conversion that you do in your code. so, try one or two first.

How about:

By the way, in terms of the order of items in Dictionary, you can read the reason here

For purposes of enumeration, each item in the dictionary is treated as a KeyValuePair structure representing a value and its key. The order in which the items are returned is undefined.

So, what about keeping the column headers in a list? In this way, you can keep the order. The code would be something like below.

But please take it just as an example for ordered items. Again, try some libraries first, then you'll see cooler functionalities and get tidier code with those.

public BindingList<UserModel> GetUsersAdvanced()
{
    users = new BindingList<UserModel>();
    string[] lines = File.ReadAllLines("AdvancedDataSet.csv");
    var cols = lines.First().Split(',').ToList();

    // validate column name duplication here!

    foreach (var line in lines.Skip(1))
    {
        if (line != null)  data = line.Split(',');

        for (var i = 0; i < cols.Count(); i++)
        {
            var key = cols[i];
            var dict = new Dictionary<string, object>();

            if (data[i] == "0" || data[i] == "1")
            {
                Boolean IsAlive = Extensions.ToBoolean(data[i]);
                dict.Add(key, IsAlive);
            }
            else
                dict.Add(key, data[i]);

            var currentUser = DictionaryToObject<UserModel>(dict);
            users.Add(currentUser);
        }

        return users;
    }
}

thank you so much. I was doing this to learn, and was hoping that someone would respond. — Shahin, May 31 '19 at 02:34

How to import data from .csv text files with different column headings into memory

1 Answers1

Linked