How to Read CSV File in C#
This article will explore how to create a C# CSV Reader using the IronXL library.
IronXL - Excel Library
IronXL is a powerful Excel library that provides C# developers with the ability to create, load, read, and edit Excel spreadsheets in various formats. Designed specifically for .NET, IronXL prioritizes speed, accuracy, and ease of use. It allows for saving Excel files in different formats and loading various spreadsheet formats into Excel for efficient data reading.
IronXL supports Excel workbook formats with different file extensions, including CSV and TSV, XLS and XLSX, XSLT and XLSM. It is compatible with the latest version of the .NET Framework, as well as all previous versions up to 2.0. IronXL can be used on various platforms, including Linux, MacOS, Azure, Docker, and AWS.
How to make a CSV Reader in C# using IronXL?
Prerequisites
To convert data from a CSV file to an Excel file and read it in C#, we require the following tools:
- Visual Studio: is an essential Integrated Development Environment (IDE) for working on C# .NET applications. It is the official and recommended IDE for developing C# apps, and it can be downloaded and installed from the Microsoft website. However, other IDEs that support a C# environment can also be used if preferred by the developer.
Create Console App: Follow the steps to create a simple Console Application.
Open Visual Studio and click "Create a Project".
New Project Window
Choose "Console App" from the list of options available. Make sure the selected language is C#.
Create a new Console App
Next, name your project whatever you want.
Configuration
- The next step in project configuration is selecting the .NET Framework. The latest version available is .NET 7.0, which is supported in the standard term. However, to avoid any potential errors, you can opt for the more stable version of .NET 6.0, as Microsoft provides long-term support for it. It's important to choose the appropriate version based on your specific requirements.
- IronXL - It is the .NET Excel library that is designed to work with Excel spreadsheets. It must be installed in your C# application before using it. You can download and install the package from multiple sources:
- NuGet - the package manager for .NET. You can directly download IronXL from the NuGet website.
- Visual Studio NuGet Package Manager: Open the Package Manager by going to Tools > NuGet Package Manager > Manage Packages for Solution...
- Download the IronXL .NET Excel DLL zip file directly from the Iron website and add it to the Project Solution as a project reference.
Add IronXL Namespace
Once you have the prerequisites, the next step is to add the IronXL
namespace on top of the source code in the main.cs
file:
using IronXL;
using IronXL;
Imports IronXL
Open an Existing CSV File
IronXL provides an easy way to read CSV files in C#. First, open a CSV file for reading. It is a file type which is based on rows and columns. Here, the WorkBook
class is used along with its LoadCSV
method to open a CSV file. The code is as follows:
// Load the CSV file and convert it to XLSX format.
var csv = WorkBook.LoadCSV("color_srgb.csv", fileFormat: ExcelFileFormat.XLSX, ListDelimiter: ",");
// Load the CSV file and convert it to XLSX format.
var csv = WorkBook.LoadCSV("color_srgb.csv", fileFormat: ExcelFileFormat.XLSX, ListDelimiter: ",");
' Load the CSV file and convert it to XLSX format.
Dim csv = WorkBook.LoadCSV("color_srgb.csv", fileFormat:= ExcelFileFormat.XLSX, ListDelimiter:= ",")
The LoadCSV
method in IronXL allows you to open a CSV file and convert it to XLSX format. This method provides options to specify the list delimiter used in the CSV file. In this example, the default delimiter, which is a comma, is used. The resulting file is opened and can be further processed using IronXL's features for reading and manipulating Excel spreadsheets.
CSV File
Get Worksheet from CSV Reader Class
In the previous step, the CSV file was opened as an Excel workbook using IronXL. Now, get the default worksheet from the workbook using the WorkSheet
class. The following example demonstrates how to get the worksheet for reading CSV data:
// Retrieve the default worksheet from the loaded workbook.
WorkSheet ws = csv.DefaultWorkSheet;
// Retrieve the default worksheet from the loaded workbook.
WorkSheet ws = csv.DefaultWorkSheet;
' Retrieve the default worksheet from the loaded workbook.
Dim ws As WorkSheet = csv.DefaultWorkSheet
For more information on working with Excel worksheets, you can visit this code examples page.
Read CSV Data using C# DataTable
Once the CSV is loaded successfully and the data is available as a worksheet, the data can be read from the CSV file very easily in a C# DataTable.
Convert Worksheet to DataTable
First, create a DataTable
instance and convert the worksheet data to a table using the ToDataTable
method. The following code helps to achieve this task:
// Convert the worksheet data to a DataTable.
DataTable dt = ws.ToDataTable(true);
// Convert the worksheet data to a DataTable.
DataTable dt = ws.ToDataTable(true);
' Convert the worksheet data to a DataTable.
Dim dt As DataTable = ws.ToDataTable(True)
Start Reading CSV Data
Now, iterate through all the records using the DataTable
instance. The data is received in rows and columns. First, then move through each column to get its value. To get all the records along with the header row, use the following code snippet:
// Iterate over each row in the DataTable.
foreach (DataRow row in dt.Rows)
{
// Iterate over each column in the current row.
for (int i = 0; i < dt.Columns.Count; i++)
{
// Print each column value in the current row.
Console.Write(row[i] + " ");
}
// Move to the next line after printing all columns of the current row.
Console.WriteLine();
}
// Iterate over each row in the DataTable.
foreach (DataRow row in dt.Rows)
{
// Iterate over each column in the current row.
for (int i = 0; i < dt.Columns.Count; i++)
{
// Print each column value in the current row.
Console.Write(row[i] + " ");
}
// Move to the next line after printing all columns of the current row.
Console.WriteLine();
}
' Iterate over each row in the DataTable.
For Each row As DataRow In dt.Rows
' Iterate over each column in the current row.
For i As Integer = 0 To dt.Columns.Count - 1
' Print each column value in the current row.
Console.Write(row(i) & " ")
Next i
' Move to the next line after printing all columns of the current row.
Console.WriteLine()
Next row
In the above code, a foreach
loop is used to get a single record from a collection of rows. Then inside a nested for loop, the number of columns is counted and finally, the data from each row is printed on the screen. The output is formatted similarly to a CSV file.
Output
Summary
This article demonstrated how to create a CSV reader in C# using the IronXL library. Loading CSV files is made easy with IronXL as it supports the CSV format in Excel spreadsheets. The DataTable
object is used to create an elegant CSV reader and format the output to match the original file.
IronXL also provides convenient conversion between different file formats and allows for creating Excel files from scratch without requiring Interop and Microsoft Excel to be installed. It is also compatible with C# DataSet
and DataTable
, providing developers with flexibility in interconverting data without relying on third-party applications. With its powerful features, IronXL is a valuable tool for C# developers to work with Excel spreadsheets and handle CSV data efficiently.
IronXL is free for development. However, for commercial use, you need to purchase its license starting from $749. You can also use its free trial to test its compiled .dll compatibility with your project in production mode.
Frequently Asked Questions
What is the powerful library used for handling Excel files in C#?
IronXL is a powerful Excel library designed for C# developers, enabling them to create, load, read, and edit Excel spreadsheets in various formats. It is compatible with the .NET Framework and can be used on multiple platforms like Linux, MacOS, Azure, Docker, and AWS.
What are the prerequisites for creating a CSV Reader in C#?
The prerequisites include having Visual Studio or another compatible IDE for C# development, creating a Console Application, and installing the IronXL library via NuGet or downloading the .NET Excel DLL from IronXL's website.
How do you add the necessary namespace for handling Excel in C#?
To add the IronXL namespace in a C# project, include the following line at the top of your source code in the main.cs file: using IronXL;
How can you open an existing CSV file in C#?
Using IronXL, you can open an existing CSV file by utilizing the WorkBook class and its LoadCSV method. This allows you to load the CSV file and convert it to XLSX format for further processing.
How do you get a worksheet from a CSV file in C#?
After opening a CSV file as a workbook, you can use the WorkSheet class to retrieve the default worksheet for reading CSV data.
How can you convert worksheet data to a DataTable in C#?
To convert worksheet data to a DataTable, create a DataTable instance and use the ToDataTable method provided by IronXL's Range class.
What is the method to read CSV data using a DataTable in C#?
To read CSV data using a DataTable, iterate over each row using a foreach loop and then iterate over each column within the row to access and print the data.
Can the library used for Excel operations be used for commercial purposes?
Yes, IronXL can be used for commercial purposes, but it requires purchasing a license. There is also a free trial available for testing its compatibility with your project.
Does the library support conversion between different file formats?
Yes, IronXL supports convenient conversion between different file formats, allowing developers to create Excel files from scratch and handle CSV data without relying on Interop or Microsoft Excel.