Advanced Forms – Part 9: Display Types Hi…
Database Design: Import CSV Into MySQL
Blog
Database Design – Part 4: Importing a CSV File Into a MySQL Table
Hi and welcome to Five’s Database Design series. This series of blog posts teaches you how to create a MySQL database from scratch in Five. From adding table fields to creating many-to-many relationships and importing data – we cover everything you need to know to build the perfect database.
This blog post explains how to import a CSV file into a MySQL table. Five lets you develop a MySQL database, one of the world’s most popular relational database management systems (RDBMS). MySQL stores data in related tables, and often developers are asked to populate tables with (real or dummy) data. This can be a time-consuming process. However, with Five’s Import CSV feature, data can be mapped and imported in just a few clicks.
Importing CSV data into MySQL tables is an important step in the development process. Once we have data in our database, we can start thinking about writing SQL queries or creating visualizations, forms, or reports. Inside Five we do not need to write complicated SQL commands to import data, but can do so entirely in point and click using the Import CSV feature.
About Five: More Than Just a Database
Five is a rapid application development environment for building database-driven web applications. Every application developed in Five has its own, fully customizable MySQL database.
In Five, you can create your database like in MySQL Workbench, with one important difference. You don’t need to write SQL commands to do so.
Five gives you the power of a database GUI in an easy-to-use and intuitive web application. You can create tables, assign data types, define table relationships, and import or query data.
Beyond its database features, Five lets you develop an entire web application on your database. Five makes it easy to build a modern web app with forms, charts, dashboards, and PDF reports that are all connected to your database.
Unlike traditional SQL tools, which only serve as a graphical user interface for a database (but are usually not very user-friendly), Five allows you to build and launch a web interface connected to a database. This makes Five ideal for rapidly creating internal databases to store data about products, inventory, business partners, to-do lists, members, suppliers, or other data.
To view Five’s web interface for databases, visit our customer database sample application here.
Five’s Database Design Tools: Tables
Five’s Tables feature lets us view our database tables one by one. For each table, we can view its fields, indices, and where and how it is used in our application.
We can also import or export the data stored in each table. Five gives a pre-built importing and exporting feature, which accepts or generates a CSV file with data.
Let’s learn how to use the Import CSV into Table and Export Table to CSV features.
Using Five to Import a CSV to MySQL
To import data stored in a CSV into a database table using Five, follow these steps:
Prerequisites:
Have at least one application with one existing database table ready in Five, as well as a CSV file with data. The CSV file should have a column header in row 1, followed by data in all subsequent rows.
Tip: Column Headers and Field Name
The CSV file you wish to import should have a column header with descriptive names for each column in row 1. Ideally, the column names match the field names in your database. For example, if you plan to import a CSV file with 3 columns, FirstName, LastName, and City, your database table should have 3 fields with the same names: FirstName, LastName, and City.
Five will automatically match CSV columns to database fields if the names are the same.
1. In Five, select your application and click Manage.
2. Now go to Data > Tables.
3. Click the Import CSV to Table button.
4. Select the table you wish to import data to from the dropdown box.
5. You can choose to Replace Existing Data by clicking the checkbox. In our case, we upload fresh data, so we will not check the box.
6. Click Choose File and select the CSV you wish to import into your MySQL table.
7. If the column headers and field names match, Five will now automatically map the data import for you.
In case there is no match between a field name and a column header, you can manually select or change a field. Remember that the list of dropdown boxes on the right refers to the fields coming from your CSV file. The (read-only) list of fields on the left, on the other hand, refers to your database fields.
If your CSV does not contain a primary key, select Generated from the dropdown box for your primary key field. Five will then automatically generate a primary key for each record in your CSV file. Do not import data without a key field, as Five uses keys to establish relationships between tables.
8. Last, click the Tick ✔️ Button to save. You will now see a pop-up message that says Import of CSV completed successfully.
Done! We have just imported a CSV file into one of our MySQL database tables.
Import CSV into MySQL
Here is a short video on how to import a CSV into MySQL.
Exporting a Table to CSV
You can also export your database table to CSV. Instead of clicking the Import CSV into Table button, click the Export Table to CSV button right next to you to export the records stored inside your database table.
Importing and Exporting CSV Files into MySQL: Practical Tips
Sometimes developers encounter error messages when trying to import a CSV file into MySQL. Usually, these error messages come from data type mismatches, especially about date and date & time formats. To read more about these error messages and find out how to fix them, visit our documentation on importing CSV files into MySQL.
If you would like to import an extremely large CSV with millions of records, our advice is to split it into multiple smaller files and import these one-by-one. Five is designed to handle data at scale, but for large-scale imports a step-by-step approach is advised.
Finding Help
Have more questions? Join our user community to connect with others and get answers.
