How to Use C# to Convert Datatable to CSV

Which Installation Method Should I Use?

Download from the official site using the following link: https://ironsoftware.com/csharp/excel/docs/

or

• In Visual Studio, select the Project menu
• Click Manage NuGet Packages
• Search for IronXL.Excel
• Click Install

What NuGet Command Should I Use?

Install-Package IronXL.Excel

Why Choose NuGet Package Manager?

NuGet is the preferred method for .NET developers because it automatically manages dependencies and keeps libraries current. The IronXL package includes all necessary components for converting spreadsheet file types and working with various Excel formats.

Figure 1 - IronXL.Excel NuGet Package

How to Tutorial

What Namespace Do I Need to Import?

First, import the IronXL namespace. IronXL provides comprehensive support for importing and exporting DataSet and DataTable objects, making it ideal for database-driven applications.

using IronXL;

using IronXL;

Imports IronXL

What's the Complete Code Example?

Add the following code:

:path=/static-assets/excel/content-code-examples/how-to/csharp-database-to-csv-datatable.cs

using IronXL;
using System;
using System.Data;

// Create a new DataTable object
DataTable table = new DataTable();

// Add a single column named "Example_DataSet" of type string
table.Columns.Add("Example_DataSet", typeof(string));

// Add rows to the DataTable
table.Rows.Add("0");
table.Rows.Add("1");
table.Rows.Add("2");
table.Rows.Add("3");
table.Rows.Add("1");
table.Rows.Add("2");
table.Rows.Add("3");

// Create a new Excel workbook and set its author metadata
WorkBook wb = WorkBook.Create(ExcelFileFormat.XLS);
wb.Metadata.Author = "OJ";

// Get the default worksheet
WorkSheet ws = wb.DefaultWorkSheet;

// Initialize rowCounter for Excel sheet rows
int rowCount = 1;

// Loop through each row in the DataTable and add the data to the Excel worksheet
foreach (DataRow row in table.Rows)
{
    // Populate worksheet cells with data from DataTable
    ws["A" + (rowCount)].Value = row[0].ToString();
    rowCount++;
}

// Save the workbook as a CSV file
wb.SaveAsCsv("Save_DataTable_CSV.csv", ";"); // Will be saved as: Save_DataTable_CSV.Sheet1.csv

IRON VB CONVERTER ERROR developers@ironsoftware.com

How Does the Code Work Step by Step?

The above code creates a data table, creates a new workbook specifying 'OJ' as its owner, then uses a foreach loop to insert data from the data table into the Excel worksheet. Finally, the SaveAsCsv method exports the datatable to CSV.

The process breakdown:

1. DataTable Creation: Initialize a new DataTable and define its schema by adding columns. This resembles defining a database table structure.

2. Data Population: Add rows to the DataTable using the Rows.Add() method. Each row represents a record for CSV export.

3. Workbook Generation: IronXL's WorkBook.Create() method initializes a new Excel workbook. You can also create spreadsheets with multiple worksheets if needed.

4. Cell Population: The foreach loop iterates through DataTable rows and maps each value to a specific worksheet cell using cell reference syntax (e.g., "A1", "A2").

5. CSV Export: The SaveAsCsv() method handles CSV formatting complexities, including proper escaping of special characters and delimiter handling.

What About Advanced DataTable Scenarios?

For complex DataTables with multiple columns, extend the code like this:

// Create a DataTable with multiple columns
DataTable advancedTable = new DataTable();
advancedTable.Columns.Add("ID", typeof(int));
advancedTable.Columns.Add("Name", typeof(string));
advancedTable.Columns.Add("Price", typeof(decimal));

// Add sample data
advancedTable.Rows.Add(1, "Product A", 29.99m);
advancedTable.Rows.Add(2, "Product B", 49.99m);
advancedTable.Rows.Add(3, "Product C", 19.99m);

// Create workbook and worksheet
WorkBook workbook = WorkBook.Create();
WorkSheet worksheet = workbook.DefaultWorkSheet;

// Add headers
for (int col = 0; col < advancedTable.Columns.Count; col++)
{
    worksheet[1, col + 1].Value = advancedTable.Columns[col].ColumnName;
}

// Add data rows
for (int row = 0; row < advancedTable.Rows.Count; row++)
{
    for (int col = 0; col < advancedTable.Columns.Count; col++)
    {
        worksheet[row + 2, col + 1].Value = advancedTable.Rows[row][col];
    }
}

// Save as CSV with comma delimiter
workbook.SaveAsCsv("products.csv", ",");

// Create a DataTable with multiple columns
DataTable advancedTable = new DataTable();
advancedTable.Columns.Add("ID", typeof(int));
advancedTable.Columns.Add("Name", typeof(string));
advancedTable.Columns.Add("Price", typeof(decimal));

// Add sample data
advancedTable.Rows.Add(1, "Product A", 29.99m);
advancedTable.Rows.Add(2, "Product B", 49.99m);
advancedTable.Rows.Add(3, "Product C", 19.99m);

// Create workbook and worksheet
WorkBook workbook = WorkBook.Create();
WorkSheet worksheet = workbook.DefaultWorkSheet;

// Add headers
for (int col = 0; col < advancedTable.Columns.Count; col++)
{
    worksheet[1, col + 1].Value = advancedTable.Columns[col].ColumnName;
}

// Add data rows
for (int row = 0; row < advancedTable.Rows.Count; row++)
{
    for (int col = 0; col < advancedTable.Columns.Count; col++)
    {
        worksheet[row + 2, col + 1].Value = advancedTable.Rows[row][col];
    }
}

// Save as CSV with comma delimiter
workbook.SaveAsCsv("products.csv", ",");

IRON VB CONVERTER ERROR developers@ironsoftware.com

What Does the Output Look Like?

The output Excel worksheet displays as follows:

Figure 2 - Datatable output to CSV

How Do I Handle Large DataTables?

When working with large DataTables containing thousands of rows, IronXL maintains excellent performance. The library handles substantial datasets efficiently. For production environments, apply your license key to remove watermarks and enable full functionality.

What About Error Handling?

Always implement proper error handling when working with file operations:

try 
{
    // Your DataTable to CSV conversion code
    WorkBook wb = WorkBook.Create();
    // ... rest of the code
    wb.SaveAsCsv("output.csv", ",");
    Console.WriteLine("CSV file created successfully!");
}
catch (Exception ex)
{
    Console.WriteLine($"Error creating CSV: {ex.Message}");
}

try 
{
    // Your DataTable to CSV conversion code
    WorkBook wb = WorkBook.Create();
    // ... rest of the code
    wb.SaveAsCsv("output.csv", ",");
    Console.WriteLine("CSV file created successfully!");
}
catch (Exception ex)
{
    Console.WriteLine($"Error creating CSV: {ex.Message}");
}

IRON VB CONVERTER ERROR developers@ironsoftware.com

Library Quick Access

IronXL API Reference Documentation

Learn more and share how to merge, unmerge, and work with cells in Excel spreadsheets using the handy IronXL API Reference Documentation.

IronXL API Reference Documentation