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….
[...] Originally posted here: Export data from MS EXCEL sheet to MySQL table – PHP bScript/b | php b…/b [...]
can this handle multiple sheets in a single excel file???
Yes caparuni, It is possible but you have to do some extra coading. Don’t worry i will give that code . Thanks
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.
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 ?
it gives parse errors on so many lines.
damn man jst wastage of time
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.
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.
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
… 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;
?>
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….
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.
i put everything like the above instruction but it cant load or save data in to mysql what is the problem please help me.
php code problem ??
The filename /tmp/phpzuwEUc is not readable
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 !
hi i am used same script but i have a problem for date , at time of convert it will change date format
Hello Bipin,
First of all thanks for comment. Can you tell me at which time you get this ? After saving data in MySQL ?
Can this handle multiple sheets in a single excel file. If possible can you pls give me the code for that. Thanx in advance
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.
Thanx. I’ll be waiting for the same…
Hi, i meant to ask you that can this code handle multiple sheets in a single excel file???
This script handle only one sheet in excel. I am trying to make a script which handle multiple sheets.
Where is the form for upload an excel file?
hello agustu,
Please check the give code carefully. At the last, there is a form by which you can upload EXCEL file.
Thanks.
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..
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
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…
Thank you I really helped
hey the script isn't working