将csv文件数据插入mysql [英] insert csv file data into mysql

问题描述

我想从csv文件中插入数据到mysql表。从 region_codes.csv 文件导入数据。在第三列有3列的region_codes.csv文件中，它有，分隔的数据，包括那些逗号如何在mysql中插入。

  DROP TABLE IF EXISTS`region_codes`; 
 CREATE TABLE`region_codes`（
`country_code` CHAR（2）NULL，
`region_no` varchar（5）NOT NULL，
`region`VARCHAR 
 INDEX`idx_country_code`（`country_code`）
）COLLATE ='utf8_bin'ENGINE = MyISAM; 
  

使用 LOAD DATA LOCAL INFILE

  LOAD DATA LOCAL INFILE'C：/region_codes.csv'INTO TABLE' region_codes`终止'，'''''''''''''''''''''''''''''''''''' >如何将大量数据插入到csv文件中的mysql  region_codes 表。
 
 
 
  / strong>  
解决方案
如果它适用于你，你可以尝试下面的语法，否则提供c​​sv数据：
  INFILE'C：/region_codes.csv'INTO TABLE`region_codes` FIELDS ESCAPED BY'\\'FIELDS TERMINATED BY'，'ENCLOSED BY''LINES TERMINATED BY'\r\\\
' 
 
如果上面的语法不起作用，那么再次通过下面的命令导出数据，并通过下面的给定命令导入。 / p> 
 
 
  select * into outfile'C：/region_codes.csv'以'，'结尾的字段'\\\
'from`region_codes`; 
  
 
 
现在使用下面的命令
 加载数据本地文件'C：/region_codes.csv'INTO TABLE`region_codes` FIELDS ESCAPED BY'\\'终止'，'''''''''''''''''''''''''''' 
  
注意：如果手动准备数据，则需要手动更正。
 
 
 
如果仍然无法工作，请附加您的CSV数据以检查确切的问题。
 
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.

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