excel to mysql woes

Topics: User Forum
Dec 21, 2008 at 10:00 AM
hi everyone.
i am using PHPExcel to read to contents of an xlsx file and insert them into a db. this is how it works:

get amount of worksheets.
foreach worksheet
get worksheet name
find first row of text in worksheet
get length of row
foreach cell
get text in cell
create a mysql table -> table-name = worksheet-name, columns = first-row-cell-values.
foreach row in the worksheet
addslashes to each cell value, and insert it into the newly created mysql table.
rinse and repeat.

this workes great for me in english.
my problems start when my excel tables contain some columns in hebrew (not the column title, just the contents). the inserting of the hebrew values works fine, but when i "SELECT ... ORDER BY a-column-with-hebrew" the order is all wrong.

before i am told to go to mysql help, i tried that, and when i create an identical table directly using mysql command line or phpmyadmin, the order is fine. the order only gets messed up when i insert the values using phpexcel, and this is why i come to you.

here is an example of a simple test table i created that illustrates the problem (this is an export from phpMyAdmin):
-- Table structure for table `sheet1`
--

CREATE TABLE IF NOT EXISTS `sheet1` (
  `id` bigint(20) unsigned NOT NULL auto_increment,
  `col` varchar(10839) NOT NULL,
  `bla` varchar(10839) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=9 ;

--
-- Dumping data for table `sheet1`
--

INSERT INTO `sheet1` (`id`, `col1`, `col2`) VALUES
(1, 'א', 'h'),
(2, 'ב', 'g'),
(3, 'ג', 'f'),
(4, 'ד', 'e'),
(5, 'ה', 'd'),
(6, 'ו', 'c'),
(7, 'ז', 'b'),
(8, 'ח', 'a');


when i order by col1, the order i get is 1, 7, 8, 2, 3, 4, 5, 6. it should be 1, 2, 3, 4, 5, 6, 7, 8
when i order by col2, the order i get is 8, 7, 6, 5, 4, 3, 2, 1. this is the correct order.
as you can see, the table is in utf-8 encoding, so that's not the problem.

here are some table layout tests i made to try figuring out the problem:
* 1 column in hebrew, single char values - order correct.
* 1 column in hebrew, multi-char values - order correct.
* 1 column in hebrew, multi-char values + white spaces - order correct.
* 2 columns in hebrew - order correct.
* 2 columns 1 hebrew 1 english, order by english - order correct.

2 columns 1 hebrew 1 english, order by hebrew - order messed up.

any1 have any ideas? i am at a loss.

thanx a bunch for any help.
bobbinsbro.
Developer
Dec 21, 2008 at 11:19 AM
The problem is with your connection character set between PHP and MySQL. It looks like you are using latin1.
http://dev.mysql.com/doc/refman/5.1/en/charset-connection.html

Even though your script is correctly using UTF-8 and your MySQL database collation is correctly set to e.g. utf8_unicode_ci, then you need to inform MySQL that your SQL queries are using UTF-8.

You will notice the error if you go in phpMyAdmin and look at the table you have created with your PHP script. Here is a reconstruction:
http://img530.imageshack.us/img530/3271/phpmyadminze1.png

Solution:
You can set the connection character set during run-time like this. The critical part is the set names shortcut statement:

$connect = mysql_connect('localhost', 'account', 'password');
$db = mysql_select_db('test');

$sql = "set names 'utf8'";
mysql_query($sql);

$sql = <<<sql
CREATE TABLE IF NOT EXISTS `sheet1` (
  `id` bigint(20) unsigned NOT NULL auto_increment,
  `col1` varchar(10839) NOT NULL,
  `col2` varchar(10839) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=9 ;
sql;
mysql_query($sql);

$sql = <<<sql
INSERT INTO `sheet1` (`id`, `col1`, `col2`) VALUES
(1, 'א', 'h'),
(2, 'ב', 'g'),
(3, 'ג', 'f'),
(4, 'ד', 'e'),
(5, 'ה', 'd'),
(6, 'ו', 'c'),
(7, 'ז', 'b'),
(8, 'ח', 'a');
sql;
mysql_query($sql);

Alternatively, if you have access to the MySQL configuration file my.ini (Windows) or my.cnf (Linux), you can edit it and restart the server so it will permanently work as expected.

Dec 21, 2008 at 12:07 PM
wow dude. awesome. problem solved. 
thanx a lot.
bobbinsbro.
Apr 9, 2010 at 9:12 PM

hola bobb me ayudas enviandome este codigo por favor , o me explicas como debe realizarse..

 

gracias