How to import a CSV file in ASP.NET Core using C# and UseCSV

CSV import is one of those things most developers feel initially excited about, but end up hating - encoding issues, special characters, inconsistent date formats, and large files are just some of the problems you will face, especially if you deal with CSV files generated by Excel.

Of course, that’s just the back-end, data processing part of the puzzle.  When you factor in the time needed to build an import wizard front-end, you’re likely looking at weeks of effort.

Fortunately, with tools like UseCSV, you can add a modern CSV import feature to your ASP.NET app in a fraction of the time.

What you will build

In this tutorial, you will create a simple ASP.NET blog that can import data from a CSV or Excel file. You will be using a simple JSON file for storage, and UseCSV for beautiful importing workflow. In about 30 minutes, you will have a functioning blog that can be maintained by simply re-importing your latest posts.

Here is a video showing the end result of this project:

Prerequisites

To follow this tutorial, you will need:

• A UseCSV developer account. Sign up for free at usecsv.com
• .NET SDK 7 installed on a Windows or MacOS machine
• A sample CSV file for testing. You can use this Google Sheets file.

What is UseCSV?

UseCSV is a fully featured CSV import package that solves two big problems: having a great-looking CSV import wizard for end users, and painlessly processing any CSV file you throw at it.  All you have to do is embed UseCSV into your front end using the provided JS library and configure a webhook to be called when the data is processed.

You get a clean JSON result mapped to your data model, and your users get a beautiful import wizard that lets them match CSV file columns to your column names and fix any errors found in the file.

The main reasons to UseCSV are:

• Frontend libraries that add the UseCSV import button and modal to your app
• Supports CSV and all Excel formats (xlsx, xls, xlt), including legacy Excel versions
• Handles large import files
• Includes a library of common validation rules to choose from

Step 1 - Setup a new .NET project

Create a project folder

Open a terminal window and create a new ASP.NET Razor Pages project by running the following command:

Copy text
dotnet new webapp -n CSVBlog

The preceding command:

• Creates a new web app using the Model-View-Controller pattern.
• Creates a directory named CSVBlog with the source files for the app. You can use a different name for your project if you wish.

Trust the development certificate

Run the following command:

Copy text
dotnet dev-certs https --trust

This command will install an SSL certificate for use with HTTPS endpoints during development. It might prompt you to confirm the installation.

Start the app

Verify the application has been set up correctly by running the following commands:

Copy text
cd CSVBlog
dotnet watch --launch-profile https

The above commands will:

• Open the project directory
• Run the .NET app in watch mode, refreshing as you make changes to the files
• Use the HTTPS profile

Leave this command running. Use a new terminal window to run all commands below.

Step 2 - Create a simple blog from a JSON file

If you successfully completed the steps above, you should have a basic ASP.NET Razor pages project running in your browser that looks like this:

Let’s turn it into a blog.

Create a sample JSON data file

In your project root folder, create a new directory named Data, then add a posts.json file to it with the following content:

./Data/posts.json

Copy text
[
  {
    "Category": "Programming",
    "Title": "Getting Started with ASP.NET Core",
    "Date": "2023-08-15",
    "Content": "ASP.NET Core is a powerful framework for building web applications. In this tutorial, we'll cover the basics of setting up a new ASP.NET Core project, including how to configure your development environment and create a simple 'Hello World' application.",
  }
]

Create a blog post model

Create a C# class to represent a blog post. Place it in the Models folder.

./Models/BlogPost.cs

Copy text
namespace CSVBlog.Models;
public class BlogPost
{
    public string Date { get; set; }
    public string Category { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }
}

Update the Home Controller

Now we need to modify your HomeController to read the data from a JSON file and return it to the view for rendering. We also need to tell it to update the JSON file when a POST request is received, which we will use later to import blog posts from a CSV file.

First, install the Newtonsoft JSON package. Open a terminal window and type the following command:

Copy text
dotnet add package Newtonsoft.Json

Next, update the Index.cshtml.cs file in the Pages folder to instruct the app to load the JSON library and your newly created BlogPost class. Add these two lines to the beginning of the file:

./Pages/Index.cshtml.cs

Copy text
using Newtonsoft.Json;
using CSVBlog.Models;
... 

Now turn your attention to the IndexModel class in the same file and replace it with the following code

./Pages/Index.cshtml.cs

Copy text
...

public class IndexModel : PageModel
{
    private readonly ILogger<IndexModel> _logger;

    public IndexModel(ILogger<IndexModel> logger)
    {
        _logger = logger;
    }

    public List<BlogPost> BlogPosts { get; set; }

    public void OnGet()
    {
        var filePath = "Data/posts.json";
        BlogPosts = new List<BlogPost>();

        if (!System.IO.File.Exists(filePath)) return;

        try
        {
            var json = System.IO.File.ReadAllText(filePath);
            BlogPosts = JsonConvert.DeserializeObject<List<BlogPost>>(json) ?? new List<BlogPost>();
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "An error occurred while deserializing the blog posts.");
            return;
        }
    }

    public IActionResult OnPost([FromBody] List<BlogPost> newBlogPosts)
    {
        try
        {
            var updatedJson = JsonConvert.SerializeObject(newBlogPosts, Formatting.Indented);
            System.IO.File.WriteAllText("Data/posts.json", updatedJson);  // Adjust the path as needed
            BlogPosts = newBlogPosts;

            return Page();
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "An error occurred while updating the blog posts.");
            return BadRequest("An error occurred while updating the blog posts.");
        }
    }
}

The code above:

• ensures the BlogPosts property is made available to the homepage view for rendering purposes.
• When the page is loaded, the onGet() method populates it with data from your JSON file
• When a POST request is made to the homepage, the onPost() method receives a JSON payload and write it back to the posts.json file. We will rely on this method to update our blog.

Display blog posts on your homepage

Open the file Index.cshtml located in the Pages folder, and add the following code to end of the file:

./Pages/Index.cshtml

Copy text
...
<div class="container pt-5 w-75 ">
    @if (Model.BlogPosts != null && Model.BlogPosts.Any())
    {
        foreach (var post in Model.BlogPosts)
        {
            <div class="row pt-4 pb-3 border-bottom">
                <div class="col-12 col-sm-3 mb-2">
                    <div class="text-uppercase small font-weight-bold">
                        <a href="/" class="text-decoration-none" aria-label="Category">@post.Category</a>
                        <p class="text-muted">@post.Date</p>
                    </div>
                </div>
                <div class="col-12 col-sm-9">
                    <div class="mb-2">
                        <a href="/" aria-label="Article" class="text-decoration-none text-dark">
                            <h3 class="font-weight-bold">@post.Title</h3>
                        </a>
                    </div>
                    <p class="text-muted">
                        @post.Content
                    </p>
                </div>
            </div>
        }
    }
</div>
<div class="text-center py-5">
    <button id="import-data-button" class="btn btn-primary">
        Import Posts
    </button>
</div>
<form method="post"></form>

This code will loop through the BlogPosts loaded from your JSON file and display them on the blog.

Notice the empty form at the bottom - it is there to capture the anti-forgery token that is automatically inserted into forms as hidden input field by ASP.NET Core framework. We won’t use the form itself, but we will need the token for an ajax call that imports posts from a CSV.

Step 4 - Add the ability to import blog posts from CSV or Excel

If you completed the steps above successfully, your live app should now display the placeholder post from your JSON file:

Let’s add the blog post import functionality.

Configure your importer

Go to usecsv.com and open a free account. After you log in, you will see a screen showing your first importer.

You can think of an importer as a schema for each type of import you have (i.e. users, posts, orders, etc.). Click on the importer name to configure it

In the top section, make the following changes and click Save Changes:

• Name: CSV Blog
• Where to send uploaded data: OnData Callback
• Development Mode: ON

These settings configure the friendly name for our importer and specify the data should be processed on the client, without sending it to UseCSV servers. Development mode ensures we can test without hitting our data limits quickly.

Scroll down to the columns section, and add the columns for each blog post property defined in the BlogPost class.

For each column:

• Enter a name that is identical to the property in your BlogPost class (case sensitive)
• Turn on the “Must be matched” and “Value cannot be blank options”
• Add a description and example that will help users understand what the column is about

When done, your columns section should look like this:

Add the importer to your front-end

Now it’s time to add the importer to your homepage front-end.

Create new file home.js file in wwwroot/js folder, and paste the following code. Make sure you replace the your-importer-key with the actual key obtained in the previous step.

./wwwroot/js/home.js

Copy text
var importButton = document.getElementById("import-data-button");
importButton.onclick = function () {
  useCsvPlugin({
    importerKey: "your-importer-key",
    onData: updateBlog,
  });
};

async function updateBlog(data){
// To-Do: Updates blog using the data from the imported file
}

This code initializes the importer you confgured earlier and attaches it to the “Import Data” button on your page. It also specifies imported data should be passed to the updateBlog function, which we will expand upon next.

For now, let’s open the Index.cshtml file and again and add the following lines to the end of the file:

./Pages/Index.cshtml

Copy text
...
<script src="https://unpkg.com/@@usecsv/js/build/index.umd.js"></script>
<script src="~/js/home.js"></script>

This code loads the importer library from the CDN and your local javascript file that handles the import process.

Finally, check if the importer works by clicking the Import Posts button in your app. You should see the importer UI load with an initial screen describing supported columns.

Post the imported data to your back-end

Back in your home.js file, replace the updateBlog function with the following code:

./wwwroot/js/home.js

Copy text
...
async function updateBlog(data) {
  try {
    const token = document.querySelector(
      'input[name="__RequestVerificationToken"]'
    ).value;

    const posts = JSON.stringify(data.rows);

    const response = await fetch("/", {
      method: "POST",
      body: JSON.stringify(data.rows),
      headers: {
        RequestVerificationToken: token,
        "Content-Type": "application/json",
      },
    });

    if (response.ok) {
      window.location.reload();
    } else {
      const text = await response.text();
      alert(
        `Failed to update blog posts. Status: ${response.status}, Message: ${text}`
      );
    }
  } catch (error) {
    console.error("Error:", error);
    alert(`An error occurred: ${error}`);
  }
}

This code:

• receives the data from the CSV importer
• reads the anti-forgery token from the hidden input field automatically inserted by ASP.NET framework
• creates a POST request to homepage and sends the data to the OnPost() method, which in turns updates the post.js file in the Data folder
• Reloades the homepage to display the newly uploaded posts

Step 5 - Test the importer

If you completed all of the steps correctly, test the importing process by using a sample data file on Google Sheets. Feel free to modify the file in any way you want.

Once you go through the importer steps, you should see your blog update with the posts from your CSV file, as shown in the video below.

Notice how UseCSV intelligently mapped the Content Summary column from the CSV file to the actual Content column you have in the system.

There are many more features like this that make UseCSV a great choice for managing the CSV or Excel file imports.

Next steps

In the tutorial above, you learned how to create a basic blog import functionality using ASP.NET Razor Pages and UseCSV.

You could expand on this by adding the ability to open each post on a separate page, and put the Import Posts button behind a login so only you can use it. This would effectively give you a fully functioning personal blog you could maintain with nothing but an Excel file.

By eliminating the hard work related to manually handling the upload and processing the CSV or Excel files, you are able to free up the time for more creative and valuable work.

Continue exploring UseCSV features to see what else is possible.