CSV to DataTable in VB.Net or C#


Overview

The following short example will show how to load a CSV file and populate a DataTable. Sebastien Lorien has a time tested fast CSV reader library available both by source code and via Nuget that will do most of the heavy lifting with CSV for us (because why re-create the wheel?).

Here are the links you'll need to get the CSV reader library, you will want to either add the Nuget package to your project or download the source, compile it to the version of the framework you're using and then reference it (the Nuget package is running 2.0/3.5 currently, I've compiled against 4.0 and 4.5 without issues):

In this example I have the filename as an input like you do, you could also have a string, the stream, whatever you need:

VB.Net

        ''' <summary>
        ''' Uses Lumenworks Fast CSV reader to load a DataTable
        ''' </summary>
        ''' <param name="fileName"></param>
        Public Function CsvToDataTable(fileName As String) As DataTable
            Dim data As String = My.Computer.FileSystem.ReadAllText(fileName)
            Dim dt As New DataTable
    
            Using sr As New StringReader(data)
                ' The true indicates it has header values which can be used to access fields by their name, switch to
                ' false if the CSV doesn't have them
                Using csv As New LumenWorks.Framework.IO.Csv.CsvReader(sr, True)
                    dt.Load(csv)
                End Using
    
                sr.Close()
    
            End Using
    
            Return dt
    
        End Function

C#

/// <summary>
/// Uses Lumenworks Fast CSV reader to load a DataTable
/// </summary>
/// <param name="fileName"></param>
/// <returns></returns>
/// <remarks></remarks>
public DataTable CsvToDataTable(string fileName)
{
    string data = System.IO.File.ReadAllText(fileName);
    var dt = new DataTable();

    using (StringReader sr = new StringReader(data))
    {
        // The true indicates it has header values which can be used to access fields by their name, switch to
        // false if the CSV doesn't have them
        using (var csv = new LumenWorks.Framework.IO.Csv.CsvReader(sr, true))
        {
            dt.Load(csv);
        }
        
        sr.Close();
    }

    return dt;
}

Leave a comment

Please note that we won't show your email to others, or use it for sending unwanted emails. We will only use it to render your Gravatar image and to validate you as a real person.