I'm currently tasked with writing an app that transfer data from Excel Worksheet to SQL Server table using NodeJS with xlsx package from npm.
First of all, I'm very new to the concept of asynchronous programming and I'm also new at using NodeJS. If you could explain it thoroughly, it would be very helpful.
The problem: My Excel Sheet has 8 column headers, I'm supposed to take data from row 2 to the end (something like A2 to H2, A3 to H3, and so on until the end of the row).
I call node service in my git-bash. It's going to readFile the excel file, loop through it per row and console.log() it like this:
Data of index 2000 7/10/16 20161007142953_BP12350 COV_DEB_20161007142953_BP12350 0 50000 3896 51 A 0145 Takengon / 3986 UNIT SIMPANG BALIK TAKENGON
Data of index 2001 7/10/16 20161007142954_BP12350 FEE_DEB_20161007142953_BP12350 0 50000 3896 51 A 0145 Takengon / 3986 UNIT SIMPANG BALIK TAKENGON
, then I queried it using INSERT INTO as seen below in runner.js. What I want to do is to read 1000 rows per timeInterval of 15000ms (because there are about 40,000 rows of data per excel file).
Sometimes I get error of Connection Timeout or UnhandledPromiseRejectionWarning. How do I deal with this?
This is my service.js
var util = require('util');
var sql = require('mssql');
var db = require('./doSql');
var runner = require('./runner');
var timeInterval = 15000;
var readData = setInterval(runner.readDataXL, timeInterval);
This is my runner.js
var util = require('util');
var fs = require('fs');
var sql = require('mssql');
var db = require('./doSql');
var settings = require('./settings')
var async = require('async');
if (typeof require !== 'undefined') XLSX = require('xlsx');
var add = 50;
var runner = {
readDataXL : function () {
var workbook = XLSX.readFile(__dirname + '/file/test2.xlsx');
// var sheetname = workbook.Props.DocParts[0];
var sheetname = workbook.Props.SheetNames[0];
var length = (workbook.Sheets[sheetname]['!ref']).substr(4, workbook.Sheets[sheetname]['!ref'].length);
var effdate, kodebatch, transdesc, debit, kredit, user, id, branch = "";
// console.log(workbook);
var index = 1;
var indexj = 1;
async.each(workbook.Sheets[sheetname], function(item, callback) {
var dataAwal = item;
if(index > 9) {
switch(indexj) {
case 1 : effdate = dataAwal['w'];indexj++;break;
case 2 : kodebatch = dataAwal['w'];indexj++;break;
case 3 : transdesc = dataAwal['w'];indexj++;break;
case 4 : debit = dataAwal['w'];indexj++;break;
case 5 : kredit = dataAwal['w'];indexj++;break;
case 6 : user = dataAwal['w'];indexj++;break;
case 7 : id = dataAwal['w'];indexj++;break;
case 8 : branch = dataAwal['w'];indexj++;
var all = util.format("Data of index " + parseInt(index/8) + " " + effdate + " " + kodebatch + " " + transdesc + " " + debit + " " + kredit + " " + user + " " + id + " " + branch );
console.log(all);
var sqlgiropremi = "INSERT INTO [ownerList].dbo.Rekonsiliasi_Premi_Fahmi_Test values('" + effdate + "','" + kodebatch + "','" + transdesc + "','" + debit + "','" + kredit + "','" + user + "','" + id + "','" + branch + "','" + parseInt(index/8) + "')";
db.executeSql(sqlgiropremi, function (data, err) {
if(err) {
console.log("sql : " + sqlgiropremi + "error : " + err)
} else {
console.log("success ");
}
});
indexj = 1;
break;
}
}
index++;
});
}
}
module.exports = runner;
This is my doSql.js
var sqlDB = require('mssql');
var util = require('util');
var settings = require('./settings');
module.exports.executeSql = function(sql, callback) {
var conn = new sqlDB.ConnectionPool(settings.dbConfig);
conn.connect()
.then(function() {
var transaction = new sqlDB.Transaction(conn);
transaction.begin()
.then(function() {
var rolledBack = false;
transaction.on('rollback', function(aborted) {
rolledBack = true;
});
var request = new sqlDB.Request(transaction);
request.query(sql)
.then(function(recordset) {
transaction.commit()
.then(function(err) {
callback(recordset);
})
.catch(function(err) {
console.log("transaction error : ", err);
});
})
.catch(function(err) {
if(!rolledBack) {
transaction.rollback()
.then(function(err_) {
console.log(err);
callback(null, err);
})
.catch(function(err_) {
console.log("request error : ", err);
callback(null, err);
})
}
})
})
.catch(function(err) {
console.log("begin error : ", err);
callback(null, err);
});
})
.catch(function(err) {
console.log("connect error : ", err);
callback(null, err);
})
sqlDB.close();
}
Sometimes I get success entries and sometimes I get errors like these:
sql : INSERT INTO [ownerList].dbo.Rekonsiliasi_Premi_Fahmi_Test values('7/10/16','20161007142953_BP12350','COV_DEB_20161007142953_BP12350','0','50000','3896','51','A 0145 Takengon / 3986 UNIT
SIMPANG BALIK TAKENGON','2000')error : ConnectionError: Failed to connect to 192.168.43.179:1433 - connect EADDRINUSE 192.168.43.179:1433
connect error : { ConnectionError: Failed to connect to 192.168.43.179:1433 - connect EADDRINUSE 192.168.43.179:1433
at Connection.tedious.once.err (C:\Users\DELL\Node JS\XLSX\node_modules\mssql\lib\tedious.js:216:17)
at Connection.g (events.js:291:16)
at emitOne (events.js:96:13)
at Connection.emit (events.js:188:7)
at Connection.socketError (C:\Users\DELL\Node JS\XLSX\node_modules\tedious\lib\connection.js:699:14)
at C:\Users\DELL\Node JS\XLSX\node_modules\tedious\lib\connection.js:590:25
at Socket.onError (C:\Users\DELL\Node JS\XLSX\node_modules\tedious\lib\connector.js:48:9)
at emitOne (events.js:96:13)
at Socket.emit (events.js:188:7)
at emitErrorNT (net.js:1276:8)
code: 'ESOCKET',
originalError:
{ ConnectionError: Failed to connect to 192.168.43.179:1433 - connect EADDRINUSE 192.168.43.179:1433
at ConnectionError (C:\Users\DELL\Node JS\XLSX\node_modules\tedious\lib\errors.js:12:12)
at Connection.socketError (C:\Users\DELL\Node JS\XLSX\node_modules\tedious\lib\connection.js:699:30)
at C:\Users\DELL\Node JS\XLSX\node_modules\tedious\lib\connection.js:590:25
at Socket.onError (C:\Users\DELL\Node JS\XLSX\node_modules\tedious\lib\connector.js:48:9)
at emitOne (events.js:96:13)
at Socket.emit (events.js:188:7)
at emitErrorNT (net.js:1276:8)
at _combinedTickCallback (internal/process/next_tick.js:74:11)
at process._tickCallback (internal/process/next_tick.js:98:9)
message: 'Failed to connect to 192.168.43.179:1433 - connect EADDRINUSE 192.168.43.179:1433',
code: 'ESOCKET' },
name: 'ConnectionError' }
sql : INSERT INTO [ownerList].dbo.Rekonsiliasi_Premi_Fahmi_Test values('7/10/16','20161007142954_BP12350','FEE_DEB_20161007142953_BP12350','0','50000','3896','51','A 0145 Takengon / 3986 UNIT
SIMPANG BALIK TAKENGON','2001')error : ConnectionError: Failed to connect to 192.168.43.179:1433 - connect EADDRINUSE 192.168.43.179:1433
(node:3576) UnhandledPromiseRejectionWarning: Unhandled promise rejection (rejection id: 2): ConnectionError: Failed to connect to 192.168.43.179:1433 - connect EADDRINUSE 192.168.43.179:1433
(node:3576) UnhandledPromiseRejectionWarning: Unhandled promise rejection (rejection id: 4): ConnectionError: Failed to connect to 192.168.43.179:1433 - connect EADDRINUSE 192.168.43.179:1433
(node:3576) UnhandledPromiseRejectionWarning: Unhandled promise rejection (rejection id: 6): ConnectionError: Failed to connect to 192.168.43.179:1433 - connect EADDRINUSE 192.168.43.179:1433
(node:3576) UnhandledPromiseRejectionWarning: Unhandled promise rejection (rejection id: 8): ConnectionError: Failed to connect to 192.168.43.179:1433 - connect EADDRINUSE 192.168.43.179:1433
(node:3576) UnhandledPromiseRejectionWarning: Unhandled promise rejection (rejection id: 10): ConnectionError: Failed to connect to 192.168.43.179:1433 - connect EADDRINUSE 192.168.43.179:1433
If there's any better way to loop it or on how to implement it, please guide me.
via Fahmi R
No comments:
Post a Comment