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条)

报告相同问题?

悬赏问题

  • ¥50 切换TabTip键盘的输入法
  • ¥15 可否在不同线程中调用封装数据库操作的类
  • ¥15 微带串馈天线阵列每个阵元宽度计算
  • ¥15 keil的map文件中Image component sizes各项意思
  • ¥20 求个正点原子stm32f407开发版的贪吃蛇游戏
  • ¥15 划分vlan后,链路不通了?
  • ¥20 求各位懂行的人,注册表能不能看到usb使用得具体信息,干了什么,传输了什么数据
  • ¥15 Vue3 大型图片数据拖动排序
  • ¥15 Centos / PETGEM
  • ¥15 划分vlan后不通了