How do I get SQL LOAD DATA from CSV to honour the column type when loading?
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.
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
精彩评论