JIYIK CN >

This tutorial article will introduce two CSVmethods to import data from a comma separated value ( ) file into a MySQL database and insert it into a table.

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

LOAD DATA INFILE SQLImporting CSV into MySQL

Let's first look at how to use LOAD DATA INFILE SQLthe statement to programmatically import a CSV file.

Before you import a file, you need to create a 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 the following command.

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 accept NULL values ​​and that the table has no primary key, because it is not known in advance whether the data in the CSV file is complete and standardized.

Another suggestion is to use only fields even for numeric data VARCHARto prevent processing errors caused by incorrect data formats in the file. After importing the data, you can clean it directly in the database, read the data of the table, and perform necessary verification and corrections.

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

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

LOAD DATA INFILEThe statement requires you to specify the source file and the target table. You can also optionally specify other parameters such as the field delimiter, text qualifier, and the presence of a header row.

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

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

Now you know how to import CSV files programmatically in MySQL.

Importing CSV files into MySQL using HeidiSQL

Let's look at how to use a graphical MySQL client such as HeidiSQL for interactive operations.

The steps are the same as we saw above.

• Create Table
• Import the contents of the file into it.

When using HeidiSQL to create a table, you need to connect the client to the MySQL server, select the database to import data into, then right-click on the database and select Create New - Table.

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

In this case, the name of the table is BooksCSVand the fields are the same as we used in the previous examples.

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

After creating the table, you can import data from the CSV file. But before starting the import process, you need to enable loading local data on the client and server components by changing the startup parameters of the client and server components. When enabling loading local data, you must consider some safety factors. To learn more about them, read this article in the official MySQL documentation.

When you are ready to import the CSV file, go to the Tools menu of HeidiSQL and select the Import CSV File option. You have to specify the path of the CSV file to be imported and select the database and table where the imported data will be inserted. If the table has as many fields as the fields on the file, you can uncheck the table fields. Finally, click 导入！and the process will begin.

To perform the import, the graphical interface issues the same LOAD DATA command as the programmatic option we described earlier, so the end result is almost the same - the database table will be populated with the data read from the CSV file.

For more information about using the LOAD DATA statement, see this article in the MySQL official documentation.