EmbulkのExcelファイル読み込みのメモ。
|
|
Embulkのパーサープラグインの勉強がてら、Apache POIでExcelファイルを読み込むパーサープラグインを作ってみた。
(ちなみに先にHiroyuki SatoさんがRubyのrooでExcelファイルを読み込むプラグイン(embulk-parser-roo-excel)を作られているので、そちらの方がいいかも)
読み込む元のファイルはファイル入力プラグイン(ローカルファイルとか)で指定する。
このプラグインを使う為には、embulk-parser-poi_excelプラグインをインストールしておく必要がある。
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以降用は0.2系。プラグインのソースはembulk-parser-excel-poi。[2023-08-11]
Embulk 0.10ではgemを使わないインストール方法になった。以下のようにしてインストールする。
(この例では、バージョンは0.2.0。最新バージョンはMavenセントラルリポジトリーで確認できる)
Embulk 0.10〜0.11.2の場合 $ export m2_repo=〜 $ mvn dependency:get -Dartifact=io.github.hishidama.embulk:embulk-parser-excel-poi:0.2.0 Embulk 0.11.3以降の場合 [2023-06-02] $ java -jar embulk-0.11.3.jar install io.github.hishidama.embulk:embulk-parser-excel-poi:0.2.0
$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を指定すること。
指定されたシートの一番左側の列から順番に読み込まれる。
A | B | C | D | E | F | |
1 | 日付 | 種類 | 名前 | 形状 | モンスター | |
2 | 2015/10/7 | 水の宝珠 | ベホマラーの奇跡 | 1 | アークデーモン | |
3 | 2015/10/7 | 光の宝珠 | おたけびの技巧 | 2 | フォレスドン | |
4 |
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の表示では、日本語がずれる(苦笑)
読み込む列を明示的に指定することも出来る。
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]
シート名はsheetsで指定する。[2017-01-28]
parser: type: poi_excel sheets: [DQ10-orb]
カンマで区切って複数のシート名を書くことが出来る。
sheets: [Sheet1, Sheet2]
ver0.1.6では、ワイルドカード「*」(0個以上の文字にマッチ)、「?」(1個の文字にマッチ)が使える。
sheets: ["Sheet?"]
→シート毎のオプションの指定方法
→シート名を値として出力する方法
Excelファイル内では、セルの値はブランク・文字列・数値・真偽値・エラーという型で保持されている。[2017-07-16]
パーサープラグインでは、この型をEmbulkの型(string, long, double, boolean,
timestamp)に変換して出力する(outputプラグインへ渡す)。
変換先 (Embulkの型) |
変換元(Excelのセルの型) | ||||
---|---|---|---|---|---|
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 |
例外を発生させる。 |
セルに数式(計算式)が入っている場合、デフォルトではその計算を行う。[2017-01-28]
ver0.1.6では、(計算を行わずに)セルにキャッシュされている値を取得することも出来る。
columns:
- {name: a, type: long, value: cell_value}
- {name: b, type: long, value: cell_value}
- {name: c, type: long, value: cell_value, formula_handling: cashed_value}
デフォルトは「evaluate
」。
セルの数式を計算する際に、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 〜
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]
A | B | C | D | |
1 | テーブル説明 | サンプル1 | ||
---|---|---|---|---|
2 | テーブル名 | example1 | ||
3 | ||||
4 | カラム説明 | カラム名 | データ型 | |
5 | カラム1 | col1 | text | |
6 | カラム2 | col2 | numeric | |
7 |
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ではファイル名はファイル入力プラグインが扱う範疇なので、パーサーでは取得できない)
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 | 光の宝珠 | おたけびの技巧 | フォレスドン | +--------------+----------+-------------------------+-----------------+-----------------+---------------------+
セルのスタイル(背景色や罫線等)やフォント(文字の色等)を取得することが出来る。
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":"MS Pゴシック","index":0,"italic":false,"strikeout":false,"type_offset":0,"underline":0}
さすがにこれは多すぎるので、必要な属性だけを絞ることが出来る。
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は基本的にぞれぞれの属性固有のものにする必要がある。
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では、シート個別の設定を行うことが出来る。
A | B | C | D | E | F | |
1 | 日付 | 種類 | 名前 | 形状 | モンスター | |
2 | 2015/10/7 | 水の宝珠 | ベホマラーの奇跡 | 1 | アークデーモン | |
3 | 2015/10/7 | 光の宝珠 | おたけびの技巧 | 2 | フォレスドン | |
4 |
A | B | C | D | E | F | |
1 | ||||||
2 | 日付 | 入手元 | 種類 | 名前 | ||
3 | 2015/10/5 | フォレスドン | 水の宝珠 | ザオリクの戦域 | ||
4 | 2015/10/8 | 達人クエスト | 光の宝珠 | 妖精たちのポルカの閃き | ||
5 |
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だったのでログ出力をそれに合わせた)
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 |
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 | | | | +-------------------------+-----------+-----------+-----------+
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 |
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っぽい使い方だと思う。
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]
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
〜