Export data from MS EXCEL sheet to MySQL table – PHP Script
Export data from MS EXCEL sheet to MySQL table – PHP Script
Hello Friends !!
Today i write post on Export records from EXCEL sheet to MySQL table.I have PHP script for that. I already use that script and it’s working.
I will give you whole source code and explain you how to use this script.First of all you have to download excelreader file. Here this is a zip file and you have to extract it into your project directory.
After this make one PHP file called “excel_upload.php“. Copy this code and paste in this PHP file. You have to set the fields as your table.
PHP script shown below :
$con = mysql_connect(‘HOSTNAME’, ‘USERNAME’, ‘PASSWORD’);
if (!$con)
{
die(‘Could not connect: ‘ . mysql_error());
}
mysql_select_db(“YOUR DATABASE”, $con);
$edata = new Spreadsheet_Excel_Reader();
// Set output Encoding.
$edata->setOutputEncoding(‘CP1251′);
if($_FILES['file']['tmp_name'])
{
$edata->read($_FILES['file']['tmp_name']);
}
error_reporting(E_ALL ^ E_NOTICE);
$arr=array();
for ($i = 2; $i <= $edata->sheets[0]['numRows']; $i++)
{
for ($j = 1; $j <= $edata->sheets[0]['numCols']; $j++)
{
$arr[$i][$j]=$edata->sheets[0]['cells'][$i][$j];
}
$addsql = “insert into jos_users (`field1` ,`field1` ,`field1`,`field1`) “;
$addsql = $addsql.”VALUES (‘”.$arr[$i][1].”‘,’”.$arr[$i][2].”‘,’”.$arr[$i][3].”‘,’”.$arr[$i][4].”‘)”;
$ans=mysql_query($addsql);
}
mysql_close($con);
?>
<form name=”frm” method=”post” enctype=”multipart/form-data” id=”frm”>
<input type=”file” name=”file” class=”TextboxCss” size=”30″ /><input name=”btn_save” type=”submit” class=”Button1Css” id=”btn_save” value=”Save” >
</form>
In above code, first loop for rows, and second loop for columns. First loop starts from 2 because generally first row
contains title of column.
$arr is an array which fetch every row one bye one and store data in it. It is a two dimentional array.
If you have any problem with this code then comment on this post. If you have any new about any post then also comment me.
Thanks….
about 1 year ago
can this handle multiple sheets in a single excel file???
about 1 year ago
Yes caparuni, It is possible but you have to do some extra coading. Don’t worry i will give that code . Thanks
about 9 months ago
After all my hardworks and some necessary changes i hv found the erro below. pls i need some assistance asap…
The filename C:\wamp\tmp\phpC8.tmp is not readable.
about 9 months ago
Hello Mr. B.S. Das,
I have to see your script. Then i can tell you what is the problem. Can u send your script code to me ?
about 8 months ago
it gives parse errors on so many lines.
damn man jst wastage of time
about 8 months ago
I’ve tried this code several times; it runs but no data loaded.
I tried using csv and xls file.
Pls. someone should help urgently.
about 8 months ago
Hello ISAAC,
First Please check the database connection.
You have to set the fields respective the XLS rows and columns.
If u give me the format of your XLS file then I will give u code for that XLS fie.
Thanks for comment.
about 8 months ago
I used both excel 2003 and 2007.
The columns are :
Symbol Pclose Close Open High Low Deals Volume Value
The connection to the database is perfect and works for other data entries.
Thanks so much for your assistance
about 8 months ago
… and the complete code is:
setOutputEncoding(‘CP1251′);
if($_FILES['file']['tmp_name'])
{
$edata->read($_FILES['file']['tmp_name']);
}
error_reporting(E_ALL ^ E_NOTICE);
$arr=array();
for ($i = 2; $i sheets[0]['numRows']; $i++)
{
for ($j = 1; $j sheets[0]['numCols']; $j++)
{
$arr[$i][$j]=$edata->sheets[0]['cells'][$i][$j];
}
$addsql = “insert into $db_table (‘SYMBOL’ ,’PCLOSE’ ,’OPEN’,'HIGH’,'LOW’, ‘CLOSE’, ‘DEALS’, ‘VOLUME’, ‘VALUE’) “;
$addsql = $addsql.”VALUES (‘”.$arr[$i][1].”‘,’”.$arr[$i][2].”‘,’”.$arr[$i][3].”‘,’”.$arr[$i][4].”‘,’”.$arr[$i][5].”‘,’”.$arr[$i][6].”‘,’”.$arr[$i][7].”‘,’”.$arr[$i][8].”‘,’”.$arr[$i][9].”‘)”;
$ans=mysql_query($addsql);
mysql_query($addsql);
}
mysql_close($con);
//LOAD DATA INFILE ‘path/file.txt’ INTO TABLE your_table;
?>
about 8 months ago
Hello Isaac,
Your code is perfect. I have tested it.
But you have to consider some points.
1) You have to save your EXCEL SHEET as .xls extension.
2) You have to download excelreader (download link given above) and put your PHP file in which you write this code in this downloaded folder.
I think the problem is that code does not found excelreader.php file.
Please check these points.
If any query then comment.
Thanks for your reply….
about 7 months ago
Hi,
Thanks for the code. Basis this I’ve a query. I would appreciate if anyone of you can provide me the code for the same.
I’ve a website on carpool and my visitors register themselves on my site. The registration data is stored into a .csv file using a php script apgForm. Now I want to add a functionality of search to my website wherein a user can enter from and to location and basis this input the script should return matches from the .csv file stored on the webserver.
Hope I clarify my requirement clearly. Immediate help would be highly appreciable.
about 5 months ago
i put everything like the above instruction but it cant load or save data in to mysql what is the problem please help me.
about 5 months ago
php code problem ??
The filename /tmp/phpzuwEUc is not readable
about 3 months ago
I have a little problem with the script. When i upload xls i have this error :
Deprecated: Assigning the return value of new by reference is deprecated in C:\wamp\www\revivre.org\web_root\admin\excelreader\Excel\reader.php on line 259
reader.php line 259 : $this->_ole =& new OLERead();
thanks for your help !
about 2 months ago
hi i am used same script but i have a problem for date , at time of convert it will change date format
about 2 months ago
Hello Bipin,
First of all thanks for comment. Can you tell me at which time you get this ? After saving data in MySQL ?
about 2 months ago
Can this handle multiple sheets in a single excel file. If possible can you pls give me the code for that. Thanx in advance
about 2 months ago
Hello Sujith ,
Thanks for comment. This script handle only single sheet of excel file. I will try to make it for multiple sheets. If i will get success then I will post it here.
about 1 month ago
Thanx. I’ll be waiting for the same…
about 1 month ago
Hi, i meant to ask you that can this code handle multiple sheets in a single excel file???
about 1 month ago
This script handle only one sheet in excel. I am trying to make a script which handle multiple sheets.
about 4 weeks ago
Where is the form for upload an excel file?
about 4 weeks ago
hello agustu,
Please check the give code carefully. At the last, there is a form by which you can upload EXCEL file.
Thanks.
about 4 weeks ago
thanks for reply mr.admin,
yes i can see the script for upload an excel file but when i run to the browser this form is not showing up..
about 4 weeks ago
even i tried u make a form myself but its still not showing up. I’m using apache2triad on my local server.. please help me solve this prob cause i really need. thanks before
about 1 week ago
I am getting that “…The filename /tmp/phpk8ujhs is not readable…” error as well. I dod not see a answer to that question, can you tell me what i am doing wrong?
thanks…