null values after csv import

Question

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"

Answer 1

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.