How to import a CSV file in ASP.NET Core using C# and UseCSV
Sep 28, 2023
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:
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:
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:
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:
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:
Create a blog post model
Create a C# class to represent a blog post. Place it in the Models folder.
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:
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:
Now turn your attention to the
IndexModel class in the same file and replace it with the following code
The code above:
- ensures the
BlogPostsproperty 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.jsonfile. 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:
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.
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:
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:
- 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.
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.