CSV-file values to List

Question

I have a CSV file that I want some values from. One problem is that I don't know how many columns the file has. The number can be different every time I get a new CSV file. It will always have columns and rows with values. I will get it from a normal excel-file.

I want the method to return a List<List>.

ListA(FirstName, LastName, PhoneNumber... and so on) here I don't know how many items ListA will have. It can be different every time.

Inside ListA I want lists of persons like this:

ListA[FirstName] = List1(Zlatan, Lionel, Anders.....)

ListA[LastName] = List2(Ibrahimovic, Messi, Svensson.....) .. and so on.

Answer 1

You could create a class Person

class person {
    private string FirstName;
    private string LastName;
    // others
}

Open the File and split each row in the file with the String.Split()-Method then convert each value and create Objects, which you can add to a List.

List<Person> persons = new List<Person>();
persons.Add(personFromFile);

Thats a pretty short solution but it works

Edit: Variable Fields per Row

If thats the case you could use a List<string[]> stringArraylist; and then add the results of the String.Split()-Method to it.

List<string[]> stringArraylist;
stringArraylist = new List<string[]>();
stringArraylist.Add("Andrew;Pearson;...;lololo;".Split(';'));

Is that more of what you wanted?

Answer 2

There are a lot of questions on SO that deal with parsing CSV files. See here for one: Reading CSV files in C#. I am fairly certain there are some solutions built in to .NET, though I can't recall what they are at the moment. (@ZoharPeled suggested TextFieldParser)

Most of the parsing solutions with give you a collection of rows where each item is a collection of columns. So assuming you have something like a IEnumerable<IList<string>>, you could create a class and use LINQ queries to get what you need:

public class CSVColumns 
{
    public IEnumerable<IList<string>> CSVContents { get; private set; }

    public CSVColumns(IEnumerable<IList<string>> csvcontents)
    {
        this.CSVContents = csvcontents;
    }

    public List<string> FirstNames
    {
        get { return GetColumn("FirstName"); }
    }

    public List<string> LastNames
    {
        get { return GetColumn("LastName"); }
    }

    /// <summary>
    /// Gets a collection of the column data based on the name of the column
    /// from the header row.
    /// </summary>
    public List<string> GetColumn(string columnname) 
    {
        //Get the index of the column with the name
        var firstrow = CSVContents.ElementAtOrDefault(0);

        if (firstrow != null)
        {
            int index = -1;
            foreach (string s in firstrow)
            {
                index++;
                if (s == columnname)
                {
                    return GetColumn(index, true);
                }
            }
        }

        return new List<string>();
    }

    /// <summary>
    /// Gets all items from a specific column number but skips the
    /// header row if needed.
    /// </summary>
    public List<string> GetColumn(int index, bool hasHeaderRow = true) 
    {
        IEnumerable<IList<string>> columns = CSVContents;

        if (hasHeaderRow)
            columns = CSVContents.Skip(1);

        return columns.Select(list =>
            {
                try
                {
                    return list[index];
                }
                catch (IndexOutOfRangeException ex)
                {
                    return "";
                }
            }
        ).ToList();
    }
}

Answer 3

I finally got a solution and it's working for me. My friend made it so all creed to him. No user here on stackoverflow so I post it instead.

    private List<Attributes> LoadCsv()
    {
        string filename = @"C:\Desktop\demo.csv";
        // Get the file's text.
        string whole_file = System.IO.File.ReadAllText(filename);

        // Split into lines.
        whole_file = whole_file.Replace('\n', '\r');
        string[] lines = whole_file.Split(new char[] { '\r' },
            StringSplitOptions.RemoveEmptyEntries);

        // See how many rows and columns there are.
        int num_rows = lines.Length;
        int num_cols = lines[0].Split(';').Length;

        // Allocate the data array.
        string[,] values = new string[num_rows, num_cols];

        // Load the array.
        for (int r = 0; r < num_rows; r++)
        {
            string[] line_r = lines[r].Split(';');
            for (int c = 0; c < num_cols; c++)
            {
                values[r, c] = line_r[c];
            }
        }

        var attr = new List<Attributes>();
        for (var r = 0; r < num_rows; r++)
        {
            if (r == 0)
            {
                for (var c = 0; c < num_cols; c++)
                {
                    attr.Add(new Attributes());
                    attr[c].Name = values[r, c];

                    attr[c].Value = new List<String>();
                }
            }
            else
            {
                for (var b = 0; b < num_cols; b++)
                {
                    var input = values[r, b];
                    attr[b].Value.Add(input);
                }
            }
        }
        // Return the values.
        return attr;
    }