C# Read CSV File into DataTable: Complete Developer Guide

Why Is CSV to DataTable Conversion Essential?

CSV to DataTable conversion allows developers to transform comma-separated values (CSV data) into structured objects like a DataTable for further processing. Whether you're handling inventory data, customer records, or transaction logs, being able to convert CSV to a new DataTable efficiently is crucial. Using the first row as column headers, you can ensure that datatable columns align with your database table schema, making it perfect for Excel to SQL operations.

Traditional approaches often struggle with large files, different delimiters, or memory management. IronXL eliminates these challenges and handles different delimiters, quoted fields, and embedded commas seamlessly. IronXL's CSV reading capabilities eliminate these common pain points while providing additional features like data validation and file size optimization.

How to Install IronXL?

Getting started with IronXL requires just a simple NuGet package installation. Open the NuGet Package Manager Console in Visual Studio and execute:

Install-Package IronXL.Excel

Once installed, add the IronXL namespace to your project:

using IronXL;
using System.Data;

using IronXL;
using System.Data;

This provides access to all the powerful CSV processing capabilities without any Excel dependencies. For more details on IronXL's extensive API, refer to the API Reference Documentation. If you're using Docker containers, additional configuration steps may be required.

Why Choose NuGet Over Manual Installation?

NuGet automatically manages dependencies, ensuring all required libraries are installed correctly. It simplifies version updates—one command updates IronXL and all dependencies. NuGet integration with Visual Studio provides IntelliSense support immediately after installation. Package restore functionality ensures team members get the same version automatically. For enterprise environments requiring specific versions, NuGet also supports private package feeds.

Install with NuGet

PM >  Install-Package IronXL.Excel

Check out IronXL on NuGet for quick installation. With over 10 million downloads, it’s transforming PDF development with C#. You can also download the DLL.

How to Convert CSV to DataTable?

The beauty of IronXL lies in its simplicity. Here's how to read a CSV file and convert it to a DataTable:

// Load CSV file into a WorkBook object
WorkBook workbook = WorkBook.LoadCSV("sales_data.csv", 
    fileFormat: ExcelFileFormat.XLSX);

// Access the default worksheet
WorkSheet worksheet = workbook.DefaultWorkSheet;

// Convert to DataTable with headers
DataTable dataTable = worksheet.ToDataTable(true);

// Display the data
foreach (DataRow row in dataTable.Rows)
{
    foreach (var item in row.ItemArray)
    {
        Console.Write($"{item}\t");
    }
    Console.WriteLine();
}

// Load CSV file into a WorkBook object
WorkBook workbook = WorkBook.LoadCSV("sales_data.csv", 
    fileFormat: ExcelFileFormat.XLSX);

// Access the default worksheet
WorkSheet worksheet = workbook.DefaultWorkSheet;

// Convert to DataTable with headers
DataTable dataTable = worksheet.ToDataTable(true);

// Display the data
foreach (DataRow row in dataTable.Rows)
{
    foreach (var item in row.ItemArray)
    {
        Console.Write($"{item}\t");
    }
    Console.WriteLine();
}

The LoadCSV method intelligently parses your CSV file, automatically handling common cases such as quoted fields and embedded commas. The fileFormat parameter tells IronXL how to interpret the CSV data internally, ensuring it's treated as an Excel-compatible structure. Once loaded, the ToDataTable(true) method converts the worksheet into a DataTable, with the parameter true indicating that the first row should be used as column headers. This results in a clean, structured DataTable with named columns that match your CSV headers. For more advanced manipulation options, explore the complete Excel to DataTable tutorial.

The conversion process also preserves data types intelligently—numbers remain numeric, dates are parsed correctly, and text fields maintain their formatting. This automatic type inference saves considerable development time compared to manual parsing approaches. For scenarios requiring custom data formatting, you can apply cell data formats before conversion.

How to Import DataTable into SQL Server?

Once you have your DataTable, importing it into SQL Server becomes remarkably efficient using SqlBulkCopy:

using System.Data;
using Microsoft.Data.SqlClient;
using IronXL;

class Program
{
    static void Main(string[] args)
    {
        // Create connection string
        string connectionString = @"Data Source=localhost;Initial Catalog=SalesDB;
                           Integrated Security=True;TrustServerCertificate=True;";

        // Read CSV into DataTable
        WorkBook workbook = WorkBook.LoadCSV("inventory_report.csv");
        DataTable dataTable = workbook.DefaultWorkSheet.ToDataTable(true);

        // Bulk insert into SQL Server
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
            {
                // Set destination table name
                bulkCopy.DestinationTableName = "dbo.Inventory";

                // Map DataTable columns to SQL table columns
                bulkCopy.ColumnMappings.Add("ProductCode", "product_code");
                bulkCopy.ColumnMappings.Add("Quantity", "quantity");
                bulkCopy.ColumnMappings.Add("LastUpdated", "last_updated");

                // Set batch size for better performance
                bulkCopy.BatchSize = 1000;

                // Write data to SQL Server
                bulkCopy.WriteToServer(dataTable);
            }
        }

        Console.WriteLine($"Successfully imported {dataTable.Rows.Count} records");
    }
}

using System.Data;
using Microsoft.Data.SqlClient;
using IronXL;

class Program
{
    static void Main(string[] args)
    {
        // Create connection string
        string connectionString = @"Data Source=localhost;Initial Catalog=SalesDB;
                           Integrated Security=True;TrustServerCertificate=True;";

        // Read CSV into DataTable
        WorkBook workbook = WorkBook.LoadCSV("inventory_report.csv");
        DataTable dataTable = workbook.DefaultWorkSheet.ToDataTable(true);

        // Bulk insert into SQL Server
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
            {
                // Set destination table name
                bulkCopy.DestinationTableName = "dbo.Inventory";

                // Map DataTable columns to SQL table columns
                bulkCopy.ColumnMappings.Add("ProductCode", "product_code");
                bulkCopy.ColumnMappings.Add("Quantity", "quantity");
                bulkCopy.ColumnMappings.Add("LastUpdated", "last_updated");

                // Set batch size for better performance
                bulkCopy.BatchSize = 1000;

                // Write data to SQL Server
                bulkCopy.WriteToServer(dataTable);
            }
        }

        Console.WriteLine($"Successfully imported {dataTable.Rows.Count} records");
    }
}

The SqlBulkCopy class provides exceptional performance for large-scale data imports. The ColumnMappings collection allows you to map DataTable columns to database columns with different names, ensuring flexibility in your data schema. The BatchSize property optimizes memory usage by processing records in chunks rather than loading everything at once. This approach can handle millions of records efficiently, making it ideal for enterprise-scale data operations. Learn more about IronXL's DataSet and DataTable capabilities.

For additional data integrity, consider implementing transaction support around your bulk copy operations. This ensures that partial imports can be rolled back if errors occur. You can also leverage IronXL's data validation features to pre-validate data before import.

How to Handle Different Delimiters?

Not all CSV files use commas. IronXL seamlessly handles various delimiters, making it perfect for international data formats or legacy systems:

using IronXL;
using System;
using System.Data;
using System.IO;

class Program
{
    static void Main()
    {
        // --- Tab-delimited file ---
        string tsvPath = @"export_data.tsv";
        WorkBook tsvWorkbook = WorkBook.LoadCSV(tsvPath, ExcelFileFormat.XLSX, "\t");

        // --- Semicolon-delimited file ---
        string semiPath = @"european_data.csv";
        string tempCsv = Path.Combine(Path.GetTempPath(), "european_data_comma.csv");

        // Replace semicolons with commas for proper parsing
        var lines = File.ReadAllLines(semiPath);
        for (int i = 0; i < lines.Length; i++)
        {
            lines[i] = lines[i].Replace(';', ',');
        }
        File.WriteAllLines(tempCsv, lines);

        WorkBook semiWorkbook = WorkBook.LoadCSV(tempCsv, ExcelFileFormat.XLSX);

        // Print both workbooks
        PrintWorkbook(tsvWorkbook, "Tab-delimited File");
        PrintWorkbook(semiWorkbook, "Semicolon-delimited File");
    }

    static void PrintWorkbook(WorkBook workbook, string name)
    {
        Console.WriteLine($"\n--- {name} ---\n");
        DataTable dt = workbook.DefaultWorkSheet.ToDataTable(true);

        // Print column headers
        foreach (DataColumn col in dt.Columns)
        {
            Console.Write($"{col.ColumnName}\t");
        }
        Console.WriteLine();

        // Print rows
        foreach (DataRow row in dt.Rows)
        {
            foreach (var item in row.ItemArray)
            {
                Console.Write($"{item}\t");
            }
            Console.WriteLine();
        }
    }
}

using IronXL;
using System;
using System.Data;
using System.IO;

class Program
{
    static void Main()
    {
        // --- Tab-delimited file ---
        string tsvPath = @"export_data.tsv";
        WorkBook tsvWorkbook = WorkBook.LoadCSV(tsvPath, ExcelFileFormat.XLSX, "\t");

        // --- Semicolon-delimited file ---
        string semiPath = @"european_data.csv";
        string tempCsv = Path.Combine(Path.GetTempPath(), "european_data_comma.csv");

        // Replace semicolons with commas for proper parsing
        var lines = File.ReadAllLines(semiPath);
        for (int i = 0; i < lines.Length; i++)
        {
            lines[i] = lines[i].Replace(';', ',');
        }
        File.WriteAllLines(tempCsv, lines);

        WorkBook semiWorkbook = WorkBook.LoadCSV(tempCsv, ExcelFileFormat.XLSX);

        // Print both workbooks
        PrintWorkbook(tsvWorkbook, "Tab-delimited File");
        PrintWorkbook(semiWorkbook, "Semicolon-delimited File");
    }

    static void PrintWorkbook(WorkBook workbook, string name)
    {
        Console.WriteLine($"\n--- {name} ---\n");
        DataTable dt = workbook.DefaultWorkSheet.ToDataTable(true);

        // Print column headers
        foreach (DataColumn col in dt.Columns)
        {
            Console.Write($"{col.ColumnName}\t");
        }
        Console.WriteLine();

        // Print rows
        foreach (DataRow row in dt.Rows)
        {
            foreach (var item in row.ItemArray)
            {
                Console.Write($"{item}\t");
            }
            Console.WriteLine();
        }
    }
}

This code demonstrates loading CSV files with different delimiters into IronXL WorkBook objects. Tab-delimited files are read using "\t" as the separator, while semicolon-delimited files are converted to standard CSV format before loading. Each workbook's default worksheet is converted into a DataTable with ToDataTable(true), which uses the first row as column headers. For more complex delimiter scenarios, you can also explore converting between spreadsheet file types.

The PrintWorkbook method then iterates through the DataTable and displays the data in the console, including the headers and all rows. This flexibility addresses many of the common CSV parsing challenges discussed by developers. When working with European data formats, IronXL's delimiter handling proves particularly valuable, as it accommodates regional differences in data formatting standards.

What Are the Next Steps?

IronXL transforms the complex task of CSV database imports into a straightforward, reliable process. With just a few lines of code, you can:

• Read CSV files from any file path using LoadCSV functionality
• Convert CSV data into a new DataTable with proper data formatting
• Preserve column headers and datatable columns through intelligent parsing
• Import millions of rows into a database table efficiently using bulk operations

The library handles the intricacies of CSV parsing, memory management, and data type conversions automatically, letting you focus on your business logic rather than file processing details. Whether you're building ASP.NET applications, working with Blazor, or developing .NET MAUI apps, IronXL provides consistent, reliable CSV processing across all platforms.

Ready to streamline your data import workflow? Start your free trial to find the perfect fit for your team's needs. With comprehensive documentation, extensive code examples, and enterprise support, you'll be processing CSV files like a pro in no time.