I tried to import a csv file into mysql with php script. It is a simple solution if you have a small set of data to import. But if you have hundreds of thousands of records, don't even think about it. You need something different to read through a large dataset quickly. MySQL's LOAD DATA INFILE command works like a charm and can be executed from the command line.
Using LOAD DATA INFILE allows you to load csv or any other delimited data file to the mysql table with a single command. It comes with several options and we will see below what they are and how to use them to import the csv data set into the mysql table.
Using LOAD DATA INFILE Command:
The LOAD DATA INFILE command in mysql loads data from a file into the table at high speed. The file path specified must be absolute or relative.
1. Importing CSV File into MySQL:
Let's say we have a mysql table 'customers' with the following structure.
MySQL Table: Customers
CREATE TABLE `customers`(
`id` INT(8) NOT NULL ,
`name` VARCHAR(30) NOT NULL ,
`email` VARCHAR(40) NOT NULL ,
`location` VARCHAR(25) NOT NULL ,
PRIMARY KEY (`id`)
);
And a csv file customers.csv with data that we want to insert into the 'customers' table.
CSV File: Customers.csv
Now to import the csv data into the table, use the following sql statement.
LOAD DATA INFILE 'c:/tmp/customers.csv'
INTO TABL E customers
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;
Executing the above sql will import records to the given 'customers' table.
Our csv file has a column header as the first row, so we used the option IGNORE 1 LINES which will skip the first row. If you don't have the column header, don't use it.
Also make sure the file path is correct. You can use the format 'c:/tmp/customers.csv' or 'c:\\tmp\\customers.csv'.
This method will only work when your mysql table has the same sequence of columns as of the csv file.
2. Importing File with Different Column Sequence from Table:
Consider the scenario where your table and csv file column sequence are different. In this case, you must explicitly specify the order of the column name to ensure that the data is imported correctly.
LOAD DATA INFILE 'c:/tmp/customers.csv'
INTO TABLE tbl_customers
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(id,name,email,location);
3. Importing Data to Remote MySQL Database Server:
If you have your file on the local machine and not on the server, use the word LOCAL. This will import file from the client (local system) to a remote mysql database server.
LOAD DATA LOCAL INFILE 'c:/tmp/customers.csv'
INTO TABLE tbl_customers
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(id,name,email,location);
When you include the LOCAL option, the file is expected to be located on the client and not on the server.
You can also use the mysqlimport utility to load csv data into the database. It's very similar to LOAD DATA INFILE, except that mysqlimport supports multiple file loading and the latter does not.
If you have a large dataset to import, go for the LOAD DATA INFILE command, as it is extremely faster than doing it with any other script. I hope this tutorial is useful to you. Please share it on social media if you like it.
