views:

265

answers:

3

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
A: 

How about setting CHARACTER SET utf8_unicode or to your locale?

S.Mark
When I try:LOAD DATA INFILE 'coinsArab.csv' CHARACTER SET utf8_unicode INTOTABLE coins FIELDS TERMINATED BY ',' ENCLOSED BY '' ESCAPED BY '\\' IGNORE 1 LINES;I get an error saying there is an error in my sql, near CHARACTER SET...Is it in the wrong place?
Krustal
imm, could you try with `utf8_unicode_ci`? I mentioned for utf8 encoding basically. correct syntax you could fine here - http://dev.mysql.com/doc/refman/5.1/en/load-data.html
S.Mark
I figured out my syntax error, feel pretty stupid, but it still results in ????? ??? instead of whats in the file. When I copy the text from the excel and paste it in manually it works just fine, I don't know if that helps.
Krustal
do you have phpmyadmin? please take a look some setting there for collations of fields or you could use `SHOW CREATE TABLE coins` as Yada pointed out.
S.Mark
A: 

Hi Krustal,

I am having the exact same problem. Did you ever solve your problem? Is so, please let me know. Thanks.

A: 

Hi,

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.

Krustal