Why upload the file, when all you need is the data in it? Cleaner, faster avoid connection timeouts, corrupted data just by adding excel_uploader js

    ma man32

    0/5 stars (0 votes)

    Doing bulk upload of data from excel files reliably can be a nut-job at times. This is how to tame the beast with a simple JavaScript library.

    Instead of first uploading the entire file to the server and then processing it, this library will save you some bandwidth by processing the file on the local machine and uploading the extracted data (which is what you actually want) to the server in a batch.

    Because it does the upload in batches, issues such as connection timeout are taken care of. In addition to this, it reports the data that cause an exception on the server and make them available for download as an Excel file.

    I need to do a bulk upload of user records from an excel file ranging from 3000 to 16000 rows. Each excel record (a row) will be used to create a user account in the system. This involves trips to more than one database tables, sending email, data validation and one or more loops.

    The first technique that comes to mind is to look for an excel library, upload the excel file to the server, process the excel file and finally return success or error message — all in one HTTP Request!

    The shortcomings of this method are:

    - HTTP Request timeout, especially with programming languages that are not multithreaded

    - Unreliable error reporting

    and so on.

    What I did is to extract the data with JavaScript on the client browser and push these data to the server in batches.

    On the server-side of things, the data received is processed in a try…catchblock and data that raise an exception is stored in an error array. Once, the server is done processing the current batch it sends an OK response to the client, and alongside, the error data — if any.

    One other technique that is key to this process is Database Transaction Management. All database interactions for a particular record is done in the confines of a DB transaction — on the server side. If an error occurred during database interaction, the transaction is rolled back otherwise it is committed.

    In programming, there is usually more than one way to achieve a feat, each with its own shortcomings. However, this technique is reliable in terms of error reporting and HTTP connection management.

     

    Notice

    Commenting only available for logged in users