Learning MySQL and MariaDB

Chapter 15. Bulk Importing Data

You might be asked one day to create a MySQL or MariaDB database that will replace an existing database that uses a different database system—or some other format used for storing data. Or you might be asked to take the data from an application that was not designed for databases, like a spreadsheet. So that you don’t have to manually enter the data, there are ways you can import it. This chapter explains how to bulk import data into a database.

When using other applications, export the data from the source application to a format that MySQL can read, such as a text file containing data separated by particular characters. If you’re given a large amount of data to import, hopefully it will already be well organized and in a data text file. Then you can use the LOAD DATA INFILE statement to import the data.

This isn’t an overly difficult task, but the processing of large amounts of data can be intimidating the first time. It can be a barrier to migrating data to MySQL and MariaDB. There are many nuances to consider for a clean import, which is especially important if you want to automate the process. There may also be restraints to consider when importing data onto a server provided by a web hosting company. We’ll cover all of these in this chapter.

Preparing to Import

To import data into MySQL or MariaDB, the data needs to be in a compatible format. Both database systems will accept a simple text file in which the values are delimited in some way. The ...