A File Conversion Is in Process Please Try Again
Procedure Large Data from Excel File with a Cakewalk
The excel_uploader.js JavaScript library lets y'all extract the data with JavaScript on the client browser and button these data to the server in batches. Information technology reports the data that cause an exception on the server and makes them available for download as an Excel file. It can be fine-tuned to our gustatory modality base of operations on available resources. To use the JavaScript file in a project: we need to get the library from its Github repo and include information technology in the project alongside jQuery, xlsxmin.js, FileSaver.js and sweetalert.js.
@ seunmatt
Seun MattJava addict! Experienced in building FinTech products. Crafting crawly products and sharing my noesis.
Why upload the file, when all y'all need is the information in it? Cleaner, faster avert connection timeouts, corrupted data only by adding excel_uploader js
ane. Overview
Doing bulk upload of information from excel files reliably can be a nut-job at times. This is how to tame the creature with a simple JavaScript library.
Instead of beginning uploading the unabridged file to the server and then processing it, this library will save yous some bandwidth past processing the file on the local auto and uploading the extracted information (which is what you actually want) to the server in a batch.
Because it does the upload in batches, problems such as connection timeout are taken care of. In addition to this, information technology reports the data that crusade an exception on the server and make them available for download as an Excel file
two. The Problem
I need to practise a bulk upload of user records from an excel file ranging from 3000 to 16000 rows. Each excel record (a row) will exist used to create a user account in the organization. This involves trips to more than i 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 render success or mistake message — all in 1 HTTP Request!
The shortcomings of this method are:
- HTTP Request timeout, particularly with programming languages that are not multithreaded
- Unreliable fault reporting
and so on.
3. The Solution
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 endeavor…catch block and information that raise an exception is stored in an error assortment. One time, the server is done processing the current batch information technology sends an OK response to the client, and alongside, the error information — if any.
One other technique that is key to this process is Database Transaction Management. All database interactions for a detail tape is washed 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 than one way to achieve a feat, each with its own shortcomings. Still, this technique is reliable in terms of error reporting and HTTP connection management.
iv. Using The excel_uploader.js JavaScript Library
I implemented the solution higher up in a simple JavaScript file called excel_uploader.js. To use the JavaScript file in a project: we need to become the library from its Github repo and include it in the projection alongside jQuery, xlsx.min.js, FileSaver.js and optionally bootstrap 4 and sweetalert.js:
<!-- required -->
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.ii.1/jquery.min.js"></script>
<!--- optional -->
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/js/bootstrap.min.js"></script>
<script src="https://unpkg.com/sweetalert/dist/sweetalert.min.js"></script>
<!-- needed by older browsers -->
<script src="https://github.com/eligrey/Blob.js"></script>
<!-- Required -->
<script src="https://rawgit.com/eligrey/FileSaver.js/src/FileSaver.js"></script>
<script src="https://unpkg.com/xlsx/dist/xlsx.full.min.js"></script>
<script src="https://cdn.rawgit.com/SeunMatt/excel_uploader/4f4ebd93/src/excel_uploader.js"></script>
Notation that Blob.js is required on older browsers for FileSaver.js to work.
Side by side, we'll add together this to the HTML folio we desire the excel upload to accept place:
<script>
$(certificate).ready( function () {
new ExcelUploader({
maxInAGroup: yard,
serverColumnNames: ["Proper name", "Email", "Phone Number"],
importTypeSelector: "#dataType",
fileChooserSelector: "#fileUploader",
outputSelector: "#tableOutput",
extraData: {_token: "23333323323223323232"}
});
});
</script>
From the snippet above, we instantiate the ExcelUploader object with configuration parameters. Following is an explanation of each parameter and what it does.
maxInAGroup: This parameter controls how many records to be sent to the server per time. The default is 1000 so if there are 5000 records in the source excel file, the information will exist uploaded to the server in 5 batches. Information technology can exist fine-tuned to our taste base of operations on bachelor resources.
serverColumnNames: This is an array of the columns the server is expecting. Every time excel_uploader pushes data to the server, it also sends a column_map.
The cavalcade map is generated past using the lowercase course of the elements in serverColumnNames as the central and the index of the corresponding excel column as value.
For instance, if the server is expecting the uploaded excel data to contain 3 columns — Proper name, Electronic mail and Phone Number.
The user will commencement upload an excel file, then map which column represents what data in the excel. Then excel_uploader volition compose the server column names using the lowercase of the serverColumnNames, replacing white spaces with an underscore (_) character.
And then the server at the end will receive a JSON payload similar this:
{
"column_map":{"name":0,"email":2,"phone_number":1},
"data":[
["name1", "08010xxx", "[electronic mail protected]"],
["name2", "08020xxx", "[email protected]"],
["name3", "08030xxx", "[electronic mail protected]"],
...
]
} Which tin can be interpreted as the cavalcade name is at index nix (0) of the data, email is at alphabetize two (two) and phone_number is at index ane.
importTypeSelector: This is the jQuery selector for the HTML select field. The value of the Select options is the actual URL to transport the excel data to if that choice is selected. This manner it's possible to apply the same page for multiple uploads. Just add the destination URL as an option in the Import Type Selector.
fileChooserSelector: This is the jQuery selector for the HTML file input field. For actually choosing the excel file from local storage by the user.
outputSelector: This is the jQuery selector for the HTML div that is used for displaying progress, status and error messages.
extraData: This is the data to exist sent to the server alongside the uploaded data and cavalcade mapping. For example, CSRF token. It is an optional parameter.
Now that we accept looked at the options, we must have noticed we need to add some markups to our web folio for this to work.
Hence the need for the following HTML:
<!--This is the import type selector-->
<div form="form-group">
<select form="grade-command" id="dataType">
<choice value="-1" disabled selected>Select Data to Import</option>
<option selected value="http://localhost/excel_uploader/demo/php/demo.php">Import Posts Information</option>
</select>
</div>
<!-- This is the file chooser input field-->
<div grade="class-grouping">
<input type="file" id="fileUploader" class="btn btn-fill btn-primary btn-big" />
</div>
<!-- This is the Blank output/progress div-->
<div id="tableOutput"></div>
The complete HTML can exist institute on the demo.html page.
Note: The corresponding Ids of the HTML elements were used to configure the ExcelUploader object in the JavaScript snippet above. That'south all we need to do on the front-end. Simple isn't it?
five. A Little Bit of Server-Side Processing
excel_uploader.js library is server-side agnostic! Information technology will work with almost any server-side engineering science. It merely needs to know the URL to push button data via the importTypeSelector and that'southward all.
Information technology volition button data to the server in a Postal service asking using jQuery ajax. The Post request payload will contain two compulsory entries: data and column_map.
data: This an array of an array — representing the data from the excel file uploaded by the user. Information technology is in JSON format and should be decoded to a cardinal-value data structure such as an associative array in PHP or a Map in Java or its equivalent in other server-side programming languages.
The outer array represents the whole while the inner assortment represents the information on each row of the excel file. Hence the number of the length of data[0] will is the number of columns mapped during upload while the length of information array itself is the full number of rows/records available in the uploaded file.
This is an example data received in a post request:
{
"column_map":{"proper name":0,"email":2,"phone_number":1},
"data":[
["name1", "08010xxx", "[e-mail protected]"],
["name2", "08020xxx", "[email protected]"],
["name3", "08030xxx", "[email protected]"],
...
]
} 5.1. Accessing the Information
We understood, from the previous department, that the column_map acquaintance column names to their corresponding index in the arrays independent in data.
We can, therefore, admission the data past parsing the JSON payload to a key-value data construction like an associative array (PHP) or a Map (Java).
In PHP CodeIgniter, we can do this:
$columnMap = json_decode($this->input->post("column_map"), truthful); //to get the name of the outset row
$proper noun = $data[0][$columnMap["proper noun"]];
//to go the email of the first row
$e-mail = $information[0][$columnMap["email"]];
//to get the phone_number in first row
$phoneNumber = $data[0][$columnMap["phone_number"]];
//we can definitely utilize a loop to process all the data
If yous noticed, from the snippet to a higher place, we did non have to memorize the corresponding index of each column in the data array. We simply use the column_map which is much easier.
The same thing is applicable if the server-side is using Java programming language in a framework similar Leap Boot:
//extract the index of the columns we're expecting from the map
int nameIndex = Integer.parseInt(columnMap.get("name").toString());
int emailIndex = Integer.parseInt(columnMap.get("email").toString());
int phoneIndex = Integer.parseInt(columnMap.get("phone_number").toString());
//save a user
User user = new User();
user.setName(data.get(nameIndex));
user.setEmail(information.go(emailIndex));
user.setPhone(data.get(phoneIndex));
user.setPassword("newHashedPassword");
User savedUser = userRepository.relieve(user);
The complete demo code for PHP and Java can be institute here.
five.2. Processing the Information
One of the best means to handle the incoming post information is to have ii methods (or functions).
One in which the POST payload will be decoded and the information assortment will be looped over permit'southward call it uploadUserData().
In this function, at that place will be an $errorArray variable which itself must be an array of assortment. The data assortment, decoded from the Mail service payload, will be looped over and each element of that data (corresponding to a row on the excel file) volition be processed past another method doUserUpload().
The method doUserUpload volition perform database interactions in a endeavor … catch block wrapped in a database transaction. If any of the database interaction fails, it will rollback the database changes and render false otherwise it returns true.
If doUserUpload returns false, the particular record/row under processing will be added to $errorArray
Instance implementation of uploadUserData() in PHP:
//this is where the information will be handled and process
foreach ($data equally $datum) {
if(!empty($datum) && !$this->doUserUpload($datum, $columnMap)) {
//that means at that place was an error processing the file
//and then we will just add together this $datum to the errorArray
array_push($errorArray, $datum);
}
}
//after all the data has been processed, let's respond to the customer
if(!empty($errorArray)) {
//at that place was an mistake somewhere. Let's ship the client the afflicted information
$this->respond(json_encode(["data" => $errorArray]));
} else{
$this->respond(json_encode(["success" => "OK"]));
}
The complete example server-side processing can be found in demo.php . The respective Java demo can besides exist found here .
5.iii. Server Response
The key step to this menses is the server response. The server is expected to respond in JSON and the content-type should be awarding/json.
If any of the uploaded information result in an error while processing, and it'due south added to the errorArray, the resulting error Assortment should exist sent to the excel_uploader.js, otherwise, a success bulletin should exist sent:
if(!empty($errorArray)) {
//in that location was an error somewhere. Let's transport the user some data that are affected.
$this->answer(json_encode(["information" => $errorArray]));
} else{
$this->respond(json_encode(["success" => "OK"]));
} Or in Coffee:
if(errors.isEmpty()) {
//all is well during the procedure
responseMap.put("success", "OK");
render ResponseEntity.ok(responseMap);
} //at that place are errors and some information are not processed.
// Let'south send those data back to the client
//note that we're sending the OK response 200
//it'due south just that we're appending some error information that might have been nowadays
responseMap.put("data", errors);
return ResponseEntity.ok(mapper.writeValueAsString(responseMap));
If we noticed an invalid information is existence sent and we'll like to notify the user, we can transport a JSON response with an mistake entry and a 200 condition code — because we actually handled the error ourselves:
if(count($information[0]) < 3 || count($columnMap) < iii) {
$this->respond(json_encode([
"mistake" => "The Information Seems non to be complete! " . count($columnMap) . " columns were mapped instead of 3"]), 200);
} Note: the JSON response should take a STATUS code of 200 for excel_uploader.js to exist able to process it.
If at that place were whatsoever other server-generated errors, they volition be handled in the fails() callback of the ajax method.
vi. The Soup is Set up!
With but a little scrap of JavaScript, a sprinkle of config and HTML, we are good to become. The good thing is that we can use the same page to upload information to many endpoints fifty-fifty with an boosted payload. Just add together the URL every bit another option in the importTypeSelector and nosotros are skillful.
I want to hear about your experiences integrating this uncomplicated tool into your project. If yous discover whatsoever bug, kindly study it to Github.
Github: https://github.com/SeunMatt/excel_uploader
Tags
Related Stories
Source: https://hackernoon.com/process-large-data-from-excel-file-with-a-breeze-3db39e189c82
0 Response to "A File Conversion Is in Process Please Try Again"
Post a Comment