MYSQL import data from csv using LOAD DATA INFILE

You can use LOAD DATA INFILE command to import csv file into table.

Check this link MySQL - LOAD DATA INFILE.

LOAD DATA LOCAL INFILE 'abc.csv' INTO TABLE abc
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(col1, col2, col3, col4, col5...);

For MySQL 8.0 users:

Using the LOCAL keyword hold security risks and as of MySQL 8.0 the LOCAL capability is set to False by default. You might see the error:

ERROR 1148: The used command is not allowed with this MySQL version

You can overwrite it by following the instructions in the docs. Beware that such overwrite does not solve the security issue but rather just an acknowledge that you are aware and willing to take the risk.

You probably need to set the FIELDS TERMINATED BY ',' or whatever the delimiter happens to be.

For a CSV file, your statement should look like this:

LOAD DATA INFILE 'data.csv' INTO TABLE tbl_name
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

Before importing the file, you must 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 following table :

enter image description here

CREATE TABLE USING FOLLOWING QUERY :

CREATE TABLE IF NOT EXISTS `survey` (
  `projectId` bigint(20) NOT NULL,
  `surveyId` bigint(20) NOT NULL,
  `views` bigint(20) NOT NULL,
  `dateTime` datetime NOT NULL
);

YOUR CSV FILE MUST BE PROPERLY FORMATTED FOR EXAMPLE SEE FOLLOWING ATTACHED IMAGE :

enter image description here

If every thing is fine.. Please execute following query to LOAD DATA FROM CSV FILE :

NOTE : Please add absolute path of your CSV file

LOAD DATA INFILE '/var/www/csv/data.csv' 
INTO TABLE survey 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

If everything has done. you have exported data from CSV to table successfully

Syntax:

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL]
INFILE 'file_name' INTO TABLE `tbl_name`
CHARACTER SET [CHARACTER SET charset_name]
FIELDS [{FIELDS | COLUMNS}[TERMINATED BY 'string']] 
[LINES[TERMINATED BY 'string']] 
[IGNORE number {LINES | ROWS}]

See this Example:

LOAD DATA LOCAL INFILE
'E:\\wamp\\tmp\\customer.csv' INTO TABLE `customer`
CHARACTER SET 'utf8'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

Insert bulk more than 7000000 record in 1 minutes in database(superfast query with calculation)

    LOAD DATA LOCAL INFILE "'.$file.'"
    INTO TABLE tablename
    FIELDS TERMINATED by \',\'
    LINES TERMINATED BY \'\n\'
    IGNORE 1 LINES
    (isbn10,isbn13,price,discount,free_stock,report,report_date)
     SET RRP = IF(discount = 0.00,price-price * 45/100,IF(discount = 0.01,price,IF(discount != 0.00,price-price * discount/100,@RRP))),
         RRP_nl = RRP * 1.44 + 8,
         ID = NULL

RRP and RRP_bl is not in csv but we are calculated that and after insert that.

MYSQL import data from csv using LOAD DATA INFILE

Related

Recent Posts