Need some help importing an excel worksheet into MySQL

[ThunderAI]

Yes, i know a csv would be better, yes i know load data is better.  Unforuntitly i need to gain access to one page of an excel sheet in a workpage called 'add'. There are many posts on this site which talk about excel, ut i dont understand where to start. I know how to import a csv using file(), but how do you import one worksheet from an xls file into MySQL?

[trq]

You'll need to look into COM, its a little off topic for a php forum but once you get the com side sorted, php provides a means to access it via the COM extension.

[ThunderAI]

You'll need to look into COM, its a little off topic for a php forum but once you get the com side sorted, php provides a means to access it via the COM extension.

 

Thanks for the direction... I found this on the PHP manual site:

 

Simple convert xls to csv
<?php
// starting excel 
$excel = new COM("excel.application") or die("Unable to instanciate excel"); 
print "Loaded excel, version {$excel->Version}\n"; 

//bring it to front 
#$excel->Visible = 1;//NOT
//dont want alerts ... run silent 
$excel->DisplayAlerts = 0; 

//open  document 
$excel->Workbooks->Open("C:\\mydir\\myfile.xls"); 
//XlFileFormat.xlcsv file format is 6
//saveas command (file,format ......)
$excel->Workbooks[1]->SaveAs("c:\\mydir\\myfile.csv",6); 

//closing excel 
$excel->Quit(); 

//free the object 
$excel->Release(); 
$excel = null; 
?> 

 

 

it looks deseptivly simple, but there is no mention as to the actual workbook page.  How do I gain access to a specific page?

[trq]

How do I gain access to a specific page?

 

As I sadi, COM is a little out of the scope for a php frum. Hell, I don't even use windows let allone com or excel.

 

You'll want to google for information regarding the excel.application object.

[ThunderAI]

How do I gain access to a specific page?

 

As I sadi, COM is a little out of the scope for a php frum. Hell, I don't even use windows let allone com or excel.

 

You'll want to google for information regarding the excel.application object.

 

ok, will do.. and report back

 

[ThunderAI]

I think I got it to work, well it works, but i am sure it is not perfect.

 

<?php
// starting excel 
$excel = new COM("excel.application") or die("Unable to instanciate excel"); 
print "Loaded excel, version {$excel->Version}\n"; 

//bring it to front 
//$excel->Visible = 1;//NOT
//dont want alerts ... run silent 
$excel->DisplayAlerts = 0; 

//open  document 
$excel->Workbooks->Open("file"); 
//XlFileFormat.xlcsv file format is 6
//saveas command (file,format ......)

$sheet = $excel->WorkSheets(3);
$sheet->activate;
$excel->Workbooks[1]->SaveAs("fileb",6); 

//closing excel 
$excel->Quit(); 

//free the object 
unset($excel);
//$excel->Release(); 
$excel = null; 
?> 

 

The thing I am not sure about is the release of the $excel object.