Has anyone tried using the excel import function that comes with phpmyadmin http://www.phpmyadmin.net/home_page/ - it does not require any additional plug-ins or scripts and is fairly straightforward to use. In phpmyadmin, if you click on the database table which you wish to import the data to , there is a link on the bottom left corner which says "insert data from a text file into the table" - although it says text file it still can be used to import an excel file. When you click on this link you will be taken to a page where you will be asked for the file name (the excel file should be saved as a csv file), how the fields in the file are terminated (you would change this to a "," without the quotation marks), and other data formatting attributes (these are usually left blank). I have never found any problem when using this
| |
|
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
#2
-
- Kontributors
-
- 1,142 posts
Trap Grand Marshal Member
- Gender:Male
- Location:Mexico
- Interests:Music, Animations, Graphics, almost everything that has to do with computers.
- myCENT:NEGATIVE[-36.23]
Posted 25 September 2005 - 06:14 AM
Cool, I just wish you coulve posted this like a couple of months ago. A friend who is a programmer had to make a little php program that would import a huge excel file into phpmyadmin. He first exported the excel as text and then run it through his program and everything was loaded into phpmyadmin. This method wouldve saved us a lot of time.
#3
-
- Kontributors
-
- 42 posts
Newbie [Level 3]
- Location:Haarlem, The Netherlands
- Interests:Ice-skating and nightskates,<br />Dancing, <br />webdevelopment (java, JSP and PHP)
Posted 08 October 2005 - 02:47 PM
Hmmzzz I wil start working with that soon.
I once started a website that was using excell as a database (only to view not to change) ..
So all people in one company could look at the same data via intranet (php)...
I once started a website that was using excell as a database (only to view not to change) ..
So all people in one company could look at the same data via intranet (php)...
#4
-
- Kontributors
-
- 5,876 posts
Hail Caesar!
- Interests:Trap17 Free Web Hosting, No Ads
Posted 11 April 2008 - 05:52 PM
Import From Excel File Into Mysql Database using Java
Import From Excel File Into Mysql Database
I have to create a java desktop application that upload/loads the data from the MS Excel file (which is also on the desktop) into the specified table in a MySQL DB..
Import From Excel File Into Mysql Database
I have to create a java desktop application that upload/loads the data from the MS Excel file (which is also on the desktop) into the specified table in a MySQL DB..
#5
-
- Kontributors
-
- 5,876 posts
Hail Caesar!
- Interests:Trap17 Free Web Hosting, No Ads
Posted 16 April 2008 - 01:12 PM
So is it possible to transfer a database on MYSQL to another one using this method?
Regrads
Visual basic guide
Http://www.Visualbasicguide.Com
-question by Edison
Regrads
Visual basic guide
Http://www.Visualbasicguide.Com
-question by Edison
#6
-
- Kontributors
-
- 5,876 posts
Hail Caesar!
- Interests:Trap17 Free Web Hosting, No Ads
Posted 28 April 2008 - 01:08 AM
how to import excel file to the database in php
Import From Excel File Into Mysql Database
Hi every body,,does any body can help me?in regards in importing an excel file to the data base in php language.Atually am building on it but theres nothing was there in my database
-reply by jai2
Import From Excel File Into Mysql Database
Hi every body,,does any body can help me?in regards in importing an excel file to the data base in php language.Atually am building on it but theres nothing was there in my database
-reply by jai2
#7
-
- Kontributors
-
- 5,876 posts
Hail Caesar!
- Interests:Trap17 Free Web Hosting, No Ads
Posted 09 June 2008 - 09:37 AM
how to upload csv file and make store data in batabase
Import From Excel File Into Mysql Database
Hai I want to Upload an CSV file and make store data in DB...
-question by Upendar
Import From Excel File Into Mysql Database
Hai I want to Upload an CSV file and make store data in DB...
-question by Upendar
#9
-
- Kontributors
-
- 5,876 posts
Hail Caesar!
- Interests:Trap17 Free Web Hosting, No Ads
Posted 25 August 2008 - 08:53 AM
How we can import an excel file into MYSQL database.
Import From Excel File Into Mysql Database
Please send me a correct PHP code to import an excel file into MYSQL databse.
Thanks.
-question by Jitendra Kumar
Import From Excel File Into Mysql Database
Please send me a correct PHP code to import an excel file into MYSQL databse.
Thanks.
-question by Jitendra Kumar
#10
-
- Kontributors
-
- 31 posts
Newbie [Level 2]
Posted 04 September 2008 - 03:35 AM
Hi,
I'm wrestling with the same issue for some time. We want to import our XLS files if possible to a MYSQL database and make them search-able. By the way I had another question that someone here may know the answer and that is how to invert this XLS file into TXT format to import it to MYSQL anyway?
Thank you for your help.
I'm wrestling with the same issue for some time. We want to import our XLS files if possible to a MYSQL database and make them search-able. By the way I had another question that someone here may know the answer and that is how to invert this XLS file into TXT format to import it to MYSQL anyway?
Thank you for your help.
#11
-
- Kontributors
-
- 5,876 posts
Hail Caesar!
- Interests:Trap17 Free Web Hosting, No Ads
Posted 09 September 2008 - 04:23 AM
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
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
#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
