1

I am try to import from .CSV to mysql database and I am using LOAD DATA LOCAL INFILE function but every time I import the data I get the Arabic characters as (????????) what can I do to solve this problem

here is my SQL

LOAD DATA INFILE 'C:/wamp/www/academyOfArt/workShop/books.csv' INTO TABLE `books_library` CHARACTER SET 'utf8' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' (title,cover,authorName,pagesNum,publishingHouse,publishingYear,details)

OK this is my table headers

id - cat - instituteId - title - cover - authorName -   pagesNum -  publishingHouse - publishingYear - details

Row statistics

Format  Compact
Collation   utf8_general_ci

+-----------------+--------------+------+-----+---------+----------------+
| Field           | Type         | Null | Key | Default | Extra          |
+-----------------+--------------+------+-----+---------+----------------+
| id              | int(11)      | NO   | PRI | NULL    | auto_increment |
| cat             | int(11)      | NO   |     | NULL    |                |
| instituteId     | int(11)      | NO   |     | NULL    |                |
| title           | varchar(255) | NO   |     | NULL    |                |
| cover           | blob         | NO   |     | NULL    |                |
| authorName      | varchar(255) | NO   |     | NULL    |                |
| pagesNum        | int(11)      | NO   |     | NULL    |                |
| publishingHouse | varchar(255) | NO   |     | NULL    |                |
| publishingYear  | year(4)      | NO   |     | NULL    |                |
| details         | mediumtext   | NO   |     | NULL    |                |
+-----------------+--------------+------+-----+---------+----------------+

Hay I notes when run show variables like 'char%' I got

mysql> show variables like 'char%';
+--------------------------+-----------------------------------------------+
| Variable_name            | Value                                         |
+--------------------------+-----------------------------------------------+
| character_set_client     | latin1                                        |
| character_set_connection | latin1                                        |
| character_set_database   | utf8                                          |
| character_set_filesystem | binary                                        |
| character_set_results    | latin1                                        |
| character_set_server     | latin1                                        |
| character_set_system     | utf8                                          |
| character_sets_dir       | c:\wamp\bin\mysql\mysql5.6.17\share\charsets\ |
+--------------------------+-----------------------------------------------+
Yousef Altaf
  • 2,631
  • 4
  • 46
  • 71

3 Answers3

3

You're loading the CSV file as UTF-8, and your table is UTF-8, that looks ok.

If the question marks in your result look like this: �, and displaying Arabic characters works in other cases, then it's likely the CSV file isn't UTF-8 encoded.

Double-check that the CSV file is UTF-8 encoded. Convert it if necessary. (For advice on that, one would need to know what platform you're on.)

Edit: this seems to be a limitation of Excel, which apparently can't generate UTF-8 CSV files! Here is a number of workarounds.

Community
  • 1
  • 1
Pekka
  • 442,112
  • 142
  • 972
  • 1,088
  • I am on windows platform using excel to export the `CSV` file, I didn't check about the CSV file is UTF-8 encoded, yes I can see the Arabic characters in the CSV file correctly but when try to import them I get this `�, ` – Yousef Altaf Apr 17 '16 at 14:37
  • 2
    Damn, Excel has everyting except generating utf 8 csv – Mihai Apr 17 '16 at 14:54
  • Hay guys I am so confused with this, First I got it working the halfway with the `notepad` trick but now I got it `Arabic` characters which is not rendering the right way in my page, why? when I insert something throw my PHP code it go's to db like this `شارع جماÙ` and this rendered on page as decoded and the words comes like 'عربي'. – Yousef Altaf Apr 17 '16 at 21:14
0

I have created my own solution to solve this issue, I have built a simple application using Laravel Framework & Laravel Excel Library, and it works properly with arabic text.

If you would like to use it, I can publish it on GitHub

Marwan
  • 1,802
  • 2
  • 17
  • 22
-1

ok; the secret is not in utf8 encoding but windows-1256 encoding so here what you should do first save your excel file as txt with TAB delimiters , then use NOTEPAD ++ or any of text editor to replace all TAB (to ensure from your selecting copy the empty spaces between any of two columns in the file and paste it in the replace what text box ) with "," and hit replace all second in your phpmyadmin you can import your file and use windows-1256 as character set of the file and in the format choose CSV and make sure that the column separator is "," and also make sure that the count of columns in your file is as the same as in your datatable and hit GO

waqwaq
  • 11
  • 1