transfer excel worksheet data to mysql table

[cmghosh]

how to transfer excel worksheet data to mysql table?

[cmghosh]

this code is for mysql-excel

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

<?php 
 
//EDIT YOUR MySQL Connection Info: 
/*$DB_Server = "mysql.hosting-advantage.com";        //your MySQL Server */
$DB_Server = "localhost";        //your MySQL Server 
$DB_Username = "__MUNGED__";                 //your MySQL User Name 
$DB_Password = "__MUNGED__";                //your MySQL Password 
$DB_DBName = "test";                //your MySQL Database Name 
$DB_TBLName = "data_transfer";                //your MySQL Table Name 
//$DB_TBLName,  $DB_DBName, may also be commented out & passed to the browser 
//as parameters in a query string, so that this code may be easily reused for 
//any MySQL table or any MySQL database on your server 
 
//DEFINE SQL QUERY: 
//you can use just about ANY kind of select statement you want - 
//edit this to suit your needs! 
$sql = "Select * from $DB_TBLName"; 
 
//Optional: print out title to top of Excel or Word file with Timestamp 
//for when file was generated: 
//set $Use_Titel = 1 to generate title, 0 not to use title 
$Use_Title = 1; 
//define date for title: EDIT this to create the time-format you need 
$now_date = date('m-d-Y H:i'); 
//define title for .doc or .xls file: EDIT this if you want 
$title = "Dump For Table $DB_TBLName from Database $DB_DBName on $now_date"; 
/* 
 
Leave the connection info below as it is: 
just edit the above. 
 
(Editing of code past this point recommended only for advanced users.) 
*/ 
//create MySQL connection 
$Connect = @mysql_connect($DB_Server, $DB_Username, $DB_Password) 
     or die("Couldn't connect to MySQL:<br>" . mysql_error() . "<br>" . mysql_errno()); 
//select database 
$Db = @mysql_select_db($DB_DBName, $Connect) 
     or die("Couldn't select database:<br>" . mysql_error(). "<br>" . mysql_errno()); 
//execute query 
$result = @mysql_query($sql,$Connect) 
     or die("Couldn't execute query:<br>" . mysql_error(). "<br>" . mysql_errno()); 
 
//if this parameter is included ($w=1), file returned will be in word format ('.doc') 
//if parameter is not included, file returned will be in excel format ('.xls') 
if (isset($w) && ($w==1)) 
{ 
     $file_type = "msword"; 
     $file_ending = "doc"; 
}else { 
     $file_type = "vnd.ms-excel"; 
     $file_ending = "xls"; 
} 
//header info for browser: determines file type ('.doc' or '.xls') 
header("Content-Type: application/$file_type"); 
header("Content-Disposition: attachment; filename=database_dump.$file_ending"); 
header("Pragma: no-cache"); 
header("Expires: 0"); 
 
/*    Start of Formatting for Word or Excel    */ 
 
if (isset($w) && ($w==1)) //check for $w again 
{ 
     /*    FORMATTING FOR WORD DOCUMENTS ('.doc')   */ 
     //create title with timestamp: 
     if ($Use_Title == 1) 
     { 
         echo("$title\n\n"); 
     } 
     //define separator (defines columns in excel & tabs in word) 
     $sep = "\n"; //new line character 
 
     while($row = mysql_fetch_row($result)) 
     { 
         //set_time_limit(60); // HaRa 
         $schema_insert = ""; 
         for($j=0; $j<mysql_num_fields($result);$j++) 
         { 
         //define field names 
         $field_name = mysql_field_name($result,$j); 
         //will show name of fields 
         $schema_insert .= "$field_name:\t"; 
             if(!isset($row[$j])) { 
                 $schema_insert .= "NULL".$sep; 
                 } 
             elseif ($row[$j] != "") { 
                 $schema_insert .= "$row[$j]".$sep; 
                 } 
             else { 
                 $schema_insert .= "".$sep; 
                 } 
         } 
         $schema_insert = str_replace($sep."$", "", $schema_insert); 
         $schema_insert .= "\t"; 
         print(trim($schema_insert)); 
         //end of each mysql row 
         //creates line to separate data from each MySQL table row 
         print "\n----------------------------------------------------\n"; 
     } 
}else{ 
     /*    FORMATTING FOR EXCEL DOCUMENTS ('.xls')   */ 
     //create title with timestamp: 
     if ($Use_Title == 1) 
     { 
         echo("$title\n"); 
     } 
     //define separator (defines columns in excel & tabs in word) 
     $sep = "\t"; //tabbed character 
 
     //start of printing column names as names of MySQL fields 
     for ($i = 0; $i < mysql_num_fields($result); $i++) 
     { 
         echo mysql_field_name($result,$i) . "\t"; 
     } 
     print("\n"); 
     //end of printing column names 
 
     //start while loop to get data 
     while($row = mysql_fetch_row($result)) 
     { 
         //set_time_limit(60); // HaRa 
         $schema_insert = ""; 
         for($j=0; $j<mysql_num_fields($result);$j++) 
         { 
             if(!isset($row[$j])) 
                 $schema_insert .= "NULL".$sep; 
             elseif ($row[$j] != "") 
                 $schema_insert .= "$row[$j]".$sep; 
             else 
                 $schema_insert .= "".$sep; 
         } 
         $schema_insert = str_replace($sep."$", "", $schema_insert); 
         //following fix suggested by Josue (thanks, Josue!) 
         //this corrects output in excel when table fields contain \n or \r 
         //these two characters are now replaced with a space 
         $schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert); 
         $schema_insert .= "\t"; 
         print(trim($schema_insert)); 
         print "\n"; 
     } 
} 
 
?>

[cmghosh]

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.

[cmghosh]

<?php
  if($_POST[submit])
    {
      if(!empty($_POST[email]))
        {
          $email=$_POST[email];
 
          $c1=strpos($email,'@');
          $c2=strpos($email,'.');
 
 
          if(strpos($email,'@') == false)
            {
              $err_msg="Invalid Email...............";
            }
          if(strpos($email,'.') == false)
            {
              $err_msg="Invalid Email...............";
            }
          if($c2==$c1+1)
            {
              $err_msg="Invalid Email...............";
            }
          if(strlen(substr($email,$c2))< 3)
            {
              $err_msg="Invalid Email...............";
            }
 
        }
      else
        {
          $err_msg="Email should not be blank......";
        }
    }
?>
 
<html>
  <head>
    <title>Email Verification and Pagination Demonstration</title>
  </head>
  <body bottommargin="0" leftmargin="0" rightmargin="0" topmargin="0">
    <table width="100%">
      <tr>
        <td style="padding-top:50px;">
          <table align="center" width="50%">
          <? if(isset($err_msg)) {?>
            <tr>
              <td colspan="3" height="45">
                <font face="Arial, Helvetica, sans-serif" color="#FF0000" size="-1"><?=$err_msg?></font>
              </td>
            </tr>
          <?  } ?>
            <form name="frm_email" method="post" action="">
            <tr>
              <td>Enter Email</td>
              <td>:</td>
              <td><input type="text" name="email" size="25" value="<?=$_POST[email]?>" /></td>
            </tr>
            <tr>
              <td colspan="3" align="center">
                <input type="submit" name="submit" value="SUBMIT" />              </td>
            </tr>
            </form>
          </table>
        </td>
      </tr>
    </table>
  </body>  
</html>

[pbmods]

Heya, CMG.

Please use CODE tags when posting source code:

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

[cmghosh]

$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);
}

[cmghosh]

<?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);
?>

[cmghosh]

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);
}

[cmghosh]

<?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;
}
?>

[cmghosh]

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;
}

?>

[cmghosh]

<?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;
}
}

?>

[cmghosh]

<?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>

[cmghosh]

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;
}