1. IronXL
  2. IronXL Blog
  3. Using IronXL
  4. C# Read CSV File Line By Line

Published June 30, 2022

Read CSV File Using C# (Code Example Tutorial)

As a developer, having many different methods to read a CSV file and extract data from various file-based formats such as CSV, Excel, and spreadsheets in C# presents a challenge in determining which method would best address our problem. However, recently I came across a program called IronXL for using Excel in the NuGet Package Manager console, which does not require office interop or the installation of MS Office, is highly efficient and effective, allowing us as developers to quickly Read one line, Generate, and Edit Excel files across .NET apps and websites. In this article, I'll walk you through getting started with this library.

How to Read CSV File Line By Line in C#

  1. Install C# library to read CSV file line by line
  2. Utilize advance LoadCSV method to read CSV file
  3. Catch loading a nonexistence file with Exists method in C#
  4. Convert CSV data into database with ToDataTable method
  5. Use SaveAs method to export Excel file, while CSV data converted to Excel automatically

What exactly is a CSV file?

CSVs (Comma Separated Values) are widely used in spreadsheets and databases to import and export table data. Data is usually stored on a single line, separated by commas. For our projects, we need to use our own CSV Help package. Although the comma-separated file is a simple data format, there are many variations. Different delimiters, new lines, and quotes are examples of this. With the help of the CSV Help library, you can read and write CSV data.

How to read CSV Files in C

You must first install IronXL before using it to read CSV files in MVC, ASP, 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.

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#

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:

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. When converting CSV files to C#. NET, I recently noticed the following options: It's the first time I've worked with only one string. Instead of a simple, use a split ('). Strings. To separate the values in a comma, use Split(). This article will look at the best ways to use C++'s CSV parser functionality in C.XcPC.NET.

Parsing records using the StreamReader class

The first step is to verify that the record you want to parse exists. mHasException and mLastException are from a base exception elegance that the parsing class inherits in the following code block. A ValueTuple is the go-back type (established the usage of the NuGet package deal supervisor).

if (!File.Exists(_inputFileName))
{
    mHasException = true;
    mLastException = new FileNotFoundException($"Missing {_inputFileName}");
    return (mHasException, new List<DataItem>(),new List<DataItemInvalid>() );
}
if (!File.Exists(_inputFileName))
{
    mHasException = true;
    mLastException = new FileNotFoundException($"Missing {_inputFileName}");
    return (mHasException, new List<DataItem>(),new List<DataItemInvalid>() );
}
If Not File.Exists(_inputFileName) Then
	mHasException = True
	mLastException = New FileNotFoundException($"Missing {_inputFileName}")
	Return (mHasException, New List(Of DataItem)(),New List(Of DataItemInvalid)())
End If
VB   C#

If the document already exists, the next step is to create numerous variables for use in validation functions and back sorts to include legitimate and, if necessary, invalid statistics while examining data from the CSV file.

var validRows = new List<DataItem>();
var invalidRows = new List<DataItemInvalid>();
var validateBad = 0;

int index = 0;

int district = 0;
int grid = 0;
int nCode = 0;
float latitude = 0;
float longitude = 0;
var validRows = new List<DataItem>();
var invalidRows = new List<DataItemInvalid>();
var validateBad = 0;

int index = 0;

int district = 0;
int grid = 0;
int nCode = 0;
float latitude = 0;
float longitude = 0;
Dim validRows = New List(Of DataItem)()
Dim invalidRows = New List(Of DataItemInvalid)()
Dim validateBad = 0

Dim index As Integer = 0

Dim district As Integer = 0
Dim grid As Integer = 0
Dim nCode As Integer = 0
Dim latitude As Single = 0
Dim longitude As Single = 0
VB   C#

C# Records — Reading CSV Data

The reader will go on to the following file due to this process. In trygetfield, we read all the rows in the CSV field files. We use the read function on fields in CSV files as record fields. Here is a code example. 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#

Converting a CSV File string line to Excel Format

The procedure is straightforward. It is usually accomplished using just one line of code to convert a string line.

Formats for converting CSV to Excel include:

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. We utilize a WorkBook to open an Excel file in C#. Load the Excel file and choose its location (.xlsx). To open the file for reading, use the one-line code examples below:

//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#.

IronXL's Excel API can be used without the need for Interop using C# or VB.NET. Excel spreadsheets may be read, edited, and created, as can other Excel formats such as XLS/XLSX/CSV/TSV. You can get five items for two if you use various frameworks.

Without the need for Interop, using IronXL's Excel API is a breeze. You can look at, edit, and create Excel spreadsheets and deal with various Excel codecs such as XLS/XLSX/CSV/TSV.

You can launch without a watermark using IronXL licensing keys.

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

You can also try IronXL free for 30 days with a trial license key.

Iron Software allows you to purchase their entire package for a reduced price.

IRONPDF, IRONXL, IRONOCR, IRONBARCODE, and the IRONWEBSCRAPER are all part of the Iron Software suite.

This is definitely an option worth exploring.