来自.CSV的'LOAD DATA INFILE'警告 - 整数和日期 [英] Warnings on 'LOAD DATA INFILE' from .CSV - Integers and Dates

问题描述

这可能是一个非常简单的问题，但是对于我来说，当我尝试将这个CSV文件导入到SQL表中时，我看不到我缺少什么。

我一直用我的int'idRefs'作为0，而我的日期'dob'作为所有12 31 1969

---

警告的例子：

  |警告| 1366 |不正确的整数值：'
 10002'列1的| idRef'列
 |警告| 1265 |在第1行的列'dob'被截断的数据
  

LOAD DATA INFILE语句 -

  mysql> LOAD DATA INFILE'/home/user/Documents/pplList.csv'INTO TABLE people 
 FIELDS TERMINATED BY'，'LINES TERMINATED BY''IGNORE 1 LINES 
（idRef，lastName，firstName，dob， rsNum，slNum，firstVisit，pplPref，coachName）; 
  

p>

  idRef，lastName，firstName，dob，rsNum，slNum，firstAppt，pplPref，coachName
 10001，BlankA，NameA， 4/15 / 68,1000000,4600000,3 / 31/08，Positive，John Jay
 10002，BlankB，NameB，10/28 / 78,1000001,4600001,8 / 19/11，Positive，John Jay
  

'people'表SQL代码 -

  DROP TABLE IF EXISTS people; 
 CREATE TABLE people 
（
 id smallint unsigned NOT NULL auto_increment，
 idRef int unsigned NOT NULL，
 lastName varchar（255），
 firstName varchar （$ 255），
 dob date NOT NULL，
 rsNum int unsigned NOT NULL，
 slNum int unsigned NOT NULL，
 firstAppt date NOT NULL，
 pplPref varchar（255） ，
 coachName varchar（255），
 PRIMARY KEY（id）
）; 
  

解决方案

idRef

我不知道为什么你选择用双引号结尾，但是 LINES TERMINATED BY'' 选项确实照顾到了 a>。

但是，由于您覆盖了默认的行终止符，所以您的实际的换行符不再被认为是数据以外的任何东西。因此，在每一条 id 数据的开头都有一个换行符。

警告。

我会避免使用并使用普通行结尾，但快速解决方法是：

LINES TERMINATED BY'\\\
'。

dob

出生日期问题有点不同，我不确定这个问题，但我会考虑stor在您的CSV文件中以标准格式显示日期，因为它们现在比较模糊。

而不是 4/15/68 ，试试 1968-15-04 。

MySQL可能会解释两年的日期 68 作为 1968 （我愿意），尽管 DATE 列支持当年（和所有其他年份回到 1000 ！），它可能会根据您的弱输入格式选择进一步限制。

DATE 类型用于具有日期部分但没有时间部分的值。 MySQL以'YYYY-MM-DD'格式检索并显示 DATE 值。支持的范围是'1000-01-01'到'9999-12-31'。

This is probably a pretty simple issue but for the life of me I don't see what I am missing when I try to import this CSV file into my SQL table.

I keep ending up with my int 'idRefs' with 0s, and my date 'dob' as all 12 31 1969

---

Examples of the warnings:

| Warning | 1366 | Incorrect integer value: '
10002' for column 'idRef' at row 1  |
| Warning | 1265 | Data truncated for column 'dob' at row 1 

The LOAD DATA INFILE statement --

mysql> LOAD DATA INFILE '/home/user/Documents/pplList.csv' INTO TABLE people 
FIELDS TERMINATED BY ',' LINES TERMINATED BY '"' IGNORE 1 LINES 
(idRef, lastName, firstName, dob, rsNum, slNum, firstVisit, pplPref, coachName);

Just some examples from the CSV --

idRef,lastName,firstName,dob,rsNum,slNum,firstAppt,pplPref,coachName"
10001,BlankA,NameA,4/15/68,1000000,4600000,3/31/08,Positive,John Jay"
10002,BlankB,NameB,10/28/78,1000001,4600001,8/19/11,Positive,John Jay"

The 'people' table SQL code --

DROP TABLE IF EXISTS people;
CREATE TABLE people
(
id   smallint unsigned NOT NULL auto_increment,
idRef   int unsigned NOT NULL,
lastName    varchar(255), 
firstName   varchar(255),
dob   date NOT NULL,
rsNum   int unsigned NOT NULL,
slNum   int unsigned NOT NULL,
firstAppt   date NOT NULL,
pplPref   varchar(255),
coachName   varchar(255),
PRIMARY KEY   (id)
);

解决方案

idRef

I don't know why you chose to end your lines with a double-quote, but the LINES TERMINATED BY '"' option does take care of that.

However, since you overrode the default line terminator, your actual newline characters are no longer considered to be anything more than data; consequently, there is a newline character at the start of each piece of id data.

This is actually evident in the warning.

I'd avoid the " altogether and use normal line endings, but the quick fix is:
LINES TERMINATED BY '"\n'.

dob

The date of birth issue is a little different and I'm not sure about that one, but I'd consider storing dates in a standard format within your CSV file, as they are rather ambiguous right now.

Instead of 4/15/68, try 1968-15-04.

MySQL may be interpreting the two-year date 68 as 1968 (I would) and, though the DATE column supports that year (and all other years going back to 1000!), it may be choosing to apply further restrictions based on your weak input format.

The DATE type is used for values with a date part but no time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. The supported range is '1000-01-01' to '9999-12-31'.

这篇关于来自.CSV的'LOAD DATA INFILE'警告 - 整数和日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！