How Can I Efficiently Load CSV Data into a .NET DataTable?

Importing CSV Data into a .NET DataTable: A Practical Guide

Efficiently handling CSV data is crucial for many .NET applications. This article demonstrates how to seamlessly import CSV files into a .NET DataTable, a powerful ADO.NET structure for managing tabular data.

Leveraging the OleDb Provider

While standard ADO.NET lacks direct CSV import functionality, the OleDb provider provides a robust solution. A common challenge, however, involves the correct interpretation of numeric data. We'll address this using a schema.ini file (explained below).

A Reusable C# Method

The following C# function reads a CSV file and populates a DataTable, handling header rows appropriately:

public static DataTable LoadCsvToDataTable(string filePath, bool hasHeaderRow)
{
    string directory = Path.GetDirectoryName(filePath);
    string filename = Path.GetFileName(filePath);
    string header = hasHeaderRow ? "Yes" : "No";
    string sqlQuery = $"SELECT * FROM [{filename}]";

    using (OleDbConnection connection = new OleDbConnection(
        $"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={directory};Extended Properties=\"Text;HDR={header}\""))
    using (OleDbCommand command = new OleDbCommand(sqlQuery, connection))
    using (OleDbDataAdapter adapter = new OleDbDataAdapter(command))
    {
        DataTable dataTable = new DataTable();
        dataTable.Locale = CultureInfo.CurrentCulture; // Ensures correct data type handling
        adapter.Fill(dataTable);
        return dataTable;
    }
}

This method uses the current culture to ensure accurate data type interpretation, preventing common issues with numeric values. This approach avoids the need for manual type conversions.

The above is the detailed content of How Can I Efficiently Load CSV Data into a .NET DataTable?. For more information, please follow other related articles on the PHP Chinese website!