How to Efficiently Load a CSV File into a .NET DataTable and Handle Numeric Values as Text?

Efficiently Loading CSV Files into .NET DataTables: Handling Numeric Values as Text

Importing CSV data into a .NET DataTable is a frequent task. While ADO.NET offers powerful data handling, direct CSV import isn't built-in. We can use the OleDb provider, but correctly managing numeric data as text requires careful attention.

This solution uses a schema.ini file for precise control. The following code demonstrates this approach:

<code class="language-csharp">using System.Data;
using System.Data.OleDb;
using System.Globalization;
using System.IO;

static DataTable LoadCsvIntoDataTable(string filePath, bool isFirstRowHeader)
{
    string header = isFirstRowHeader ? "Yes" : "No";
    string directory = Path.GetDirectoryName(filePath);
    string fileName = Path.GetFileName(filePath);
    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;
        adapter.Fill(dataTable);
        return dataTable;
    }
}</code>

This method efficiently reads CSV data into a DataTable, preserving numeric values as text strings as needed, thanks to the configuration provided by the schema.ini file.

The above is the detailed content of How to Efficiently Load a CSV File into a .NET DataTable and Handle Numeric Values as Text?. For more information, please follow other related articles on the PHP Chinese website!