Excel To MySQL Via PHP ODBC And COM

Relates to PHP and MySQL, COM

As a keen statistician with a fascination for weather variation over time (in particular in relation to equilibrium tidal variations) I have acquired comprehensive weather data over the past few years from my digital weather station. All the archive pressure and temperature readings are tucked away in Excel spreadsheets - this year I have actually moved over to Open Office as my spreadsheet of choice. I finally decided it was time to move some of this data into a MySQL database on my server, where I could then manipulate the data for display on the web.

To start with I have decided to keep things simple, storing average daily pressure and temperature for graphical display through the GD library. In part for experimentation, I had a look last weekend at the options for achieving the transfer using the PHP Command Shell.

Firstly I tried using ODBC by creating a System DSN for the Microsoft Excel Driver, and SQL to extract the data. The inbuilt ODBC PHP functions are fairly intuitive and the script was quite simple to build. However the solution was too proprietary since I had to first use MS Query to establish the field names for the required columns and synchronising the query itself:


define("COL_DATE", "F2");
define("COL_AVG_PRESSURE", "F27");
define("COL_AVG_TEMP", "F30");

[..snip..]

$s_query = "SELECT `" . $arr_month . "$`." . COL_DATE
         . ", `" . $arr_month . "$`." . COL_AVG_TEMP . " "
         . ", `" . $arr_month . "$`." . COL_AVG_PRESSURE . " "
         . "FROM `" . $arr_month . "$` "
         . "ORDER BY `" . $arr_month . "$`." . COL_DATE;

Instead I decided to have a look at the DCOM abilities of PHP. Unfortunately documentation is sparse in the PHP Manual and across the Web generally, with only one clear example of accessing Excel that I could find. So I decided to trial and error a bit to see how much of the Excel Object Model is recognised. Based in part on the example mentioned above this was the best solution I could get to run:


$xls = new COM("Excel.sheet") or die("Did not connect");
print "Application name:{$xls->Application->value}\n";
print "Loaded Version: {$xls->Application->version}\n";
foreach($years as $year)
{
  $workbook = "C:\weather\_" . $year . ".xls";  
  $wkb = $xls->Application->Workbooks->Open($workbook) 
         or die("Failed to Open Workbook"); 
  $xls->Application->Visible = 1;
  foreach($sheets as $sheet)
  {
    $month = (array_search($sheet, $sheets) + 1);
    $ws = $wkb->Worksheets($sheet);
    $ws->activate;
    for ($day = 1, $i = First_Cell; 
         $i <= Last_Cell; $i++, $day++)
    {
      if (checkdate($month, $day, $year))
      {
        $arr_day[0] = $year . "-" 
                    . $month . "-" . $day;
        $cell = $ws->Cells($i, AD);
        $cell->activate;
        $arr_day[1] = (! empty($cell->value)) 
                    ? sprintf("%01.1f", $cell->value) 
                    : NULL;  
        $cell = $ws->Cells($i, AA);
        $cell->activate;
        $arr_day[2] = (! empty($cell->value)) 
                    ? round($cell->value) 
                    : NULL;          
        $values[] = $arr_day;
      }      
    }
  }
  $xls->Application->ActiveWorkbook->Close("False");
}
$xls->Release();
unset($xls);

This algorithm quite literally steps through each required cell in turn, by cycling through the monthly worksheets and yearly files (note the constants AA and AD define the indexes of the required columns). It is however quite inefficient, and a faster method would be to extract values as range objects using a call like $xls->Application->Range('Jan!$AA$6:$AA$36).Value, but the PHP COM library does not seem to accept this. Still, the above code works and could be manipulated and applied to future data. However, I will probably look to use Python in the future, with a broader implementation of COM via the Win32Com module and its integration with Open Office. At least I now know that MS Office documents can be manipulated by PHP.

Posted on Thursday, Feb 12, 2004 at 21:26:53.

Comments on Excel To MySQL Via PHP ODBC And COM (10)

α comment

Actually why I am writing this is to know how to put my code also in the forums, and who ever wants to the code they can see and use… Please help me in this regard.

Thanks in advance. Jyotsna.

Posted by Jyotsna
Wednesday, May 05, 2004 at 11:17:47

β comment

Sorry but the current comments system makes it very difficult to post code samples. It uses the PHP strip_tags() function to remove all markup which also tends to chop up code snippets! :(

If you have code you wish to share with the PHP community why not try

PHP Freaks - http://www.phpfreaks.com/ or PHP Builder - http://www.phpbuilder.com/

I hope to improve the comment system soon, but it is very hard to find the time!!!

Posted by Tom
Wednesday, May 05, 2004 at 12:34:23

γ comment

hi, can we use this code on linux server also.

Posted by Kashif
Wednesday, Jul 21, 2004 at 09:32:12

δ comment

can u help me to convert msexcel to mysql

Posted by Rajavelmurugan
Tuesday, Nov 16, 2004 at 12:11:42

ε comment

I have a simple MySql database, I use PHP script to get or insert any data to the database. Now I would like to ask a question: How can I write a php script to get any datas from exel before I put them to mysql database?

Posted by sethserey
Tuesday, Mar 01, 2005 at 04:52:47

ζ comment

i have developed a php script to convert msaccess/excel databases to mysql… email me if interested…

Posted by z33ro
Friday, Aug 05, 2005 at 03:25:33

η comment

muoreh@gmail.com

Posted by z33ro
Friday, Aug 05, 2005 at 03:26:19

θ comment

I want to insert data from excel sheet to mysql table. I have excel sheet and table ready. I saw a post above (Posted by z33ro on Aug 05, 2005). So please send me the script ASAP.

Thanks

Posted by Lature Laxman
Monday, Nov 14, 2005 at 09:00:19

ι comment

I want to convert Excel serial date to My sql Date Time ? Howz this possible? Or can I get the Date value (with the format in which it exists in Excel) in PHP ?

Posted by Dhanya
Saturday, Dec 10, 2005 at 09:25:03

κ comment

I have a simple MySql database, I use PHP script to get or insert any data to the database. Now I would like to ask a question: How can I write a php script to get any datas from exel before I put them to mysql database? can any one help me to write the code Thanks

Posted by Zahraa
Monday, Jan 23, 2006 at 08:15:54

Breadcrumbs Trail

[ Home ] -> TW Blog -> Feb 04 -> Excel To MySQL Via PHP ODBC And COM
Site Map

The Severn Solutions website achieves the following standards:

[ XHTML 1.0 ] [ CSS 2 ] [ WAI AA ] [ Bobby AA ]

Page compiled in 0.011 seconds