export the data from excel into a database

160 pts.
Tags:
Excel 2003
Microsoft Excel
ODBC
Hi Is there a way to transfer the data in excel sheet into a table in any databse with the use of ODBC connection?

Answer Wiki

Thanks. We'll let you know when a new response is added.

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 synchronizing 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.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following