How do I get SQL LOAD DATA from CSV to honour the column type when loading?

Question

I am loading a CSV file into MySQL (5.1) using CREATE TABLE and LOAD DATA. I have a number of columns which are textual types (categories) but contain numerical values. When I create the table I assign these columns as VARCHAR(254) but the JDBC driver throws a SQLException "Data truncated for column AgeGroup at row 1".

My data looks like this:

ID,AgeGroup
xxx,4
xxy,3
xyx,6
...

My create table statement looks like this

CREATE TABLE abc(ID VARCHAR(254), AgeGroup VARCHAR(254))

My load statement looks like this

LOAD DATA INFILE \myfile.csv\ INTO TABLE abc FIELDS TERMINATED BY ','

I am guessing that it is a type conversion issue because AgeGroup is a textual database column but I am sticking what appear to be numbers in it.

How do I - can I - force the LOAD DATA command to honour the datatype of the column and convert the contents of the file?

I'm using the official MySQL JDBC Driver.

Answer 1

I am surprised that this doesn't work. I mean, I would expect this behaviour when sticking string values in numeric columns - not the otherway around.

Anyway, you can use an extension of the LOAD DATA syntax introduced in v5.0 to compute arbitrary expressions from your raw data:

LOAD DATA INFILE \myfile.csv\ 
INTO TABLE abc 
FIELDS TERMINATED BY ','
(@ID, @AgeGroup)
SET ID = @ID
,   AgeGroup = CAST(@AgeGroup AS UNSIGNED)

(see: http://dev.mysql.com/doc/refman/5.1/en/load-data.html