HTA SQLツール

概要

今は廃れたHTA(HTML Application)で書いた、汎用SQLツールです。 Windows上で動作し、

  • CSVファイル(*.csv)
  • Excelファイル(.xls、.xlsx、.xlsm、.xlsb)
  • Accessファイル(*.mdb)

などをADODB経由で開いてSQLクエリを実行できます。

CSVファイルはディレクトリ⇔データベース、CSVファイル⇔テーブルとして扱われ、 ExcelファイルはExcelブック⇔データベース、シート⇔テーブルとして扱われます。

主に、SQLクエリを実行した結果をExcelに貼り付けて使用することを想定しており、 出力された表はExcelに貼り付けやすいようTSV形式で持っています。 select intoで直接ファイルに書き出すこともできます。

スクリーンショット

ダウンロード

Gistからダウンロード

もしくは以下のソースコードをメモ帳にコピー&ペーストし、拡張子をhtaに変更してください。

<html>
<head>
<!--
  HTA SQL Tool by 330k
  Copyright (c) 2016 330k
  This software is released under the MIT License.
  http://opensource.org/licenses/mit-license.php
-->
<title>HTA SQL Tool by 330k</title>
<HTA:APPLICATION ID="USQLEXE" APPLICATIONNAME="USQLEXE" BORDER="thick" BORDERSTYLE="normal"
CAPTION="yes" ICON="C:\WINDOWS\system32\rsnotify.exe" INNERBORDER="no"
MAXIMIZEBUTTON="yes" MINIMIZEBUTTON="yes" SHOWINTASKBAR="yes" SINGLEINSTANCE="no"
SYSMENU="yes" VERSION="1.0" WINDOWSTATE="normal" SCROLL="no" SCROLLFLAT="no"
SELECTION="yes" CONTEXTMENU="yes" NAVIGABLE="yes" />
</head>
<script type="text/javascript">
var doc = document;
eval('var document = doc');

var shell = new ActiveXObject('WScript.Shell');
//var http = new ActiveXObject("Msxml2.XMLHTTP");
var fso = new ActiveXObject('Scripting.FileSystemObject');
var ado;
var result_tsv = '';

window.onunload = function(){
    closeADO();
};
window.onresize = function(){
    document.all.result.style.height = document.body.clientHeight - document.all.t1.clientHeight - document.all.status.clientHeight; }; window.onload = function(){
    window.onresize();
    try{
        document.all.sql.value = shell.RegRead("HKCU\\SQLEXEC\\SQLLOG");
//        document.all.filename.value = shell.RegRead("HKCU\\SQLEXEC\\LASTDB");
//        alert(USQLEXE.commandLine);
    }catch( e ){
//    	alert(e.message);
    }
};

function loadDatabase(){
    var filepath = document.getElementById('fileselector').value || document.getElementById('filename').value;
    var readonly = document.getElementById('readonly').value;
    var hdr = document.getElementById('hdr').value;
    document.getElementById('filename').value = filepath;
    document.getElementById('fileselector').parentNode.innerHTML = document.getElementById('fileselector').parentNode.innerHTML;
    setTimeout(function(){
        _loadDatabase(filepath, readonly, hdr);
    }, 10); // wait for unlock file
    return false;
}

function _loadDatabase( filepath, readonly, hdr ){
    closeADO();
    ado = new ActiveXObject("ADODB.Connection");
    var connect = [];

    if( filepath.match(/\.xls$/i) ){
        connect.push('Driver={Microsoft Excel Driver (*.xls)}; DBQ=' + filepath + ';HDR=' + (hdr ? 'Yes' : 'No') + ';ReadOnly=' + (readonly ? 1 : 0) + ';"');
        connect.push('Provider=Microsoft.Jet.OLEDB.4.0;Excel 8.0;DATABASE=' + filepath + ';HDR=' + (hdr ? 'Yes' : 'No') + ';ReadOnly=' + (readonly ? 1 : 0) + ';"');
        connect.push('Provider=Microsoft.ACE.OLEDB.12.0;Data Source=' + filepath + ';Extended Properties="Excel 8.0;HDR=' + (hdr ? 'Yes' : 'No') + ';ReadOnly=' + (readonly ? 1 : 0) + ';"');
        connect.push('Provider=MSDASQL.1;Extended Properties="DBQ=' + filepath + ';DefaultDir=C:\;Driver={Microsoft Excel Driver (*.xls)};DriverId=790;"');
    }else if( filepath.match(/(\.xlsx|\.xlsm|\.xlsb)$/i) ){
        connect.push('Provider=Microsoft.ACE.OLEDB.12.0; Data Source=' + filepath + '; Extended Properties="Excel 12.0;HDR=' + (hdr ? 'Yes' :'No') + ';ReadOnly=' + (readonly ? 1 : 0) + ';"');
        connect.push('Provider=Microsoft.ACE.OLEDB.12.0; Data Source=' + filepath + '; Extended Properties="Excel 12.0 Xml;HDR=' + (hdr ? 'Yes' :'No') + ';ReadOnly=' + (readonly ? 1 : 0) + ';"');
        connect.push('Provider=Microsoft.ACE.OLEDB.12.0; Data Source=' + filepath + '; Extended Properties="Excel 12.0 Macro;HDR=' + (hdr ? 'Yes' :'No') + ';ReadOnly=' + (readonly ? 1 : 0) + ';"');
        connect.push('Provider=Microsoft.ACE.OLEDB.12.0; Data Source=' + filepath + '; Extended Properties="Excel 12.0;IMEX=1;HDR=' + (hdr ? 'Yes' :'No') + ';ReadOnly=' + (readonly ? 1 : 0) + ';"');
        connect.push('Provider=Microsoft.ACE.OLEDB.12.0; Data Source=' + filepath + '; Extended Properties="Excel 14.0;HDR=' + (hdr ? 'Yes' :'No') + ';ReadOnly=' + (readonly ? 1 : 0) + ';"');
        connect.push('Provider=Microsoft.ACE.OLEDB.12.0; Data Source=' + filepath + '; Extended Properties="Excel 14.0;IMEX=1;HDR=' + (hdr ? 'Yes' :'No') + ';ReadOnly=' + (readonly ? 1 : 0) + ';"');
        connect.push('Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}; DBQ=' + filepath + ';ReadOnly=' + (readonly ? 1 : 0));
    }else if( filepath.match(/\.csv$/i) ){
        connect.push('Driver={Microsoft Text Driver (*.txt; *.csv)}; DBQ=' + fso.GetParentFolderName(filepath) + ';ReadOnly=' + (readonly ? 1 : 0) + ';FirstRowHasNames=' + (hdr ? '1' : '0') + ';MaxScanRows=8;');
        connect.push('Driver={Microsoft Text Driver (*.txt; *.csv)}; DBQ=' + fso.GetParentFolderName(filepath) + ';ReadOnly=' + (readonly ? 1 : 0) + ';FirstRowHasNames=' + (hdr ? '1' : '0') + ';');
        connect.push('Provider=Microsoft.Jet.OLEDB.4.0;TEXT;DATABASE=' + fso.GetParentFolderName(filepath) + ';ReadOnly=' + (readonly ? 1 : 0) + ';FirstRowHasNames=' + (hdr ? '1' : '0') + ';');
    }else if( filepath.match(/(\.txt|\.tsv)$/i) ){
        connect.push('Driver={Microsoft Text Driver (*.txt; *.csv)}; DBQ=' + fso.GetParentFolderName(filepath) + ';ReadOnly=' + (readonly ? 1 : 0) + ';FirstRowHasNames=' + (hdr ? '1' : '0') + ';Format=TabDelimited' + ';MaxScanRows=8;');
        connect.push('Driver={Microsoft Text Driver (*.txt; *.csv)}; DBQ=' + fso.GetParentFolderName(filepath) + ';ReadOnly=' + (readonly ? 1 : 0) + ';FirstRowHasNames=' + (hdr ? '1' : '0') + ';Format=TabDelimited' + ';');
        connect.push('Provider=Microsoft.Jet.OLEDB.4.0;TEXT;DATABASE=' + fso.GetParentFolderName(filepath) + ';ReadOnly=' + (readonly ? 1 : 0) + ';FirstRowHasNames=' + (hdr ? '1' : '0') + ';Format=TabDelimited' + ';');
    }else if( filepath.match(/\.mdb$/i) ){
        connect.push('Provider=Microsoft Office 12.0 Access Database Engine OLE DB Provider;Data Source="' + filepath + '";');
        connect.push('Provider=Microsoft.Jet.OLEDB.4.0;Data Source="' + filepath + '";');
    }else if( filepath.match(/(\.html|\.html)$/i) ){
        connect.push('Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties="HTML Import;DATABASE=' + filepath + '";');
    }else{
        alert('Unsupported File!');
    }

    document.all.result.innerHTML = '';
    for(var i in connect){
        document.all.result.innerHTML += 'Connect String:<br /><pre>' + connect[i] + '</pre>';
        try{
            ado.Open(connect[i]);
            setStatusText('Connected Successfully.');
            break;
        }catch(e){
//            alert(e.message);
            document.all.result.innerHTML += 'Failed:<pre>' + e.message + '</pre>';
            setStatusText('Connection Failed.');
        }
    }
}
function executeSQL( sql ){
    try{
        var times = [new Date()];

        setStatusText('');

        var rs = ado.Execute(sql);
        times.push( new Date() );

        var buf2 = createTableFromRecordSet( rs );
        times.push( new Date() );

        document.all.result.innerHTML = '';
        document.all.result.appendChild( buf2.dom_table );
        window.result_tsv = buf2.tsv;
        times.push( new Date() );

        setStatusText("Records: " + buf2.count
            + ",\tSQL Execution Time: " + (times[1] - times[0])
            + "ms,\tTable Construction Time: " + (times[2] - times[1])
            + "ms,\tDrawing Time: " + (times[3] - times[2]) + "ms");
        shell.RegWrite("HKCU\\SQLEXEC\\SQLLOG", sql, "REG_SZ");
        shell.RegWrite("HKCU\\SQLEXEC\\LASTDB", document.all.filename.value, "REG_SZ")

    }catch( e ){
        alert(e.message);
    }
}
function getTableStructure(){
    try{
        //var rs = ado.OpenSchema(20);
        var rs = ado.OpenSchema(4);
        document.all.result.innerHTML = '';
        document.all.result.appendChild( createTableFromRecordSet( rs ).dom_table );

    }catch( e ){
        alert(e.message);
    }
}
function copyTable(){
    clipboardData.setData("Text", window.result_tsv); } function setStatusText( mes ){
    document.all.status.innerHTML = mes;
}
function closeADO(){
    try{
        ado.Close();
    }catch( e ){

    }
    ado = null;
    setStatusText('Disconnected Successfully.'); 
}

function createTableFromRecordSet( rs ){
    var table = document.createElement('table');
    var tbody = document.createElement('tbody');
    var row;
    var cell;
    var buf_tsv = [];
    var buf_tsv_row = [];
    var count = 0;

    if( !rs.Eof ){
        var fc = rs.Fields.Count;

        row = document.createElement('tr');
        for( var i = 0; i < fc; i++ ){
            cell = document.createElement('th');
            cell.innerText = rs.Fields(i).name;
            cell.title = rs.Fields(i).Type;
            row.appendChild(cell);
            buf_tsv_row.push( escapeTSV(rs.Fields(i).name) );
        }
        tbody.appendChild(row);
        buf_tsv.push( buf_tsv_row.join('\t') );
        while( !rs.Eof ){
            row = document.createElement('tr');
            buf_tsv_row = [];
            for( var i = 0; i < fc; i++ ){
                var v = rs.Fields(i).value;
                cell = document.createElement('td');
                cell.innerText = v;
                row.appendChild(cell);
                buf_tsv_row.push( escapeTSV(v) );
            }
            tbody.appendChild(row);
            buf_tsv.push( buf_tsv_row.join('\t') );
            count++;
            rs.MoveNext();
        }
        table.appendChild(tbody);
    }

    return {"count":count,"dom_table":table,"tsv":buf_tsv.join('\n')}
}

function escapeTSV(data){
    if(typeof data == 'string'){
        data = '"' + data.replace(/"/g,'""') + '"';
    }
    return data;
}
</script>
<style type="text/css">
*    { margin: 0; padding: 0; font-family: sans-serif; font-size: 10pt; }
/*table    { table-layout: fixed; }*/
/*td    {}*/
textarea    { font-family: FixedSys,monospace; font-size: 10pt; }
button    { width: 100px; font-size: 9pt;}
#result table    { border-collapse: collapse; border: 1px solid black; }
#result td,th    { border: 1px solid black; vertical-align: bottom; word-break: keep-all; }
#result th    { background: rgb(192,192,192); }
pre { font-family: monospace; }
</style>
<body>
<form id="f1" onsubmit="return false;" action="#">
<table id="t1" style="width:100%;height:100px;padding:10px;">
<tr>
    <td>
        Select Database(*.xls, *.xlsx or *.csv) File
        <label for="readonly"><input type="checkbox" id="readonly" checked="checked" />Read Only</label>
        <label for="hdr"><input type="checkbox" id="hdr" checked="checked" />Use First Row as Headers</label><br />
        <input type="text" id="filename" style="width:80%;" readonly="readonly" /><span><input type="file" id="fileselector" onchange="loadDatabase();" style="width:20%;" /></span>
    </td>
    <td style="width:100px;">
        <button onclick="loadDatabase();" style="height:50%;">Reopen</button><br />
        <button onclick="closeADO();" style="height:50%;">Close</button>
    </td>
</tr>
<tr>
    <td>
        SQL<br />
        <textarea id="sql" style="width:100%;height:100px;scroll:auto;" wrap="off">select * from [Sheet1$]</textarea>
    </td>
    <td>
        <button onclick="executeSQL(this.form.sql.value);">Execute SQL</button><br />
        <button onclick="copyTable();">Copy Table</button><br />
        <button onclick="getTableStructure();">Meta Information</button>
    </td>
</tr>
</table>
</form>
<div id="result" style="width:100%;height:500px;overflow:scroll;background:white;padding:10px;border-top:1px solid gray;border-bottom:1px solid threedhighlight;"></div> <div id="status" style="width:1980px;height:20px;overflow:hidden;background:buttonface;"></div>
</body>
</html>

動作OS

  • Windows XP
  • Windows Vista
  • Windows 7
  • Windows 8

ExcelファイルやAccessファイルを扱うにはMicrosoft Officeが必要です。