php - How to preprocess a local CSV file before uploading to a server? - Stack Overflow

admin管理员组

文章数量:1389761

I am trying to set up a web page where trusted users can upload local CSV files that will be parsed, validated, reviewed by the users, and then inserted into various tables on a MySQL database.

The rest of the site is written using PHP and jQuery. I know how to read a CSV file into PHP and generate a query. The problem is that the validation and parsing of the file is interactive-- the user needs to be asked for various information about the data, match up columns from the file with fields in the database, and be given the opportunity to review and update their answers until they're sure everything is correct.

It seems kind of 1990's to reload the whole page every time the user changes something, so I assumed that AJAX would be the way to do it client-side, but it seems that opening local files is specifically prevented by browsers for "security reasons".

My question is:

Is there a generally accepted pattern for securely and efficiently letting a user make decisions based on the contents of a file before actually uploading the file to the server? Or do people really just send dozens of POST requests in such situations?

By the way, we do not use Windows or anything closed-source, so unfortunately the solution cannot rely on Internet Explorer or ActiveX.

Thanks.

I am trying to set up a web page where trusted users can upload local CSV files that will be parsed, validated, reviewed by the users, and then inserted into various tables on a MySQL database.

The rest of the site is written using PHP and jQuery. I know how to read a CSV file into PHP and generate a query. The problem is that the validation and parsing of the file is interactive-- the user needs to be asked for various information about the data, match up columns from the file with fields in the database, and be given the opportunity to review and update their answers until they're sure everything is correct.

It seems kind of 1990's to reload the whole page every time the user changes something, so I assumed that AJAX would be the way to do it client-side, but it seems that opening local files is specifically prevented by browsers for "security reasons".

My question is:

Is there a generally accepted pattern for securely and efficiently letting a user make decisions based on the contents of a file before actually uploading the file to the server? Or do people really just send dozens of POST requests in such situations?

By the way, we do not use Windows or anything closed-source, so unfortunately the solution cannot rely on Internet Explorer or ActiveX.

Thanks.

• php
• javascript
• user-interaction

Share Improve this question Follow asked Apr 4, 2012 at 21:42 bokovbokov 3,54422 gold badges3434 silver badges5050 bronze badges 5

• 3 You'd still have to let the user upload the file, but segregate it until the user's done with their manipulations. only then do you 'mit' the file to wherever it has to go. – Marc B Commented Apr 4, 2012 at 21:43
• Yes, exactly. So I'm asking how one can segregate the file client-side. – bokov Commented Apr 4, 2012 at 21:46
• No there isn't really apart from using a plugin like flash. It's quite mon in that situation to allow a user to edit a file such as an image and then upload the result or the original file with alterations as an xml or something. It would be a massive security problem if javascript was allowed to inspect files on the user's machine and can't be done. You can't even post a file using javascript without a plugin for this reason. – Gats Commented Apr 4, 2012 at 21:47
• since it's the clients uploading the file, presumably it'd be up to them to keep things segregated on their own machine. If you mean keep the uploads separate on the server, then that's up to your upload handler. stuf them into a 'working' directory for the review phase. when the user signs off on the file, you move it to the final destination. – Marc B Commented Apr 4, 2012 at 21:49
• I mean, do I really have to send the file to the server as a POST, have the server guess the header row and ask the user if it's the right one, then send another POST where the user specified the right header row so the correct column names can be shown, and then another POST where the user matches up the column names with the fields they're supposed to go to? The whole file gets sent three times and two of those times only a couple of lines get read. – bokov Commented Apr 4, 2012 at 21:53

Add a ment  | 

4 Answers 4

Sorted by: Reset to default 6

You don't need to make any request to the server if you use the javascript FileReader API available starting from firefox 3.6 and Chrome 7. Fortunately really interesting articles exist that explain quite clearly how the API works.

http://www.html5rocks./en/tutorials/file/dndfiles/

If you have concerns about the support for the API in the different browsers it is displayed at the end of this other article:

https://developer.mozilla/en/DOM/FileReader

I've recently used this API for uploading both text and binary files so don't hesitate to get back to me if you decide you want to try it and you have any doubt.

For an example of how to do it efficiently, try the Papa Parse library.

It can handle very large files without problems.

You have two broad classes of solution:

• Upload the CSV and rely on server side logic to perform the transformations, and maybe interact with the user. This could include loading a browser page which implements the second choice after the upload.
• Have the user copy and paste the file into a browser text box, and use browser-based javascript or applets to transform the text, perhaps interacting locally or with a webserver.

Either can be done with standards-based implementations. The most unusual option is the second choice relying on user copy/paste. For this reason, most implementations would choose some variation of the first option.

Where to put the main logic of file transformation is up to you. There need not be any security issues with this if care is taken.

HTML:

<form id = "emailform" action = "admincsvupload" role="form" method="post" enctype="multipart/form-data">
<div class="form-group">
<label class="sr-only" for="csvfile">Upload CSV</label>
<input type="file" name = "csvfile" class="form-control" id="csvfile" placeholder="Select CSV file">                 
</div>
<button type="button" class="btn btn-success" id="btnLoad">Load</button>
<button type="submit" class="btn btn-success" id="btnSubmit" style="display:none">Upload Now!</button>
</form>

0%

Javascript :

  // File load as HTMl n a table before actuly upload it on the server



    function updateProgress(evt) {
    // evt is an ProgressEvent.
    if (evt.lengthComputable) {
        var percentLoaded = Math.round((evt.loaded / evt.total) * 100);
        // Increase the progress bar length.
        if (percentLoaded < 100) {
            progress.style.width = percentLoaded + '%';
            progress.textContent = percentLoaded + '%';
        }
    }
}

     function readBlob(opt_startByte, opt_stopByte) {
    var progress = document.querySelector('.percent');
    var files = document.getElementById('csvfile').files;
    if (!files.length) {
        alert('Please select a file!');
        return;
    }

    var file = files[0];
    var start = 0;
    var stop = file.size - 1;
    progress.style.width = '0%';
    progress.textContent = '0%';
    var reader = new FileReader();

    //Reader progress 
    reader.onprogress = updateProgress;

    // If we use onloadend, we need to check the readyState.
    reader.onloadend = function (evt) {
        if (evt.target.readyState == FileReader.DONE) { // DONE == 2
            var data = evt.target.result;
            var delimiter = ',';
            var escape = '\n';
            var rows = data.split(escape);
            var tbl = document.createElement('table');
            tbl.style.width = '100%';
            //tbl.setAttribute('border', '1', "green");
            tbl.className = "table table-hover table-condensed dataTable";
            var tbdy = document.createElement('tbody');

            for (index in rows) {
                var tr = document.createElement('tr'); // creating new row 
                var items = rows[index].split(delimiter);
                for (itemindex in items) {
                    var td = "";
                    if (index == 0) {
                        td = document.createElement('th');
                    } else {
                        td = document.createElement('td');
                    }

                    td.appendChild(document.createTextNode(items[itemindex])); // creating new cell 
                    tr.appendChild(td); // add to current tr
                }

                tbdy.appendChild(tr); // add new row (tr) to table 
            }
            tbl.appendChild(tbdy);

            document.getElementById('byte_content').innerHTML=tbl;

        }
    };

    // Progress Loading
    reader.onloadstart = function(e) {
      document.getElementById('progress_bar').className = 'loading';
    };


    reader.onload = function(e) {
      // Ensure that the progress bar displays 100% at the end.
      progress.style.width = '100%';
      progress.textContent = '100%';
      setTimeout("document.getElementById('progress_bar').className='';", 2000);
    }
    var blob = file.slice(start, stop + 1);
    reader.readAsBinaryString(blob);
    document.querySelector('#btnLoad').style.display = "none";
    document.getElementById("btnSubmit").style.display = "block";


}

//Change event if user select a new file.
document.querySelector('#csvfile').addEventListener('change', function (evt) {
        return readBlob();

}, false);

本文标签： phpHow to preprocess a local CSV file before uploading to a serverStack Overflow