code to import excel data into mysql using php

[padma525]

Hello Sir

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

pls hel me

Thanks

[blocblue]

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.

[nav33n]

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

[sangeetha.n]

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


?>

[sangeetha.n]

Or visit this site : http://legend.ws/blog/tips-tricks/csv-php-mysql-import

[Pja]

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.

[FreeGroup]

Hi,

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

Greetings

Andreas

[cwarn23]

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.

[chrishea]

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

[__avd]

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.

[mtik]

Thanks for all the info.