0

I'm inserting contents into table as bulk upload format (.csv).

In my CSV some columns include special characters but server can't insert special characters included rows into db and stops execution.

How can I insert these values?

I'm trying:

$field1= addslashes(trim($data[0]));

and I'm using mysqli_real_escape_string, it's working but it omits all special characters from the fields...

$field2= mysqli_real_escape_string(trim($data[1]));
Tonechas
  • 13,398
  • 16
  • 46
  • 80
Renjitha22
  • 213
  • 1
  • 7
  • 22

2 Answers2

1

Do not call any conversion routines. Simply declare that the .csv file is encoded latin1. Do that with this clause in the LOAD DATA statement:

CHARACTER SET latin1

You have not said what charset the column/table is. Please provide SHOW CREATE TABLE. The column can be either latin1 or utf8.

Also, use mysqli_set_charset to establish which charset your client code is using. (It does not need to be the same as the column/table.)

You say "omits special characters". Do you mean that data is stored into the table(s), but with strings truncated at the special characters? See "Truncated text" in Trouble with utf8 characters; what I see is not what I stored .

You say "stops execution". That seems unlikely; please provide more details, such as the SQL that caused the termination, whether other commands come be run after it, whether you needed to start (not "restart") mysqld again.

You say "special characters"; let's see some examples. You used escaping to get $field variables, but what did you then do with those variables?

Community
  • 1
  • 1
Rick James
  • 135,179
  • 13
  • 127
  • 222
0

MySQL should do the conversion automatically, as long as you tell it what encoding your data is using. With the mysqli extension you use the mysqli::set_charset() function.

If you app is not using ISO-8859-1 and only your current data set does, you need to declare the application encoding and convert data yourself. You have at least three functions:

In either case, you'll get SQL errors if incompatible conversions happen. It shouldn't be the case if databases uses an encoding that contains all ISO-8859-1 characters (such as UTF-8).

Note: get rid of addslashes(), it serves no purpose at all and can only corrupt your data.

Álvaro González
  • 142,137
  • 41
  • 261
  • 360