Search code examples
phpmysqlsqlencodingmamp

Encoding problems with SQL LOAD DATA INFILE? Or different

I have a simple query

LOAD DATA LOCAL INFILE '$file'
INTO TABLE `test` 
FIELDS TERMINATED BY ';'
IGNORE 1 LINES
(Province, Locality, Latitude, Longitude)

Problem is, I have two .txt files, each almost the same content, but different sources, so with file1 everything work perfect, but with file2 I have problems. First of all the output is like

�K�a�m�p�o�t�

DB structure:

id int NOT NULL AUTO_INCREMENT,
Province varchar(255),
Locality varchar(255),
Latitude varchar(12),
Longitude varchar(12),
PRIMARY KEY (id)

my files: file1 file2

Is it an encoding problem?

Solution

  • That should be Kampot??

    Please provide the HEX for the source file. It smells like you have UTF-16, not UTF-8. Perhaps it came from some Microsoft product?

    So, this may be the solution:

    LOAD DATA ... CHARACTER SET ucs2 ...