HTA SQLツール

January 13, 2016 (Last Modified: January 27, 2023)

ソフトウェア

今は廃れたHTA(HTML Application)で書いた、汎用SQLツール。 AccessがなくてもCSVファイルやExcelファイルに対してSQLを実行。

概要

特別なソフトのインストール無しで動作し、

CSVファイル(.csv)、TSVファイル(.tsv)
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.top = document.all.top_panel.clientHeight;
  document.all.result.style.height = document.body.clientHeight -
document.all.top_panel.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);
//    alert(shell.RegRead("HKCU\\SQLEXEC\\SQLLOG"));
  }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
      + ",  SQL Execution Time: " + (times[1] - times[0])
      + "ms,  Table Construction Time: " + (times[2] - times[1])
      + "ms,  Drawing 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   { vertical-align: top; }
textarea  { font-family: FixedSys,monospace; font-size: 10pt; }
button  { width: 10em; 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="#">
<div id="top_panel" style="position:absolute;width:100%;">
  <div id="database_select" style="width:100%;padding:0
10px;background:#eee;">
    <div>Select Database(*.xls, *.xlsx or *.csv) File</div>
    <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:70%;" readonly="readonly"
/><input type="file" id="fileselector" onchange="loadDatabase();"
style="width:1%;" />
    <button onclick="loadDatabase();">Reopen</button>
    <button onclick="closeADO();">Close</button>
  </div>
  <div id="sql_panel" style="width:100%;padding:0 10px;background:#eee;">
    SQL<br />
    <textarea id="sql" style="width:100%;height:10em;scroll:auto;"
wrap="off">select * from [Sheet1$]</textarea>
    <button onclick="executeSQL(this.form.sql.value);">Execute SQL</button>
    <button onclick="copyTable();">Copy Table</button>
    <button onclick="getTableStructure();">Meta Information</button>
  </div>
</div>
</form>
<div id="result" style="position: absolute;
width:100%;height:500px;overflow:scroll;background:white;padding:20px;border-top:1px
solid gray;border-bottom:1px solid threedhighlight;"></div>
<div id="status" style="position: absolute; bottom: 0; left: 0; width:
100%; height: 2em; padding: 0.5em; overflow: hidden; background:
buttonface;"></div>
</body>
</html>

動作OS

Windows XP
Windows Vista
Windows 7
Windows 8
Windows 10

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