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:
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:
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:
dotnet add package CsvHelper
Read CSV into a DataTable:
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
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:
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:
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
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
