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".
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.
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.com/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.org/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:
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);