CODEWITHSUNDEEP
phpmysqlunicodeencodingutf-8
Daan
Daan

Reputation: 12246

Can't import special characters from CSV file

I'm trying to import a CSV file into a mysql table. The entire database has a utf8_unicode_ci encoding. The CSV file also has an UTF8 encoding. But when I try to run the following it just shows blank characters in the table instead of the special character:

$query = "LOAD DATA LOCAL INFILE 'mycsvfile.csv' INTO TABLE location_temp 
          CHARACTER SET UTF8
          FIELDS TERMINATED BY ';'
          ENCLOSED BY '\"'
          LINES TERMINATED BY '\r\n'";
$parameters = array ();
$connection->QueryExec ( $query, $parameters ); //Connection class

In the table above I only have one column location, the content of the csv file is: "ä";

I've also tried to change the value of the csv to "testthät"; in the table then comes: testth it just skips after the special character.

EDIT: This is how I create my database connection:

try {
    $this->conn = new PDO ( "mysql:host=" . $host . ";charset=utf8;dbname=" . $database, $user, $pass );
    $this->conn->exec ( "set names utf8" );
} catch ( PDOException $e ) {
    echo "Something went wrong while connecting to the database: " . $e->getMessage ();
}

Thanks in advance

Upvotes: 2

Views: 2997

Answers (1)

Rick James
Rick James

Reputation: 142528

The truncation indicates that it is not utf8. You say it is cp1252? Check SHOW COLLATION LIKE 'CP125%'; If you don't see cp1252, then latin1 is probably what you need.

Change the LOAD ... to specify cp1252 (or latin1) and in the dsn for the connection via PDO. (That is better than using SET NAMES.)

The principles:

  • The client (PHP) is getting latin1 encoding for the data.
  • The client must inform the MySQL server what encoding the data is in. (dsn)
  • The chatter between the client and server will translate the bytes if needed. This could happen if your column(s) say utf8 while the client is latin1. This inconsistency is ok.

More discussion

Upvotes: 1

Related Questions