MySQL LOAD DATA LOCAL INFILE只导入单行 [英] MySQL LOAD DATA LOCAL INFILE only imports a single row

问题描述

我们有一个CSV文件，其中包含数千条记录。我想通过phpmyadmin将这些行导入到MySQL表。这里是使用的命令：

 加载数据local infile'/var/www/html/deansgrads_201280.csv'
进入表ttu_nameslist 
以'，'结尾的字段
'''包含的
以'\r\\\
'结束的行
（firstname，middlename，lastname，city，县，州，州，代码，类别）
  

表中有一个ID字段设置为auto

输入数据文件行：

 Aaron，Al，Brockery，Cookeville，Putnam，TN，201280，deanslist 
Aaron，Dan，Mickel，Lebanon，Wilson，TN，201280，deanslist
   
 
 
 表结构：
  CREATE TABLE `ttu_nameslist`（
`id` int（11）NOT NULL，
`firstname` varchar（50）NOT NULL，
`middlename` varchar（50）NOT NULL，
 `lastname` varchar（50）NOT NULL，
`city` varchar（50）NOT NULL，
`county` varchar NULL，
`termcode` varchar（6）NOT NULL，
`category` varchar（10）NOT NULL，
 PRIMARY KEY（`id`）
）ENGINE = MyISAM DEFAULT CHARSET = latin1 
  
我做错了为什么在添加一行后退出？
解决方案
您说ID字段具有 AUTO_INCREMENT 属性，但没有提及它在 CREATE TABLE 语句中。这是问题的一部分。
 
 
 
另一部分是截断警告。 CSV文件中的某些行可能包含太长的数据，无法放入列中。 
 
 
 
您确定CSV档案有效吗？您可以将这些文字栏的大小增加到更大的值（例如200） （a.k.a.每行具有相同数目的值等）。你应该检查这些字符串是否包含逗号（，），虽然这不应该是一个问题。
 
We have a CSV file with thousands of records in it.  I want to import these rows into a MySQL table via phpmyadmin.  here is the command used:
load data local infile '/var/www/html/deansgrads_201280.csv' 
into table ttu_nameslist
fields terminated by ','
enclosed by '"'
lines terminated by '\r\n'
(firstname, middlename, lastname, city, county, state, termcode, category)
There is an ID field in the table that is set to auto-increment.  When we execute this SQL only the first line is imported into the table.  

Input data file lines:
"Aaron","Al","Brockery","Cookeville","Putnam","TN","201280","deanslist"
"Aaron","Dan","Mickel","Lebanon","Wilson","TN","201280","deanslist"
Table structure:
CREATE TABLE `ttu_nameslist` (
  `id` int(11) NOT NULL,
  `firstname` varchar(50) NOT NULL,
  `middlename` varchar(50) NOT NULL,
  `lastname` varchar(50) NOT NULL,
  `city` varchar(50) NOT NULL,
  `county` varchar(50) NOT NULL,
  `state` varchar(2) NOT NULL,
  `termcode` varchar(6) NOT NULL,
  `category` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
What am I doing wrong why does it quit after adding one row?
解决方案
You say that the ID field has the AUTO_INCREMENT attribute, but there's no mention of it in the CREATE TABLE statement. This is part of the problem.

The other part is those truncation warnings. Some of the rows in the CSV file probably contain data that is too long to fit inside the columns. Increase the size of those text columns to a bigger value (let's say 200) and try again.

Are you absolutely sure that the CSV file is valid ? (a.k.a. each row has the same number of values etc.). You should probably check if those strings contain commas (,), although that shouldn't be an issue.

                        
这篇关于MySQL LOAD DATA LOCAL INFILE只导入单行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！