I always employ multiple techniques and free tools to extract and upload excel files to mysql database until my boss told me that the client should be able to do it themselves from thier website, so after scowering the web, i was able to put multiple pieces of code from different sources into this :
Note: you have to convert the excel file into csv file before upload, table column have to match with your database table coulmns
PHP Code:
<?php
if ($_REQUEST["id"] == "2" && $_REQUEST["do"] == "uploadfile"){
if($_POST['table_name'] != ""){
$table = $_POST['table_name'];
if ($_FILES["balance_file"]["type"] == "application/csv"){
if ($_FILES["balance_file"]["error"] > 0){
echo "Return Code: " . $_FILES["balance_file"]["error"] . "<br />";
}else{
echo "<div><span style=\"color: #FF0000 \">";
echo "File: " . $_FILES["balance_file"]["name"] . "<br />";
echo "Type: " . $_FILES["balance_file"]["type"] . "<br />";
echo "Size: " . ($_FILES["balance_file"]["size"] / 1024) . " Kb<br />";
echo "Temp file: " . $_FILES["balance_file"]["tmp_name"] . "<br /><br />";
echo"</div></span>";
///////////////////////detele files from database/////////////////////////
$clearfiles = "TRUNCATE TABLE $table";
$clearfilesresult = mysql_query($clearfiles) or die(mysql_error());
if($clearfilesresult){
/////////////upload file////////////////////////////////////////////////
move_uploaded_file($_FILES["balance_file"]["tmp_name"],"excel_files/" . $_FILES["balance_file"]["name"]);
if (file_exists("excel_files/". $_FILES["balance_file"]["name"])){
$txtfile = "excel_files/".$_FILES["balance_file"]["name"];
$loadsql = 'LOAD DATA LOCAL INFILE "'.$txtfile.'" INTO TABLE '.$table.' FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY """" LINES TERMINATED BY "\r\n"';
$loadsqlresult = mysql_query($loadsql) or die(mysql_error());
if($loadsqlresult ){
echo"<script>alert('The file was uploaded and exported to database successfully!')</script>";
unlink($txtfile);
} else {
echo"<script>alert('There was a problem with exporting file to database!')</script>";
}
}
}
else {
echo "<script>alert('ERROR')</script>";
}
}
}
else
{
echo "<script>alert('Invalid file type. Please upload a file with .csv extention')</script>";
}
} else{
echo"<script>alert('Please indicate which file you are uploading by selecting the appropriate radio button!')</script>";
}
}
if ($_REQUEST["id"] == "2"){?>
<form action="forms.php?id=<?php echo($_REQUEST["id"]);?>&do=uploadfile" method="post" enctype="multipart/form-data">
<table cellpadding="2" cellspacing="2" class="smalltxt">
<? /*<tr>
<td>Balance Date & Time:</td>
<td><input id="balance_date" name="balance_date" type="text" class="text" value="<?php echo date('Y-m-d H:i:s'); ?>" />
<span>please update time to correct balance time.</span></td>
</tr>*/ ?>
<tr>
<td width="125" align="left">Share/Thrift/PYCA</td>
<td width="229" align="left"><input type="radio" name="table_name" id="table_name" value="pie_member_transactions" /></td>
</tr>
<tr>
<td align="left">Loan Balance</td>
<td align="left"><input type="radio" name="table_name" id="table_name" value="pie_member_loan_balance" /></td>
</tr>
<tr>
<td align="left">Term Deposite</td>
<td align="left"><input type="radio" name="table_name" id="table_name" value="pie_member_term_deposit_balance" /></td>
</tr>
<tr>
<td align="left">Balance File .csv:</td>
<td align="left"><input id="balance_file" name="balance_file" type="file" class="text"/></td>
</tr>
<tr>
<td align="left"><input class="text" type="Submit" name="sub1" id="sub1" value="Upload File" /></td>
<td align="left"><input class="text" type="Reset" name="res1" id="res1" value="Reset" /></td>
</tr>
</table>
</form>
<?php }?>
For some reason this method just seem to work faster than php fgetcsv method. It uploaded to the database 35,0000 records in about 3 seconds.