transfer excel worksheet data to mysql table

how to transfer excel worksheet data to mysql table?

re: transfer excel worksheet data to mysql table

this code is for mysql-excel

================================================== ======
export a mySQL database table to an EXCEL file.
database table dump to WORD document possible also.

re: transfer excel worksheet data to mysql table

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

re: transfer excel worksheet data to mysql table

re: transfer excel worksheet data to mysql table

Heya, CMG.

Please use CODE tags when posting source code:

[CODE=php]
PHP code goes here.
[/CODE]

re: transfer excel worksheet data to mysql table

$rows = file('textfile.csv');
for($i = 0; $i < sizeof($rows); $i++) {
list($field1, $field2, $field3) = explode(',', $rows[$i]);
$query = "insert into tableName values ('$field1', '$field2', '$field3')";
mysql_query($query);
}

re: transfer excel worksheet data to mysql table

<?php
$row = 1;
$handle = fopen("test.csv", "r");
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
$num = count($data);
echo "<p> $num fields in line $row: <br /></p>\n";
$row++;
for ($c=0; $c < $num; $c++) {
echo $data[$c] . "<br />\n";
}
}
fclose($handle);
?>

re: transfer excel worksheet data to mysql table

A much simpler way to map the heading/column names to the elements on each line. It also doesn't fill up one big array which could cause you to run out of memory on large datasets. This loads one at a time so you can process/insert to db/etc...

$handle = fopen('somefile.csv', 'r');
if ($handle)
{
set_time_limit(0);

//the top line is the field names
$fields = fgetcsv($handle, 4096, ',');

//loop through one row at a time
while (($data = fgetcsv($handle, 4096, ',')) !== FALSE)
{
$data = array_combine($fields, $data);
}

fclose($handle);
}

re: transfer excel worksheet data to mysql table

<?php

print_r(buildStock('stock.csv'));

function buildStock($File) {
$handle = fopen($File, "r");
$fields = fgetcsv($handle, 1000, ",");

while($data = fgetcsv($handle, 1000, ",")) {
$detail[] = $data;
}

$x = 0;
$y = 0;

foreach($detail as $i) {
foreach($fields as $z) {
$stock[$x][$z] = $i[$y];
$y++;
}
$y = 0;
$x++;
}
return $stock;
}
?>

re: transfer excel worksheet data to mysql table

final version...

<?php
private function parseCsvLine($str) {
$delimier = ';';
$qualifier = '"';
$qualifierEscape = '\\';

$fields = array();
while (strlen($str) > 0) {
if ($str{0} == $delimier)
$str = substr($str, 1);
if ($str{0} == $qualifier) {
$value = '';
for ($i = 1; $i < strlen($str); $i++) {
if (($str{$i} == $qualifier) && ($str{$i-1} != $qualifierEscape)) {
$str = substr($str, (strlen($value) + 2));
$value = str_replace(($qualifierEscape.$qualifier), $qualifier, $value);
break;
}
$value .= $str{$i};
}
} else {
$end = strpos($str, $delimier);
$value = ($end !== false) ? substr($str, 0, $end) : $str;
$str = substr($str, strlen($value));
}
$fields[] = $value;
}
return $fields;
}

?>

re: transfer excel worksheet data to mysql table

<?php
function parseCsvLine($str) {
$delimier = ';';
$qualifier = '"';
$qualifierEscape = '\\';

$fields = array();
while (strlen($str) > 0) {
if ($str{0} == $delimier)
$str = substr($str, 1);
if ($str{0} == $qualifier) {
$value = '';
for ($i = 1; $i < strlen($str); $i++) {
if (($str{$i} == $qualifier) && ($str{$i-1} != $qualifierEscape)) {
$str = substr($str, (strlen($value) + 2));
$value = str_replace(($qualifierEscape.$qualifier), $qualifier, $value);
break;
}
$value .= $str{$i};
}
} else {
$end = strpos($str, $delimier);
$value = ($end > -1) ? substr($str, 0, $end) : $str;
$str = substr($str, strlen($value));
}
$fields[] = $value;
}
return $fields;
}

?>

================================================== ===========================================

<?
function parseCsvLine($str) {
$delimier = ';';
$qualifier = '"';
$qualifierEscape = '\\';

$fields = array();
while (strlen($str) > 0) {
if ($str{0} == $delimier)
$str = substr($str, 1);
if ($str{0} == $qualifier) {
$value = '';
for ($i = 1; $i < strlen($str); $i++) {
if (($str{$i} == $qualifier) && ($str{$i-1} != $qualifierEscape)) {
$str = substr($str, (strlen($value) + 2));
break;
}
$value .= $str{$i};
}
} else {
$end = strpos($str, $delimier);
$value = ($end > -1) ? substr($str, 0, $end) : '';
$str = substr($str, strlen($value));
}
$fields[] = $value;
}
return $fields;
}
?>

================================================== =====================

<?php

/* assumes a single line of input; automatically determines the number of fields */
function parse_line($input_text, $delimiter = ',', $text_qualifier = '"') {
$text = trim($input_text);

if(is_string($delimiter) && is_string($text_qualifier)) {
$re_d = '\x' . dechex(ord($delimiter)); //format for regexp
$re_tq = '\x' . dechex(ord($text_qualifier)); //format for regexp

$fields = array();
$field_num = 0;
while(strlen($text) > 0) {
if($text{0} == $text_qualifier) {
preg_match('/^' . $re_tq . '((?:[^' . $re_tq . ']|(?<=\x5c)' . $re_tq . ')*)' . $re_tq . $re_d . '?(.*)$/', $text, $matches);

$value = str_replace('\\' . $text_qualifier, $text_qualifier, $matches[1]);
$text = trim($matches[2]);

$fields[$field_num++] = $value;
} else {
preg_match('/^([^' . $re_d . ']*)' . $re_d . '?(.*)$/', $text, $matches);

$value = $matches[1];
$text = trim($matches[2]);

$fields[$field_num++] = $value;
}
}

return $fields;
} else {
return false;
}
}

?>

re: transfer excel worksheet data to mysql table

<?php
if(isset($_POST[submit]))
{
$sendto=$_POST[sendto];
$subject=$_POST[subject];
$msg=$_POST[message];
$chk=mail($sendto,$subject,$msg);

if(isset($chk))
{
$msg="Your mail is successfully submitted....";
}
}
?>

<html>
<head>
<title>Email Demonstration</title>
</head>

<body>
<table align="center" width="60%" cellspacing="10">
<form name="email" action="" method="post">
<tr>
<td colspan="3">Send Your Email ....</td>
</tr>
<? if(isset($msg)) {?>
<tr>
<td colspan="3"><?=$msg?></td>
</tr>
<? } ?>
<tr>
<td colspan="3">
Server Name : &nbsp;&nbsp;<? echo $_SERVER['SERVER_NAME'];?><br>
Docuent Root : &nbsp;&nbsp;<? echo $_SERVER['DOCUMENT_ROOT'];?><br>
Gateway Interface : &nbsp;&nbsp;<? echo $_SERVER['GATEWAY_INTERFACE'];?><br>
Host Name : &nbsp;&nbsp;<? echo $_SERVER['HTTP_HOST'];?><br>
Remote Address : &nbsp;&nbsp;<? echo $_SERVER['REMOTE_ADDR'];?><br>
Remote Port : &nbsp;&nbsp;<? echo $_SERVER['REMOTE_PORT'];?><br>
Server Administrator : &nbsp;&nbsp;<? echo $_SERVER['SERVER_ADMIN'];?><br>
Server Port : &nbsp;&nbsp;<? echo $_SERVER['SERVER_PORT'];?><br>
Server Protocol : &nbsp;&nbsp;<? echo $_SERVER['SERVER_PROTOCOL'];?><br>
Server Software : &nbsp;&nbsp;<? echo $_SERVER['SERVER_SOFTWARE'];?><br>
Server Signature : &nbsp;&nbsp;<? echo $_SERVER['SERVER_SIGNATURE'];?><br>
</td>
</tr>

<tr>
<td>Whom to send</td>
<td>:</td>
<td><input type="text" size="30" name="sendto"></td>
</tr>
<tr>
<td>Subject</td>
<td>:</td>
<td><input type="text" size="40" name="subject"></td>
</tr>
<tr>
<td>Message</td>
<td>:</td>
<td>
<textarea name="message" cols="40" rows="15"></textarea> </td>
</tr>
<tr>
<td colspan="3" align="center"><input type="submit" name="submit" value="SUBMIT"></td>
</tr>
</form>
</table>
</body>
</html>

re: transfer excel worksheet data to mysql table

creating dynamic buttons

a.link_style
{ padding-left:10px;
padding-right:10px;
padding-top:3px;
padding-bottom:3px;
background-color:#666666;
color:#FFFFFF;
border:solid 1px #000000;
text-decoration:none;
}
a.link_style:hover
{ padding-left:10px;
padding-right:10px;
padding-top:3px;
padding-bottom:3px;
background-color:#CCCCCC;
color:#000000;
border:solid 1px #000000;
text-decoration:none;
}