3 Steps To Quickly Build a Web-Based Database Application

How To Convert a CSV into a Web-Based Database Application in 3 Steps

Turn A Spreadsheet Into a Web-Based Database Application on MySQL – A Beginner’s Guide

Using Microsoft Excel or Google Sheets as a database is the perfect starting point for many application development ideas. And rightfully so.

For a small amount of data, the columns and rows of a spreadsheet are the perfect places for storing your data. It is easy and free to get started with an Excel document or on Google Sheets. And it works fine for as long as the data stays manageable. But what if your data grows larger, or your spreadsheet becomes critical to your business?

Is Excel a Database?

Many successful application development ideas quickly outgrow what a spreadsheet is designed to handle.

The larger a spreadsheet grows in size, the slower, buggy, and inefficient they get. Or, the more users require access to the spreadsheet, the more insecure they become. The painful lesson for anyone testing the limits of their spreadsheet is this: Excel is not a database.

The same applies to Google Sheets. Google Sheets can be used to store data. It cannot be used as a relational database.

The proper way to store data in a safe, secure, and reliable manner is by using a relational database. For any business that is data-based, relational databases will be part and parcel of their IT landscape.

Now, follow along as we explain how to turn a spreadsheet into a web-based database application.

Prerequisites, Approach & Prior Knowledge

In this tutorial, we will go through the steps of turning a spreadsheet into an online database.

Build a Web-Based Database Application
Sign Up to Follow this Tutorial

We will show you to create a web-based database application using a MySQL database:

1. We will start by importing our data from Google Sheets into MySQL, the world’s most popular open-source database.
2. We will then build a form inside our web application to give end-users the possibility to interact with our data.
3. We will also write an SQL query to query our data.

It doesn’t matter if your data source is on Google Sheets or a Microsoft Excel spreadsheet. The steps to go from Excel to MySQL are the same.

To build a web-based online database application, we will use Five, an online database builder for rapid web application development on MySQL.

To follow along, make sure you have signed up for a free Five trial.

You will not need any other MySQL GUI, such as MySQL Workbench, or Google Apps scripts. Instead, we will import the data contained in your spreadsheet straight into MySQL using Five’s CSV import function.

This tutorial does assume, however, that you have a basic understanding of relational databases and how they store data. In particular, you should know what Primary Keys and Foreign Keys are. If you are new to relational databases, we recommend completing a free SQL courses on W3School or Udemy first.

EXPERT TIP: A spreadsheet stores data in a flat, single-table format, meaning all of your is typically within one sheet. A relational database stores data differently using related tables.

In a relational database, data is stored in related tables so that no data needs to be stored twice. This explains why they are more performant than a spreadsheet.

When converting a CSV to a web app, the hardest part is getting the database model and the table relationships right. This is where the learning curve is the steepest. Problems in your data structure can haunt you later on in your application development. The easy part is then importing your Excel to MySQL.

What is Five?

Five is a low-code development environment for building online database applications on a MySQL database.

Inside Five, you can import a CSV file into a MySQL database in just a few clicks. You can then also build a web front end on your MySQL database. This front end lets your end-users read or write data from or to the database.

Five gives you several powerful features to create and manage your MySQL database. Inside Five, you can:

1. create and manage a MySQL database visually from scratch,
2. define data and display types for each database field. Five has a variety of commonly used data types, such as strings, floats, Booleans, radio buttons, ratings, etc.
3. create relationships between tables without writing any code,
4. import data from a CSV into a MySQL database in just a few clicks,
5. add new fields to existing databases, and pre-fill these fields based on fixed values, existing fields, or SQL queries.
6. Create a web-based database application with a pre-defined user interface.

All of this happens in an intuitive, point-and-click interface. Five even generates the primary keys for you, so that each record inside a table can be uniquely identified. This ensures data integrity, even for large datasets.

Turning A CSV Into a Web-Based Database Application

Here are the three steps that we will take to turn a spreadsheet into a web-based database application:

1. Create your MySQL table or Entity Relationship Diagram (ERD) using Five and import your data stored inside a CSV file.
2. Build a form to create, read, update, or delete data inside your web-based database application.
3.  Preview your application.

Done! These three steps describe everything we need to do to build a CRUD application from Google Sheets.

To see how Five helps software developers build a web-based database application on MySQL, watch this video:

1. Creating Tables

The first step in creating a MySQL database with Five is defining your table. This can be done entirely in point-and-click. Use Five’s Table Wizard to create your tables in no time at all. Find the Table Wizard by clicking on Data > Table Wizard. 

To define a table correctly, put your Excel column names as the table field names. This enables Five to then automatically match the column names to the field names. It makes importing your Excel data to MySQL more seamless.

How To Import Google Sheets to MySQL

To import a CSV to MySQL, use Five’s in-built import functionality. This is as simple as selecting your CSV file. Just make sure that the data contained in your CSV file maps to the fields inside your MySQL database correctly.

Five’s CSV import function can be found by clicking on Data > Tables > Import CSV into Table (see image below to find the icon to launch the import feature).

EXPERT TIP: When you import your CSV, Five will automatically assign a Primary Key to each record. This is how Five identifies your record. You can see your Primary Key by looking at your table fields. The Field marked as a GUID is your table’s Primary Key.

2. Building The Database Application’s Front End

To build a web-based database application, we can create forms inside Five.

By using Five’s Form Wizard, we can automatically create form fields for each database field. This form will then be accessible to end-users. Other elements that can be developed inside of Five are charts or dashboards for data visualization, business intelligence, or reports.

Five is best used to build business applications, as it provides an out-of-the-box pre-built user interface for an online database:

3. Preview Your Application

To preview your application, all you have to do is click the deploy button inside Five. Note that when you are developing the application using Five’s free trial, you will not be able to deploy the application to the web. The free trial is a contained environment to develop and test applications free of charge locally.

Build a Web-Based Database Application

For a more detailed, step-by-step guide on how to build a web-based database application, follow our free step-by-step tutorial here.

Five Reasons Why You Should Replace Your CSV With a Web-Based Database Application

If you’re new to programming, MySQL online databases might seem intimidating at first. The way they store data is different from your typical Excel or Google Sheets spreadsheet.

However, they are extremely powerful, easy to set up, and will not break, even when your dataset grows in size. They are also extremely popular, powering most of the world’s largest websites.

And they have been around for a long time. Relational databases, such as MySQL, SQLite, PostgreSQL, or Microsoft SQL Server are a technology that is truly tried and tested.

There are plenty of advantages of a MySQL online database over an Excel or CSV file. To name just a few:

1. Size: A MySQL database can store much more data than regular spreadsheets (Excel or Google Sheets). As of the time of writing, the maximum number of cells in a Google Sheet is 10 million. And once you’re nearing 10 million, expect performance to deteriorate quickly. A relational database stores data much more efficiently and can deal with much larger datasets than a spreadsheet can.
2. Speed: Because of the way SQL databases are set up, data retrieval is much faster and more efficient in a relational database than in a spreadsheet. Data retrieval and storage are extremely efficient through the use of (primary and foreign) keys that serve as unique identifiers for each row and that help connect related tables.
3. Data Integrity and Security: Inside Five, you can easily assign Create / Read / Update / Delete (CRUD) permissions for different user roles and per database table. To give an example: Your field staff is only allowed to read and update data, but not allowed to create or delete it. This can be easily set up inside Five, and without writing any code. This also helps ensure data integrity. Neither Excel nor Google Sheets allows for such granular management of access rights and CRUD permissions.
4. Data Types: online databases use data types that define the permissible input for each cell. Five offers a wide range of standard data types, such as strings, floats, integers, etc. In addition, Five can help validate end-user input through custom display types. These can be regular expressions that help check whether an input is a valid email address. Through the use of data and display types, it’s easy to avoid poor data quality.
5. Data Sciences: nowadays, most data is used for further analysis, be it through dashboards or more advanced data science concepts. Through the use of an online database, you have the right foundation to support your application’s growth from day one.

Last, Excel or Google spreadsheets are not relational databases. They do not offer safe ways to store, retrieve or share data.

Where Do I Start?

Sign up for a free trial on our website. Once you have access to Five, you can develop and test your web-based database application free of charge. And once your application is ready for deployment, sign up for a paid subscription and launch the application to your end-users, starting from as little as US$29.99 per app and month with unlimited end-users.