Error with 'LOAD DATA LOCAL INFILE'

[bowens44]

I get an error when importing a csv file.

Query OK, 16394 rows affected, 176 warnings (0.27 sec)
Records: 16394 Deleted: 0 Skipped: 0 Warnings: 176

LOAD DATA LOCAL INFILE '/path/janfeb2008.csv'
into table tablename
fields terminated by ','
lines terminated by '\r\n'

(
ticket_number,
title,
incident_type,
@date_occurred,
@date_resolved,
keyword,
sub_component,
priority,
c1_customer,
reporter_name,
c2_resolvedby,
sub_application,
env_location,
component,
logged_by_dep,
route_queue,
contact_name
)
set
date_occurred = str_to_date(@date_occurred, '%m/%d/%Y'),
date_resolved = str_to_date(@date_resolved, '%m/%d/%Y')

I can't seem to find the problem. Not all rows of all columns contain data but I didn't think this was an issue.

Version info:
mysql Ver 14.12 Distrib 5.0.45, for redhat-linux-gnu (i386) using readline 5.0

 

[bowens44]

forgot to include table structure:

CREATE TABLE tablename
(
ticket_number varchar(20),
title varchar(250),
incident_type varchar(5),
date_occurred date,
date_resolved date,
keyword varchar(50),
sub_component varchar(40),
priority varchar(1),
c1_customer varchar(100),
reporter_name varchar(50),
c2_resolvedby varchar(60),
sub_application varchar(40),
env_location varchar(15),
component varchar(10),
logged_by_dep varchar(10),
route_queue varchar(15),
contact_name varchar(50),
recnum INT NOT NULL AUTO_INCREMENT PRIMARY KEY

);

 

[guelphdad]

what error do you get? You didn't post it, or did you mean the 176 warnings? did you use:

SHOW WARNINGS

to see what the warnings are?

 

[ingresman]

CSV can be fraught. Often string fields may have stray commas in them which throws off the import. Usualy string fields get double quotes roundthem and you tell load about them, or you use semi-colons which tend to be much less common in the real world.

 

[bowens44]

Oops. Thought I included that ......

Warning | 1261 | Row 4225 doesn't contain data for all columns
Warning | 1261 | Row 4226 doesn't contain data for all columns
Warning | 1261 | Row 4227 doesn't contain data for all columns
Warning | 1261 | Row 4228 doesn't contain data for all columns
Warning | 1261 | Row 4229 doesn't contain data for all columns
Warning | 1261 | Row 4230 doesn't contain data for all columns
Warning | 1261 | Row 4231 doesn't contain data for all columns
Warning | 1261 | Row 4232 doesn't contain data for all columns
Warning | 1261 | Row 4233 doesn't contain data for all columns
Warning | 1261 | Row 4234 doesn't contain data for all columns
Warning | 1261 | Row 4235 doesn't contain data for all columns
Warning | 1261 | Row 4236 doesn't contain data for all columns
Warning | 1261 | Row 4237 doesn't contain data for all columns
Warning | 1261 | Row 4238 doesn't contain data for all columns

There are 176 of these warnings. I don't see nay obvious difference in the rows that have warnings and those that don't.

 

[ingresman]

Can you provide a dump of record 4230 and of 500 (which I presume works).
Interesting you seem to have a dense set of bad records. Were these records created by an application of some sort ?