MySQL incorrect ineger value (encoding)

Question

Alright here is my delema. I have a .rpt file that is encoded in UCS-2 and is basically two columns separated by a ','.

My query is;

load data infile 'file_name' into table

 table_name fields terminated by ',' 

lines terminated by '\n';

the text is simply two int columns. I keep getting an error that says: Error code: 1366 Incorrect integer value: 'ÿþ1' for column 'column1' at row 1

I've tried creating the table using usc-2 default collation and still the same issue. I've tried converting the .rpt into different encodings to match the table, with no luck.

Thanks

Did you try to specify proper integer values in the file? ÿþ1 is by no means a proper integer value. — Michael J.V., Apr 20 '11 at 15:56
the first character is an integer. not sure where ÿþ1 comes from. — Lexicon, Apr 20 '11 at 16:15

Johan · Accepted Answer · 2011-04-21T00:15:32.737

Load data infile can input just about anything except UCS-2.

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

Note It is not possible to load data files that use the ucs2 character set.

(halfway down the page)

Sample C code to translate ucs-2 to ascii

for( i=0; i!=len; ++i) {
  if( (ucs2[i]>=0) && (ucs2[i]<127)) {
    ascii[i] = ucs2[i];
  }
  else {
    conversion_failed();
    ascii[i] = '\0';
  }
}

Linux command line script

iconv -f UCS-2BE -t ascii oldfile > newfile

(not 100% sure the correct input description is "UCS-2BE", it might be some variation. Please double check iconv documentation)

MySQL incorrect ineger value (encoding)

1 Answers1