LOAD DATA INFILE
statement to import CSV file into MySQL table.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.discounts
with the following structure:discounts
table as follows:discounts.csv
file contains the first line as column headings and other three lines of data.c:tmpdiscounts.csv
file into the discounts
table.FIELD TERMINATED BY ','
and enclosed by double quotation marks specified by ENCLOSED BY '
‘.LINES TERMINATED BY 'n'
.IGNORE 1 ROWS
option.discounts
table to see whether the data is imported.SET
clause in the LOAD DATA INFILE
statement.discount_2.csv
file is in mm/dd/yyyy
format.discounts
table, we have to transform it into MySQL date format by using str_to_date() function as follows: Movavi screen recorder studio.LOAD DATA INFILE
statement.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:windowstemp
on Windows or /tmp
on Linux. This folder is not configurable or determined by MySQL.LOCAL
option in the statement. If you load a big CSV file, you will see that with the LOCAL
option, it will be a little bit slower to load the file because it takes time to transfer the file to the database server.LOCAL
option.LOAD DATA LOCAL
has some security issues that you should be aware of to avoid potential security risks. LOAD DATA LOCAL
and using MySQL Workbench. With these techniques, you can load data from other text file formats such as tab-delimited.