InfoBright LOAD DATA INFILE Wrong data or column definition

Question

I'm using BRIGHTHOUSE engine.

I have a column defined as varchar(100). When loading data into the table, if the column data exceeds 100 characters, the engine will throw 'Wrong data or column definition' error, and fail to complete the loading process.

I'm wondering wether I can change the behaviour, simply discard the extra characters, just like what MySQL will do.

I've checked out the reject file feature, but it's not what I want.

The only way I can think of is to truncate the column data when generating the data file. It's workable, but not as easy as in MySQL.

Thanks in advanced.

Answer 1

You can use SET clause of LOAD DATA INFILE to truncate values that are being read from file on the fly

LOAD DATA INFILE '/path/o/your/file.txt'
INTO TABLE table_name
  FIELDS TERMINATED BY ','
  OPTIONALLY ENCLOSED BY '"' 
  LINES TERMINATED BY '\n'
(column1, @column2, column3, ...) -- first you read a value into a variable
SET column2 = LEFT(@column2, 100) -- then you assign truncated value to the column