Warnings on 'LOAD DATA INFILE' from .CSV -

Question

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

Answer 1

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