ExcelSQL=エクセル⇔DBデータ変換ツール
他所にはない圧倒的に便利なエクセルからSQLサーバーへのデータ変換ツール
データベースを使用するアプリケーションの開発において、初期データやテスト用のサンプルデータを投入する方法は沢山ありますが、エクセルで作成したデータを投入する方法が最もポピュラーでしょう。関数やセル参照式を使用して大量のデータを作成し、これらをまとめてデータベースに投入するのが一般的な方法です。
エモーションソフトのExcelSQLはこのとき投入するExcelデータをSQLに変換し、データベースへの一括データ投入や変更、或いは削除を可能にするツールです。
ExcelSQLの特徴
このツールでは先ずデータ入力用エクセルワークブックの作成を行います。接続先のデータベースを指定して、入力シートを作成する複数のテーブルをExcelSQLの画面上で選択して入力用エクセルワークブック作成を指示すると、瞬時に指定されたパスにデータ入力用のエクセルワークブックを作成してくれます。
ユーザーは作成されたワークブック上の入力シートに任意の方法（関数、セル参照式、連続データ作成等）でデータを作成し、その後ExcelSQLで再度このワークブックを指定してSQLの作成を実行すれば、これも瞬く間に複数データ登録用のSQLファイルを作成してくれます。
ExcelSQLは対象となるテーブルの項目属性を解析して保持するので、SQLを作成する際に型変換誤りを起こすことはありません。以下のような便利で分かりやすい入力値の設定規則を適用します。
- 数字のみから成るデータであっても登録先テーブル項目が文字型であれば文字列として処理するSQLを作成します。
- 前方0詰めの数字から成る「001」のような値を設定する場合には、列に文字型指定をするか入力時にセルの先頭に「’」(アポストロフィ)を設定します。ExcelSQLは自動的にこの「’」を入力値よりスキップします。
- セルの入力値に改行コードが含まれる場合には、そのままセル内に改行コードを含めた文字列をセットします。SQL作成時にはその改行コードも含めてSQL文を作成されます。結果として登録先のテーブル項目にも改行コード付きの文字列がセットされます。
- テーブル側のアイデンティティーや既定値、トリガー等のように自動的にセットされる項目は、入力シートの上の指定でSQL作成時に設定項目から除外することが出来ます。
使い方の紹介
手順１．最初にExcelSQLのフロント画面をお見せします。ExcelSQLは現在マクロ付きのエクセルワークブックとして用意されています。ユーザーは以下の手順を実行します。
- ユーザーはフロントページ後方のワークシートで入力シート作成対象となるテーブル名を指定します。後方のワークシートは「管理系」とか「契約系」と名前が付けられていますが、これはテーブルをグループ化する際のグループ名です。ユーザーが任意の名前をセットすることが可能です。
- 「処理対象エクセルファイル」のテキストエリアに出力するエクセルワークブックのパスを記入します。
- 「実行する処理」でラジオボタン「項目名タイトル書き出し」を選択します。
- 実行ボタンをクリックします。
これだけの操作で瞬時に指定されたパスにデータ入力用のワークブックを作成しデスクトップ上でアクティブにします。入力用ワークブックの中には指定したテーブルの入力シートが全て作成されています。
データ入力用シートには対象となるテーブル名と、入力項目の名前とデータ型が表示されています。主キー項目はヘッダ列の色が水色に塗り分けられているので直ぐに判別できます。
手順２．入力用エクセルワークブックの入力用シートに投入するデータの値を入れていきます。実際に入力値の設定例を見てみましょう。以下の画面はアプリケーションが使用するメッセージマスターの初期データの設定例です。
- B列のUpdateTimeは入力時や更新時のトリガーとしてデータベース側で自動的にセットされるため5行目の*をクリアします。するとExcelSQLはこの項目をSQL文の項目設定対象から外しています。
- E列の「MessageID」には「=”ToSpnRegC” & Right(1000 + Row(C6) – 5, 3)」のように連番を生成するための関数を含む式がセットされています。ExcelSQLはSQL生成時には関数式ではなく表示されている値を設定値として認識します。
- I列の項目「Message」には改行コードを含む長い文字列が指定されています。（エクセルではこの列を折り返して全体を表示する設定にしてあります。）
手順3．この状態でExceSQLのフロントページに戻り次の処理を実行します。
- 「SQL出力先ファイル」に出力するSQLファイルのパスを指定します。
- 実行する処理として「SQLファイルの書き出し」と「DBへの書き込みを行わない」を指定します。
- 実行をクリックします。「サンプルデータの取り込み処理実行？」ダイアログが出るので「はい（Y)」をクリックします。
手順４．SQLファイルが出力されます。これをSQL Server Management Studioのようなクライアントツールに読み込ませて実行します。実行の際にはツールの画面上で個々のSQL文の処理結果をチェックしましょう。
手順５．クライアントツールで実行結果を確かめてみます。今回の処理対象となったメッセージマスターにエクセルで指定したデータが正しくセットされていることが確認できます。
以上データ投入手順のみを紹介しましたが、他にデータの削除や更新、または「更新を試みて更新対象が無ければ挿入」のようなSQLを出力するオプションもあります。
更にSQLファイルの出力のみでなく、そのままExcelSQLからSQLサーバーにデータを投入することも可能です。この場合処理の成否が入力シートの最後の列に書き込まれ、エラーになった行についてはエラーの理由もセルに書き込まれます。
- お薦めする方法はデータ入力用と削除用のSQLをセットで作成する方法です。エクセルデータの一部にエラーがあり投入に失敗した場合には削除用SQLを使用していったん投入したデータを削除します。その後データを修正して再度入力用のSQLファイルを出力して再投入するというやり方です。
完成版はどのような形式になるか？
現在のバージョンはマクロ付きのエクセルワークブックとして作成しています。ただしこの形式ではユーザーが自分たちの開発対象となるアプリケーション用にカスタマイズするために、一部マクロ内の定数指定やワークシート名とフロントページのコントロールのプロパティーを変えたりしなければなりません。
この作業は元のプログラムを壊すリスクがあるため、あまり良いやり方とは言えません。やはりこのツールは単独のWindowsアプリケーションとして提供するべきと考えます。その際には以下のような設定をUIから行えるようにしたいと思っています。
- 接続先DBの指定（接続文字列とエリアス設定。ただしパスワードを含むため内部保持時は暗号化が必要）
- 処理対象とするテーブルの選択とグループ化
- 親子関係にあるテーブルの追加・変更・削除には外部キー制約よりSQLの出力順序を自動調整する。（データ追加の際には親テーブルを先に、削除の際には子テーブルを先にSQLを出力する。）
リリーススケジュール
2026年秋を予定しています。SQExcelのポータルサイトをご参照ください。
