How To Read Data From .CSV File In C#

Hello Everyone! In this article, I'm going to show you how to read or extract data from a CSV format file in C#.

As a developer, having a plethora of different approaches and practices available at our hards to read and extract the data from different file formats like csv, excel and spreadsheets in C# draws a challenge in finding one option that will solve our problem. However, I found a tool called IronXL.Excel in the NuGet Package Manager console which doesn't require office interop or the need to install MS Office and is very efficient and effective which allows us developers to Read, Generate and Edit Excel files among .NET applications and websites quickly. I will walk through the steps to get started using this library.

Step 1

Install the IronXL.Excel package from NuGet Package Manager in Visual Studio or click this link to download directly from NuGet.

Step 2

Add the Namespace by adding "using IronXL;" to the top of your .cs file.

The ReadExcel Method:

The default method of the IronXL library is ReadExcel which can perform the read operation over an excel file.

This method will read the file data in the Workbook class object. One can perform the operation on Workbook class object based on the requirement.

/// <summary>
/// this method will read the excel file and copy its data into a datatable
/// </summary>
/// <param name="fileName">name of the file</param>
/// <returns>DataTable</returns>
private DataTable ReadExcel(string fileName)
{
    WorkBook workbook = WorkBook.Load(fileName);
    // Work with a single WorkSheet.
    //you can pass static sheet name like Sheet1 to get that sheet
    //WorkSheet sheet = workbook.GetWorkSheet("Sheet1");
    //You can also use workbook.DefaultWorkSheet to get default in case you want to get first sheet only
    WorkSheet sheet = workbook.DefaultWorkSheet;
    //Convert the worksheet to System.Data.DataTable
    //Boolean parameter sets the first row as column names of your table.
    return sheet.ToDataTable(true);
 }

The first entry made in excel or csv file will be considered as column header and the remaining entries will be considered as rows.

The result set will be in the form of DataTable.

Step 3

The below code is used for loading data into the data table from the file using ReadExcel() method in a csv file.

public void ReadCSVData(string csvFileName){
var csvFilereader = new DataTable();
csvFilereader  = ReadExcel(csvFileName);
}

Step 4

The entire data of the csv file is retrieved into the DataTable variable.

The usage of this variable depends upon the requirements.

To access the columns of the csv file, one can use the below code snippet.

string columnData = csvFilereader.Columns[0].ToString(); 

Similarly, to access the rows of the csv file, the below code snippet can be used.

string rowData = csvFilereader.Rows[0].[0].ToString();

Sample Image of .csv File

Note

Whatever the first entry you made to .csv file will be considered as column headings and the remaining entries will be considered as rows

Step 5

Now consider making the model parameters or properties as per the data.

The model class for the above csv file looks like below.

public class SearchParameters  
{  
    public string FirstName{ get; set; }  
    public string LastName{ get; set; }  
    public string Email{ get; set; }  
}

After having the model class declared with the required properties, one can prepare the List of it using the class name simply.

List<SearchParameters> searchParameters = new List<SearchParameters>(); 

Step 6

Once the List has been created, you can add each row to the List as per the property binding.

for (int i = 0; i < csvFilereader.Rows.Count; i++)  
{  
    searchParameters.Add(new SearchParameters { FirstName= csvFilereader.Rows[i][0].ToString(), LastName= csvFilereader.Rows[i][1].ToString(), Email = csvFilereader.Rows[i][2].ToString() });  
}  

Step 7

Now all the rows of the csv file are binded to the searchCSVParameters List.

That's it, one can now use the data of the List.

In the example shown below, the values extracted are sent to another class layer for business logic purposes.

foreach (var searchparameter in searchParameters)  
{  
    var response = await _CSVManager.GetDataByEntry(searchparameter.FirstName, searchparameter.LastName, searchparameter.Email);  
}  

By following the steps above, one can load the data of a csv file into a data table and bind the values to the properties such that they will be used for future use.

Thanks for reading this article. All the best for your future endeavors!