mysql插入csv文件数据,如何将CSV文件数据插入MySQL数据库

I want to insert data into mysql table from csv file. Import data from region_codes.csv file. In region_codes.csv file having 3 columns in 3rd columns it had , separated data, include those commas how to insert in mysql.

DROP TABLE IF EXISTS `region_codes`;

CREATE TABLE `region_codes` (

`country_code` CHAR(2) NULL,

`region_no` varchar(5) NOT NULL,

`region` VARCHAR(45) NULL,

INDEX `idx_country_code` (`country_code`)

) COLLATE='utf8_bin' ENGINE = MyISAM;

Using LOAD DATA LOCAL INFILE I import the data but only 1000 rows are imported outof 4066 rows.

LOAD DATA LOCAL INFILE 'C:/region_codes.csv' INTO TABLE `region_codes` FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

How to insert huge amount of data into mysql region_codes table from csv file.

Screenshot:

解决方案

You can try below syntax if it works for you otherwise provide csv data:

LOAD DATA LOCAL INFILE 'C:/region_codes.csv' INTO TABLE `region_codes` FIELDS ESCAPED BY '\\' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

If above syntax does not work then export data by below command again and import by below given command.

select * into outfile 'C:/region_codes.csv' fields terminated by ',' optionally enclosed by '"' lines terminated by '\n' from `region_codes`;

Now use below command (to ignore column heading line)

LOAD DATA LOCAL INFILE 'C:/region_codes.csv' INTO TABLE `region_codes` FIELDS ESCAPED BY '\\' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;

Note: If data is prepared manually then need to correct it manually.

If still not work then attach your csv data to check exact problem.