drb88830
drb88830
2014-02-25 13:41

将巨大的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 doukoumi3389 7年前

    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?'); 
    }
    
    点赞 评论 复制链接分享
  • duanjiao2978 duanjiao2978 7年前

    I have the same solution for my client. First user uploads a file, and after that i TRUNCATE the table and use LOAD DATA LOCAL INFILE. It is very fast. I use it for 100k records. I was measuring the insert time, and for 60466 records with usual INSERT it was 6,5071s, and with LOAD 0,510s. So the difference is as you can see really big. Here is the reference to it https://dev.mysql.com/doc/refman/4.1/en/load-data.html

    For example I have it like this:

    $insert='LOAD DATA LOCAL INFILE "'.$csv_file.'" 
    INTO TABLE im_serialnumbers CHARACTER SET latin1
    FIELDS TERMINATED BY ";"
    IGNORE 1 LINES
    (sn,l,gdatum1,artik,cust,lag,nr,freetext,
    chnr,snr,idatum2,odatum2,vdatum)';
    

    And use the absolute path to .csv file.

    点赞 评论 复制链接分享

为你推荐