Export data from MS EXCEL sheet to MySQL table – PHP Script

[...] 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???

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

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:wamptmpphpC8.tmp is not readable.

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 ?

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

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.

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.

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

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;

?>

… 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….

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.

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.

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

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:wampwwwrevivre.orgweb_rootadminexcelreaderExcelreader.php on line 259

reader.php line 259 : $this->_ole =& new OLERead();

thanks for your help !

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

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 ?

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

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.

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…

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???

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.

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?

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.

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..

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

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…

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

thank u very much dude

Can we insert records from xlsx format. I mean it is not uploading data from ms office 2007 format’s file. can you tell me how to read xlsx file…