Consider a table to store SSN and DOB.
CREATE TABLE fbi
(
ssn BIGINT,
dob DATE
)
Data was loaded into the table:
LOAD DATA LOCAL INFILE C:\test.csv
INTO TABLE fbi
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n';
SELECT * FROM fbi;
It is showing null values for DOB. I don't understand the error.
"ssn","dob"
5,"1952-11-15"
6,"1973-12-23"
6,"1951-12-23"
1,"1962-03-21"
It most likely has to do with the date formatting in your csv file. MySQL likes dates in the format yyyy-mm-dd. e.g. 2010-10-09. You might be able to get more information by issuing the following command in the MySQL command console immediately after your import:
show warnings;
UPDATE:
I see that your date field is quoted. If you have a quoted date, you'll need to tell MySQL that by adding OPTIONALLY ENCLOSED BY '"' to your import command (see MySQL manual for LOAD DATA INFILE). Try this:
LOAD DATA LOCAL INFILE C:\test.csv
INTO TABLE fbi
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n';
BTW: I hope those SSN's you've posted are fake or mangled in some unrecoverable way.
