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

Answer 1

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)