How to read a CSV file into a .NET Datatable

CSV

.NET

Datatable

File Reading

Programming Tutorial

How to read a CSV file into a .NET Datatable

Interview Questions practice on Codemia

Over 8,000 real interview questions from top companies, searchable by company and role.

Browse interview questions

C# & .NET

Introduction

Reading a CSV file into a DataTable in .NET can be done with the built-in TextFieldParser class, manual StreamReader parsing, or the popular CsvHelper NuGet package. TextFieldParser handles quoted fields and delimiters correctly out of the box. For production code with complex CSV files (embedded commas, multiline fields, different encodings), CsvHelper is the most reliable option.

Using TextFieldParser (Built-in)

TextFieldParser is part of Microsoft.VisualBasic.FileIO and works in any .NET project:

csharp

1using Microsoft.VisualBasic.FileIO;
2using System.Data;
3
4public DataTable ReadCsvToDataTable(string filePath)
5{
6    var dataTable = new DataTable();
7
8    using (var parser = new TextFieldParser(filePath))
9    {
10        parser.TextFieldType = FieldType.Delimited;
11        parser.SetDelimiters(",");
12        parser.HasFieldsEnclosedInQuotes = true;
13
14        // Read header row
15        string[] headers = parser.ReadFields();
16        foreach (string header in headers)
17        {
18            dataTable.Columns.Add(header);
19        }
20
21        // Read data rows
22        while (!parser.EndOfData)
23        {
24            string[] fields = parser.ReadFields();
25            dataTable.Rows.Add(fields);
26        }
27    }
28
29    return dataTable;
30}

Add the Microsoft.VisualBasic reference if it is not already included. In .NET Core/.NET 5+, add the NuGet package Microsoft.VisualBasic.

Using StreamReader (Manual Parsing)

For simple CSV files without quoted fields:

csharp

1using System.Data;
2using System.IO;
3
4public DataTable ReadSimpleCsv(string filePath)
5{
6    var dataTable = new DataTable();
7
8    using (var reader = new StreamReader(filePath))
9    {
10        // Read header
11        string headerLine = reader.ReadLine();
12        if (headerLine == null) return dataTable;
13
14        string[] headers = headerLine.Split(',');
15        foreach (string header in headers)
16        {
17            dataTable.Columns.Add(header.Trim());
18        }
19
20        // Read data
21        string line;
22        while ((line = reader.ReadLine()) != null)
23        {
24            string[] values = line.Split(',');
25            DataRow row = dataTable.NewRow();
26
27            for (int i = 0; i < headers.Length && i < values.Length; i++)
28            {
29                row[i] = values[i].Trim();
30            }
31
32            dataTable.Rows.Add(row);
33        }
34    }
35
36    return dataTable;
37}

This approach breaks on fields containing commas, newlines, or quotes. Use TextFieldParser or CsvHelper for robust parsing.

Using CsvHelper (NuGet Package)

Install the package:

bash

dotnet add package CsvHelper

Read CSV into a DataTable:

csharp

1using CsvHelper;
2using CsvHelper.Configuration;
3using System.Data;
4using System.Globalization;
5using System.IO;
6
7public DataTable ReadCsvWithCsvHelper(string filePath)
8{
9    var dataTable = new DataTable();
10
11    using (var reader = new StreamReader(filePath))
12    using (var csv = new CsvReader(reader, CultureInfo.InvariantCulture))
13    {
14        using (var dataReader = new CsvDataReader(csv))
15        {
16            dataTable.Load(dataReader);
17        }
18    }
19
20    return dataTable;
21}

CsvDataReader adapts CsvHelper to the IDataReader interface, which DataTable.Load() consumes directly.

Custom Configuration

csharp

1var config = new CsvConfiguration(CultureInfo.InvariantCulture)
2{
3    Delimiter = ";",                    // Semicolon-delimited
4    HasHeaderRecord = true,             // First row is header
5    MissingFieldFound = null,           // Ignore missing fields
6    BadDataFound = null,                // Skip malformed rows
7    TrimOptions = TrimOptions.Trim,     // Trim whitespace
8    Encoding = System.Text.Encoding.UTF8
9};
10
11using (var reader = new StreamReader(filePath))
12using (var csv = new CsvReader(reader, config))
13using (var dataReader = new CsvDataReader(csv))
14{
15    dataTable.Load(dataReader);
16}

Typed Column DataTable

By default, all columns are strings. To set specific data types:

csharp

1public DataTable ReadTypedCsv(string filePath)
2{
3    var dataTable = new DataTable();
4
5    // Define columns with types
6    dataTable.Columns.Add("Name", typeof(string));
7    dataTable.Columns.Add("Age", typeof(int));
8    dataTable.Columns.Add("Salary", typeof(decimal));
9    dataTable.Columns.Add("HireDate", typeof(DateTime));
10
11    using (var parser = new TextFieldParser(filePath))
12    {
13        parser.TextFieldType = FieldType.Delimited;
14        parser.SetDelimiters(",");
15
16        // Skip header
17        parser.ReadFields();
18
19        while (!parser.EndOfData)
20        {
21            string[] fields = parser.ReadFields();
22            DataRow row = dataTable.NewRow();
23            row["Name"] = fields[0];
24            row["Age"] = int.Parse(fields[1]);
25            row["Salary"] = decimal.Parse(fields[2]);
26            row["HireDate"] = DateTime.Parse(fields[3]);
27            dataTable.Rows.Add(row);
28        }
29    }
30
31    return dataTable;
32}

Handling Large Files

For CSV files that are too large to load entirely into memory:

csharp

1public void ProcessLargeCsv(string filePath, int batchSize = 1000)
2{
3    using (var reader = new StreamReader(filePath))
4    using (var csv = new CsvReader(reader, CultureInfo.InvariantCulture))
5    {
6        csv.Read();
7        csv.ReadHeader();
8
9        var batch = new DataTable();
10        // Clone structure from first read
11        foreach (string header in csv.HeaderRecord)
12        {
13            batch.Columns.Add(header);
14        }
15
16        int count = 0;
17        while (csv.Read())
18        {
19            var row = batch.NewRow();
20            for (int i = 0; i < csv.HeaderRecord.Length; i++)
21            {
22                row[i] = csv.GetField(i);
23            }
24            batch.Rows.Add(row);
25            count++;
26
27            if (count >= batchSize)
28            {
29                ProcessBatch(batch);
30                batch.Clear();
31                count = 0;
32            }
33        }
34
35        if (batch.Rows.Count > 0)
36        {
37            ProcessBatch(batch);
38        }
39    }
40}

Writing a DataTable Back to CSV

csharp

1public void WriteCsv(DataTable dataTable, string filePath)
2{
3    using (var writer = new StreamWriter(filePath))
4    using (var csv = new CsvWriter(writer, CultureInfo.InvariantCulture))
5    {
6        // Write header
7        foreach (DataColumn column in dataTable.Columns)
8        {
9            csv.WriteField(column.ColumnName);
10        }
11        csv.NextRecord();
12
13        // Write rows
14        foreach (DataRow row in dataTable.Rows)
15        {
16            foreach (DataColumn column in dataTable.Columns)
17            {
18                csv.WriteField(row[column]);
19            }
20            csv.NextRecord();
21        }
22    }
23}

Common Pitfalls

Splitting on commas with String.Split: Fields containing commas (e.g., "New York, NY") break manual Split(',') parsing. Use TextFieldParser or CsvHelper which handle quoted fields correctly.
Encoding issues: CSV files from Excel are often in Windows-1252 encoding, not UTF-8. If you see garbled characters, specify the encoding: new StreamReader(filePath, Encoding.GetEncoding(1252)).
Empty rows and trailing newlines: Many CSV files end with a blank line. Check for empty rows before adding them to the DataTable: if (fields.All(f => string.IsNullOrEmpty(f))) continue;.
Column count mismatch: If a row has fewer fields than the header, dataTable.Rows.Add(fields) throws an exception. Check the field count before adding, or use CsvHelper with MissingFieldFound = null to handle this gracefully.
Date and number locale: decimal.Parse("1.234,56") fails in US locale. Use CultureInfo.InvariantCulture or the specific culture matching your CSV data format.

Summary

Use TextFieldParser for a built-in solution that handles quoted fields correctly
Use CsvHelper (NuGet) for production code with complex CSV formats
Manual StreamReader + Split only works for simple, well-formed CSV files
Set HasFieldsEnclosedInQuotes = true to handle quoted fields with embedded commas
Process large files in batches to avoid memory issues
Always specify encoding when reading CSV files from external sources

OOD Fundamentals

Master object-oriented design from first principles, SOLID, design patterns, and classic interview problems with hands-on coding.

View the course

Track what you have practised

A free account saves your progress, solutions and study plan across every problem on Codemia.

Interview Questions practice on Codemia

Over 8,000 real interview questions from top companies, searchable by company and role.

Browse interview questions

How to read a CSV file into a .NET Datatable

Interview Questions practice on Codemia

Introduction

Using TextFieldParser (Built-in)

Using StreamReader (Manual Parsing)

Using CsvHelper (NuGet Package)

Custom Configuration

Typed Column DataTable

Handling Large Files

Writing a DataTable Back to CSV

Common Pitfalls

Summary

Related reading

OOD Fundamentals

Track what you have practised

Interview Questions practice on Codemia