How to Import Export CSV file in ASP.NET Core

A CSV (Comma-Separated Values) file is a simple and widely used format for storing tabular data, where each line represents a row and each value is separated by a comma. It is commonly used for exchanging data between different programs, such as spreadsheets and databases, because it is lightweight and easy to read.

In this tutorial we are going to learn how to Import and Export data from a CSV file in ASP.NET Core. The whole source codes of this tutorial can be downloaded from my GitHub repository.

ASP.NET CORE – Import CSV file records to SQL Server Database

I have a CSV file containing employee data. The employee fields are Name, Designation, Salary and DOB. The CSV file is shown by the below image. You can download this csv file from the GitHub repository itself.

Let’s import this CSV file in ASP.NET Core.

Firstly, I install the System.Data.OleDb package from NuGet. It is the Data Provider for OLE DB data sources and will be used to read the CSV file.

Run the following NuGet command to install this provider.

Install-Package System.Data.OleDb

Next, I create the Employee.cs entity class with the same fields as given in the csv file. These are Name, Designation, Salary and DOB.

public class Employee
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Designation { get; set; }
    public Double Salary { get; set; }
    public DateTime DOB { get; set; }
}

Installing Entity Framework Core

Since I will insert the CSV file’s data to the SQL Server database therefore I will need Entity Framework Core. So install the 3 packages:

Install-Package Microsoft.EntityFrameworkCore.SqlServer
Install-Package Microsoft.EntityFrameworkCore.Design
Install-Package Microsoft.EntityFrameworkCore.Tools

Next, add DbContext file called CompanyContext.cs to the app with the following code.

public class CompanyContext : DbContext
{
    public CompanyContext(DbContextOptions<CompanyContext> options) : base(options)
    {
    }
    public DbSet<Employee> Employee { get; set; }
}

To the appsettings.json file, add the database connection string like given below.

"ConnectionStrings": {
  "DefaultConnection": "Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=Company;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False"
}

Register the DbContext in the program class as shown below.

builder.Services.AddDbContext<CompanyContext>(options =>
  options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));

With everything set up, perform the EF Core Migrations by running the following commands one by one.

add-migration Migration1
Update-Database

Create the View and the Controller

Create a new controller called CsvController.cs. In this controller I will add the code for importing csv file’s data.

Firstly, inject “IWebHostEnvironment” and “CompanyContext” on the constructor. IWebHostEnvironment will be needed for reading the wwwroot folder and CompanyContext is used for performing EF core operations.

private IWebHostEnvironment hostingEnvironment;
private CompanyContext context;

public CsvController(IWebHostEnvironment environment, CompanyContext context)
{
    this.context = context;
    hostingEnvironment = environment;
}

After this add action method called “ImportCsv” to the controller. This action method will perform the CSV Import operation. See the code given below.

public IActionResult ImportCsv()
{
    return View();
}

[HttpPost]
public async Task<IActionResult> ImportCsv(IFormFile csvfile)
{
    // By old OleDbConnection way

    string path = Path.Combine(hostingEnvironment.WebRootPath, "CSV/" + csvfile.FileName);
    using (var stream = new FileStream(path, FileMode.Create))
    {
        await csvfile.CopyToAsync(stream);
    }

    string folderPath = Path.Combine(hostingEnvironment.WebRootPath, "CSV"); 
    string connectionString = $@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={folderPath};Extended Properties=""text;HDR=YES;FMT=Delimited;IMEX=1;MaxScanRows=0""";

    using (var conn = new OleDbConnection(connectionString))
    {
        conn.Open();
        var query = $"SELECT * FROM [{csvfile.FileName}]"; // The file name is used in the query
        using (var adapter = new OleDbDataAdapter(query, conn))
        {
            var dataTable = new DataTable();
            adapter.Fill(dataTable);

            List<Employee> records = dataTable.AsEnumerable().Select(row => new Employee
            {
                Name = row.Field<string>("Name"),// Use .Field<T>() for type safety and null handling
                Designation = row.Field<string>("Designation"),
                Salary = row.Field<Double>("Salary"),
                DOB = row.Field<DateTime>("DOB")
            }).ToList();

            context.AddRange(records);
            context.SaveChanges();
            ViewBag.Result = "Import Successful";
        }
    }

    return View();
}

The action method of type Post receives the csv file uploaded from the view. I then save this csv file inside the wwwroot/CSV folder by using the IWebHostEnvironment object.

string path = Path.Combine(hostingEnvironment.WebRootPath, "CSV/" + csvfile.FileName);
using (var stream = new FileStream(path, FileMode.Create))
{
    await csvfile.CopyToAsync(stream);
}

Next, with System.Data.OleDb provider the CSV file is read. Note that this csv file is stored inside the wwwroot/CSV folder.

string folderPath = Path.Combine(hostingEnvironment.WebRootPath, "CSV");
string connectionString = $@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={folderPath};Extended Properties=""text;HDR=YES;FMT=Delimited;IMEX=1;MaxScanRows=0""";

The actual CSV reading code is given below where a select query – SELECT * FROM [{csvfile.FileName}] is executed to read the CSV data. Then the data is filled to a DataTable using SqlDataAdapter.

Once the DataTable is filled, it is enumerated to fill the data in a List of Employees and then finally the EF Core AddRange() method is used to insert the data to the database table. See the below code to understand it’s working.

using (var conn = new OleDbConnection(connectionString))
{
    conn.Open();
    var query = $"SELECT * FROM [{csvfile.FileName}]"; // The file name is used in the query
    using (var adapter = new OleDbDataAdapter(query, conn))
    {
        var dataTable = new DataTable();
        adapter.Fill(dataTable);

        List<Employee> records = dataTable.AsEnumerable().Select(row => new Employee
        {
            Name = row.Field<string>("Name"),// Use .Field<T>() for type safety and null handling
            Designation = row.Field<string>("Designation"),
            Salary = row.Field<Double>("Salary"),
            DOB = row.Field<DateTime>("DOB")
        }).ToList();

        context.AddRange(records);
        context.SaveChanges();
        ViewBag.Result = "Import Successful";
    }
}

Create a new razor view file called ImportCsv.cshtml with the following code.

@{
    ViewData["Title"] = "Import CSV";
}

<h1 class="bg-info text-white">Import CSV</h1>
<a asp-controller="Home" asp-action="Index" class="btn btn-secondary">Back</a>
<h2 class="bg-success text-white">@ViewBag.Result</h2>
<form method="post" enctype="multipart/form-data">
    <div class="form-group">
        <label>Select CSV File</label>
        <input type="file" name="csvfile" class="form-control" />
    </div>
    <button type="submit" class="btn btn-primary">Import</button>
</form>

Let’s run the app to test the CSV import feature. The form will contain a file upload where I need to select the CSV file. Click the import button to perform the import task.

Lets check the Employee table, where I can see all the data is successfully inserted to the table.

So this successfully completes the CSV import feature in ASP.NET Core. Next, I will perform the CSV export in ASP.NET Core.

ASP.NET CORE – Export CSV file records from SQL Server Database

I will now perform the export of Employee records from the SQL Server database to a CSV file. First of all I will show all the Employees in an HTML Table. This HTML table will have a checkbox against each record, for enabling user to select the employees whose data needs to be saved to a CSV file. After the employees are selected, the user clicks a button to generate this CSV file.

The below image shows this:

Start by adding a new ViewModel called EmployeeViewModel.cs. This is the same like Employee.cs class except that a new property “IsChecked” is added for the checkboxes. Recall that I will be adding checkboxes against each record for enabling the user to select the records from the html table.

public class EmployeeViewModel
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Designation { get; set; }
    public Double Salary { get; set; }
    public DateTime DOB { get; set; }
    public bool IsChecked { get; set; }
}

Add a new action method called “ExportCsv” to the controller. This action method reads the employee records from the database and returns them to the view where they will be displayed in an HTML Table.

public IActionResult ExportCsv()
{
    List<Employee> eList = context.Employee.ToList();

    List<EmployeeViewModel> records = eList.AsEnumerable().Select(row => new EmployeeViewModel
    {
        Id = row.Id,
        Name = row.Name,
        Designation = row.Designation,
        Salary = row.Salary,
        DOB = row.DOB
    }).ToList();

    return View(records);
}

Next, add the razor view file called ExportCsv.html with the following code.

@{
    ViewData["Title"] = "Export CSV";
}

@model List<EmployeeViewModel>

<h1 class="bg-info text-white">Export CSV</h1>
  
<a asp-controller="Home" asp-action="Index" class="btn btn-secondary">Back</a>

<form method="post">
    <table class="table table-sm table-bordered">
        <tr>
            <th>Id</th>
            <th>Name</th>
            <th>Designation</th>
            <th>Salary</th>
            <th>Date of Birth</th>
            <th></th>
        </tr>

        @for (int i = 0; i < Model.Count; i++)
        {
            <tr>
                <td>
                    <label asp-for="@Model[i].Id">@Model[i].Id</label>
                    <input type="hidden" asp-for="@Model[i].Id" />
                </td>
                <td><label asp-for="@Model[i].Name">@Model[i].Name</label></td>
                <td><label asp-for="@Model[i].Name">@Model[i].Designation</label></td>
                <td><label asp-for="@Model[i].Name">@Model[i].Salary</label></td>
                <td><label asp-for="@Model[i].Name">@Model[i].DOB</label></td>
                <td><input type="checkbox" asp-for="@Model[i].IsChecked" /></td>
            </tr>
        }
    </table>
    <button type="submit" class="btn btn-primary">Export</button>
</form>

In the above code the html table is created to show all the employee records. Also note the checkbox given against each employee to enable user to select the employee records.

<input type="checkbox" asp-for="@Model[i].IsChecked" />

Also notice a hidden field which will transfer the Ids of employees to the post action method in the controller.

<input type="hidden" asp-for="@Model[i].Id" />

Next, add the ExportCsv action of type POST. This action method will perform the creation of CSV file from the selected employee records. See the below code.

[HttpPost]
public IActionResult ExportCsv(List<EmployeeViewModel> empList)
{
    var selectedRecords = empList.Where(r => r.IsChecked).Select(r => r.Id).ToList();
    var emp = context.Employee.Where(o => selectedRecords.Contains(o.Id)).ToList();

    var sb = new StringBuilder();

    sb.Append("Id" + ',' + "Name" + ',' + "Designation" + ',' + "Salary" + ',' + "DOB"); // header
    sb.Append("\r\n"); // New line after header

    foreach (var e in emp)
    {
        sb.Append(e.Id.ToString() + ',' + e.Name + ',' + e.Designation + ',' + e.Salary + ',' + e.DOB);
        sb.Append("\r\n");
    }

    return File(Encoding.UTF8.GetBytes(sb.ToString()), "text/csv", "exportdata.csv");
}

I first read all the employee Id that contains the IsChecked property value to be true. These are the selected employees.

var selectedRecords = empList.Where(r => r.IsChecked).Select(r => r.Id).ToList();

Then with EF Core, I read these Id’s records from the database.

var emp = context.Employee.Where(o => selectedRecords.Contains(o.Id)).ToList();

With the StringBuilder, I create a CSV file (which is comma separated), adding both the header row and the employee data of selected employees.

Then converting the data to a CSV file and downloading this file to the client browser from the memory.

return File(Encoding.UTF8.GetBytes(sb.ToString()), "text/csv", "exportdata.csv");

I can now select the employee records for generating the CSV file. Lets say I select employee with Id 2 and 10. The generated CSV will contain only these 2 records. Check the generated CSV file image given below.

In the tutorial I created both the CSV import and export CSV feature in ASP.NET Core. I hope you understood each and every part of it. You can use the source codes of this tutorial by downloading the GitHub repository. The link is given at the top.