drb88830 2014-02-25 13:41
浏览 55
已采纳

将巨大的CSV文件上传到MySQL数据库脚本

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.

3/19 Data Added

$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)";
  • 写回答

2条回答 默认 最新

  • doukoumi3389 2014-05-08 13:05
    关注

    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?'); 
    }
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥15 树莓派与pix飞控通信
  • ¥15 自动转发微信群信息到另外一个微信群
  • ¥15 outlook无法配置成功
  • ¥30 这是哪个作者做的宝宝起名网站
  • ¥60 版本过低apk如何修改可以兼容新的安卓系统
  • ¥25 由IPR导致的DRIVER_POWER_STATE_FAILURE蓝屏
  • ¥50 有数据,怎么建立模型求影响全要素生产率的因素
  • ¥50 有数据,怎么用matlab求全要素生产率
  • ¥15 TI的insta-spin例程
  • ¥15 完成下列问题完成下列问题