Hallo,

i have tried finding help in the Forum, but could not find my problem situation.
When loading the Data VARCHAR Values are cutoff by one character.
If I remove ESCAPED BY ‘\’‘ then it works fine, but i use ’ as escape Character (example ‘1235’|‘This’‘s fun’)

Table DDL:
CREATE TABLE OLAP.TEST (
ATTRIB_ID   VARCHAR(5)
) ENGINE = BRIGHTHOUSE;

IMPORT FILE:
cat /tmp/loader_test.dat
‘12345’

LOAD COMMAND:
LOAD DATA INFILE ‘/tmp/loader_test.dat’ INTO TABLE OLAP.TEST FIELDS TERMINATED BY ‘|’ ENCLOSED BY ‘\’’ ESCAPED BY ‘\’’ ;

RESULT
mysql> SELECT * FROM OLAP.TEST
  -> ;
+—————-+
| ATTRIB_ID |
+—————-+
| 1234     |
+—————-+
1 rows in set (0.00 sec)

Any pointers would be great, thanks.
Thorsten

It looks like a bug. As workaround I suggest using different characters for enclosing and escaping.

thanks for the reply.
Ok good to know that my syntax seems to be correct.

I am exporting it from a IBM DB2 Database, EXPORT command does not have a option to define a seperate “ESCAPED BY” option, so am unable to use your workaround.

I am still using IB_3.5.2_r11794_12393(ice) so will upgrade to ICE 4.0.4 tommorow and hope the bug is fixed

I have very similar problem:
some of my load files have text fields that contains sign ” as a valid part of a text.
I do not enclose my text and do not use clauses “ENCLOSED BY” and “ESCAPE BY”, but when loader encounter sign ” in the text it gives me error (wrong data or column definition).
I use pipe as a delimiter.
What should I do?
C_George

One option would to be to remove the ” from the input file(Altering the Data)
or
I am using PDI CE(http://kettle.pentaho.com/), and create a Transformation where you read the data from a file/database and output it to the Infobright Database.

Because PDI CE casts the data in Java, it is able load the data with no problems.
The downside is that performance is decrease compared to a load from command line.

If you have any questions about PDI just let me know.

Thorsten Grahl

Hi C_George,

my load files have text fields that contains sign ” as a valid part of a text.
I do not enclose my text and do not use clauses “ENCLOSED BY”

Your LOAD syntax is wrong with respect to the load files. Or vice versa - your load files have wrong contents in respect to the LOAD syntax you use. Either remove ” characters from the load files, or add ENCLOSED BY ‘NULL’ to the LOAD statement (provided you load after issuing set @bh_dataformat=‘txt_variable’).

Thanks Janusz,

All is working fine now - ENCLOSED BY ‘NULL’ did the trick.
Thanks
C_George