I have an Excel file containing data(list of Phone numbers), and i want that data from, Excel file to be imported in to my Database table(for example called phonenumber_list) using PHP code? i know how to convert MySQl in to Excel, but can it possible in reverse? Please Help!
Add a comment
|
2 Answers
CSV
If you can convert the Excel file to CSV first, you can use mysqlimport to import CSV. This is probably the quickest method for getting the data into MySQL.
You can do this from PHP using LOAD DATA INFILE. This is a sample SQL statement to import data.csv:
LOAD DATA INFILE 'data.csv' INTO TABLE phonenumber_list
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;
Excel
If you cannot use CSV, and need to work with raw Excel files, you will need a PHP library which is capable of reading Excel files.
There are a few available, but I don't know how reliable or how well maintained they are:
Pear: Spreadsheet_Excel_Writer
You may also want to look at the alternative approach of using the Excel API, but you'll need Excel installed to do that. There's a little information about that here:
http://www.sydphp.org/presentations/010606-excel.html
If you use this approach, you will need to write some code that reads and parses the Excel file, and sends it to MySQL row-by-row. This may work out a lot slower than a bulk CSV import.
2 Comments
Harish Kurup
mysqlimport and LOAD DATA INFILE, use only CSV or will it work with EXCEl file too...
Mike
Sorry, I didn't make that clear.
LOAD DATA INFILE will only work with CSV files. I have updated my answer.First you have to convert the Excel file into CSV format (comma delimited) file.
Then use the following PHP script to insert it in MySQL:
<?php
error_reporting(E_ALL & ~E_NOTICE);
$connect = mysql_connect("localhost","root","");
@mysql_select_db("dbname");
$inputFile=@$_FILES[csv][name];
$ext=end(explode('.',$inputFile));
if($ext=='csv')
{
$tmpFile=@$_FILES[csv][tmp_name];
$f = @fopen($tmpFile, 'r');
$contents = @fread($f, 10000);
fclose($f);
$fileLines = explode("\r\n", $contents); // explode to make sure we are only using the first line.
$fieldList_header = explode(',', $fileLines[0]);
$csv_header=array("ID","Name","Address","Email-Id","Contact-No"); // Excel file heading
$i=0;
//index keys for each column
$ID_key=null;
$Name_key=null;
$Address_key=null;
$Email_ID_key=null;
$Contact_No_key=null;
foreach($csv_header as $csv)
{
//searching index for each value
if(in_array($csv,$fieldList_header))
{
$key=array_search($csv,$fieldList_header);
if($i==0)
$ID_key=$key;
if($i==1)
$Name_key=$key;
if($i==2)
$address_key=$key;
if($i==3)
$Email_ID_key=$key;
if($i==4)
$Contact_No_key=$key;
}
else
{
echo "Failed to search : ".$csv." blank space will be inserted.<br>";
}
$i++;
}
echo "<table border=1px>";
echo "<tr>";
echo "<td>";
echo "Id";
echo "</td>";
echo "<td>";
echo "Name";
echo "</td>";
echo "<td>";
echo "Address";
echo "</td>";
echo "<td>";
echo "Email ID";
echo "</td>";
echo "<td>";
echo "Contact No";
echo "</td>";
echo "</tr>";
$j=1;
for($i=2;$i<count($fileLines)-1;$i++)
{
$fieldList_other = explode(',', $fileLines[$i]);
echo "<tr>";
echo "<td>";
echo $fieldList_other[$ID_key];
echo "</td>";
echo "<td>";
echo $fieldList_other[$Name_key];
echo "</td>";
echo "<td>";
echo $fieldList_other[$Address_key];
echo "</td>";
echo "<td>";
echo $fieldList_other[$Email_ID_key];
echo "</td>";
echo "<td>";
echo $fieldList_other[$Contact_No_key];
echo "</td>";
echo "</tr>";
$query="insert into Contact (EmployeeName,DateOfJoining,SalaryPerDay,Address,Designation,BalanceLeaves,ContactNumber) values('$fieldList_other[$ID_key]','$fieldList_other[$Name_key]','$fieldList_other[$Address_key]','$fieldList_other[$Email_ID_key]','$fieldList_other[$Contact_No_key]')";
$result=mysql_query($query);
if(!$result)
echo "Record $j failed.<br>";
else
echo "Record $j inserted in database.";
$j++;
}
echo "</table>";
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Import a CSV File with PHP & MySQL</title>
<script>
<!--
function noempty()
{
var y=document.forms["form1"]["csv"].value;
if (y==null || y=="" )
{
alert("Please enter the required field");
return false;
}
}
-->
</script>
</head>
<body>
<form action="<?php echo $_SERVER["PHP_SELF"]; ?>" method="post" enctype="multipart/form-data" name="form1" id="form1">
<center>
</br></br>
Choose your file: <br />
<input name="csv" type="file" id="csv" />
<input type="submit" name="Submit" value="Submit" onclick="return noempty()"/>
</center>
</form>
</body>
</html>
This script will be helpful to you. Because whatever the order of Excel file columns the respective data is only entered in MySQL.
Comments
