Contribution: Simple but effective csv uploader

[leocrawf]

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

[Utech22]

Not bad,not bad, nice script.

[owen]

Good work. However, please make it into a class and state its requirements. So that noobs can use it efficiently and that you can provide updates to your contribution.

There is also a security bug in the implementation where somebody can hack the tablename. I suggest you use an array constant for that instead.

[Sutra]

I like... Faster than fgetcsv? I definitely like...