Win10 + node.js から SQL Server 2019 Express に CSVファイルを書き込んでみる 修正版(tedious-connection-pool)

Win10 + node.js から SQL Server 2019 Express に CSVファイルを書き込んでみる 修正版

目的

以下の環境で郵便番号検索の17ISHIKA.CSVを書き込んでみる
Win10 + node.js から SQL Server 2019 Express に CSVファイルを書き込んでみるからの変更点
・tediousjs/tedious-connection-poolに修正する
・Csv Parse - Sync APIに修正する
・Tedious Requestで Event: done は INSERT では用途が違う？ので削除する
あとは同じ
Win10 + node.js -> Ubuntu 18.04 + SQL Server 2019 Express

対象テーブル

データの並びは以下に準拠
郵便番号データの説明
対象データにユニークキーになりそうなデータが無いので
8桁のテキストを追加する

CREATE TABLE [dbo].[ZIPCODE](
    [SEQ] [nchar](8) NOT NULL,
    [PREFCODE] [nchar](3) NULL,
    [KUBUNCODE] [nchar](8) NULL,
    [POSTAL5] [nchar](5) NULL,
    [POSTAL] [nchar](8) NULL,
    [PREFKANA] [nchar](20) NULL,
    [CITYKANA] [nchar](40) NULL,
    [ADDRKANA] [nchar](80) NULL,
    [PREFKANJI] [nchar](20) NULL,
    [CITYKANJI] [nchar](40) NULL,
    [ADDRKANJI] [nchar](80) NULL,
    [FLG1] [int] NULL,
    [FLG2] [int] NULL,
    [FLG3] [int] NULL,
    [FLG4] [int] NULL,
    [FLG5] [int] NULL,
    [FLG6] [int] NULL
) ON [PRIMARY]

サンプルコード

地元県だと名前感あるので 17ISHIKA.CSV を使う

var async = require('async');
var ConnectionPool = require('tedious-connection-pool');
var Request = require('tedious').Request;
var TYPES = require('tedious').TYPES;
var fs = require('fs');
var iconv = require('iconv-lite');
var parse = require('csv-parse/lib/sync')

var strsql = "INSERT INTO ZIPCODE (";
strsql = strsql + "  SEQ";
strsql = strsql + ", PREFCODE";
strsql = strsql + ", KUBUNCODE";
strsql = strsql + ", POSTAL5"
strsql = strsql + ", POSTAL"
strsql = strsql + ", PREFKANA"
strsql = strsql + ", CITYKANA"
strsql = strsql + ", ADDRKANA"
strsql = strsql + ", PREFKANJI"
strsql = strsql + ", CITYKANJI"
strsql = strsql + ", ADDRKANJI"
strsql = strsql + ", FLG1"
strsql = strsql + ", FLG2"
strsql = strsql + ", FLG3"
strsql = strsql + ", FLG4"
strsql = strsql + ", FLG5"
strsql = strsql + ", FLG6"
strsql = strsql + ") VALUES (";
strsql = strsql + "  @SEQ";
strsql = strsql + ", @PREFCODE";
strsql = strsql + ", @KUBUNCODE";
strsql = strsql + ", @POSTAL5"
strsql = strsql + ", @POSTAL"
strsql = strsql + ", @PREFKANA"
strsql = strsql + ", @CITYKANA"
strsql = strsql + ", @ADDRKANA"
strsql = strsql + ", @PREFKANJI"
strsql = strsql + ", @CITYKANJI"
strsql = strsql + ", @ADDRKANJI"
strsql = strsql + ", @FLG1"
strsql = strsql + ", @FLG2"
strsql = strsql + ", @FLG3"
strsql = strsql + ", @FLG4"
strsql = strsql + ", @FLG5"
strsql = strsql + ", @FLG6"
strsql = strsql + ");";

var connectionConfig = {
    userName: 'demo',
    password: 'demo',
    server: '192.168.5.49',
    options: {
        database: 'demo'
    }
};

var poolConfig = {
    min: 5,
    max: 10,
    log: false
}
var pool = new ConnectionPool(poolConfig, connectionConfig);

pool.on('error', function(err) {
    console.error(err);
    console.log('err:pool.on');
    pool.drain();
    process.exit(0);
});

var startTime = new Date();

iconv.skipDecodeWarning = true;

pool.acquire(function (err, connection) {
    if (err) {
        console.error(err);
        return;
    }
    request = new Request("TRUNCATE TABLE ZIPCODE;", function(err) {
        if (err) {
            console.log(err);
            console.log('err:request:trunc');
            pool.drain();
            process.exit(0);
        } else {
            connection.release();
        }
    });
    connection.execSql(request);
});

var numcnt = 0;
async.forEachSeries( fs.readFileSync('17ISHIKA.CSV', 'binary').toString().split('\n'), function (line, callback) {

    line = iconv.decode(line, 'Shift_JIS');     //iconv-liteでShift-jisからutf8に変換
    if (line.length <= 0) {
        console.log('END:length <= 0');
        pool.drain();
        process.exit(0);
    }
    line = parse(line);                         // Csv Parse - Sync API

    pool.acquire(function (err, connection) {
        if (err) {
            console.error(err);
            return;
        }
        numcnt = numcnt + 1;
        request = new Request(strsql, function(err) {
            if (err) {
                console.log(err);
                console.log('err:request');
                connection.exit;
                process.exit(0);
            } else {
                connection.release();
                callback();
            }
        });

        request.addParameter('SEQ',       TYPES.NVarChar, ('00000000' + numcnt).slice(-8));
        request.addParameter('PREFCODE',  TYPES.NVarChar, line[0][0].substr(0, 2));
        request.addParameter('KUBUNCODE', TYPES.NVarChar, line[0][0].trim());
        request.addParameter('POSTAL5',   TYPES.NVarChar, line[0][1].trim());
        request.addParameter('POSTAL',    TYPES.NVarChar, line[0][2].trim());
        request.addParameter('PREFKANA',  TYPES.NVarChar, line[0][3].trim());
        request.addParameter('CITYKANA',  TYPES.NVarChar, line[0][4].trim());
        request.addParameter('ADDRKANA',  TYPES.NVarChar, line[0][5].trim());
        request.addParameter('PREFKANJI', TYPES.NVarChar, line[0][6].trim());
        request.addParameter('CITYKANJI', TYPES.NVarChar, line[0][7].trim());
        request.addParameter('ADDRKANJI', TYPES.NVarChar, line[0][8].trim());
        request.addParameter('FLG1',      TYPES.NVarChar, parseInt(line[0][9]));
        request.addParameter('FLG2',      TYPES.NVarChar, parseInt(line[0][10]));
        request.addParameter('FLG3',      TYPES.NVarChar, parseInt(line[0][11]));
        request.addParameter('FLG4',      TYPES.NVarChar, parseInt(line[0][12]));
        request.addParameter('FLG5',      TYPES.NVarChar, parseInt(line[0][13]));
        request.addParameter('FLG6',      TYPES.NVarChar, parseInt(line[0][14]));
        connection.execSql(request);
    });

}, function(err) {
    console.log('fin');
    pool.drain();
    process.exit(0);
});

参考にしたのは以下のサイト

How to use object in Asyn forEachSeries
async.js forEach vs forEachSeries
CSV Parser for Node.js
csv
tediousjs/tedious-connection-pool
tedious-connection-pool
手順 1:Node.js 開発用に開発環境を構成する
手順 3:Node.js を使用した SQL への接続を概念実証する