Export CSV in ASP.NET Core Web API

A common request for any application containing data, is for that data to be exportable to a CSV file so that users can easily use the data applications such as Excel.

Over the years I've seen many developers create and even try to read CSVs by assuming a CSV is as simple as a bunch of values with a comma between them. Now while a data in this format is a valid CSV, it's not the only possibility. For instance what if one of the values contains a comma? The CSVs spec supports all these scenarios and as a result, if you want to do anything with CSV files, I'd always use a library that has already been written to handle it for you.

Creating a CSV

The first part of exporting a CSV in ASP.NET is to first have a CSV file to export. To create the file I'm using a library called CsvHelper.

As good as this is, quite annoyingly the only instructions are for saving a file to a disc. While I'm sure this is relevant for many people, I'd rather hold it in memory and then return it.

In this example, I'm doing an EF Core query to a stored procedure to create my result set and then using a stream writer to a memory stream for the CSV writer to write to.

var data = await _context.ExportData.FromSqlRaw("EXEC [ExportData]").ToListAsync();

using (var memoryStream = new MemoryStream())
{
  using (var streamWriter = new StreamWriter(memoryStream))
  using (var csvWriter = new CsvWriter(streamWriter, CultureInfo.InvariantCulture))
  {
     csvWriter.WriteRecords(data);
  }
}

Export the CSV File from a Controller

I now have some code which has loaded my CSV into a memory stream. The next part is to create a controller that returns the file.

This is a regular controller inheriting from ControllerBase, but there's a few things to note:

  1. The Get method return type is Task<FileResult> to define that the return type should be a file.
  2. In the attributes I have [Produces("text/csv")] to inform the swagger docs that this API returns CSV files.
  3. The final response is calling return File.
using CsvHelper;
using MyApp.Database;
using Microsoft.AspNetCore.Mvc;
using System.Globalization;
using Microsoft.EntityFrameworkCore;

namespace API.Controllers
{
  [Route("api/[controller]")]
  [ApiController]
  public class CSVExportController : ControllerBase
  {
      private readonly Context _context;

      public CSVExportController(Context context)
      {
          _context = context;
      }

      // GET: api/CSVExport
      [HttpGet]
      [ProducesResponseType(StatusCodes.Status200OK)]
      [Produces("text/csv")]
      public async Task<FileResult> Get()
      {

          var data = await _context.ExportData.FromSqlRaw("EXEC [ExportData]").ToListAsync();

          using (var memoryStream = new MemoryStream())
          {
              using (var streamWriter = new StreamWriter(memoryStream))
              using (var csvWriter = new CsvWriter(streamWriter, CultureInfo.InvariantCulture))
              {
                  csvWriter.WriteRecords(data);
              }

              return File(memoryStream.ToArray(), "text/csv", $"Export-{DateTime.Now.ToString("s")}.csv");
          }
      }

  }
}

In the parameters for the File method I'm calling .ToArray() on the memory stream to turn it into a byte array. I'm also specifying the content type and a filename containing the current datetime. The .ToString("s") on the datetime will format the date into a sortable string.