Import From Excel File Into Mysql Database
Import From Excel File Into Mysql Database
How to import excel data into MySQL database using PHP script? Do anyone know about it? could you help?
-reply by Jenny
| |
|
Welcome to KnowledgeSutra - Dear Guest 
, Please Register here to get Your own website. - Ask a Question / Express Opinion / Reply
w/o Sign-Up!
| |
0
16 replies to this topic
#12
-
- Kontributors
-
- 5,876 posts
Hail Caesar!
- Interests:Trap17 Free Web Hosting, No Ads
Posted 10 September 2008 - 10:41 AM
How we can import entries from database into excel sheet.
Import From database Into excel sheet
Please send me a correct PHP code to import entries of database into excel sheet.
Thanks in advance.
-question by Sunil Kumar
---- admin reply ------
Please use a tool called PHPmyADMIN to export into EXCEL directly
OR
EXPORT into CSV format.
Import From database Into excel sheet
Please send me a correct PHP code to import entries of database into excel sheet.
Thanks in advance.
-question by Sunil Kumar
---- admin reply ------
Please use a tool called PHPmyADMIN to export into EXCEL directly
OR
EXPORT into CSV format.
#14
-
- Kontributors
-
- 5,876 posts
Hail Caesar!
- Interests:Trap17 Free Web Hosting, No Ads
Posted 04 August 2009 - 10:03 PM
THANKS!Import From Excel File Into Mysql DatabaseIt's 4 years later, but just wanted to add my thanks. I'm struggling to learn all this stuff and spend hours searching the net for answers to my php / mysql / etc. Questions. Thought I'd need to download special software or write some fancy php code - NO! - your solution was a piece of cake!! More people should write-up their easy solutions and get them out for others to see. Thanks again.-reply by David
#15
-
- Kontributors
-
- 5,876 posts
Hail Caesar!
- Interests:Trap17 Free Web Hosting, No Ads
Posted 17 November 2009 - 09:29 AM
import excel file to database in phpImport From Excel File Into Mysql Database
<?php $data = array(); function add_person( $first, $middle, $last, $email ) { global $data; $data []= array( 'first' => $first, 'middle' => $middle, 'last' => $last, 'email' => $email ); } if ( $_FILES['file']['tmp_name'] ) { $dom = DOMDocument::load( $_FILES['file']['tmp_name'] ); $rows = $dom->getElementsByTagName( 'Row' ); $first_row = true; foreach ($rows as $row) { if ( !$first_row ) { $first = ""; $middle = ""; $last = ""; $email = ""; $index = 1; $cells = $row->getElementsByTagName( 'Cell' ); foreach( $cells as $cell ) { $ind = $cell->getAttribute( 'Index' ); if ( $ind != null ) $index = $ind; if ( $index == 1 ) $first = $cell->nodeValue; if ( $index == 2 ) $middle = $cell->nodeValue; if ( $index == 3 ) $last = $cell->nodeValue; if ( $index == 4 ) $email = $cell->nodeValue; $index += 1; } add_person( $first, $middle, $last, $email ); } $first_row = false; } } $username = "root"; $password = ''; $hostname = "localhost"; $con = mysql_connect($hostname,$username,$password) or die("Unable to connect to MySQL"); mysql_select_db("sample",$con); mysql_error(); foreach($data as $row) { mysql_query("insert into names values('','$row[first]','$row[middle]','$row[last]','$row[email]')"); } ?>
<html> <head> </head> <body> <form action='import.Php' method='post' name='names' enctype='multipart/form-data'> <table> <tr> <td>Upload file :</td> <td><input type='file' name='file'></td> </tr> <tr> <td></td> <td><input type='submit' value='upload'></td> </tr> </table> </form> </body></html>
-reply by kuppa pavan
#16
-
- Kontributors
-
- 5,876 posts
Hail Caesar!
- Interests:Trap17 Free Web Hosting, No Ads
Posted 02 December 2009 - 03:16 PM
I gett he following error with this script...Import From Excel File Into Mysql Database
Fatal error: Call to a member function getelementsbytagname() on a non-object
I get the above error with this script. I have not changed anything in the script and my excel file has four columns with no headers in the first row. Can anyone help?!?
-reply by Nicholas
#17 Guest_joddor_*
-
- Guests
Posted 07 September 2011 - 07:40 PM
<?php
/* vim: set expandtab sw=4 ts=4 sts=4: */
/**
* Microsoft Office Excel 2007 XLSX import plugin for phpMyAdmin
*
* @todo Pretty much everything
* @version $Id$
* @package phpMyAdmin-Import
*/
if (! defined('PHPMYADMIN')) {
exit;
}
/**
* The possible scopes for $plugin_param are: 'table', 'database', and 'server'
*/
if (isset($plugin_list)) {
$plugin_list['xlsx'] = array(
'text' => 'strImportXLSX',
'extension' => 'xlsx',
'options' => array(
array('type' => 'bool', 'name' => 'col_names', 'text' => 'strImportColNames'),
),
'options_text' => 'strOptions',
);
/* We do not define function when plugin is just queried for information above */
return;
}
ini_set('memory_limit', '256M');
set_time_limit(120);
/* Append the PHPExcel directory to the include path variable */
set_include_path(get_include_path() . PATH_SEPARATOR . getcwd() . '/libraries/PHPExcel/');
require_once './libraries/PHPExcel/PHPExcel.php';
require_once './libraries/PHPExcel/PHPExcel/Reader/Excel2007.php';
$objReader = new PHPExcel_Reader_Excel2007();
$objReader->setReadDataOnly(true);
$objReader->setLoadAllSheets();
$objPHPExcel = $objReader->load($import_file);
$sheet_names = $objPHPExcel->getSheetNames();
$num_sheets = count($sheet_names);
$tables = array();
$tempRow = array();
$rows = array();
$col_names = array();
for ($s = 0; $s < $num_sheets; ++$s) {
$current_sheet = $objPHPExcel->getSheet($s);
$num_rows = $current_sheet->getHighestRow();
$num_cols = PMA_getColumnNumberFromName($current_sheet->getHighestColumn());
if ($num_rows != 1 && $num_cols != 1) {
for ($r = 1; $r <= $num_rows; ++$r) {
for ($c = 0; $c < $num_cols; ++$c) {
$cell = $current_sheet->getCellByColumnAndRow($c, $r)->getCalculatedValue();
if (! strcmp($cell, '')) {
$cell = 'NULL';
}
$tempRow[] = $cell;
}
$rows[] = $tempRow;
$tempRow = array();
}
if ($_REQUEST['xlsx_col_names']) {
$col_names = array_splice($rows, 0, 1);
$col_names = $col_names[0];
for ($j = 0; $j < $num_cols; ++$j) {
if (! strcmp('NULL', $col_names[$j])) {
$col_names[$j] = PMA_getColumnAlphaName($j + 1);
}
}
} else {
for ($n = 0; $n < $num_cols; ++$n) {
$col_names[] = PMA_getColumnAlphaName($n + 1);
}
}
$tables[] = array($sheet_names[$s], $col_names, $rows);
$col_names = array();
$rows = array();
}
}
unset($objPHPExcel);
unset($objReader);
unset($rows);
unset($tempRow);
unset($col_names);
/* Obtain the best-fit MySQL types for each column */
$analyses = array();
$len = count($tables);
for ($i = 0; $i < $len; ++$i) {
$analyses[] = PMA_analyzeTable($tables[$i]);
}
/**
* string $db_name (no backquotes)
*
* array $table = array(table_name, array() column_names, array()() rows)
* array $tables = array of "$table"s
*
* array $analysis = array(array() column_types, array() column_sizes)
* array $analyses = array of "$analysis"s
*
* array $create = array of SQL strings
*
* array $options = an associative array of options
*/
/* Set database name to the currently selected one, if applicable */
if (strlen($db)) {
$db_name = $db;
$options = array('create_db' => false);
} else {
$db_name = 'XLSX_DB';
$options = NULL;
}
/* Non-applicable parameters */
$create = NULL;
/* Created and execute necessary SQL statements from data */
PMA_buildSQL($db_name, $tables, $analyses, $create, $options);
unset($tables);
unset($analyses);
$finished = true;
$error = false;
/* Commit any possible data in buffers */
PMA_importRunQuery();
?>
Reply to this topic
1 user(s) are reading this topic
0 members, 1 guests, 0 anonymous users
