NDW

アプリ開発やトラブルシューティング等のノウハウ、キャンプや登山の紹介や体験談など。

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

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

関連記事

.NET Core(C#): JsonSerializer実践オプションとコンバータ

業務アプリの開発を想定したJsonSerializerの使い方とサンプルです。 概要 .NET Core 3.1の標準パッケージSystem.Text.Jsonに含まれるJsonSerializerを …

Javaによるzipファイルの安全な解凍方法

以前、業務アプリ(Java)でzipファイルの操作が必要となったため、Javaにおけるzip圧縮解凍について調査しました。また、zip4jを使った圧縮・解凍についても説明しました。 ここでは、もう少し …

WindowsでQRコードを生成(VBScript)

概要 Windows環境でVBScriptを使用してQRコードを生成する方法を紹介します。 このVBScriptは、Excelを起動し、バーコードコントロールを使ってQRコードを生成しています。 動作 …

VBAでケバブ・スネーク・パスカル・キャメル変換

開発の現場では、Excelに定義したクラスやテーブル等の設計内容に基づいて、VBAで自動的にソースコードやSQL文等の成果物を生成したい場合があります。この処理を実装する場合、Excel上の項目名を、 …

Java/JavaEE開発キットの作成

Javaアプリ開発者のローカルPCに、ファイル展開するだけで開発環境をできるようにするための開発キットの準備について記載します。 ★随時更新予定★ 背景 Java/JavaEE等を使ったアプリ開発では …