How to Convert CSV to Datatable in C#

Working with CSV (comma-separated values) files is a common task for developers, particularly in scenarios involving data exchange between different systems or bulk data import/export operations. In C#, efficient handling of CSV files can be achieved using libraries like IronXL. IronXL is a powerful library that simplifies the process of reading, writing, and manipulating Excel and CSV files. This article will explore how to transform CSV files into DataTable using IronXL, covering practical examples, use cases, and various scenarios.

How to Convert a CSV File to a DataTable

Install IronXL: Use NuGet Package Manager in Visual Studio with Install-Package IronXL.Excel.

1. Load CSV File: Use WorkBook.LoadCSV("filePath.csv") to load the CSV file.
2. Select Worksheet: Access the first worksheet with workbook.WorkSheets.First().
3. Convert to DataTable: Use sheet.ToDataTable(true) where true indicates that the first row contains column names.
4. Display or Use DataTable: Iterate through dataTable.Rows and row.ItemArray to access and manipulate data.

Introduction to IronXL

IronXL is a .NET library designed to make it easier for developers to work with Excel and CSV files in C#. It supports reading and writing Excel files (XLS, XLSX) and CSV files with ease. IronXL is particularly useful for developers who need to manipulate spreadsheet data without relying on Excel Interop or other complex libraries.

Installation

Before we dive into code examples, let's ensure you have IronXL installed in your project. You can install IronXL via NuGet Package Manager in Visual Studio:

Install-Package IronXL.Excel

This command will add IronXL to your project, allowing you to access its functionalities for handling CSV and Excel files.

Basic Example: Reading CSV data to DataTable

The most straightforward use case for converting a CSV file to a Data Table is simply reading the CSV file and loading its contents into a DataTable. Here's how you can achieve this using IronXL:

using IronXL;
using System.Data;

static void Main(string[] args)
{
    WorkBook workbook = WorkBook.LoadCSV("Data.csv");

    // Select the first worksheet
    WorkSheet sheet = workbook.WorkSheets.First();

    // Convert the worksheet to a DataTable
    DataTable dataTable = sheet.ToDataTable(true);

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

In this example, WorkBook.LoadCSV("Data.csv") loads the CSV file into an IronXL WorkBook object. The workbook.WorkSheets.First() statement selects the first worksheet from the loaded workbook, as CSV files typically have only one sheet. The sheet.ToDataTable(true) method convert csv data to a DataTable. The true parameter indicates that the first row contains the datatable columns. The nested foreach loops iterate over the rows and columns of the DataTable, printing the data to the console.

Practical Use Cases

1. Export Data to SQL from CSV File

Many applications require importing data from CSV files into their databases. For instance, a user might want to upload customer records or product inventories. Using IronXL, you can easily read the CSV data and insert it into a database.

static void Main(string[] args)
{
    WorkBook workbook = WorkBook.LoadCSV("Data.csv"); // file path

    // Select the first worksheet
    WorkSheet sheet = workbook.WorkSheets.First(); // default value

    // Convert the worksheet to a DataTable
    DataTable dataTable = sheet.ToDataTable(true);
    string connectionString = @"Server=localhost;Database=test_DB;Trusted_Connection=True;TrustServerCertificate=True;";
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();
        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
        {
            bulkCopy.DestinationTableName = "DummyData";
            bulkCopy.WriteToServer(dataTable);
        }
    }
}

In this example, WorkBook.LoadCSV("Data.csv") and workbook.WorkSheets.First() load the CSV file and select the first worksheet. The sheet.ToDataTable(true) method converts the worksheet to a DataTable. The true parameter indicates whether the first row should be treated as column headers. Using SqlConnection and SqlBulkCopy, a connection to the database is opened using the provided connection string, and the DataTable is efficiently inserted into the specified database table.

2. Data Transformation and Cleaning

Often, raw CSV data needs to be transformed or cleaned before it can be used. IronXL allows you to read the data into a DataTable, perform necessary transformations, and then use the cleaned data.

static void Main(string[] args)
{
    string filepath = "Data.csv";
    WorkBook workbook = WorkBook.LoadCSV("Data.csv");

    // Select the first worksheet
    WorkSheet sheet = workbook.WorkSheets.First();

    // Convert the worksheet to a DataTable
    DataTable dataTable = sheet.ToDataTable(true);
    // Example transformation: Convert all strings to uppercase
    foreach (DataRow row in dataTable.Rows)
    {
        for (int i = 0; i < dataTable.Columns.Count; i++)
        {
            row[i] = row[i].ToString().ToUpper();
        }
    }

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

In this example, WorkBook.LoadCSV("Data.csv") and workbook.WorkSheets.First() load the CSV file and select the first worksheet. The sheet.ToDataTable(true) method return DataTable. The true parameter indicates whether the first row should be treated as column names. The nested foreach loops iterate over each cell in the DataTable, converting the string values to uppercase. The transformed data is then printed to the console.

3. Handling Different Delimiters

CSV files can use different delimiters, such as commas, semicolons, pipes (|), or tabs. IronXL allows you to specify the delimiter when loading the CSV file.



The following source code will load a csv file with custom delimiters.

static void Main(string[] args)
{

    WorkBook workbook = WorkBook.LoadCSV("Data.csv",ExcelFileFormat.XLSX, "|");

    // Select the first worksheet
    WorkSheet sheet = workbook.WorkSheets.First();

    DataTable dataTable = sheet.ToDataTable(true);
    // Display the data
    foreach (DataRow row in dataTable.Rows)
    {
        foreach (var item in row.ItemArray)
        {
            Console.Write(item + "\t");
        }
        Console.WriteLine();
    }
}

In this example, WorkBook.LoadCSV("Data", ExcelFileFormat.XLSX,"|") loads the CSV file using a "|" as the delimiter. The workbook.WorkSheets.First() statement selects the first worksheet from the loaded workbook. The sheet.ToDataTable(true) method converts the worksheet to a new DataTable.



After loading the CSV file, you can dim lines of data to handle various processing tasks or transformations before converting them into a DataTable.

Conclusion

In conclusion, IronXL provides a robust solution for converting CSV files into DataTables in C#, making data manipulation and integration seamless. Whether you're handling simple CSV data imports, performing data transformations, or managing files with custom delimiters, IronXL’s powerful features simplify these tasks efficiently. By leveraging IronXL, developers can save valuable time and avoid the complexities often associated with working with CSV and Excel files.

To experience the benefits of IronXL firsthand, consider taking advantage of the free trial available. This trial allows you to explore IronXL's capabilities without any initial commitment. For long-term use, you can opt for a commercial license, which offers extensive support and additional features tailored to meet your needs. Start integrating IronXL into your projects today and enhance your data processing workflows with ease.