How to Read a CSV File into a .Net Datatable

How to read a CSV file into a .NET Datatable

Here's an excellent class that will copy CSV data into a datatable using the structure of the data to create the DataTable:

A portable and efficient generic parser for flat files

It's easy to configure and easy to use. I urge you to take a look.

Populating a dataset from a CSV file

You need to run a SELECT statement against the CSV file to fill the dataset:

Edit: here's some sample code from http://carllbrown.blogspot.co.uk/2007/09/populate-dataset-from-csv-delimited_18.html

string FileName = ...
OleDbConnection conn = new OleDbConnection
       ("Provider=Microsoft.Jet.OleDb.4.0; Data Source = " + 
         Path.GetDirectoryName(FileName) + 
         "; Extended Properties = \"Text;HDR=YES;FMT=Delimited\"");

conn.Open();

OleDbDataAdapter adapter = new OleDbDataAdapter
       ("SELECT * FROM " + Path.GetFileName(FileName), conn);

DataSet ds = new DataSet("Temp");
adapter.Fill(ds);

conn.Close();

C# Reading CSV to DataTable and Invoke Rows/Columns

(Adding as another answer just to make it uncluttered)

void ProcessMyFiles(string folderName)
{
    List<MyData> d = new List<MyData>();
    var files = Directory.GetFiles(folderName);
    foreach (var file in files)
    {
        OpenAndParse(file, d);
    }

    string[] headers = GetHeaders(files[0]);
    DataGridView dgv = new DataGridView {Dock=DockStyle.Fill};
    dgv.DataSource = d;
    dgv.ColumnAdded += (sender, e) => {e.Column.HeaderText = headers[e.Column.Index];};

    Form f = new Form();
    f.Controls.Add(dgv);
    f.Show();
}

string[] GetHeaders(string filename)
{
    var lines = File.ReadAllLines(filename);
    var parsed = lines.Select(l => l.Split(';')).ToArray();
    return new string[] { parsed[0][0], parsed[1][0], parsed[2][0], parsed[1][0] };
}

void OpenAndParse(string filename, List<MyData> d)
{
    var lines = File.ReadAllLines(filename);
    var parsed = lines.Select(l => l.Split(';')).ToArray();
    var data = new MyData
    {
        Col1 = parsed[0][1],
        Col2 = parsed[1][1],
        Col3 = parsed[2][1],
        Col4 = parsed[1][2]
    };
    d.Add(data);
}

public class MyData
{
    public string Col1 { get; set; }
    public string Col2 { get; set; }
    public string Col3 { get; set; }
    public string Col4 { get; set; }
}

How to Read CSV file

There is a class TextFieldParser located in Microsoft.VisualBasic.FileIO namespace.

This class can be configured like:

using TextFieldParser parser = new TextFieldParser(stream)
{
    Delimiters = new[] { "," },
    HasFieldsEnclosedInQuotes = true,
    TextFieldType = FieldType.Delimited,
    TrimWhiteSpace = true,
};

Usage of the parser:

while (!parser.EndOfData)
{
    var fields = parser.ReadFields() ?? throw new InvalidOperationException("Parser unexpectedly returned no data");
    
    // your code 
}

The benefit of using a CSV parser as mentioned is:
You can process line by line

Memory consumption is extremly low as you are reading the file sequentially instead of loading everything into memory.

Creating a DataTable from CSV File

I always use this CSV library for reading CSV files in through C# its always worked good for me.

http://www.codeproject.com/KB/database/CsvReader.aspx

Heres an example of reading a CSF file using the library

using System.IO;
using LumenWorks.Framework.IO.Csv;

void ReadCsv()
{
    // open the file "data.csv" which is a CSV file with headers
    using (CsvReader csv =
           new CsvReader(new StreamReader("data.csv"), true))
    {
        int fieldCount = csv.FieldCount;
        string[] headers = csv.GetFieldHeaders();

        while (csv.ReadNextRecord())
        {
            for (int i = 0; i < fieldCount; i++)
                Console.Write(string.Format("{0} = {1};",
                              headers[i], csv[i]));

            Console.WriteLine();
        }
    }
}

Read .csv to datatable and fill a datagridview

Your code worked for me as it is.

I just added one line to the datasource assignment after looking inside the dataset, I saw just one table is inside with name "Table" so I assigned the datamember of the datagridview:

        dataGridView1.DataSource = ds;
        dataGridView1.DataMember = "Table";

Anyway if I used ';' separator, all the values were in one column... With ',' comma separator it works ok.

The complete code of the form:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            string FileName = @"C:\mydir\testcsv.csv";

            OleDbConnection conn = new OleDbConnection
                   ("Provider=Microsoft.Jet.OleDb.4.0; Data Source = " +
                     Path.GetDirectoryName(FileName) +
                     "; Extended Properties = \"Text;HDR=YES;FMT=Delimited\"");

            conn.Open();

            OleDbDataAdapter adapter = new OleDbDataAdapter
                   ("SELECT * FROM " + Path.GetFileName(FileName), conn);

            DataSet ds = new DataSet("Temp");
            adapter.Fill(ds);

            conn.Close();

            dataGridView1.DataSource = ds;
            dataGridView1.DataMember = "Table";
        }
    }
}

Contents of the csv file:

abc,123
def,456
ijk,789
lmn,111213

For semicolon delimited files you need to add an ini file in your folder containing the csv file. How to do it exactly is described here:

How to specify the delimiter when importing CSV files via OLEDB in C#

For decimal delimiter symbol you have to add the

DecimalSymbol

directive to your Jet ini file.

See the full ini file capabilities documented in MSDN (https://msdn.microsoft.com/en-us/library/ms709353(v=vs.85).aspx)

---

Related Topics