1,105,334 Community Members

code to import excel data into mysql using php

Member Avatar
padma525
Newbie Poster
2 posts since Apr 2008
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
1
 

Hello Sir

I want the php code to import the excel data into mysql .

pls hel me

Thanks

Member Avatar
blocblue
Practically a Posting Shark
828 posts since Jan 2008
Reputation Points: 238 [?]
Q&As Helped to Solve: 162 [?]
Skill Endorsements: 16 [?]
Featured
 
0
 

Hi,
Have you considered saving the Excel data as a CSV file, then reading the content of the file into PHP using file_get_content. You should then be able to explode the data by the comma symbol or something I am sure.

What do you think?

R.

Member Avatar
nav33n
Purple hazed!
3,894 posts since Nov 2007
Reputation Points: 472 [?]
Q&As Helped to Solve: 381 [?]
Skill Endorsements: 20 [?]
Team Colleague
Featured
 
0
 

Yep. If its a csv file, then you can make use of fgetcsv to do the required operation.

Member Avatar
sangeetha.n
Newbie Poster
9 posts since Feb 2008
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

Try this... It may help you to solve your problem.

<?

/********************************************************************************************/
/* Code at http://legend.ws/blog/tips-tricks/csv-php-mysql-import/
/* Edit the entries below to reflect the appropriate values
/********************************************************************************************/
$databasehost = "localhost";
$databasename = "test";
$databasetable = "sample";
$databaseusername ="test";
$databasepassword = "";
$fieldseparator = ",";
$lineseparator = "\n";
$csvfile = "E:\Webcache\AA ONLINE\test1\english1.csv";
/********************************************************************************************/
/* Would you like to add an ampty field at the beginning of these records?
/* This is useful if you have a table with the first field being an auto_increment integer
/* and the csv file does not have such as empty field before the records.
/* Set 1 for yes and 0 for no. ATTENTION: don't set to 1 if you are not sure.
/* This can dump data in the wrong fields if this extra field does not exist in the table
/********************************************************************************************/
$addauto = 0;
/********************************************************************************************/
/* Would you like to save the mysql queries in a file? If yes set $save to 1.
/* Permission on the file should be set to 777. Either upload a sample file through ftp and
/* change the permissions, or execute at the prompt: touch output.sql && chmod 777 output.sql
/********************************************************************************************/
$save = 1;
$outputfile = "output.sql";
/********************************************************************************************/


if(!file_exists($csvfile)) {
	echo "File not found. Make sure you specified the correct path.\n";
	exit;
}

$file = fopen($csvfile,"r");

if(!$file) {
	echo "Error opening data file.\n";
	exit;
}

$size = filesize($csvfile);

if(!$size) {
	echo "File is empty.\n";
	exit;
}

$csvcontent = fread($file,$size);

fclose($file);

$con = @mysql_connect($databasehost,$databaseusername,$databasepassword) or die(mysql_error());
@mysql_select_db($databasename) or die(mysql_error());

$lines = 0;
$queries = "";
$linearray = array();

foreach(split($lineseparator,$csvcontent) as $line) {

	$lines++;

	$line = trim($line," \t");
	
	$line = str_replace("\r","",$line);
	
	/************************************************************************************************************
	This line escapes the special character. remove it if entries are already escaped in the csv file
	************************************************************************************************************/
	$line = str_replace("'","\'",$line);
	/***********************************************************************************************************/
	
	$linearray = explode($fieldseparator,$line);
	
	$linemysql = implode("','",$linearray);
	
	if($addauto)
		$query = "insert into $databasetable values('','$linemysql');";
	else
		$query = "insert into $databasetable values('$linemysql');";
	
	$queries .= $query . "\n";

	@mysql_query($query);
}

@mysql_close($con);

if($save) {
	
	if(!is_writable($outputfile)) {
		echo "File is not writable, check permissions.\n";
	}
	
	else {
		$file2 = fopen($outputfile,"w");
		
		if(!$file2) {
			echo "Error writing to the output file.\n";
		}
		else {
			fwrite($file2,$queries);
			fclose($file2);
		}
	}
	
}

echo "Found a total of $lines records in this csv file.\n";


?>
Member Avatar
sangeetha.n
Newbie Poster
9 posts since Feb 2008
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 
Member Avatar
Pja
Newbie Poster
12 posts since Sep 2007
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

How about if i want to import data in an excel file (.xls wo saving it as csv file) into mysql database table by using php.I have downloaded PHP Excel Reader from internet and create and excel template (.xls file). I also have created a simple page that allow users to browse for the .xls template and click on button 'upload and import' in order to import the data inside the .xls into mysql db. But i have no idea how to code it inside php for the import process function. i've tried some code found on G but still cannot make it work. Does anyone have any idea/reference/code on how i can make this excel import function work? Thanks.

Member Avatar
FreeGroup
Newbie Poster
4 posts since Jan 2007
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

Hi,

you can use http://www.dbTube.org
graphical odeling tool for database imports.

Greetings

Andreas

Member Avatar
cwarn23
Occupation: Genius
3,028 posts since Sep 2007
Reputation Points: 387 [?]
Q&As Helped to Solve: 281 [?]
Skill Endorsements: 15 [?]
Team Colleague
Featured
 
0
 

Hi,
Have you considered saving the Excel data as a CSV file, then reading the content of the file into PHP using file_get_content.

Since how this has already been bumped I shall add if you can export the excel file to a CSV file, phpmyadmin will allow you to import the CSV file. Very simple and nearly every server has phpmyadmin.

As a note to FreeGroup, please do not bump very old topics unless your the original author.

Member Avatar
chrishea
Posting Virtuoso
1,524 posts since Sep 2008
Reputation Points: 182 [?]
Q&As Helped to Solve: 256 [?]
Skill Endorsements: 6 [?]
 
0
 

I have successfully used a number of these approaches:
* Using PHPMyAdmin to import a csv file is pretty quick and simple. You will want to drop the header from the CSV file and you need to specify that the field separator is a comma (in PHPMyAdmin).

* You can use PHP to upload a CSV file and then write it to a database. This is probably most useful if you need to edit/modify any of the fields or create indexes as part of the upload process.

* You can also read an XLS file directly. I did some work on an earlier version (link to latest below). It has the advantage of being able to read each of the sheets from a workbook. One limitation of the older version however; was that it couldn't handle references from one sheet to the other. I haven't tried the latest version so I don't know if that is still the case.
http://code.google.com/p/php-excel-reader/downloads/list

Member Avatar
__avd
Posting Genius (adatapost)
6,703 posts since Oct 2008
Reputation Points: 1,811 [?]
Q&As Helped to Solve: 1,272 [?]
Skill Endorsements: 61 [?]
Moderator
 
1
 

You may visit : http://www.eephp.com/

ExcelExplorer is a set of PHP5 classes that can read data from a MS Excel file without using COM, ODBC, etc. It can import data from binary Excel file (XLS) using only standard PHP functions.

Member Avatar
mtik
Newbie Poster
1 post since Aug 2011
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

Thanks for all the info.

You
This article has been dead for over three months: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article