Ok... let's start to tell the story in short.
I am a banker and I am to spend a lot of time to enter various of kinds of data in our banking software. Sometimes we require to input a lot more data (e.g. salary of our staff, salary of the corporate customer who has more than 1000 employees) from a excel file one by one in hand which is really tedious and time consuming.
In our banking software there is an option to import file from the excel file but most of the time it fails to do what it is meant to do and it gives some weird message that can understand only our in house developer- I guess. So we fall in a bizarre situation when it fails and are to input all those data by hand.
So that's the the short story.
After falling in that situation I began to search a way so that I can input data after loading the page in web browser (out software is web based). As I am not the developer so I need to find a different route and I have no access to the server.
Therefore I assume that I need to have some addons in my browser which will run my code (obviously JavaScript) after loading the page. Fortunately, I have found that addons namely- Greasemonkey and it's closely cousin/variants- Violentmonkey & Tampermonkey. Anyone of those addons will do the work and it can be found in addon manager of the browser. I have installed Greasemonkey in Firefox and Tampermonkey in Google Chrome. You may choose anyone of this and the matter will not vary.
Then I thought I need a CSV parser which will read my CSV file(converted from excel file) to input the data in the software. After searching some Javascript library I have decided to use https://www.papaparse.com/ which is very easy to use.
Here I will describe the process that I have followed to solve the problem.
1. Install the greasemonkey in your browser
2. Click on the icon of greasemonkey in the toolbar of the browser.
3. Click on Create New Script.
4. Copy the following line to the editor
-----------------------------------------------------------------------------------
// ==UserScript==
// @name import csv file
// @version 1
// @grant none
// @require https://unpkg.com/papaparse@latest/papaparse.min.js
// @include *test.html
// ==/UserScript==
var input=document.createElement("input");
input.type="file";
input.id = "userfile";
document.body.appendChild(input);
var par = document.createElement("p"); //creating the paragraph element
var text = document.createTextNode("File Format(csv)-Field-Account, Name, Amount-with row heading"); //creating the text node
document.body.appendChild(par.appendChild(text)); //appending the text to
var input1= document.getElementById('userfile');
input1.addEventListener('change', function() {
Papa.parse(input.files[0], {
header: true,
complete: function(results) {
//console.log("Finished:", results.data);
var accTypes = document.getElementsByName('accType[]');
var accounts=document.getElementsByName('accNo[]');
var byNos = document.getElementsByName('byNo[]');
var names=document.getElementsByName('empName[]');
var salaries=document.getElementsByName('salary[]');
var i = 0;
//Finding the header name of the fields in CSV File (Include Field name of the data in CSV File)
var header_1 = results.meta['fields'][0];
var header_2 = results.meta['fields'][1];
var header_3 = results.meta['fields'][2];
results.data.forEach(function(row){
if(i < accTypes.length) {
var cur_acc = row[header_1];
accTypes[i].value = cur_acc.substr(7,2);
accounts[i].value = cur_acc.substr(9,6);
byNos[i].value = cur_acc.substr(15,2);
names[i].value = row[header_2];
salaries[i].value = row[header_3];
i = i + 1;
}
});
}
});
})
-----------------------------------------------------------------------------------
The first 7 lines are very important to Greasemonkey. This section starts with // ==UserScript== Tag and ends with // ==/UserScript==. These lines are marked as comments to Javascript but important configurations to Greasemonkey. Here all the instructions begin with @ sign. @name is name of the script, @version is the version number, @require is to include external file(e.g. papaparse.min.js), @include is for instructing the script to the page where it can execute (here this script will run on test.html page). It can be given to any webpage by regular expression format such as http://mysite*, *mypage.html etc.
After the above works we need to create a web page for testing called test.html.
File. test.html
--------------------------------------------
In the above I have created control for 50 records.
Now create a Excel File with Three Fields( Account No- 30501231001187609, Name- A. Kalam, Salary- 5000) and at least 50 rows with sample data.
Now Export it as CSV File from the Save As Menu of Excel Software and open the test.html file. Select the file by file browser icon and select it.
Hope this may help you to automate task at your office.
Now create a Excel File with Three Fields( Account No- 30501231001187609, Name- A. Kalam, Salary- 5000) and at least 50 rows with sample data.
Now Export it as CSV File from the Save As Menu of Excel Software and open the test.html file. Select the file by file browser icon and select it.
Hope this may help you to automate task at your office.
