I receive large CSV files from a company. I want to design a PHP script that will upload these files to MySQL database, so I don't have to manually do it in MySQL b/c these files get updated daily.
Does anybody know or have examples of how I can create a script that will delete my old files in the database then update the database with the new files from the CSV file. The CSV file contains over 200,000 lines of information. I need something that will continue and the web page won't time out.
$load = "LOAD DATA LOCAL INFILE "'.$csv_file.'"
INTO TABLE umf_novation_roster
CHARACTER SET latin1
FIELDS TERMINATED BY ";"
IGNORE 1 LINES
(memberID, LIC, GLN, MemberDate, Name, Address1, Address2,
City, State, ZipCode, DisplayZipCode, MemberPhone, SystemID, SystemName,
PrimaryDESC, Region, ParentID, ParentName, AccountManager, MemberStatus,
Network, SupplyNetwork1, SupplyNetwork2, AcademicMedCenters, AccMgrEmail)";
Instead of using the information above I used the following Javascript, plus php code. Basically this is a form, which calls the javascript functions below, then calls processupload.php. Processupload.php then uploads the file to the server.
Index.php
echo '<div id="upload-wrapper">';
echo '<div align="center">';
echo '<h3>File Uploader</h3>';
echo '<form action="processupload.php" method="post" enctype="multipart/form-data" id="MyUploadForm">';
echo '<select name="CSVFileData" id="CSVFileData"><option value="default">Select CSV Upload</option><option value="NovationRoster">Novation Roster CSV File</option><option value="MembershipRoster">Membership Roster CSV File</option><option value="GLNExport">GLN Text File</option></select><br/>';
echo '<input name="FileInput" id="FileInput" type="file" />';
echo '<input type="submit" id="submit-btn" value="Upload" />';
//echo '<img src="images/loading.gif" width="30px" id="loading-img" style="display:none;" alt="Please Wait"/>';
echo '</form>';
echo '<div id="progressbox" ><div id="statustxt">0%</div></div>';
echo '<div id="output"></div>';
echo '<div><img src="images/loading.gif" width="50px" id="loading-img" style="display:none;" alt="Please Wait"/></div>';
echo '</div>';
echo '</div>';
?>
<script type="text/javascript" src="js/jquery-1.10.2.min.js"></script>
<script type="text/javascript" src="js/jquery.form.min.js"></script>
<script type="text/javascript">
$(document).ready(function() {
var options = {
target: '#output', // target element(s) to be updated with server response
beforeSubmit: beforeSubmit, // pre-submit callback
success: afterSuccess, // post-submit callback
uploadProgress: OnProgress, //upload progress callback
resetForm: true // reset the form after successful submit
};
$('#MyUploadForm').submit(function() {
$(this).ajaxSubmit(options);
// always return false to prevent standard browser submit and page navigation
return false;
});
//function after successful file upload (when server response)
function afterSuccess()
{
$('#submit-btn').show();
$('#loading-img').hide();
$('#progressbox').delay( 1000 ).fadeOut(); //hide progress bar
$('#output').html("Data added to database");
}
//function to check file size before uploading.
function beforeSubmit(){
//check whether browser fully supports all File API
if (window.File && window.FileReader && window.FileList && window.Blob)
{
if( !$('#FileInput').val()) //check empty input filed
{
$("#output").html("No file, please select file!");
return false
if ($('#CSVFileData').val() == 'default')
{
$("#output").html("Select Value in Dropdown");
return false
}
var fsize = $('#FileInput')[0].files[0].size; //get file size
var ftype = $('#FileInput')[0].files[0].type; // get file type
//allow file types
switch(ftype)
{
//case 'image/png':
//case 'image/gif':
//case 'image/jpeg':
//case 'image/pjpeg':
case 'text/plain':
//case 'text/html':
//case 'application/x-zip-compressed':
//case 'application/pdf':
//case 'application/msword':
case 'application/vnd.ms-excel':
//case 'video/mp4':
break;
default:
$("#output").html("<b>"+ftype+"</b> Unsupported file type, please enter csv file type.");
return false
}
//Allowed file size is less than 5 MB (1048576)
//if(fsize>30000000)
if(fsize > 65000000)
{
$("#output").html("<b>"+bytesToSize(fsize) +"</b> Too big file! <br />File is too big, it should be less than 5 MB.");
return false
}
$('#submit-btn').hide();
$('#loading-img').show();
$("#output").html("");
}
else
{
//Output error to older unsupported browsers that doesn't support HTML5 File API
$("#output").html("Please upgrade your browser, because your current browser lacks some new features we need!");
return false;
}
}
//progress bar function
function OnProgress(event, position, total, percentComplete)
{
/ /Progress bar
$('#progressbox').show();
//$('#progressbar').width(percentComplete + '%') //update progressbar percent complete
$('#statustxt').html(percentComplete + '%'); //update status text
if(percentComplete>50)
{
$('#statustxt').css('color','#000'); //change status text to white after 50%
}
if (percentComplete == 100)
{
$('#output').html("File upload successful, adding data to database.");
$('#progressbox').delay( 1000 ).fadeOut();
}
}
//function to format bites bit.ly/19yoIPO
function bytesToSize(bytes) {
var sizes = ['Bytes', 'KB', 'MB', 'GB', 'TB'];
if (bytes == 0) return '0 Bytes';
var i = parseInt(Math.floor(Math.log(bytes) / Math.log(1024)));
return Math.round(bytes / Math.pow(1024, i), 2) + ' ' + sizes[i];
}
});
</script>
Processupload.php
set_time_limit(0);
ini_set('memory_limit','2048M');
if(isset($_FILES["FileInput"]) && $_FILES["FileInput"]["error"]== UPLOAD_ERR_OK)
{
############ Edit settings ##############
$UploadDirectory = '**************************';
//specify upload directory ends with / (slash)
##########################################
//check if this is an ajax request
if (!isset($_SERVER['HTTP_X_REQUESTED_WITH']))
{
die();
}
//Is file size is less than allowed size.
//if ($_FILES["FileInput"]["size"] > 30000000)
if ($_FILES["FileInput"]["size"] > 65000000)
{
die("File size is too big!");
}
//allowed file type Server side check
switch(strtolower($_FILES['FileInput']['type']))
{
case 'text/plain':
case 'application/vnd.ms-excel':
//case 'video/mp4':
break;
default:
{
die('Unsupported File!'); //output error
}
}
$File_Name = strtolower($_FILES['FileInput']['name']);
// Get File Extension
$File_Ext = substr($File_Name, strrpos($File_Name, '.'));
//$NewFileName = $_FILES['FileInput']['name'];
if ($_POST['CSVFileData'] == 'NovationRoster')
{
$NewFileName = 'NovationRoster.csv';
}
else if ($_POST['CSVFileData'] == 'MembershipRoster')
{
$NewFileName = 'MembershipRoster.csv';
}
else if ($_POST['CSVFileData'] == 'GLNExport')
{
$NewFileName = 'export.txt';
}
if(move_uploaded_file($_FILES['FileInput']['tmp_name'], $UploadDirectory.$NewFileName ))
{
header ("location: index.php?upload=".urlencode($_FILES['FileInput']['name'])."&d=".$_POST['CSVFileData']);
}
else
{
die('error uploading File!');
}
}
else
{
die('Something wrong with upload! Is "upload_max_filesize" set correctly?');
}