How to Import a CSV File Into a Table in a MySQL Database

1. LOAD DATA INFILE SQL to Import CSV to MySQL
2. Use HeidiSQL to Import CSV to MySQL

This tutorial article will present two methods to import data from a comma-separated values (CSV) file into a MySQL database and insert it into a table.

We will introduce different approaches to import a CSV file into MySQL. This task can be done programmatically, by executing a series of SQL commands; or interactively, by using the file import dialog of a graphical MySQL client, such as HeidiSQL.

LOAD DATA INFILE SQL to Import CSV to MySQL

Let’s first see how to import the CSV file programmatically using the LOAD DATA INFILE SQL statement.

Before you can import the file, you will need to create the table containing the imported data. Suppose your CSV file contains book information, divided into ISBN, Title, Author, and ListPrice fields. To create such a table, you need to execute a command like this:

CREATE TABLE BooksCSV (
    ISBN VARCHAR(255) NULL,
    title VARCHAR(255) NULL,
    author VARCHAR(255) NULL,
    ListPrice VARCHAR(255) NULL
);

It is recommended that all fields admit NULL values and that the table doesn’t have a primary key, because it is not known in advance if the data in the CSV file is complete and normalized.

Another recommendation is to only use VARCHAR fields even in the case of numeric data, to prevent errors in the process due to improperly formatted data in the file. After importing the data, it can be cleaned directly in the database, reading the table rows and making the necessary validations and corrections.

Once you have the destination table for the data, you need to identify the path of the CSV file. Suppose the full path for the file is C:\csvdata\books.csv. Then, to import that file into your table, you should execute the command:

LOAD DATA
INFILE 'c:/csvdata/books.csv'
INTO TABLE BooksCSV ` 

The LOAD DATA INFILE statement requires that you specify the source file and the destination table. Optionally, you could also specify other parameters, such as the field separator character, the text qualifier, and the existence of header rows.

In the following example, the added parameters specify that the fields are separated by commas, that strings are enclosed by double quotation marks, and that the first row must be ignored because it contains column headers.

LOAD DATA
INFILE 'c:/csvdata/books.csv'
INTO TABLE BooksCSV 
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
IGNORE 1 ROWS;

Now that you know how to import a CSV file in MySQL programmatically.

Use HeidiSQL to Import CSV to MySQL

Let’s see how to do it interactively using a graphical MySQL client, such as HeidiSQL.

The steps are the same as we have seen above:

• Create the table
• Import the content of the file into it.

To create the table using HeidiSQL, you need to connect the client to the MySQL server and select the database where you want the data to be imported. Then click the right button on the database and select the Create New - Table option.

You have to enter the name of the table in the corresponding field and add the columns. To add a column, click on the + button, and enter its name and data type.

In this example, the table is called BooksCSV, and the fields are the same we used in the previous example.

Once you finish adding columns, click Save to create the table in the database.

After creating the table, you can import the data from the CSV file. But before launching the import process, you need to enable loading local data on both the client and the server components by changing each one’s startup parameters. There are some security considerations you must take into account when enabling the loading of local data. To learn more about them, read this article from the official MySQL docs.

When you’re ready to import the CSV file, go to the Tools menu in HeidiSQL and select the Import CSV File option. You must specify the CSV file path to import and select the database and the table where you want the imported data to be inserted. You can uncheck table fields if there are more fields in the table as there are on the file. Lastly, click on the Import! and the process will start.

To perform the import, the graphical interface issues the same LOAD DATA command that we previously described for the programmatic option, so the final result is pretty much the same - the database table will be populated with data read from the CSV file.

For more information on using the LOAD DATA statement, check this article from the official MySQL documentation.