Csv Import With Null Fields

[ddaily]

I am trying to load a table and I get an error when it hits a field that is null, in this case it is a date field. What am I doing wrong? Thanks.

load data infile 'hrpdf.csv' into table pdfhr

FIELDS TERMINATED BY ',' ENCLOSED BY '”' LINES TERMINATED BY 'n'

(ssn,bigname,street,city,state,zipcode,homephone,fullperc,acrdate,hiredate,

probend,apptbud,loareason,loareturn,termcode,homedept,unitnumb,

empstat,p_t,p_f,emptype,cmpsmail,wphone,awphone,voicemail,faxnumb,

campadd,email,permaddr,permcity,permstate,permzip,emcont,emphone,

aemphone,ctznvisa,citcntry,resident,race,hispan,handic,sex,milstat,

incrmo,jobentry,jobclass,bargnunit,unioncode,edate,lastpaid,

uwid,salary,step,aptno)

Error Code : 1292

Incorrect date value: '' for column 'probend' at row 1

(94 ms taken)

and here's the first row of data:

note that after two date fields of “1978-06-12″,””,

that null field (11th field) would be a date if filled.

“531544258”,”AARHAUS, PATRICK A”,”9026 133RD AVE SE”,”NEWCASTLE”,”WA”,”98059-3331″,”4252713671″,”100.00″,”1978-06-12″,”1978-06-12″,””,”743564″,”00″,””,”00″,”043560″,”0153″,”A”,”P”,”F”,”B”,”352160″,”2066851483″,”2066851416″,””,””,”Physical Plant Annex 4″,”[email protected]”,”9026 133RD AVE SE”,”NEWCASTLE”,”WA”,”98059-3331″,”Aarhaus, Mike”,”2069378450″,””,”CT”,”US”,””,”800″,”999″,”A”,”M”,”X”,”12″,”1978-06-12″,”8828″,”06″,”01″,”2005-10-24″,”2005-10-15″,”875003841″,”4,089.00″,” K”,”1″

“532780378

[peterlaursen]

It is a 'strict mode' issue!

See:

http://www.webyog.com/faq/28_72_en.html

http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html

I cna reproduce this:

Code:

set sql_mode = 'STRICT_ALL_TABLES,NO_ZERO_IN_DATE';
load data infile 'c:\documents and settings\peter\skrivebord\test.csv' into table tablename1 FIELDS TERMINATED BY ',' ENCLOSED BY '”' LINES TERMINATED BY 'rn' (id,t,ts);

returns

Quote:

Error Code : 1292

Incorrect date value: '' for column 'ts' at row 2

(361 ms taken)

Either use latest SQLyog RC or issue the statement

Code:

set sql_mode = '';

manually

[ddaily]

Ok, set session sql_mode = ' ';

worked.

Thanks.

[Author]

Posts