I am trying to do a CSV bulk import of names into MYSQL, but during the process there is a special characters that halts the operation.
The character over the name - Pérez
Is there a way to have MYSQL to ignore this on upload? My next step is to automate the upload via a web page where a customer can just upload the CSV file and hit submit, therefore hoping to work out these glitches.
I took the suggestion of the panel and recreated my table as UTF8-Default.
ERROR 1366: Incorrect string value: '\xE9rez' for column 'acct_owner' at row 1 SQL Statement:
I tried this and I still get the same error on that special character, plus now for some reason my auto-increment column does not increment, it just captures the data from the last_update column, therefore everything shifts left.
Well in case you want to insert/load csv data with special character you can try like this
LOAD DATA INFILE 'file.csv'
IGNORE INTO TABLE table
CHARACTER SET UTF8
FIELDS TERMINATED BY ';'
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '
'
To resolve this I ended up rebuilding my DB tables, i then went ahead and prior to submitting my data I cycled through it with some code to remove the characters that I know are causing me problems. While might not be the best way it was certainly the easiest way i found. I initially had a cycle that removed any characters that were not standard A-Z or 0-9 but I discovered that caused me other problems as I needed to see ; and \ and even / in some of my details. Therefore I only concentrated on what I know was breaking the import to MYSQL.
Here is a snip of the code i used:
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
//Replace all the special characters to avoid any insert errors
$data = preg_replace('/\'/', '', $data);
$data = preg_replace('/é/', 'e', $data);
if($firstRow) { $firstRow = false; }
else {
$import="INSERT INTO ..... blah blah blah....