如何将CSV文件数据插入MySQL数据库 [英] How to insert csv file data into mysql database

问题描述

我想将数据从csv文件插入到mysql表中.从 region_codes.csv 文件导入数据.在第3列中有3列的region_codes.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;

使用LOAD DATA LOCAL INFILE导入数据，但在4066行中仅导入了1000行.

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';

如何从csv文件向mysql region_codes表中插入大量数据.

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

截屏:

推荐答案

如果可以使用以下语法，则可以尝试以下语法，否则请提供csv数据:

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.

如果仍然无法正常工作，请附加您的csv数据以检查确切的问题.

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

这篇关于如何将CSV文件数据插入MySQL数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！