This tutorial shows you how to use LOAD DATA INFILE
statement to import CSV file into MySQL table.
The LOAD DATA INFILE
statement allows you to read data from a text file and import the file’s data into a database table very fast.
Before importing the file, you need to prepare the following:
- A database table to which the data from the file will be imported.
- A CSV file with data that matches with the number of columns of the table and the type of data in each column.
- The account, which connects to the MySQL database server, has FILE and INSERT privileges.
Suppose we have a table named discounts
with the following structure:
We use CREATE TABLE statement to create the discounts
table as follows:
The following discounts.csv
file contains the first line as column headings and other three lines of data.
The following statement imports data from c:\tmp\discounts.csv
file into the discounts
table.
The field of the file is terminated by a comma indicated by FIELD TERMINATED BY ','
and enclosed by double quotation marks specified by ENCLOSED BY '"
‘.
Each line of the CSV file is terminated by a new line character indicated by LINES TERMINATED BY '\n'
.
Because the file has the first line that contains the column headings, which should not be imported into the table, therefore we ignore it by specifying IGNORE 1 ROWS
option.
Now, we can check the discounts
table to see whether the data is imported.
Transforming data while importing
Sometimes the format of the data does not match with the target columns in the table. In simple cases, you can transform it by using the SET
clause in the LOAD DATA INFILE
statement.
Suppose the expired date column in the discount_2.csv
file is in mm/dd/yyyy
format.
When importing data into the discounts
table, we have to transform it into MySQL date format by usingstr_to_date() function as follows:
Importing file from client to a remote MySQL database server
It is possible to import data from client (local computer) to a remote MySQL database server using theLOAD DATA INFILE
statement.
When you use LOCAL
option in the LOAD DATA INFILE
, the client program reads the file on the client and sends it to the MySQL server. The file will be uploaded into the database server operating system’s temporary folder e.g., C:\windows\temp
on Windows or /tmp
on Linux. This folder is not configurable or determined by MySQL.
Let’s take a look at the following example:
The only difference is the LOCAL
option in the statement. If you load a big CSV file, you will see that withLOCAL
option, it will be a little bit slower to load the file because it takes time to transfer the file to the database server.
The account that connects to MySQL server doesn’t need to have the FILE privilege to import the file when you use the LOCAL
option.
Importing the file from client to a remote database server using LOAD DATA LOCAL
has some security issues that you should be aware of to avoid potential security risks.
Importing CSV file using MySQL Workbench
MySQL workbench provides a tool to import data into a table. It allows you to edit data before making changes.
The following are steps that you want to import data into a table:
Open table to which the data is loaded.
Click Import button, choose a CSV file and click Open button
Review the data, click Apply button.
MySQL workbench will display a dialog “Apply SQL Script to Database”, click Apply button to insert data into the table.
We have shown you how to import CSV into MySQL table using LOAD DATA LOCAL
and using MySQL Workbench. With these techniques, you can load data from other text file formats such as tab-delimited.
Reference: http://www.mysqltutorial.org/import-csv-file-mysql-table/