mysql - Warnings on 'LOAD DATA INFILE' from .CSV - Integers and Dates -

this pretty simple issue life of me don't see missing when try import csv file sql table.

i keep ending int 'idrefs' 0s, , date 'dob' 12 31 1969

---

examples of warnings:

| warning | 1366 | incorrect integer value: ' 10002' column 'idref' @ row 1  | | warning | 1265 | data truncated column 'dob' @ row 1  

the load data infile statement --

mysql> load data infile '/home/user/documents/ppllist.csv' table people  fields terminated ',' lines terminated '"' ignore 1 lines  (idref, lastname, firstname, dob, rsnum, slnum, firstvisit, pplpref, coachname); 

just examples 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 chose end lines double-quote, lines terminated '"' option does take care of that.

however, since overrode default line terminator, actual newline characters no longer considered more data; consequently, there newline character @ start of each piece of id data.

this evident in warning.

i'd avoid " altogether , use normal line endings, quick fix is:
lines terminated '"\n'.

dob

the date of birth issue little different , i'm not sure one, i'd consider storing dates in standard format within csv file, rather ambiguous right now.

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

mysql may interpreting two-year date 68 1968 (i would) and, though the date column supports year (and other years going 1000!), may choosing apply further restrictions based on weak input format.

the date type used values date part no time part. mysql retrieves , displays date values in 'yyyy-mm-dd' format. supported range '1000-01-01' '9999-12-31'.