ゆっきーのブログ

実践的なシステム設計開発、アプリケーションフレームワーク開発、トラブルシューティング、 後輩育成のためのブログ。その他、キャンプ、トレッキング・登山やレジャー等。

1. システムエンジニアリング windows 開発標準化

マスタデータ生成ツール

投稿日:2018年12月2日 更新日:

開発や結合試験、本番環境等で使用するマスタデータをExcelで管理することがあります。
そのようなExcelファイルからDBに登録するためのインサート文を作成するために、いつもツールに悩むので作成してみました。

説明

Excelで定義したマスタデータに基づいて、INSERT文を作成します。

入力ファイル仕様(マスタデータ)

マスタデータを定義するExcelファイルは、1シート1テーブルを想定しています。
シート名をテーブル名、シート上の物理カラム名と型、実際のデータ値がシートに定義されている想定です。

オプション欄に文字を指定することで、指定されている値に対して例えばMD5変換、Hex変換等の特別な変換を行うこともできます。
オプションで指定できる値は次の通りです。指定された順番でこれらの変換が行われます。
例えば、値をMD5したハッシュ化し、Base64で出力したい場合、”MB”と指定します。(バイト列をINSERT文として出力できないため、オプションの最後に値を文字列化するB/H/Eのいずれかを指定してください。)

文字 説明
C PostgreSQLの暗号化関数でラップします。
暗号化キー(‘enckey’)の指定方法は「カスタマイズ方法」を参照のこと。
(例: pgp_sym_encrypt(‘value’, ‘enckey’) )
M MD5ハッシュ値のバイト列を生成します。
1 SHA-1ハッシュ値のバイト列を生成します。
2 SHA-2(SHA-256)ハッシュ値のバイト列を生成します。
B Base64文字列を生成します。(例: ‘dmFsdWU=’)
H 16進数形式の文字列を生成します。(例: ‘76616C7565’)
E PostgreSQLのbytea用の文字列を生成します。(例: E’\\x76616c7565′)
N 列・値を出力しない。(カラムの既定値や自動採番を行いたい場合に指定)

INSERT文の生成仕様

次の書式でテーブル毎のINSERT文を生成します。

-- ★対象テーブル: [テーブル名1]
-- truncate table [テーブル名1];
insert into [テーブル名]([カラム名1], [カラム名2], …) values([値1], [値2], …);
insert …
…
-- ★対象テーブル: [テーブル名2]
-- truncate table [テーブル名2];
…

[]で記載された各パラメータの説明は次の通り。

[テーブル名1], [テーブル名2], …
Excelシート名
[カラム名1], [カラム名2], …
Excelシート上の「物理名」列
[値1], [値2], …
型がchar/varchar/textを含む場合は値を引用符(‘)で囲う。それ以外の場合は、そのままの値(引用符なし)とする。どちらの場合でも、値が空の場合は、”null”とする。
ただし、任意のオプションが指定されている場合、上記内容は無視し、オプション側の処理を行う。

出力ファイル仕様

生成したINSERT文は全て、「ツール実行」シートの「出力先ファイル」で指定した単一ファイルに出力します。
UTF-8を使用するDBクライアントツールを想定しているため、出力ファイルはUTF-8(BOMなし)とします。

使い方

ダウンロード

次の場所から「マスタデータ生成.xlsm」「マスタデータ_XXX.xlsx」をダウンロードします。
(これ以外のファイルはテスト用のファイルなので無視してください。)

マスタデータの準備

次のようにマスタデータファイルを作成します。

  1. マスタデータ_XXX.xlsxを任意の名前に変更する。
  2. シート名をテーブル名に変更する。
    単一ブックで複数テーブルのマスタデータを作成する場合、シートをコピーしてください。
  3. シートの「論理名」「物理名」「型」行に各列の論理名/物理名/型を入力する。
    必須は「物理名」「型」、必要に応じてオプションも入力してください。
    列が足りない場合、コピペで追加してください。
  4. 各行にデータ値を入力する。必要に応じて行を追加する。

条件を指定して実行

「マスタデータ生成.xlsm」を開き、各種の条件を指定し、「マスタデータ生成」をクリックします。

カスタマイズ方法

本ツールのモジュール構成は次の通りです。
基本的な動作をカスタマイズする場合は、ModMstCreateモジュールを修正します。
オプション動作をカスタマイズする場合は、ModMstCreateOptionモジュールを修正します。

具体的なカスタマイズ方法は次の通りです。

  • マスタデータのデータ読取位置は、標準モジュールModMstCreateの次の変数を変更してください。
    CIDX_START … データが開始される列名(既定値:”B”)
    RIDX_CNAME … カラム名の行番号(既定値:2)
    RIDX_TYPE … 型の行番号(既定値:3)
    RIDX_OPTION … オプション値の行番号(既定値:4)
    RIDX_DATA … INSERT文の作成対象となるデータ開始行番号(既定値:5)
  • 文字列型(値を引用符で囲いたい)として扱いたい型がある場合、標準モジュールModMstCreateのIsStrType()を修正してください。
  • Cオプションを使用する場合、標準モジュールModMstCreateOptionの定数OPT_ENCRYPT_KEYに暗号化キー値を指定してください。
  • クエリの出力内容をカスタマイズしたい場合、標準モジュールModMstCreateのProcSheet(), ProcLine()等の関数を修正してください。
  • 独自のオプション変換を実装する場合、標準モジュールModMstCreateOptionに実装してください。
    ModMstCreateOption.GetOptionVal関数に、追加したオプション処理ができるよう条件分岐を追加してください。

-1. システムエンジニアリング, windows, 開発標準化

執筆者:


comment

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

関連記事

JavaEE7のJSF, Facelets, JSPの関係

JavaEEを使ったアプリ開発の際に、いつも気になるが後回しにしていたこと… HTML5への対応方法の調査等、今後の理解促進のために、調べてみた。 FaceletsとJSFとの関係は? J …

vbaでのエンコード/デコードのサンプル

Excel(vba)で、MD5/SHA-1/SHA-2(SHA-256)の出力、Hex/Base64エンコード/デコードを調べたので備忘録として残します。 動作検証した環境は、Windows10+Of …

CentOS7のマルチホーム化

サイトの存在を隠しつつも、sftpサーバを公開し、後輩と1G以上のファイルのやりとりしたい。 パブリック側のIPアドレスを教えてしまうと、どこのサーバだろうかとブラウザで開いたりするとサイトの存在がわ …

postgresqlでの拡張のインストール

PostgreSQL10で暗号化関数を使用するため拡張(pgcrypto)をインストールした際の出来事を記載します。 事象 pgcryptoの拡張をインストールしたり、インストールの確認を行うと、その …

EAP7のインストールとパッチ適用

仕事でredhat社のEnterprise Application Server7(EAP7)を扱うことが多いので備忘録として記載します。 なお、このサイトでWebアプリを公開したいと考えていますが、 …