CSV import & export – Node.js, mySQL #2

CSV import/export

次はテーブルへのcsvファイルのインポートと
テーブルからcsvファイルへのアップロードを行う機能を作成します。

まずexpressappフォルダの中に
uploadedフォルダとtableuploadフォルダを作成します。

今回はSubmitボタンを押すと
・uploadedフォルダにcsvファイルが保存される。
・そのcsvファイルを読み込んでテーブルにインポートする。
・テーブルデータがtableuploadフォルダにタイムスタンプ付きでエクスポートされる。
という流れが一度で起こるようにします。

uploadを補助するmoduleとしてmulterをインストールします。

npm install multer

node.jsとmySQLを接続するためにmysqlモジュールをインストールします。

npm install mysql

csvuploader.jsを以下のように編集します。

[csvuploader.js]

var express = require('express');
var multer = require('multer');
var app = express();
const mysql = require('mysql');

const connection = mysql.createConnection({
host : ~hostname~
user : ~username~,
password : ~password~,
database: ~databasename~,
});

//multerでfileの検索
var storage = multer.diskStorage({
destination: function (req, file, cb) {
cb(null, '~path~/uploaded')
},
filename: function (req, file, cb) {
cb(null, file.originalname)
}
});
var upload = multer({ storage: storage });

//fileのupload
app.post('/upload', upload.single('upName'), function(req, res){
console.log(req.file.originalname)
console.log(req.file.path)

connection.query("~insert MySQL code~;",
function(err, results) {
if(err){
console.log(err);
}
console.log("upload and csv load OK!")
//console.log(results);
}); //query

//テーブルをcsvファイルでアウトプット
connection.query("~insert MySQL code~;",
function(err, results) {
if(err){
console.log(err);
}
console.log("outputOK!")
}); //query
res.redirect(302, "http://localhost:8080");

app.use('/', express.static('.'));
app.listen(8080);

ターミナルで

node csvuploader.js

を実行し http://locaohost:8080
にアクセスし実際にuploadしてみます。
テーブルとtableuploadにそれぞれデータが追加されていることが確認できるはずです。

次にアウトプットされるファイルの名前を(originalname)_(日付).csvとなるようにします。

これは日付を取得するモジュールとsplitを使うと作成できます。
ファイルに

require('date-utils');

を追加します。

csvuploader.jsの
//テーブルをcsvファイルでアウトプット

の下の部分に

var dt = new Date();
var formatted = dt.toFormat("YYYY_MMDD_HH_MI_SS");
var dotsplit=[];
var dotsplit = req.file.originalname.split(".");

を追加します。

その後dotsplitとformattedを用いてMySQLのcodeを書き換えます。

MySQL workbench内から

SELECT @@secure_file_priv;

を入力してみましょう。
valueが /var/lib/mysql-files
のみの場合、直接tableuploadフォルダにロードすることができないため
保存先をmysql-filesに指定しそこからtableuploadフォルダにコピーする必要があります。

コピーに必要なモジュールをインストールします。
fs-extra をインストール

npm install fs-extra --save

jsのコードに

var fs = require('fs-extra')を追加します。

res.redirect~の下に

fs.copySync('/var/lib/mysql-files/', '~path~/tableupload/');
})

を追加します。
これでtableupload内にファイルを作成することができるようになりました。

ブラウザ上に表示

次にtableuploadにあるファイルをブラウザに並べ、ダウンロードすることができるようにします。

仕組みはnode.jsでlocalhost/filelistを作りそこにtableuploadフォルダ内のファイル名を取得します。
tableupload内のそれぞれのファイル名を文字的に切り取ることてパスとして使ったりindex.htmlのブラウザに表示したり
することができます。

nodeのコードに以下を追加します。execでシェルのコマンドを実行してfilelistページに
表示することができます。
[csvuploader.js]

app.get('/filelist',function(req,res){
const exec = require('child_process').exec;

exec('ls ~path~/tableupload/', (err, stdout, stderr) => {
if (err) { console.log(err); }
var stdoutajax = '
<p id="ajaxtext">' + stdout +"</p>
" ;
res.send(stdoutajax);
});
});

保存したらhttp://locaohost:8080
にアクセスし、tableupload内のファイル名が取得されているか確認します。

次に新しくJQueryを用いたjavascriptのファイルを作成します。
htmlget.jsという名前のファイルを作り以下のコードを書きます。
まずはfilelistの文字を取得するためにAJaXを使用します。
文字列はsplitで判別してfor文で回してsparrayに入れます。

[htmlget.js]

$(function() {
$.ajax({

url : "http://localhost:8080/filelist",
type : "GET",

}).done(function(res) {
$("#getspan").html(res);
var sparray=[];
var sparray = $("#ajaxtext").text().split("\n");
for (var i = 0; i < sparray.length - 1; i++) {
$("#outputelement").append("
<div><input class="download" name="downloadcsv" type="submit" value="download!" />" + sparray[i] +"</div>
" );
}

}
})

次にクリックしたらダウンロードできるようにします。
csvuploader.jsに以下のコードを追加します。

[csvuploader.js] 最上段に追加

var url = require('url');

下に追加
app.get('/csvoutput',function(req,res){

var url_parts = url.parse(req.url,true);
var output = url_parts.query.output;

var fileURL = '~path~' + output;
var fileName = output;

res.download(fileURL, fileName, function(err){
if (err) {
console.log('error.');
} else {
console.log('ok' );
console.log("filePath:" + fileURL);
console.log("fileName:" + fileName);
}
});//download
}); //get

またhtmlget.js側にも以下を追加します。
[htmlget.js]

$(".download").click(function(){
var params = getParameter();
params['name'] = $(this).next().text();
location.href = "http://localhost:8080/csvoutput?output=" + params['name']

function setParameter( paramsArray ) {
var resurl = location.href.replace(/\?.*$/,"");
for ( key in paramsArray ) {
resurl += (resurl.indexOf('?') == -1) ? '?':'&';
resurl += key + '=' + paramsArray[key];
}
return resurl;
}

//パラメータを取得する
function getParameter(){
var paramsArray = [];
var url = location.href;
parameters = url.split("#");
if( parameters.length > 1 ) {
url = parameters[0];
}
parameters = url.split("?");
if( parameters.length > 1 ) {
var params = parameters[1].split("&");
for ( i = 0; i < params.length; i++ ) {
var paramItem = params[i].split("=");
paramsArray[paramItem[0]] = paramItem[1];
}
}
return paramsArray;
};

});

htmlget.jsで押されたボタンのファイル名を取得し、node側のcsvoutputにクエリ文字列として送ることができます。
このクエリ文字列を取得してパスに利用することでファイルを特定し、ダウンロードすることができます。

以上で、行うと目的の要件に沿った画面が完成しました。
index.html にアクセスしファイルのアップロードからダウンロードまで
一連の動作を確認してみてください。