S-JIS[2015-10-18/2023-10-08] 変更履歴

Embulk Excelファイルの入力

EmbulkのExcelファイル読み込みのメモ。

概要 [/2023-08-11]
インストール方法 [/2023-10-08]
例 [/2020-10-03]
シート名の指定方法 [2017-01-28]
データ型の変換 [2017-07-16]
数値から文字列への変換 [2017-02-11]
セルのエラー値の扱い [2015-10-24]
数式の扱い [2017-01-28]
数式のエラーの扱い [2017-01-28]
変換エラーの扱い [2015-10-24]
結合セルの扱い [/2020-09-13]
他行の値を取得する例 [2020-09-12]
特殊な値の例 [/2023-08-11]
セルの書式の例 [/2020-09-12]
複数シートの扱い [2015-10-24]
レコードタイプ [2020-10-03]
フィルターとの組み合わせ
プレビューの注意 [/2017-05-15]

www.embulk.orgのLocal file input plugin
GitHubのembulk-parser-poi_excel
Hiroyuki Satoさんのembulk-parser-roo-excel

概要

Embulkのパーサープラグインの勉強がてら、Apache POIでExcelファイルを読み込むパーサープラグインを作ってみた。
（ちなみに先にHiroyuki SatoさんがRubyのrooでExcelファイルを読み込むプラグイン（embulk-parser-roo-excel）を作られているので、そちらの方がいいかも）

読み込む元のファイルはファイル入力プラグイン（ローカルファイルとか）で指定する。

このプラグインを使う為には、embulk-parser-poi_excelプラグインをインストールしておく必要がある。

インストール方法

Embulk 0.9以前のインストール方法

Embulk 0.9以前用は0.1系。プラグインのソースはembulk-parser-poi_excel。[2023-08-11]

Embulk 0.9以前はgemコマンドでインストールする。

$ embulk gem install embulk-parser-poi_excel

$ embulk gem list embulk-parser-poi_excel
2015-10-24 18:54:39.238 +0900: Embulk v0.7.5

*** LOCAL GEMS ***

embulk-parser-poi_excel (0.1.3)

Embulk 0.10以降のインストール方法

Embulk 0.10以降用は0.2系。プラグインのソースはembulk-parser-excel-poi。[2023-08-11]

Embulk 0.10ではgemを使わないインストール方法になったが、公式なインストール方法は発表されていない。
とりあえず以下のようにすればインストールできる。（この例では、バージョンは0.2.0。最新バージョンはMavenセントラルリポジトリーで確認できる）

プラグイン本体と依存ライブラリーをMavenローカルリポジトリーにインストールする。[2023-10-08]
```
$ export m2_repo=～
$ mvn dependency:get -Dartifact=io.github.hishidama.embulk:embulk-parser-excel-poi:0.2.0
```
Embulkのプロパティーファイル（Linuxの場合は$HOME/.embulk/embulk.properties、Windowsの場合は%USERPROFILE%\.embulk\embulk.properties）に設定を追加する。
```
plugins.parser.poi_excel=maven:io.github.hishidama.embulk:excel-poi:0.2.0
```

簡単な例

最も簡単な使い方は、config.ymlのparserのtypeにpoi_excelを指定し、シート名と（ヘッダーがある場合はスキップする行数と）columnsを指定すること。

指定されたシートの一番左側の列から順番に読み込まれる。

dq10.xls：

	A	B	C	D	E	F
1	日付	種類	名前	形状	モンスター
2	2015/10/7	水の宝珠	ベホマラーの奇跡	1	アークデーモン
3	2015/10/7	光の宝珠	おたけびの技巧	2	フォレスドン
4

config.yml：

in:
  type: file
  path_prefix: /tmp/example/dq10.xls
  parser:
    type: poi_excel
    sheets: [DQ10-orb]
    skip_header_lines: 1	# first row is header.
    columns:
    - {name: get_date, type: timestamp}
    - {name: orb_type, type: string}
    - {name: orb_name, type: string}
    - {name: orb_shape, type: long}
    - {name: drop_monster, type: string}

out:
  type: stdout

実行例

$ embulk preview config.yml
～
2015-10-18 12:33:30.942 +0900 [INFO] (preview): column.name=get_date <- cell column=A, value_type=CELL_VALUE
2015-10-18 12:33:30.942 +0900 [INFO] (preview): column.name=orb_type <- cell column=B, value_type=CELL_VALUE
2015-10-18 12:33:30.942 +0900 [INFO] (preview): column.name=orb_name <- cell column=C, value_type=CELL_VALUE
2015-10-18 12:33:30.942 +0900 [INFO] (preview): column.name=orb_shape <- cell column=D, value_type=CELL_VALUE
2015-10-18 12:33:30.942 +0900 [INFO] (preview): column.name=drop_monster <- cell column=E, value_type=CELL_VALUE
+-------------------------+-----------------+-----------------+----------------+---------------------+
|      get_date:timestamp | orb_type:string | orb_name:string | orb_shape:long | drop_monster:string |
+-------------------------+-----------------+-----------------+----------------+---------------------+
| 2015-10-07 00:00:00 UTC |            水の宝珠 |        ベホマラーの奇跡 |              1 |             アークデーモン |
| 2015-10-07 00:00:00 UTC |            光の宝珠 |         おたけびの技巧 |              2 |              フォレスドン |
+-------------------------+-----------------+-----------------+----------------+---------------------+

previewの表示では、日本語がずれる(苦笑)

読み込む列を明示的に指定することも出来る。

config.yml：

in:
  type: file
  path_prefix: /tmp/example/dq10.xls
  parser:
    type: poi_excel
    sheets: [DQ10-orb]
    skip_header_lines: 1	# first row is header.
    columns:
    - {name: get_date, type: timestamp, column_number: A}
    - {name: drop_monster, type: string, column_number: E}
    - {name: orb_type, type: string, column_number: B}
    - {name: orb_name, type: string, column_number: C}

out:
  type: stdout

column_numberを指定すると、シート上のその列のデータが読み込まれる。
（column_numberを省略した場合は前のカラムの右側が指定されたことになる）

※ver0.1.12でcolumn_numberはcell_columnに変更になった。[2020-10-03]

実行例

$ embulk preview config.yml
～
2015-10-18 12:47:18.600 +0900 [INFO] (preview): column.name=get_date <- cell column=A, value_type=CELL_VALUE
2015-10-18 12:47:18.600 +0900 [INFO] (preview): column.name=drop_monster <- cell column=E, value_type=CELL_VALUE
2015-10-18 12:47:18.600 +0900 [INFO] (preview): column.name=orb_type <- cell column=B, value_type=CELL_VALUE
2015-10-18 12:47:18.600 +0900 [INFO] (preview): column.name=orb_name <- cell column=C, value_type=CELL_VALUE
+-------------------------+---------------------+-----------------+-----------------+
|      get_date:timestamp | drop_monster:string | orb_type:string | orb_name:string |
+-------------------------+---------------------+-----------------+-----------------+
| 2015-10-07 00:00:00 UTC |             アークデーモン |            水の宝珠 |        ベホマラーの奇跡 |
| 2015-10-07 00:00:00 UTC |              フォレスドン |            光の宝珠 |         おたけびの技巧 |
+-------------------------+---------------------+-----------------+-----------------+

※ver0.1.12で、ログ出力のvalue_typeはvalueと表示されるようになった。[2020-10-03]

データ型の変換

Excelファイル内では、セルの値はブランク・文字列・数値・真偽値・エラーという型で保持されている。[2017-07-16]
パーサープラグインでは、この型をEmbulkの型（string, long, double, boolean, timestamp）に変換して出力する（outputプラグインへ渡す）。

データ変換方法
変換先（Embulkの型）	変換元（Excelのセルの型）
変換先（Embulkの型）	BLANK	STRING	NUMERIC	BOOLEAN	ERROR
string	null	そのまま出力	文字列へ変換（numeric_format）	TRUE→`"true"` FALSE→`"false"`	エラーメッセージ
long	null	longへパース（on_convert_error）	longへキャスト	TRUE→1 FALSE→0	エラーコード
double	null	doubleへパース（on_convert_error）	そのまま出力	TRUE→1 FALSE→0	エラーコード
boolean	null	`"true"`（大文字小文字無視）→true それ以外→false	0以外→true 0→false	そのまま出力	null
timestamp	null	日付へパース（format, timezone）（on_convert_error）	日付として変換（timezone）	変換対象外（on_convert_error）	変換対象外（on_convert_error）

※括弧内は、その変換で使用されるオプション（ymlファイルで指定できるもの）

ExcelのNUMERICは浮動小数点（double）である。
Excelの日付（日時）はファイル内ではNUMERICとして保持されている。Embulkの型をtimestampとしたときだけ日付として扱う。

例えば、ymlファイルで{name: get_date, type: timestamp, format: "%Y/%m/%d"}と指定した場合、
セルのデータがSTRINGであれば、formatの書式を使ってtimestamp型に変換する。
セルのデータがNUMERICであれば、それを日付データとみなしてtimestamp型に変換する（この場合、formatの指定は無関係。使われない）。

数値から文字列への変換

Excelシート内で数値になっている値をEmbulkでstringとして出力する場合、数値はStringに変換される。[2017-02-11]

この変換は、デフォルトではJavaのDouble.toStringメソッドで行われる。（Excelシート内では、数値はdoubleで保持されている）
（変換された末尾が「.0」の場合は、それを除去するという処理も入っている）
しかしこの方法だと、桁数の多い数値は指数表現になったりする。（例えば12345678は「1.2345678E7」になる）

ver0.1.7では、数値をstringに変換する際の書式を指定できる。
この書式はJavaのString.formatメソッドで浮動小数を扱うもの、つまり「%f」のものを指定する。

    - {name: format1, type: string, value: cell_value, numeric_format: "%.0f"}

セルのエラー値の扱い

Excelでは、セルの値がエラー（「#DIV/0!」や「#REF!」等）になっていることがある。[2015-10-24]

ver0.1.2では、エラー値がある場合の挙動を設定できる。

  parser:
    type: poi_excel
    sheets: [DQ10-orb]
    on_cell_error: constant
    columns:
    - {name: foo, type: string, column_number: A, value: cell_value, on_cell_error: error_code}

parser直下もしくはcolumns内に、on_cell_errorでエラー値の処理を指定する。

指定内容	説明
`constatnt`	nullを出力する。（デフォルトの挙動）
`constatnt.値`	指定された値を出力する。指定する値は、type（Embulk側のデータ型）に変換できる必要がある。（例えばtimestampの場合、formatが一致していないといけない）
`error_code`	エラーコードを出力する。 type（Embulk側のデータ型）がstringの場合、「`#DIV/0!`」等の文字列になる。 typeが数値型の場合、エラーコード（数値）になる。
`exception`	例外を発生させる。

数式のエラーの扱い

セルの数式を計算する際に、Apache POIのライブラリー内でエラーになる（例外が発生する）ことがある。[2017-01-28]
（POIのバグや仕様によってExcelと同じ計算が出来ないものがある）

ver0.1.2では、数式の計算中にエラーが発生した場合の挙動を設定できる。

指定内容	説明
`constatnt`	nullを出力する。
`constatnt.値`	指定された値を出力する。指定する値は、type（Embulk側のデータ型）に変換できる必要がある。（例えばtimestampの場合、formatが一致していないといけない）
`exception`	例外を発生させる。（デフォルトの挙動）

（POIのバグによるものはPOIが修正されるまで待つしかないが、）POI（少なくとも3.13）はExcel2007のテーブル（構造化参照）には未対応なので、構造化参照を使っている数式はエラーになる。
しかし、構造化参照の「名前」を使って他のセルを参照している場合は、そのセルのアドレス（A1とか）に変換してやればPOIでも計算できる。

ver0.1.2では、このために、数式の置換を行うことが出来る。

例えばC列のセルに「=aaa+bbb」と入っている（そしてaaaやbbbの名前が定義されていない）場合は、普通に計算するとエラーになるが、以下のようにすればaaaはA1, bbbはB1に置換されるので、「=A1+B1」として計算される。

    columns:
    - {name: a, type: long, value: cell_value}
    - {name: b, type: long, value: cell_value}
    - {name: c, type: long, value: cell_value, formula_replace: [{regex: aaa, to: A1}, {regex: bbb, to: B1}]}

また、toの文字列に「${row}」が入っている場合、その部分は現在処理中の行（の番号）に置き換えられる。

    - {name: c, type: long, value: cell_value, formula_replace: [{regex: aaa, to: "A${row}"}, {regex: bbb, to: "B${row}"}]}

regexに「[@単価]」といった構造化参照の文字列を指定してtoにA1形式でセルを指定してやれば、置換されて計算できるようになるはず。
なお、regexは正規表現で指定する（JavaのStringのreplaceAllに渡される）ので、角括弧等はエスケープしないと駄目だと思う。

変換エラーの扱い

ver0.1.2では、セルの値のEmbulkの型への変換に失敗した場合の挙動を設定できる。[2015-10-24]

  parser:
    type: poi_excel
    sheets: [DQ10-orb]
    on_convert_error: constant.0
    columns:
    - {name: foo, type: timestamp, format: "%Y/%m/%d", column_number: A, value: cell_value, on_convert_error: constant.9999/12/31}

parser直下もしくはcolumns内に、on_convert_errorで変換エラーの際の処理を指定する。

指定内容	説明
`constatnt`	nullを出力する。
`constatnt.値`	指定された値を出力する。指定する値は、type（Embulk側のデータ型）に変換できる必要がある。（例えばtimestampの場合、formatが一致していないといけない）
`exception`	例外を発生させる。（デフォルトの挙動）

結合セルの扱い

対象セルが空（BLANK）の場合、デフォルトでは、結合セルになっているかどうか判別し、結合セルであれば結合範囲の左上セルの値を取得する。[2020-09-12]

ただ、Excelファイルの仕様上、結合セルかどうかは結合データを全て検索しないと判別できない。
特にxlsxファイルの場合は結合データの取得自体もけっこう遅いようで、結合セルが多いと検索にかなりの時間がかかる。
このため、search_merged_cellオプションで結合セルを検索するかどうかを指定できる。

～
  parser:
    type: poi_excel
    sheets: [Sheet1]
#   search_merged_cell: none
#   search_merged_cell: linear_search
    search_merged_cell: tree_search
～

search_merged_cellの設定値
ver0.1.7以前	ver0.1.8	ver0.1.11	説明
`false`	`none`	`none`	結合セルの検索をしない。値は基本的にnullになる。
`true`（デフォルト）	`linear_search`	`linear_search`	結合セルを検索する。（線形探索）
	`tree_search`（デフォルト）	`tree_search`	結合セルを検索する。（TreeMapを使った二分探索）
		`hash_search`（デフォルト）	結合セルを検索する。（HashMapを使った探索）[2020-09-13]

なお、tree_searchやhash_searchはlinear_searchより高速だが、メモリーは余計に使用する。

他行の値を取得する例

ver0.1.8では、現在の行以外のセルの値を取得・出力することが出来る。[2020-09-12]

table.xls：

	A	B	C	D
1	テーブル説明	サンプル1
2	テーブル名	example1
3
4	カラム説明	カラム名	データ型
5	カラム1	col1	text
6	カラム2	col2	numeric
7

config.yml：

in:
  type: file
  path_prefix: /tmp/example/table.xls
  parser:
    type: poi_excel
    sheets: [Sheet1]
    skip_header_lines: 4
    columns:
    - {name: table_desc, type: string, cell_address: B1}
    - {name: table_name, type: string, cell_address: B2}
    - {name: column_desc, type: string, column_number: A}
    - {name: column_name, type: string}
    - {name: column_type, type: string}

out:
  type: stdout

（column_numberの代わりに）cell_addressを指定する。
「A1」なら同一シート内となる。「シート名!A1」で別シートを指定することも出来る。

実行例

$ embulk preview config.yml
～
2020-09-12 19:04:02.710 +0900 [INFO] (0001:preview): sheet=Sheet1
2020-09-12 19:04:02.720 +0900 [INFO] (0001:preview): column.name=table_desc <- cell_address=B1, value_type=CELL_VALUE
2020-09-12 19:04:02.721 +0900 [INFO] (0001:preview): column.name=table_name <- cell_address=B2, value_type=CELL_VALUE
2020-09-12 19:04:02.721 +0900 [INFO] (0001:preview): column.name=column_desc <- cell_column=A, value_type=CELL_VALUE
2020-09-12 19:04:02.722 +0900 [INFO] (0001:preview): column.name=column_name <- cell_column=B, value_type=CELL_VALUE
2020-09-12 19:04:02.722 +0900 [INFO] (0001:preview): column.name=column_type <- cell_column=C, value_type=CELL_VALUE
+-------------------+-------------------+--------------------+--------------------+--------------------+
| table_desc:string | table_name:string | column_desc:string | column_name:string | column_type:string |
+-------------------+-------------------+--------------------+--------------------+--------------------+
|             サンプル1 |          example1 |               カラム1 |               col1 |               text |
|             サンプル1 |          example1 |               カラム2 |               col2 |            numeric |
+-------------------+-------------------+--------------------+--------------------+--------------------+

特殊な値の例

セルから値を取得する他に、シート名や行番号などを取得することも出来る。
（ファイル名も欲しかったところだが、Embulkではファイル名はファイル入力プラグインが扱う範疇なので、パーサーでは取得できない）

config.yml：

in:
  type: file
  path_prefix: /tmp/example/dq10.xls
  parser:
    type: poi_excel
    sheets: [DQ10-orb]
    skip_header_lines: 1	# first row is header.
    columns:
    - {name: sheet, type: string, value: sheet_name}
    - {name: row, type: long, value: row_number}
    - {name: get_date, type: timestamp}
    - {name: orb_type, type: string}
    - {name: orb_name, type: string}
    - {name: drop_monster, type: string, column_number: E}

out:
  type: stdout

valueでsheet_nameを指定するとシート名、row_numberを指定すると行番号になる。
ver0.1.13以降では、file_nameを指定すると入力のExcelファイル名（フルパス）になる。[2023-08-11]

なお、valueを省略するとcell_valueが指定されたことになる。

実行例

$ embulk preview config.yml
～
2015-10-18 12:50:04.536 +0900 [INFO] (preview): column.name=sheet <- value_type=SHEET_NAME
2015-10-18 12:50:04.536 +0900 [INFO] (preview): column.name=row <- value_type=ROW_NUMBER
2015-10-18 12:50:04.536 +0900 [INFO] (preview): column.name=get_date <- cell column=A, value_type=CELL_VALUE
2015-10-18 12:50:04.536 +0900 [INFO] (preview): column.name=orb_type <- cell column=B, value_type=CELL_VALUE
2015-10-18 12:50:04.551 +0900 [INFO] (preview): column.name=orb_name <- cell column=C, value_type=CELL_VALUE
2015-10-18 12:50:04.551 +0900 [INFO] (preview): column.name=drop_monster <- cell column=E, value_type=CELL_VALUE
+--------------+----------+-------------------------+-----------------+-----------------+---------------------+
| sheet:string | row:long |      get_date:timestamp | orb_type:string | orb_name:string | drop_monster:string |
+--------------+----------+-------------------------+-----------------+-----------------+---------------------+
|     DQ10-orb |        2 | 2015-10-07 00:00:00 UTC |            水の宝珠 |        ベホマラーの奇跡 |             アークデーモン |
|     DQ10-orb |        3 | 2015-10-07 00:00:00 UTC |            光の宝珠 |         おたけびの技巧 |              フォレスドン |
+--------------+----------+-------------------------+-----------------+-----------------+---------------------+

セルの書式の例

セルのスタイル（背景色や罫線等）やフォント（文字の色等）を取得することが出来る。

config.yml：

in:
  type: file
  path_prefix: /tmp/example/style.xls
  parser:
    type: poi_excel
    sheets: [Sheet2]
    skip_header_lines: 1
    columns:
    - {name: note, type: string, column_number: D}
    - {name: note-style, type: string, value: cell_style}
    - {name: note-font, type: string, value: cell_font}

out:
  type: stdout

valueにcell_styleを指定すると、セルのスタイル（POIのCellStyleのほぼ全ての情報）がJSON文字列形式で取得できる。
valueにcell_fontを指定すると、セルのフォントがJSON文字列形式で取得できる。
なお、cell_styleの場合、column_numberを省略すると、直前のカラムと同じセルが対象になる。

基本的にPOIで取得できる情報そのままだが、色に関しては、RGBの十六進数6桁にしてある。

実行例は（JSON文字列が長すぎるので）省略するが、以下のような感じになる＾＾；

cell_style：
{"alignment":0,"border_bottom":1,"border_bottom_color":"000000","border_left":1,"border_left_color":"00000","border_right":1,"border_right_color":"000000","border_top":1,"border_top_color":"000000","data_format":"General""fill_background_color":"000000","fill_foreground_color":"000000","fill_pattern":0,"font_index":0,"hidden":false,"indenion":0,"locked":true,"rotation":0,"vertical_alignment":1,"wrap_text":false}

cell_font：
{"bold":false,"boldweight":400,"char_set":128,"color":null,"font_height":220,"font_height_in_points":11,"font_name":"ＭＳＰゴシック","index":0,"italic":false,"strikeout":false,"type_offset":0,"underline":0}

ver0.1.8（POI 3.17）でboldweightは無くなった。[2020-09-12]

さすがにこれは多すぎるので、必要な属性だけを絞ることが出来る。

config.yml：

in:
  type: file
  path_prefix: /tmp/example/style.xls
  parser:
    type: poi_excel
    sheets: [Sheet2]
    skip_header_lines: 1
    columns:
    - {name: note, type: string, column_number: D}
    - {name: note-style, type: string, value: cell_style, attribute_name: [fill_pattern, fill_foreground_color, fill_background_color]}
    - {name: note-font, type: string, value: cell_font, attribute_name: [bold, italic, strikeout, color]}

out:
  type: stdout

実行例

$ embulk preview config.yml
～
2015-10-24 19:05:31.505 +0900 [INFO] (preview): sheet=Sheet2
2015-10-24 19:05:31.521 +0900 [INFO] (preview): column.name=note <- cell_column=D, value_type=CELL_VALUE
2015-10-24 19:05:31.521 +0900 [INFO] (preview): column.name=note-style <- cell_column=D, value_type=CELL_STYLE, value=null
2015-10-24 19:05:31.521 +0900 [INFO] (preview): column.name=note-font <- cell_column=D, value_type=CELL_FONT, value=null
+-------------+--------------------------------------------------------------------------------------+--------------------------------------------------------------+
| note:string |                                                                    note-style:string |                                             note-font:string |
+-------------+--------------------------------------------------------------------------------------+--------------------------------------------------------------+
|       test1 | {"fill_pattern":0,"fill_foreground_color":"000000","fill_background_color":"000000"} | {"bold":false,"italic":false,"strikeout":false,"color":null} |
|       test2 | {"fill_pattern":0,"fill_foreground_color":"000000","fill_background_color":"000000"} | {"bold":false,"italic":false,"strikeout":false,"color":null} |
+-------------+--------------------------------------------------------------------------------------+--------------------------------------------------------------+

また、個別の属性のみをカラムのデータとすることも出来る。
cell_styleやcell_fontの後ろにピリオド「.」で属性名をつなげる。
この場合は返ってくる値はJSON形式ではないので、 typeは基本的にぞれぞれの属性固有のものにする必要がある。

config.yml：

in:
  type: file
  path_prefix: /tmp/example/style.xls
  parser:
    type: poi_excel
    sheets: [Sheet2]
    skip_header_lines: 1
    columns:
    - {name: note, type: string, column_number: D}
    - {name: style1, type: long, value: cell_style.fill_pattern}
    - {name: style2, type: string, value: cell_style.fill_foreground_color}
    - {name: style3, type: string, value: cell_style.fill_background_color}
    - {name: font1, type: boolean, value: cell_font.bold}
    - {name: font2, type: boolean, value: cell_font.italic}
    - {name: font3, type: boolean, value: cell_font.strikeout}
    - {name: font4, type: string, value: cell_font.color}

out:
  type: stdout

実行例

$ embulk preview config.yml
～
2015-10-24 19:07:23.227 +0900 [INFO] (preview): sheet=Sheet2
2015-10-24 19:07:23.227 +0900 [INFO] (preview): column.name=note <- cell_column=D, value_type=CELL_VALUE
2015-10-24 19:07:23.227 +0900 [INFO] (preview): column.name=style1 <- cell_column=D, value_type=CELL_STYLE, value=[fill_pattern]
2015-10-24 19:07:23.227 +0900 [INFO] (preview): column.name=style2 <- cell_column=D, value_type=CELL_STYLE, value=[fill_foreground_color]
2015-10-24 19:07:23.242 +0900 [INFO] (preview): column.name=style3 <- cell_column=D, value_type=CELL_STYLE, value=[fill_background_color]
2015-10-24 19:07:23.242 +0900 [INFO] (preview): column.name=font1 <- cell_column=D, value_type=CELL_FONT, value=[bold]
2015-10-24 19:07:23.242 +0900 [INFO] (preview): column.name=font2 <- cell_column=D, value_type=CELL_FONT, value=[italic]
2015-10-24 19:07:23.242 +0900 [INFO] (preview): column.name=font3 <- cell_column=D, value_type=CELL_FONT, value=[strikeout]
2015-10-24 19:07:23.242 +0900 [INFO] (preview): column.name=font4 <- cell_column=D, value_type=CELL_FONT, value=[color]
+-------------+-------------+---------------+---------------+---------------+---------------+---------------+--------------+
| note:string | style1:long | style2:string | style3:string | font1:boolean | font2:boolean | font3:boolean | font4:string |
+-------------+-------------+---------------+---------------+---------------+---------------+---------------+--------------+
|       test1 |           0 |        000000 |        000000 |         false |         false |         false |              |
|       test2 |           0 |        000000 |        000000 |         false |         false |         false |              |
+-------------+-------------+---------------+---------------+---------------+---------------+---------------+--------------+

複数シートの扱い

YAMLファイルのsheetsに複数のシート名を指定すれば、それらのシートからデータを読み込む。[2015-10-24]
これらのシートは同一レイアウトである前提だが、ver0.1.3では、シート個別の設定を行うことが出来る。

dq10.xls（DQ10-orb）：

	A	B	C	D	E	F
1	日付	種類	名前	形状	モンスター
2	2015/10/7	水の宝珠	ベホマラーの奇跡	1	アークデーモン
3	2015/10/7	光の宝珠	おたけびの技巧	2	フォレスドン
4

dq10.xls（DQ10-orb2）：

	A	B	C	D	E	F
1
2	日付	入手元	種類	名前
3	2015/10/5	フォレスドン	水の宝珠	ザオリクの戦域
4	2015/10/8	達人クエスト	光の宝珠	妖精たちのポルカの閃き
5

config.yml：

in:
  type: file
  path_prefix: /tmp/example/dq10.xls
  parser:
    type: poi_excel
    sheets: [DQ10-orb, DQ10-orb2]
    columns:
    - {name: get_date, type: timestamp}
    - {name: orb_type, type: string}
    - {name: orb_name, type: string}
    - {name: orb_shape, type: long}
    - {name: drop_monster, type: string}
    sheet_options:
      DQ10-orb:
        skip_header_lines: 1
      DQ10-orb2:
        skip_header_lines: 2
        columns:
          orb_type: {column_number: C}
          orb_name: {column_number: D}
          orb_shape: {value: constant.0}
          drop_monster: {column_number: B}

out:
  type: stdout

sheet_optionsは、シート名をキーとしたップ。
skip_header_linesやcolumns等を指定することが出来る。

columnsは、カラム名をキーとしたマップ。
（parserの下の）columnsと同様の指定が出来る。（name, typeを除く）

実行例

$ embulk preview config.yml
～
2015-10-24 21:56:01.592 +0900 [INFO] (preview): sheet=DQ10-orb
2015-10-24 21:56:01.608 +0900 [INFO] (preview): column.name=get_date <- cell_column=A, value_type=CELL_VALUE
2015-10-24 21:56:01.608 +0900 [INFO] (preview): column.name=orb_type <- cell_column=B, value_type=CELL_VALUE
2015-10-24 21:56:01.608 +0900 [INFO] (preview): column.name=orb_name <- cell_column=C, value_type=CELL_VALUE
2015-10-24 21:56:01.608 +0900 [INFO] (preview): column.name=orb_shape <- cell_column=D, value_type=CELL_VALUE
2015-10-24 21:56:01.608 +0900 [INFO] (preview): column.name=drop_monster <- cell_column=E, value_type=CELL_VALUE
2015-10-24 21:56:01.623 +0900 [INFO] (preview): sheet=DQ10-orb2
2015-10-24 21:56:01.623 +0900 [INFO] (preview): column.name=get_date <- cell_column=A, value_type=CELL_VALUE
2015-10-24 21:56:01.623 +0900 [INFO] (preview): column.name=orb_type <- cell_column=C, value_type=CELL_VALUE
2015-10-24 21:56:01.623 +0900 [INFO] (preview): column.name=orb_name <- cell_column=D, value_type=CELL_VALUE
2015-10-24 21:56:01.623 +0900 [INFO] (preview): column.name=orb_shape <- value_type=CONSTANT, value=[0]
2015-10-24 21:56:01.623 +0900 [INFO] (preview): column.name=drop_monster <- cell_column=B, value_type=CELL_VALUE
+-------------------------+-----------------+-----------------+----------------+---------------------+
|      get_date:timestamp | orb_type:string | orb_name:string | orb_shape:long | drop_monster:string |
+-------------------------+-----------------+-----------------+----------------+---------------------+
| 2015-10-07 00:00:00 UTC |            水の宝珠 |        ベホマラーの奇跡 |              1 |             アークデーモン |
| 2015-10-07 00:00:00 UTC |            光の宝珠 |         おたけびの技巧 |              2 |              フォレスドン |
| 2015-10-05 00:00:00 UTC |            水の宝珠 |         ザオリクの戦域 |              0 |              フォレスドン |
| 2015-10-08 00:00:00 UTC |            光の宝珠 |     妖精たちのポルカの閃き |              0 |              達人クエスト |
+-------------------------+-----------------+-----------------+----------------+---------------------+

レコードタイプ

ver0.1.12で、レコードタイプが指定できるようになった。[2020-10-03]
デフォルトでは（今まで通りだと）1レコードはExcelの1行だったが、列やシートにすることが出来る。

record_type	説明	レコード内のカラムの指定	skip_header_lines
row（デフォルト）	1レコードは1行。複数行が複数レコードになる。	主にcell_column（列指定）を使用する。	先頭行（上の行）をスキップする。
column	1レコードは1列。複数列が複数レコードになる。	主にcell_row（行指定）を使用する。	先頭列（左の列）をスキップする。
sheet	1レコードは1シート。 1シートにつき1レコードのみ出力される。	主にcell_address（セル指定）を使用する。	無視される。（スキップしない）

これに伴い、今まで列を指定するのはcolumn_numberだったが、cell_columnに変更した。
（ログ出力上は今までcell_columnだったので、YMLの設定名をそれに合わせた。これで、設定名がcell_column・cell_row・cell_addressとなり、統一がとれた）
（同様に、ログ出力のvalue_typeはvalueと表示されるようになった。YMLの設定名がvalueだったのでログ出力をそれに合わせた）

レコードタイプcolumnの例

test.xls（Sheet3）：

	A	B	C	D	E	F
1		2020/10/1	2020/10/2	2020/10/3	2020/10/4
2	項目1	123	1	888
3	項目2	456	12	777
4	項目3	789	123	666
5

config.yml：

in:
  type: file
  path_prefix: /tmp/example/test.xls
  parser:
    type: poi_excel
    sheets: [Sheet3]
    record_type: column
    skip_header_lines: 1
    columns:
    - {name: date, type: timestamp, cell_row: 1}
    - {name: f1, type: string, cell_row: 2}
    - {name: f2, type: string, cell_row: 3}
    - {name: f3, type: string, cell_row: 4}
out:
  type: stdout

実行例

$ embulk preview config.yml
～
2020-10-03 19:54:16.382 +0900 [INFO] (0001:preview): Loaded plugin embulk-parser-poi_excel (0.1.12)
2020-10-03 19:54:16.525 +0900 [INFO] (0001:preview): sheet=Sheet3
2020-10-03 19:54:16.542 +0900 [INFO] (0001:preview): record_type=COLUMN
2020-10-03 19:54:16.542 +0900 [INFO] (0001:preview): column.name=date <- cell_row=1, value=CELL_VALUE
2020-10-03 19:54:16.543 +0900 [INFO] (0001:preview): column.name=f1 <- cell_row=2, value=CELL_VALUE
2020-10-03 19:54:16.543 +0900 [INFO] (0001:preview): column.name=f2 <- cell_row=3, value=CELL_VALUE
2020-10-03 19:54:16.543 +0900 [INFO] (0001:preview): column.name=f3 <- cell_row=4, value=CELL_VALUE
+-------------------------+-----------+-----------+-----------+
|          date:timestamp | f1:string | f2:string | f3:string |
+-------------------------+-----------+-----------+-----------+
| 2020-10-01 00:00:00 UTC |       123 |       456 |       789 |
| 2020-10-02 00:00:00 UTC |         1 |        12 |       123 |
| 2020-10-03 00:00:00 UTC |       888 |       777 |       666 |
| 2020-10-04 00:00:00 UTC |           |           |           |
+-------------------------+-----------+-----------+-----------+

レコードタイプsheetの例

test.xls（Sheet4）：

	A	B	C	D	E	F
1	名前	hishidama		職業	Healer
2
3		項目11	項目12	項目13
4		123	456	789
5
6		項目21	項目22	項目23	項目24
7		abc	foo	bar	zzz
8

config.yml：

in:
  type: file
  path_prefix: /tmp/example/test.xls
  parser:
    type: poi_excel
    sheets: [Sheet4]
    record_type: sheet
    columns:
    - {name: name, type: string, cell_address: B1}
    - {name: job, type: string, cell_address: E1}
    - {name: f11, type: string, cell_address: B4}
    - {name: f12, type: string, cell_address: C4}
    - {name: f24, type: string, cell_address: E7}
out:
  type: stdout

実行例

$ embulk preview config.yml
～
2020-10-03 20:25:01.109 +0900 [INFO] (0001:preview): Loaded plugin embulk-parser-poi_excel (0.1.12)
2020-10-03 20:25:01.257 +0900 [INFO] (0001:preview): sheet=Sheet4
2020-10-03 20:25:01.271 +0900 [INFO] (0001:preview): record_type=SHEET
2020-10-03 20:25:01.272 +0900 [INFO] (0001:preview): column.name=name <- cell_address=B1, value=CELL_VALUE
2020-10-03 20:25:01.272 +0900 [INFO] (0001:preview): column.name=job <- cell_address=E1, value=CELL_VALUE
2020-10-03 20:25:01.272 +0900 [INFO] (0001:preview): column.name=f11 <- cell_address=B4, value=CELL_VALUE
2020-10-03 20:25:01.273 +0900 [INFO] (0001:preview): column.name=f12 <- cell_address=C4, value=CELL_VALUE
2020-10-03 20:25:01.273 +0900 [INFO] (0001:preview): column.name=f24 <- cell_address=E7, value=CELL_VALUE
+-------------+------------+------------+------------+------------+
| name:string | job:string | f11:string | f12:string | f24:string |
+-------------+------------+------------+------------+------------+
|   hishidama |     Healer |        123 |        456 |        zzz |
+-------------+------------+------------+------------+------------+

ちなみに、これで大量のExcelファイルを変換すると中身が1レコードのファイルがたくさん出力されると思うけど、1ファイルにまとめたいなら、後からcatコマンド辺りで結合するのが良いかなぁ。

フィルターとの組み合わせ

Excelの使い方の話だが、シートにデータを並べた後、下の方にデータ以外のメモや計算式を書くことがある。
POIではこういう行もデータとして読み込んでしまうが、不要なデータはフィルタープラグインで除去するのがEmbulkっぽい使い方だと思う。

config.yml：

in:
  type: file
  path_prefix: /tmp/example/style.xls
  parser:
    type: poi_excel
    sheets: [Sheet2]
    skip_header_lines: 1
    columns:
    - {name: note, type: string, column_number: D}

filters:
  - type: row
    conditions:
    - {column: note, operator: "IS NOT NULL"}

out:
  type: stdout

プレビューの注意

poi_excelパーサーを使って大きなxlsxファイルのプレビューを行おうとするとエラーになることがある。[2017-01-27]

$ embulk preview config.yml
2017-01-27 22:26:24.820 +0900: Embulk v0.8.15
2017-01-27 22:26:25.507 +0900 [INFO] (0001:preview): Listing local files at directory 'D:\temp\example\poi' filtering filename by prefix 'large.xlsx'
2017-01-27 22:26:25.509 +0900 [INFO] (0001:preview): Loading files [D:\temp\example\poi\large.xlsx]
2017-01-27 22:26:25.779 +0900 [INFO] (0001:preview): Loaded plugin embulk-parser-poi_excel (0.1.5)
java.lang.RuntimeException: java.io.EOFException: Unexpected end of ZLIB input stream
	at org.embulk.parser.poi_excel.PoiExcelParserPlugin.run(org/embulk/parser/poi_excel/PoiExcelParserPlugin.java:166)
	at org.embulk.spi.FileInputRunner.run(org/embulk/spi/FileInputRunner.java:153)
	at org.embulk.exec.PreviewExecutor$2$1.run(org/embulk/exec/PreviewExecutor.java:122)
～
	at org.embulk.cli.Main.main(org/embulk/cli/Main.java:23)
Caused by: java.io.EOFException: Unexpected end of ZLIB input stream
	at java.util.zip.InflaterInputStream.fill(Unknown Source)
	at java.util.zip.InflaterInputStream.read(Unknown Source)
	at java.util.zip.ZipInputStream.read(Unknown Source)
	at org.apache.poi.openxml4j.util.ZipSecureFile$ThresholdInputStream.read(ZipSecureFile.java:168)
～
	at org.embulk.cli.Main.main(Main.java:23)

Error: java.io.EOFException: Unexpected end of ZLIB input stream

xlsxファイルはzip圧縮されているので、一部分だけ読みたいと思っても、全ファイルを読み込まないといけない。
しかしEmbulkのプレビューでは、ファイルの先頭部分しか読んでくれないようだ。
なので、POIとしてはファイルが途中で終わったという例外になってしまう。

小さなExcelファイルならプレビューできるし、runなら全部読まれる。

Embulk 0.8.19だとpreview時に読み込むファイルサイズを指定できるようになったので、それを指定すれば上記のエラーは出なくなる。[2017-05-15]

config.yml：

exec:
  preview_sample_buffer_bytes: 9200000
in:
  type: file
  path_prefix: D:/temp/example/poi/large.xlsx
  parser:
    type: poi_excel
～

preview_sample_buffer_bytesに、読み込むExcelファイルのサイズより大きな値（バイト単位）を指定する。

↓実行結果

$ embulk preview config.yml
2017-05-15 22:05:48.458 +0900: Embulk v0.8.20
2017-05-15 22:05:49.395 +0900 [INFO] (0001:preview): Listing local files at directory 'D:\temp\example\poi' filtering filename by prefix 'large.xlsx'
2017-05-15 22:05:49.395 +0900 [INFO] (0001:preview): "follow_symlinks" is set false. Note that symbolic links to directories are skipped.
2017-05-15 22:05:49.395 +0900 [INFO] (0001:preview): Loading files [D:\temp\example\poi\large.xlsx]
2017-05-15 22:05:49.411 +0900 [INFO] (0001:preview): Try to read 9,200,000 bytes from input source
～

Embulk目次へ戻る / 技術メモへ戻る

メールの送信先：ひしだま