USING IRONXL

Read CSV File Using C# (Code Example Tutorial)

Updated January 29, 2024
Share:

This tutorial demonstrates how to read a CSV file using the IronXL C# library without installing additional interop, in a highly efficient and effective manner.

How to read CSV Files in C#

You must first install IronXL before using it to read CSV files in MVC, ASP.NET, or .NET Core. Here's a basic rundown of the process.

Select the Project menu in Visual Studio, Manage NuGet Packages, and Search for IronXL.Excel, and Install.

Read CSV File Using C# (Code Example Tutorial), Figure 1: Install the IronXL package in the NuGet Package Manager Install the IronXL package in the NuGet Package Manager

IronXL is a great tool to use when you need to read CSV files in C#. The code example below shows that you can read a CSV file using commas or another delimiter.

WorkBook workbook = WorkBook.LoadCSV("Weather.csv", fileFormat: ExcelFileFormat.XLSX, ListDelimiter: ",");
WorkSheet ws = workbook.DefaultWorkSheet;
workbook.SaveAs("Csv_To_Excel.xlsx");
WorkBook workbook = WorkBook.LoadCSV("Weather.csv", fileFormat: ExcelFileFormat.XLSX, ListDelimiter: ",");
WorkSheet ws = workbook.DefaultWorkSheet;
workbook.SaveAs("Csv_To_Excel.xlsx");
Dim workbook As WorkBook = WorkBook.LoadCSV("Weather.csv", fileFormat:= ExcelFileFormat.XLSX, ListDelimiter:= ",")
Dim ws As WorkSheet = workbook.DefaultWorkSheet
workbook.SaveAs("Csv_To_Excel.xlsx")
VB   C#

Read CSV File Using C# (Code Example Tutorial), Figure 2: CSV data for this tutorial CSV data for this tutorial

The object WorkBook is created. The WorkBook object's LoadCSV method is then used to indicate the name of the CSV file, its format, and the delimiters used in the CSV file being read, which are stored as a string array. Commas are utilized as delimiters in this scenario.

After that, a WorkSheet object is created; this is where the CSV file contents will be stored. The file is then renamed and stored in a new format. The CSV file data is then arranged in the worksheet in a tabular form. The output will look something like this:

Read CSV File Using C# (Code Example Tutorial), Figure 3: Data converted into Excel file Data converted into Excel file

CSV parsing in C# .NET

CSVs have several issues with how line breaks are handled in fields, and how fields might be enclosed in quotes, which prevents a simple string-split technique from working Split("'"). Instead, IronXL offers to customize delimiter using optional parameter from LoadCSV method, check the API documentation of LoadCSV for more details.

C# Records — Reading CSV Data

In the example below, the foreach is used to loop over the rows in the CSV file, and the Console is used to write the data in a record.

WorkBook workbook = WorkBook.LoadCSV("Weather.csv", fileFormat: ExcelFileFormat.XLSX, ListDelimiter: ",");
WorkSheet ws = workbook.DefaultWorkSheet;
DataTable dt = ws.ToDataTable(true);//parse sheet1 of sample.xlsx file into datatable
foreach (DataRow row in dt.Rows) //access rows
{
    for (int i = 0; i < dt.Columns.Count; i++) //access columns of corresponding row
    {
        Console.Write(row [i] + "  ");
    }
    Console.WriteLine();
}
WorkBook workbook = WorkBook.LoadCSV("Weather.csv", fileFormat: ExcelFileFormat.XLSX, ListDelimiter: ",");
WorkSheet ws = workbook.DefaultWorkSheet;
DataTable dt = ws.ToDataTable(true);//parse sheet1 of sample.xlsx file into datatable
foreach (DataRow row in dt.Rows) //access rows
{
    for (int i = 0; i < dt.Columns.Count; i++) //access columns of corresponding row
    {
        Console.Write(row [i] + "  ");
    }
    Console.WriteLine();
}
Dim workbook As WorkBook = WorkBook.LoadCSV("Weather.csv", fileFormat:= ExcelFileFormat.XLSX, ListDelimiter:= ",")
Dim ws As WorkSheet = workbook.DefaultWorkSheet
Dim dt As DataTable = ws.ToDataTable(True) 'parse sheet1 of sample.xlsx file into datatable
For Each row As DataRow In dt.Rows 'access rows
	For i As Integer = 0 To dt.Columns.Count - 1 'access columns of corresponding row
		Console.Write(row (i) & "  ")
	Next i
	Console.WriteLine()
Next row
VB   C#

Read CSV File Using C# (Code Example Tutorial), Figure 4: Accessing data from CSV file and display into the Console Accessing data from CSV file and display into the Console

Converting a CSV File string line to Excel Format

The procedure is straightforward: loading a CSV file and saving as an Excel file.

WorkBook workbook = WorkBook.LoadCSV("test.csv", fileFormat: ExcelFileFormat.XLSX, ListDelimiter: ",");
WorkSheet ws = workbook.DefaultWorkSheet;

workbook.SaveAs("CsvToExcelConversion.xlsx");
WorkBook workbook = WorkBook.LoadCSV("test.csv", fileFormat: ExcelFileFormat.XLSX, ListDelimiter: ",");
WorkSheet ws = workbook.DefaultWorkSheet;

workbook.SaveAs("CsvToExcelConversion.xlsx");
Dim workbook As WorkBook = WorkBook.LoadCSV("test.csv", fileFormat:= ExcelFileFormat.XLSX, ListDelimiter:= ",")
Dim ws As WorkSheet = workbook.DefaultWorkSheet

workbook.SaveAs("CsvToExcelConversion.xlsx")
VB   C#

Read and Manipulate Converted CSV Files using IronXL

The IronXL WorkBook class represents an Excel sheet and uses this class to open an Excel file in C#. The code examples below will load the desired Excel file into a WorkBook object:

//Load WorkBook
var workbook = WorkBook.Load(@"Spreadsheets\\sample.xlsx");
//Load WorkBook
var workbook = WorkBook.Load(@"Spreadsheets\\sample.xlsx");
'Load WorkBook
Dim workbook = WorkBook.Load("Spreadsheets\\sample.xlsx")
VB   C#

WorkSheet objects can be found in numerous WorkBooks. These are the Excel document's worksheets. If the workbook has worksheets, you can get them by name by doing the following:

//Open Sheet for reading
var worksheet = workbook.GetWorkSheet("sheetnamegoeshere");
//Open Sheet for reading
var worksheet = workbook.GetWorkSheet("sheetnamegoeshere");
'Open Sheet for reading
Dim worksheet = workbook.GetWorkSheet("sheetnamegoeshere")
VB   C#

Code for reading the cell values:

// Read from Ranges of cells elegantly.
foreach (var cell in worksheet ["A2:A10"])
{
    Console.WriteLine("Cell {0} has value '{1}'", cell.AddressString, cell.Text);
}
// Read from Ranges of cells elegantly.
foreach (var cell in worksheet ["A2:A10"])
{
    Console.WriteLine("Cell {0} has value '{1}'", cell.AddressString, cell.Text);
}
' Read from Ranges of cells elegantly.
For Each cell In worksheet ("A2:A10")
	Console.WriteLine("Cell {0} has value '{1}'", cell.AddressString, cell.Text)
Next cell
VB   C#

The following code sample can update formulas or apply to specific cells after loading and reading the workbook and worksheet. The following is the code:

// Set Formulas
worksheet ["A1"].Formula = "Sum(B8:C12)";
worksheet ["B8"].Formula = "=C9/C11";
worksheet ["G30"].Formula = "Max(C3:C7)";

// Force recalculate all formula values in all sheets.  
workbook.EvaluateAll();
// Set Formulas
worksheet ["A1"].Formula = "Sum(B8:C12)";
worksheet ["B8"].Formula = "=C9/C11";
worksheet ["G30"].Formula = "Max(C3:C7)";

// Force recalculate all formula values in all sheets.  
workbook.EvaluateAll();
' Set Formulas
worksheet ("A1").Formula = "Sum(B8:C12)"
worksheet ("B8").Formula = "=C9/C11"
worksheet ("G30").Formula = "Max(C3:C7)"

' Force recalculate all formula values in all sheets.  
workbook.EvaluateAll()
VB   C#

Conclusion and IronXL Special Offer

IronXL transforms CSVs to Excel with just two lines of code, in addition to CSV processing in C#.

Without the need for Interop, using IronXL's Excel API is a breeze. Furthermore, IronXL also offers a wide range of features to interact with Excel WorkBook, WorkSheet and Cells level such as converting between popular formats, cell data formatting, merging cells, inserting math functions, and even managing charts and adding images.

You can launch without a watermark using IronXL trial licensing keys.

Licenses start at $749 and include one year of free support and updates.

IronPDF, IronXL, IronOCR, IronBarcode, and the IronWebscraper are all part of the Iron Software suite. Iron Software allows you to purchase their entire package for a reduced price. You can use all those tools at the price of two.

This is definitely an option worth exploring.

< PREVIOUS
How to Use a CSV Parser in C#
NEXT >
C# Open Excel File Programmatically (Code Example Tutorial)

Ready to get started? Version: 2024.10 just released

Free NuGet Download Total downloads: 1,039,965 View Licenses >