Read CSV file in DataGridView

Question

I want to read a csv-file into a Datagridview. I would like to have a class and a function which reads the csv like this one:

class Import
{
    public DataTable readCSV(string filePath)
    {            
        DataTable dt = new DataTable();
        using (StreamReader sr = new StreamReader(filePath))
        {
            string strLine = sr.ReadLine();     

            string[] strArray = strLine.Split(';');

            foreach (string value in strArray)
            {
                dt.Columns.Add(value.Trim());
            } 
            DataRow dr = dt.NewRow();

            while (sr.Peek() >= 0)
            {
                strLine = sr.ReadLine();
                strArray = strLine.Split(';');
                dt.Rows.Add(strArray);
            }
        }
        return dt;
     }   
}

and call it:

Import imp = new Import();

DataTable table = imp.readCSV(filePath);
foreach(DataRow row in table.Rows)
{
 dataGridView.Rows.Add(row);
}

Result of this is-> rows are created but there is no data in the cells!!

Answer 1

First solution using a litle bit of linq

public DataTable readCSV(string filePath)
{
    var dt = new DataTable();
    // Creating the columns
    File.ReadLines(filePath).Take(1)
        .SelectMany(x => x.Split(new[] { ';' }, StringSplitOptions.RemoveEmptyEntries))
        .ToList()
        .ForEach(x => dt.Columns.Add(x.Trim()));

    // Adding the rows
    File.ReadLines(filePath).Skip(1)
        .Select(x => x.Split(';'))
        .ToList()
        .ForEach(line => dt.Rows.Add(line));
    return dt;
}

Below another version using foreach loop

public DataTable readCSV(string filePath)
{
    var dt = new DataTable();
    // Creating the columns
    foreach(var headerLine in File.ReadLines(filePath).Take(1))
    {
        foreach(var headerItem in headerLine.Split(new[] { ';' }, StringSplitOptions.RemoveEmptyEntries))
        {
            dt.Columns.Add(headerItem.Trim());
        }
    }

    // Adding the rows
    foreach(var line in File.ReadLines(filePath).Skip(1))
    {
        dt.Rows.Add(x.Split(';'));
    }
    return dt;
}

First we use the File.ReadLines, that returns an IEnumerable that is a colletion of lines. We use Take(1), to get just the first row, that should be the header, and then we use SelectMany that will transform the array of string returned from the Split method in a single list, so we call ToList and we can now use ForEach method to add Columns in DataTable.

To add the rows, we still use File.ReadLines, but now we Skip(1), this skip the header line, now we are going to use Select, to create a Collection<Collection<string>>, then again call ToList, and finally call ForEach to add the row in DataTable. File.ReadLines is available in .NET 4.0.

Obs.: File.ReadLines doesn't read all lines, it returns a IEnumerable, and lines are lazy evaluated, so just the first line will be loaded two times.

See the MSDN remarks

The ReadLines and ReadAllLines methods differ as follows: When you use ReadLines, you can start enumerating the collection of strings before the whole collection is returned; when you use ReadAllLines, you must wait for the whole array of strings be returned before you can access the array. Therefore, when you are working with very large files, ReadLines can be more efficient.

You can use the ReadLines method to do the following:

Perform LINQ to Objects queries on a file to obtain a filtered set of its lines.

Write the returned collection of lines to a file with the File.WriteAllLines(String, IEnumerable) method, or append them to an existing file with the File.AppendAllLines(String, IEnumerable) method.

Create an immediately populated instance of a collection that takes an IEnumerable collection of strings for its constructor, such as a IList or a Queue.

This method uses UTF8 for the encoding value.

If you still have any doubt look this answer: What is the difference between File.ReadLines() and File.ReadAllLines()?

Second solution using CsvHelper package

First, install this nuget package

PM> Install-Package CsvHelper 

For a given CSV, we should create a class to represent it

CSV File

Name;Age;Birthdate;Working
Alberto Monteiro;25;01/01/1990;true
Other Person;5;01/01/2010;false

The class model is

public class Person
{
    public string Name { get; set; }
    public int Age { get; set; }
    public DateTime Birthdate { get; set; }
    public bool Working { get; set; }
}

Now lets use CsvReader to build the DataTable

public DataTable readCSV(string filePath)
{
    var dt = new DataTable();

    var csv = new CsvReader(new StreamReader(filePath));
    // Creating the columns
    typeof(Person).GetProperties().Select(p => p.Name).ToList().ForEach(x => dt.Columns.Add(x));

    // Adding the rows
    csv.GetRecords<Person>().ToList.ForEach(line => dt.Rows.Add(line.Name, line.Age, line.Birthdate, line.Working));
    return dt;
}

To create columns in DataTable e use a bit of reflection, and then use the method GetRecords to add rows in DataTabble