NDW

マスタデータ生成ツール

windows

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

目次

説明

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

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

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

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

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

INSERT文の生成仕様

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

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

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

出力ファイル仕様

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

使い方

ダウンロード

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

GitHub
tools/10_開発支援/マスタデータ生成 at master · nextdoorwith/tools

tools. Contribute to nextdoorwith/tools development by creat…

マスタデータの準備

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

  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関数に、追加したオプション処理ができるよう条件分岐を追加してください。