Designing the Bulk Data Upload Feature of a Web Application

If you design web applications for businesses then I bet you would have someday come across a requirement to bulk upload the data into your web application from your clients existing legacy systems. Or you might have come across a requirement to always keep the data in sync across multiple applications.

While designing web applications for businesses, I find too many people who do not take into account how the bulk upload or bulk update of production data will get handled in their application.

Most of the web applications just have a single option available for all the businesses. That is to populate or update their production data in the application’s database using SQL queries.

Dumping the data into the database using SQL queries or by using a web-service is fine if you plan to integrate two or more applications together or in cases where automation in necessary for populating the data.

But these approaches are not very user friendly if majority of your customer base is made up of normal users who are not tech savvy.

You need to design a light weight feature to populate the production data on an ongoing basis. This feature should be able to bulk upload or update the production data.

Confused what I am blabbering about?

Let me try to explain this with an example.

Bulk Data Upload on Amazon

There are too many vendors who sell their stuff on Amazon day in and day out.

Some vendors just have couple of items to sell while some have hundreds if not thousands of items which they sell through Amazon.

Vendors who have small number of items to sell on Amazon can use Amazon’s “Add a Product” feature to create their items on Amazon.

This approach works fine to quickly create a few items to sell on Amazon. But this way of manually creating items can be a nightmare for vendors who have hundreds of items to sell.

These vendors need a way to quickly upload hundreds of items into Amazon’s database. And if need arises, mass update them at a later point in time.

Amazon could have asked such vendors to use SQL queries or exposed a web-service to populate their items. But then, not all vendors are that tech savvy, right?

So Amazon introduced a user-friendly way in the form of Excel templates which it calls as Inventory File Templates.

One can download these Excel templates, enter the product details, save the file as CSV and upload it back to Amazon.

Here is an example of how the Excel template of one of the category looks like.

Doesn’t this design make it simple for users with no technical background to upload data in bulk?

Yes it does.

By using Excel, Amazon has ensured that their users don’t have to learn a new interface. Also, at the same time Amazon managed to reduce the efforts that might have gone into creating a web interface for this task.

Furthermore, Amazon realizes that this feature will not be used by all vendors. Only vendors with large number of items to sell will be interested in this feature.

And so Amazon makes this feature available only to Professional Sellers who are charged a monthly fee.

This feature becomes a value addition for those vendors opting to pay the fees and become Professional Sellers on Amazon.

This same model can be adopted by any other software makers too.

But can we replicate this design approach in our web application?

Making Amazon’s Design Approach Generic

This design approach is Amazon specific. I mean, if you want to implement such an approach for your client, then you will have to create your own Excel templates.

Now, imagine that you are creating a web application which will be sold to multiple customers. You cannot sit and create Excel templates for every customer.

You need to make this design generic so that every customer of yours can generate their own Excel templates.

How do you do that?

Below I have mentioned one of the design approaches you can take.

**NOTE: I haven’t bothered to style the interface because the intention of this tutorial is to demonstrate how one can functionally design a feature and not how one can design an interface.

For the sake of this tutorial let us assume that the System Administrators of your customers will be generating the Excel templates and exposing them to their users who will use these templates to upload their data into your customer’s database.

Step 1: Let System Administrators Define Excel Columns

Let the System Administrators define the name of the Excel columns in a table on a web page.

Step 2: Expose Database Columns

Now in an adjacent table or column expose the database columns into which data can be populated.

Step 3: Let System Administrators Define the Mappings

Then the System Administrators can just map the defined Excel columns to the database columns. This can be done in two ways:

1. One to One Mapping
2. One to Many Mapping

For One to One Mapping, you can expose the list of database column names as a dropdown list in an adjacent column as shown below:

The System Administrators can then select the desired database column for each of the defined Excel columns.

For One to Many Mapping, you can expose the database column names in a different table and then let the System Administrators define the mappings by dragging and dropping the database columns onto the defined Excel columns.

If the System Administrator drags and drops multiple database columns onto a single Excel column, then you can display the list of the database columns as a comma separated list.

Step 4: Let System Administrators Generate Excel Template

With the mappings in place, the System Administrators can just click a button to generate the Excel file.

This way every customer of yours can generate their own Excel files without you doing it manually for them.

Step 5: Provide Interface to Expose the Excel Templates

You need to provide an interface from where the System Administrators of your customers can expose the generated Excels to their users.

This interface can be a simple hyperlink to download the Excel template. Similar to how Amazon does it.

Step 6: Provide Interface to Upload the Filled in Excel Templates

Now, once the users have downloaded the Excel templates, they can fill it up and save it. Then they can upload that filled in Excel file through an interface you have provided.

This interface can be a simple Upload File UI.

Step 7: Upload the Data to the Database

Once the users have uploaded the filled in Excel files, you need to handle the upload of data from the Excel file to the database columns as per the mappings defined by the System Administrators in step 3.

Extending the Design Further

To make this design even more user-friendly, you can even think of letting the System Administrators save the mappings. So that at a later point in time the System Administrators can just come in, edit some mappings and generate an updated Excel.

To stretch your imagination further, you can even provide a way for the System Administrators to write custom functions as mappings. Say column “Excel Column 2 = Excel Colum 1 * Excel Column 3”

Or you can go ahead and let the System Administrators define some really complex algebraic expressions.

This is just one approach. I am sure you can find many other innovative ways of doing this.

Final Thoughts

Designing the backend of any web application is often the most neglected part. Caring for the user experience of System Administrators is not the first thing on a designer’s mind.

Most of the concentration is always on the user experience of the users of the front-end of the application.

But it doesn’t have to be this way.

Furthermore, having a look at how other people have designed their application can really help us when we sit down to design our own application.

After all, we can all learn from other’s experience. Can’t we?

And finally, if you liked this post please share it. I would really appreciate it.