Search code examples
databasemysqlcollationload-data-infile

Using LOAD DATA INFILE with arabic data

I am trying to import a .csv file into a table. I have figured out how to get the data inserted by using the following query:

LOAD DATA INFILE 'examplesofdata.csv' INTO TABLE coins FIELDS TERMINATED BY ',' 
ENCLOSED BY '' ESCAPED BY '\\'  IGNORE 1 LINES;

However for several of my fields I have Arabic content which gets entered as a series of ? I assume this is because I haven't collated the database correctly or I don't fully understand the LOAD DATA INFILE query. Any advice would be greatly appreciated.

The SHOW CREATE TABLE coins; output is:

CREATE TABLE `coins` (
  `cat_num` int(11) NOT NULL,
  `reg_num` int(11) NOT NULL,
  `period` varchar(255) NOT NULL,
  `arb_period` varchar(255) character set utf8 collate utf8_unicode_ci NOT NULL,
  `ruler` varchar(255) NOT NULL,
  `arb_ruler` varchar(255) character set utf8 collate utf8_unicode_ci NOT NULL,
  `mint` varchar(255) NOT NULL,
  `arb_mint` varchar(255) character set utf8 collate utf8_unicode_ci NOT NULL,
  `date` varchar(255) NOT NULL,
  `weight` float NOT NULL,
  `diameter` float NOT NULL,
  `khedieval_num` varchar(255) NOT NULL,
  `ref` text NOT NULL,
 PRIMARY KEY  (`cat_num`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
Solution

  • So I ended up getting an answer from an old instructor for my Databases class. He told me that this problem is actually a reported bug with the current version of MySQL and that the only known solution at the time is to manually import the data through PHP or another scripting language.

    The bug for this issue is at: http://bugs.mysql.com/bug.php?id=10195

    It didn't help me too much since I was only working on a prototype, and managed a workaround in the mean time, but hopefully it can be of more use to you.