Integrating .xlsx File Uploading with a React App’s Back-end

This is a tutorial on how to develop a simple React app that renders a table of data alongside providing users the option to upload Excel spreadsheets to be parsed and inserted into a PostgreSQL database. In this tutorial, we will walk through:

• uploading and storing a file on React local state to send to the back-end
• creating a POST API route to store the file in your local directory with Multer
• parsing the file attached to the request body with read-excel-file and running an insert statement in the database

After completing this tutorial, your project will metamorphose from a sparsely-populated table of one UFO sighting into one that allows for the uploading .xlsx files to exhibit a densely-populated table of many UFO sightings! 🛸✨

The final result

Let’s get started! Clone this repo to follow along. Although this project was bootstrapped with create-react-app, I have added a small Express server file to the original project template. To start up the project, complete the following steps:

1. Create a database called excel-parser-project in the CLI. If you have the command, use createdb. Alternatively, you can use psql and CREATE DATABASE in your terminal.
2. Run these commands in your terminal to install all the dependencies and seed your database:

npm install
npm run seed

3. Start both the front and back-end servers with these two commands:

npm run start
npm run server

Uploading and storing a file on React local state

In the class constructor of your App.js component, add a selectedFile property to state and initiate it with a null value.

Underneath the table, add a form that has a label, an input JSX element with a type=”file” attribute, and a button with a type=”submit” attribute. Attach this.handleSubmit to the form’s onSubmit event, and this.handleChange into the input’s onChange event.

Next, we will write out these handleChange and handleSubmit functions. In your handleSubmit, adding e.preventDefault() is necessary because it prevents the default submission behavior from happening. In the case of an onSubmit event here, we want to prevent the form from getting automatically submitted to the server because we are adding our own logic.

In this function, we are accessing the state we set in the change handler, creating a new form data object using the JavaScript class FormData, and sending it to our back-end post route that we will write next.

You should be able to see two buttons for selecting and uploading a file. Once you click ‘upload’, your selected file will be uploaded onto this.state!

Download this excel file of UFO sighting data. For this project, I just used a spreadsheet from Data.world and cleaned up the date formatting and column headers. You can find the original unaltered source here.

Creating a POST API route to store the file in your local directory with Multer

In the top of your server index.js file, require Multer , fs, and parseFile. Multer is a middleware library that will help us gain more control of multipart form-data requests by specifying the file path and name. The fs module is native to Node.js, and will help us clean up any processed files that get deposited into our designated directory so it doesn’t get polluted over time. parseFile is a function that we will write into an adjacent helpers.js file in a later step because we love modular code!

Let’s set up Multer before defining any routes in server.js. Insert the following code:

Here, we are setting the file destination and file name. We also define the uploading function. Both of these elements will be used in the new post route. To create the new post route, set it up like so:

Great! If you try to upload a file now, it should send the file to your public directory. Check it to make sure it is there, and if it is, delete it. But because we do not want to have to delete it manually every single time a file is uploaded, add fs.unlinkSync(‘public/files/sample-file.xlsx’) to the very end of your post route. This instructs your program to automatically dispose of the file once we are done processing it. Let’s add that processing– namely, parsing the file and uploading it into our PostgreSQL database.

Create a new file in the root directory called helpers.js. At the top, require readXlsxFile. Define the schema object that the readXlsxFile will take in as an option. Because this function returns a promise, we have to use async/await to retrieve the result of the function. If you log the result, you can see that the returned result is an object with keys that represent the columns in your database, and values that represent data records.

In your server.js post route, add the following code to process the uploaded file and insert it into our database:

Your table of UFO sightings should beautifully populate with ~5000 entries after a few seconds of loading.

The full solution code can also be viewed on the solution branch of this repository on Github.