Handling multiple errors on Excel/CSV import?

Rusty Ballinger

Hey, I have a case where people are using Editor to upload Excel files. That's been working fine for a long time, but, apparently they're routinely uploading files where many of the rows are bad, and they want to see the list of which rows are bad rather than having the processing croak on the first bad one. Worst case, I stuff the bad rows + error messages into some new table and give them a way to view that, but is there an easier way to do this in Editor?

My first thought was that I might be able to stick multiple errors into the response expected by the file upload stuff here, but it doesn't look like it supports multiple errors per file.

My second thought was to switch to CSV and use the CSV import here (which I've had success with in other projects), which I think does a multi-edit post instead of a "file upload", but it looks like that doesn't support row-level errors either (and can't; there's no place for them in the response from the server).

To see this in action, on that CSV import example page, if you hit Export CSV, edit the file (remove the first name from a few rows, and the last name from a few other rows), and then Import CSV, you get the normal multi-edit form with "Multiple values" given for the various fields (all good so far), but when you hit Submit, you get just the field-level errors: "A first name is required" under "First name," and "A last name is required" under "Last name," with no way to tell which rows were the problem.

Thoughts?

allan

How are you currently doing your Excel validation? If you are doing it (or can do it) when the file is uploaded, you could return an error message through Editor's upload file validation. That's probably what I'd do, but it depends exactly on how you are doing the Excel upload and parsing.

Allan

Rusty Ballinger

How are you currently doing your Excel validation?

On the server side, in Java, using org.apache.poi.xssf.usermodel.XSSFWorkbook.

This has turned out to be a lot simpler than I expected. What I had before, on the server side in Java, was this:

/**
 * This is the response object required by
 * https://github.com/blueimp/jQuery-File-Upload/wiki/Setup
 */
public static class UploadResponse {
    private SingleFileUploadResponse[] files = new SingleFileUploadResponse[1];

    public SingleFileUploadResponse[] getFiles() {
        return files;
    }
}
public static class SingleFileUploadResponse {
    String name;
    int size;
    @JsonInclude(JsonInclude.Include.NON_NULL)
    String error;
    //  This one isn't part of the file-upload structure, but I want to
    //  display the summary to the user.
    @JsonInclude(JsonInclude.Include.NON_NULL)
    String status;
    ...

And on the client side, in JavaScript, I had this (presumably copied from another example here, although I couldn't find it just now):

$('#fileupload').fileupload({
    url: '...',
    dataType: 'json',
    disableImageLoad: true,
    acceptFileTypes: '/\.xlsx$/i',
    done: function (e, data) {
        $.each(data.result.files, function (index, file) {
            var msg = (file.error != null) ? (': ' + file.error) : (', ' + file.status);
            var msgClass = (file.error != null) ? 'text-danger' : 'text-success';
            $('<p class="' + msgClass + '"/>').text(file.name + msg).appendTo('#files');
        });
    },
    ...

So, I just added a new member to my SingleFileUploadResponse class containing the list of row errors, and back on the client side, do stuff to display it if it's not null in that done() function.

(I haven't actually finished that last part yet, but as I can see the list of errors coming across, I'm confident that it will be pretty straightforward.)

Rusty Ballinger

And on the client side, in JavaScript, I had this (presumably copied from another example here, although I couldn't find it just now):

Oh, this may have been from the jQuery-File-Upload examples here.

allan

Fantastic to hear. Are you happy with where you are with this now then?

Allan

Rusty Ballinger

Yes, thank you.