[eddyvlad]

I have to convert all the data (collumn and rows) in .xls to MySQL (Field and rows).
I have no idea where to start. I heard something about COM or active x or something that I need. My PHP is in Windows.

Help...

[Beck]

<?

define(db_host, "localhost");
define(db_user, "root");
define(db_pass, "");
define(db_link, mysql_connect(db_host, db_user, db_pass));
define(db_name, "test");
mysql_select_db(db_name);


$excel = new COM("Excel.Application") or die("Excel could not be started");


ini_set('max_execution_time','120');



$excel -> Visible = 0;



$excel -> DisplayAlerts = 0;



$data = $excel -> Workbooks -> Open("C:\userlist.xls");

$Sheets = $data -> Worksheets(1);



for($i = 3; $i <= 5; $i++){

for($x = 1; $x <= 3; $x++){
$Cell = $Sheets -> Cells($i, $x);
$Cell -> activate;
switch($x){
case 1; $name = $Cell -> value; break;
case 2; $username = $Cell -> value; break;
case 3; $country = $Cell -> value; break;
}
}



$SQL = mysql_query("INSERT INTO members VALUES(NULL, '$name', '$username', '$country')");

}


$excel -> Quit();

?>

also you can see more information and some graphics about above...

http://www.websoldier.net/index.php?...&article_id=51

[thedevshop]

You may want to checkout dbQwikSync2mySQL http://www.thedevshoponline.com/dbqwiksync.html
This will take PC databases (including excel spread sheets) and move them the mySQL, creating the required tables and moving the data. If that's what you are trying to do.

[Tatsi]

I think you can use PHP Excel Parser Pro (http://www.zakkis.ca/products) for converting .xls data to MySQL. Check the site- they have free trial version so you could try'n'see.

[eddyvlad]

Hmm... The COM thingy don't work. So I found a library script base on the Open Office.org library at Sourceforge.net. Hmm.. The script is very hard... I think it reads from the binary of the excel file coz it have...

PHP Code:

define('EXTENSION_BLOCK_POS', 0x44);
define('NUM_EXTENSION_BLOCK_POS', 0x48);
define('PROPERTY_STORAGE_BLOCK_SIZE', 0x80);
define('BIG_BLOCK_DEPOT_BLOCKS_POS', 0x4c);
define('SMALL_BLOCK_THRESHOLD', 0x1000); 

What the hell? 0x1000?? What does that mean? Well... atleast it gets the job done... I'm figuring out why it can't load large excel file. Since it's open source, I'll upload the script sometime in the future to be shared and improved on. Till then, thanks!!