csvを含むzipをjszip+papaparse for javascriptで読み sql.js で sql select - end0tknr's kipple

sql.js によるブラウザでの sqlite3 操作 - end0tknr's kipple - web写経開発

先日の上記entryでは sql.js で sqlite3のバイナリファイルを扱いましたが、今回は、csvを含むzipをjszip+papaparse for javascriptで読み、sql.js で sql select

<!DOCTYPE html>
<html lang="ja">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<script src="https://cdnjs.cloudflare.com/ajax/libs/sql.js/1.10.2/sql-wasm.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.10.1/jszip.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/PapaParse/5.4.1/papaparse.min.js"></script>
<script src="./csv_db.js"></script>
</head>
<body>
  <button onclick="csv_db.load_csv_zip()">CSV-ZIP選択</button>
  <button onclick="csv_db.select_tbl()">SQL SELECT * FROM $TABLE</button>
  <div id="sql_result"></div>
  
  <script>let csv_db = new CsvDb();</script>
</body>
</html>

'use strict';

class CsvDb {
    constructor() {}
    
    async load_csv_zip() {
        // windowsのコモン・ダイアログ?で zipを開く
        let zip_contents = await this.open_file_picker()
        // jpzipで zipを解凍
        let zip = new JSZip()
        await zip.loadAsync( zip_contents.arrayBuffer() )

        const SQL = await initSqlJs({
            locateFile:file=>
            `https://cdnjs.cloudflare.com/ajax/libs/sql.js/1.10.2/${file}`
        })
        this.db = new SQL.Database()
        this.table_names = []
        
        let csv_datas = []
        // zip に含まれる csv or tsv毎に処理
        for (const filename in zip.files) {
            const zip_content = zip.files[filename]
            if( zip_content.dir ) continue
            
            let parsed_filename = this.parse_csv_filename( filename )
            let tbl_name = parsed_filename[0]
            let csv_rows_str = await zip_content.async('text')
            // papa parseで csv or tsvをパース
            // https://www.papaparse.com/docs
            let tbl_rows   = Papa.parse(csv_rows_str.trim(),{delimiter:"\t"}).data
            let tbl_header = tbl_rows.shift()

            if(tbl_name=="planed_zumens") continue
            if(tbl_rows.length==0) continue
            // sql.jsに対し csv or tsv毎に create table, insert
            let sql_create =
                `CREATE TABLE  ${tbl_name}(${tbl_header.map(h=>`${h} TEXT`).join(',')});`
            let sql_insert =
                `INSERT INTO ${tbl_name}(${tbl_header.join(',')})
VALUES ${tbl_rows.map(r=>`(${r.map(r=>`'${r}'`).join(',')})`).join(',')};`
            this.db.run(sql_create).run(sql_insert)
            this.table_names.push(tbl_name)
        }

        document.querySelector("#sql_result").innerHTML =
            "読み込み完了. " + this.table_names.join(" ")

    }

    parse_csv_filename( org_filename ){
        let re_patern = /([^\./]+)\.(csv|tsv)$/i
        let re_result = re_patern.exec(org_filename)
        if(! re_result) return []

        let basename = re_result[1]
        let ext      = re_result[2]
        return [basename, ext]
    }
    
    // https://qiita.com/lumis/items/dd209d52c9bc7ce39db6
    async open_file_picker() {
        const pick_opts = {
            types      : [{description: "CSVを含むZIP",
                           accept:{'application/zip': ['.zip']} } ],
            multiple   : false,
            excludeAcceptAllOption: true }
        //「excludeAcceptAllOption: true 」は
        //「すべてのファイル (*.*)」の選択を無効にし
        // types に指定したファイルのみ選択okにする
        
        const [fh] = await showOpenFilePicker(pick_opts)
        const file = await fh.getFile()
        return file
    }

    select_tbl(){
        let tbl_name = this.table_names[1]
        let sql = `
SELECT * from ${tbl_name}
limit 10`;
        const stmt = this.db.prepare(sql);
        stmt.bind();
        let sql_result = "";
        while( stmt.step() ) {
            let row = stmt.getAsObject();
            sql_result += JSON.stringify(row);
        }
        document.querySelector("#sql_result").innerHTML = sql_result;
    }
}