データ登録(エクセル)

• エクセルデータの登録
  • エクセルファイルの配置
  • エクセルファイルのファイル名
  • エクセルファイルのデータ仕様
  • 全てのセルの形式を "文字列" に
  • エクセルデータのテンプレートを活用
  • エクセルファイルの粒度は？
  • エクセルデータの登録結果 (XlsDataResult)
• 日付を相対値で登録 (RelativeDate)
  • セル上にて相対値の指定
  • datapropにて相対値の指定 (DateAdjustment)
• 値のコンバート設定(dataprop)
• エクセルシート名制限回避の設定(dataprop)
• トリムしないカラムの指定(dataprop)
• 空文字を許すカラムの指定(dataprop)
• エクセルデータのシートのスキップ

エクセルデータの登録

エクセルで管理されたデータを登録します。"エクセル2003" 形式を想定しています(".xlsx" は未サポート)。データの編集がしやすいため、最もお奨めするデータファイル形式です。

エクセルファイルの配置

"[DBFluteクライアント]/playsql/data" 配下の "common/xls" ディレクトリ、および、"[dataLoadingType]/xls" 配下のエクセルファイルが登録されます。

e.g. エクセルファイルの配置(dataLoadingTypeはデフォルトの "ut") @playsql
dbflute_exampledb
 |-playsql
    |-data
       |-common
       |  |-xls
       |     |-...
       |-ut
       |  |-xls
       |     |-...

"common" は、どんな "dataLoadingType" であっても登録されます。よって、"common" 配下にはどの環境においても変わらない共通のマスタデータなどを配置すると良いでしょう。

TSVやCSVよりも後にデータ登録されます。もし、TSVやCSVよりも先にデータ登録されて欲しいエクセルデータがある場合は xls 配下ではなく、firstxls 配下に配置することで、他のデータ形式よりも先のタイミングでデータ登録されます。 @since 0.9.8.3

エクセルファイルのファイル名

エクセルのファイル名は "[任意の名前].xls" という形式で配置します。

e.g. エクセルファイルの配置: 会員系テーブルのエクセルファイル @playsql
playsql
 |-data
    |-common
    |  |-xls
    |     |-10-master.xls
    |-ut
    |  |-xls
    |     |-20-member.xls
    |     |-30-product.xls

エクセルファイルのデータ仕様

テーブル名

シート名(左のシートから順番に登録される)

カラム定義

データシートの一行目

データ定義

データシートの二行目以降

データ形式の補足

• 日付は "yyyy/MM/dd HH:mm:ss.SSS" 形式("-" 区切り可、ミリ秒・時分秒省略可)
• Time型は "HH:mm:ss" 形式
• 両端のダブルクォーテーションは、データとして扱わない(除去される)
• 1.0.5Fより前では、データはトリムされて登録される {オプションあり}
• 空文字(トリムされて空文字も含む)のセルは "null" として扱われる {オプションあり}
• Boolean型のカラムは、"1"・"t"・"true"をtrue、"0"・"f"・"false"をfalseとして扱う
• バイナリデータはバイナリファイルへのパスを指定する {バイナリデータの登録}
• 日付カラムで $sysdate.addDay(7) という風に書いて 現在日時の相対値 の指定も可 @since 1.0.1

その他補足

• NotNull制約のないカラムは省略可能
• デフォルト制約のあるカラムは省略可能
• デフォルト設定(defaultValueMap.dataprop)のあるカラムは省略可能
• 存在しないカラムが定義されている場合は登録エラー @since 1.0.4A {オプションあり}
• エクセルシート名には文字数制限がある {オプションあり}
• "#" で始まるシートはスキップ(シートのコメントアウト) {オプションあり}
• 登録データは一レコードずつログに出力される {オプションあり}
• 登録処理にはバッチ更新が利用される {オプションあり}
• 一つのシートでのレコード数制限はエクセルの仕様に準ずる
• データ行の途中に空行がある場合はエラー @since 0.9.9.4A
• データ行の最後の空行は無視される @since 0.9.9.4A

e.g. エクセルファイルの中身: 会員系テーブルのエクセルファイル @xls
|MEMBER_ID|MEMBER_NAME|BIRTHDATE |
|        1|Stojkovic  |1965/03/03|
|        2|Savicevic  |          |
|        3|...        |...       |
- - - - - - - - - - - - - - - - - -
MEMBER / MEMBER_LOGIN / MEMBER_SECURITY <-- Sheet

全てのセルの形式を "文字列" に

シートを新規作成するときには、データをセルに定義する前に全てのセルの形式を "文字列" にすることを強くお奨めします。時折、セルの形式の不正で登録に失敗することがあるためです。

逆に言うと、予期せぬエラーが発生した場合は、新たにシートを作成してセルの形式を調整することで解決することもあります。

エクセルデータのテンプレートを活用

カラム名の列挙を手動で行うのは大変です。様々なツールに支援機能がありますが、DBFluteでも支援に利用できる機能があります。 Docタスクにて、エクセルデータのテンプレートを出力する機能がありますので、そちらを利用してカラム名の列挙をコピーで済むようにすると良いでしょう。 (また、既にDB上に存在するデータをエクセルデータにしたい場合にも活用できます)

エクセルファイルの粒度は？

一つのシートに一つのテーブルデータを管理することで、一つのエクセルファイルで複数のテーブルデータが管理できます。 ただ、シートが多くなり過ぎると管理がしづらくなりますので、適度にファイルを分割して管理すると良いでしょう。 (例えば、業務上のカテゴリで分割：会員系、購入系、商品系など)

エクセルデータの登録結果 (XlsDataResult)

ReplaceSchema実行後に、エクセルデータの登録結果のファイルが出力されます(@since 1.0.1)。 エクセルファイルと同じディレクトリに xls-data-result.dfmark という名前で出力されます。

このファイルを開くと、どのエクセルファイルにどのテーブルのデータが定義されているか、どのテーブルが何レコード定義されているかを確認することができます。

e.g. 20-member.xlsと30-product.xlsのテーブルデータの内訳 @xls-data-result.dfmark

* * * * * * * * * *
*                 *
* Xls Data Result *
*                 *
* * * * * * * * * *
data-directory: playsql/data/ut/xls

[20-member.xls]
  MEMBER (20)
  MEMBER_ADDRESS (20)
  MEMBER_LOGIN (56)
  MEMBER_SECURITY (20)
  MEMBER_SERVICE (20)
  MEMBER_WITHDRAWAL (4)

[30-product.xls]
  PRODUCT (20)
  PURCHASE (60)

日付を相対値で登録 (RelativeDate)

セル上にて相対値の指定

セルに書かれた固定の日付ではなく、ReplaceSchemaが実行時の現在日時から計算された相対的な日付(RelativeDate)の値を登録することができます。 @since 1.0.1

エクセルのセルに $sysdate と書くと現在日時が登録され、$sysdate.addDay(7) と書くと現在日時から7日後が登録されます。$sysdate.addDay(7).moveToDayTerminal() と書くと現在日時から7日後の最初の瞬間(時分秒が00:00:00)が登録されます。このような形で、様々な日付操作ができます。

addDay(int)

日を進める (マイナス値なら戻る)

addMonth(int)

月を進める (マイナス値なら戻る)

addYear(int)

年を進める (マイナス値なら戻る)

add...(int)

...

moveToDayJust()

その日の最初の瞬間 (時分秒が00:00:00)

moveToDayTerminal()

その日の最後の瞬間 (時分秒が23:59:59)

moveToMonthJust()

その月の最初の瞬間 (1日の時分秒が00:00:00)

moveToMonthTerminal()

その月の最後の瞬間 (28, 29, 30, 31日の時分秒が23:59:59)

moveTo...()

...

ここで利用できるメソッドは、DBFluteランタイムの HandyDate のメソッドです。

当然のことですが、DBには調整後のデータが登録されます。 つまり、LoadDataReverseなどでデータを抽出した場合は、調整後のデータが出力されるため、調整前の情報は残っていません。

datapropにて相対値の指定 (DateAdjustment)

また、セルに書かれた固定日付に対して、相対的な調整を入れることもできます。 その場合は、loadingControlMap.dataprop にてテーブル名やカラム名を指定して相対値を指定します。 統一的な設定ができ、エクセル上に特殊な構文が入り込まないため、(要件が満たせるならば)こちらの方が使いやすいでしょう。

値のコンバート設定(dataprop)

定義されている値をそのまま登録するのではなく、指定された値に変換して登録することが可能です。 @since 0.9.9.0A

エクセルシート名制限回避の設定(dataprop)

エクセルのシート名には文字数制限があります。もし、テーブル名がその制限を超えている場合は、別名をシート名に定義して、 その別名から実際のテーブル名にマッピングすることが可能です。

エクセルファイルと同じディレクトリに "tableNameMap.dataprop" という名前のテキストファイルを配置して、 別名と実際のテーブル名のマッピングを定義します。すると、insert処理するときに別名から引いてきた実際のテーブル名で処理するようになります。

e.g. 長ーーーいテーブル名を別名定義 @tableNameMap.dataprop
map:{
    MY_ALIAS_NAME = SUPER_MIRACLE_LONG_LONG_NAME_TABLE
    ; [alias-name] = [table-name]
    ; ...
}

エクセルのシート名は、先頭にドル記号 "$" を付けたエリアス名 "$[alias-name]" という形式で記述しておきます。

e.g. エクセルのシート名に別名を指定 @xls
|MEMBER_ID|MEMBER_NAME|BIRTHDATE |
|        1|Stojkovic  |1965/03/03|
|        2|Savicevic  |          |
|        3|...        |...       |
- - - - - - - - - - - - - - - - - -
$MY_ALIAS_NAME / MEMBER_LOGIN / MEMBER_SECURITY <= Sheet

設定ファイル

tableNameMap.dataprop (UTF-8)

プロパティ

別名とテーブル名の定義

補足

• 別名は大文字小文字区別なし
• エクセルデータの登録のみで有効

トリムしないカラムの指定(dataprop)

エクセルデータ登録において、セルのデータはトリムされて扱われます。もし、"あるカラムだけはトリムされないようにしたい" というような場合に、指定されたカラムのデータだけはトリムせずに扱えるようにすることができます。

エクセルファイルと同じディレクトリに "notTrimColumnMap.dataprop" という名前のテキストファイルを配置して、 どのテーブルのどのカラムが対象なのかを指定します。

e.g. 会員の会員名称と会員アカウントはトリムしない @notTrimColumnMap.dataprop
map:{
    MEMBER = list:{ MEMBER_NAME ; MEMBER_ACCOUNT }
    ; [table-name] = list:{[column-name-list]}
    ; ...
}

設定ファイル

notTrimColumnMap.dataprop (UTF-8)

プロパティ

テーブル名とカラム名の指定

補足

• テーブル名やカラム名は大文字小文字区別なし
• エクセルデータの登録のみで有効

空文字を許すカラムの指定(dataprop)

エクセルデータ登録において、空っぽのセルは "null" として扱われます。もし、"あるカラムだけは空文字として扱いたい" というような場合に、空っぽのセルを空文字として扱えるようにすることができます。

エクセルファイルと同じディレクトリに "emptyStringColumnMap.dataprop" という名前のテキストファイルを配置して、 どのテーブルのどのカラムが対象なのかを指定します。

e.g. 会員の会員名称と会員アカウントを空文字を許す @emptyStringColumnMap.dataprop
map:{
    MEMBER = list:{ MEMBER_NAME ; MEMBER_ACCOUNT }
    ; [table-name] = list:{[column-name-list]}
    ; ...
}

設定ファイル

emptyStringColumnMap.dataprop (UTF-8)

プロパティ

テーブル名とカラム名の指定

補足

• テーブル名やカラム名は大文字小文字区別なし
• エクセルデータの登録のみで有効

エクセルデータのシートのスキップ

エクセルデータの登録において、コメントアウト("#" で始まるもの)されていないシートをスキップしたい場合に、スキップ対象のシート名を "replaceSchemaDefinitionMap.dfprop" の "skipSheet" にて、正規表現で指定することが可能です。

e.g. シート名が "P" で始まるシートをスキップ @replaceSchemaDefinitionMap.dfprop
skipSheet = P.+

設定ファイル

replaceSchemaDefinitionMap.dfprop

プロパティ

skipSheet (正規表現)

補足

• エクセルデータの登録のみで有効

SiteMap Author's BlogBTS