PDA

View Full Version : How to upload excel in MySql using PHP????



sankarbaluz
22 Sep 2010, 6:27 AM
Hi,
I am new to Extjs.I am struggling to upload excel sheet to MySql Using PHP.The uploading is working partially,as it is missing some fields & showing the error as "missing ) in parenthetical".Iam attaching the files I have worked on.

excel-upload.js

Ext.onReady(function()
{
var fibasic = new Ext.ux.form.FileUploadField
({
region:'center',
width: 400
});
var fp = new Ext.FormPanel
({
region:'center',
fileUpload: true,
width: 500,
url: 'simplecsvimport.php',
method: 'post',
height:400,
buttons: [{
text: 'Save',
handler: function()
{


}
},{
text: 'Reset',
handler: function(){
fp.getForm().reset();
}
}],
frame: true,
title: 'File Upload Form',
autoHeight: true,
bodyStyle: 'padding: 10px 10px 0 10px;',
labelWidth: 50,
defaults: {
anchor: '95%',
allowBlank: false,
msgTarget: 'side'
},
items: [{
xtype: 'fileuploadfield',
id: 'form-file',
emptyText: 'Select a File',
fieldLabel: 'Excel',
name: 'photo-path',
buttonText: '',
buttonCfg: {
iconCls: 'upload-icon'
}
}]
});
var mainPanel=new Ext.Viewport
({
renderTo:'idMain',
layout:'border',
items:
[
{
xtype:'panel',
region:'north',
title:'North'
},
fp,
{
xtype:'button',
region:'south',
text: 'Upload',
handler: function()
{
var v = fibasic.getValue();
//alert(v && v != '' ? v : 'None')
/*
Ext.Ajax.request
({
url : 'projectPHP/deleteProject.php',
method: 'POST',
//params :{del:selItem},
success: function ( result, request )
{
alert("SUCCESS");
},
failure: function ( result, request )
{
alert("FAIL");
}
});
*/


if(fp.getForm().isValid())
{
fp.getForm().submit
({
success: function(fp, o)
{
msg('Success', 'Processed file "'+o.result.file+'" on the server');
},
failure: function(form, action)
{
Ext.Msg.alert('Failure', action.result.msg);
}
});
}


}
}
]
})

});
simplecsvimport.php


<?php


$databasehost = "localhost";
$databasename = "test";
$databasetable = "names";
$databaseusername ="root";
$databasepassword = "";
$fieldseparator = ",";
$lineseparator = "\n";
$csvfile = "filename.csv";


$addauto = 0;
$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);


$line = str_replace("'","\'",$line);


$linearray = explode($fieldseparator,$line);


$linemysql = implode("','",$linearray);


if($addauto)
//$query = "insert into $databasetable (CompanyName,Name,Designation,Address1,Address2,Address3,City,State,Pincode,email,Mobile) values('$linemysql');";
$query = "insert into $databasetable(CompanyName,Name,Designation,Address1,Address2,Address3,City,State,Pincode,email,Mobile)values('','$linemysql');";
else
//$query = "insert into $databasetable (CompanyName,Name,Designation,Address1,Address2,Address3,City,State,Pincode,email,Mobile) values('$linemysql');";
$query = "insert into $databasetable(CompanyName,Name,Designation,Address1,Address2,Address3,City,State,Pincode,email,Mobile)values('$linemysql');";

$queries .= $query . "\n";


@mysql_query($query);
}


@mysql_close($con);


if($save) {
chmod($csvfile, 0777);
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";
?>

steffenk
22 Sep 2010, 7:56 AM
Hi,

even you are new, look to your post. Would you answer to it?
* wrong forum (go to help section)
* ???? same as !!!! why? very very very urgent? This are all problems
* no code boxes - you code and never saw other forum posts? never looked to the editor where you put your question in?
* writing one sentence but 100s lines of code, doesn't motivate to read

Please edit your post and correct this.

22 Sep 2010, 10:04 AM
Moving to help.

michaelc
22 Sep 2010, 10:25 AM
This is not a extjs issue, it's a PHP issue.
Excel is not a csvfile, it's excel you need to use a plug in like this
http://sourceforge.net/projects/phpexcelreader/

sankarbaluz
22 Sep 2010, 10:45 AM
Hi,
Thanks for the reply...Could you just provide a simple example for uploading excel sheet in to MySql using PHP....

22 Sep 2010, 10:47 AM
No. This is not a PHP forum.

sankarbaluz
22 Sep 2010, 10:51 AM
I am struggling to do the program for the past 3 days..plz help me out...I am using Extjs as front end.....

22 Sep 2010, 10:55 AM
I understand and empathize with your situation. however, did you read steffen's post?!

michaelc
22 Sep 2010, 11:32 AM
I am struggling to do the program for the past 3 days..plz help me out...I am using Extjs as front end.....

PHP by itself will not read an excel file, if you go to the link provided download the reader package it has examples and code.
can not get simpler.

sankarbaluz
22 Sep 2010, 8:15 PM
Thanks Michael,
I am going through the example you given.Hope that will get the result.Thanks a lot=;