TalendでExcelファイルの読み込み

8012 ワード
小ネタ Excel Talend Excel テキストリンク

はじめに

エクセルファイルをTalendで読み込んで集計しようとしたところ
日付形式のデータがエクセル側で設定したフォーマットとは異なっていたので
備忘録として残しておきます。

環境

使用環境 バージョン
OS Windows10
TalendOpenStudio 7.2.1
OpenJDK 11.0.4
Office365 1902

準備したエクセルファイル

ExcelTestData.xlsx
※拡張子「xlsx」ファイル

※作業時間：「End」-「Start」、 セルの書式設定：ユーザ定義（h:mm）
累積時間：「1行前の作業時間」＋「作業時間」、セルの書式設定：ユーザ定義（[h]:mm）

Talendでの読み込み

  • tFileListコンポーネントで、ExcelTestData.xlsxを置いた任意の場所を指定します。
  • tFileInputExcelコンポーネントで、Excelファイルを読み込みます。
    • 「Excel2007のファイル形式(xlsx)を読み取る」にチェックを付けます。
    • スキーマにカラムを5個設定します。（タイプは全てString）
  • tLogRowコンポーネントで、標準出力します。
    • モードをテーブルにします。

実行

TalaendStudioの実行ジョブで

ジョブ readExcel を 11:35 05/10/2019 に開始しました。
[statistics] connecting to socket on port 3421
[statistics] connected
.---------+----------------------------+----------------------------+----------------------------+----------------------------.
|                                                          tLogRow_1                                                          |
|=--------+----------------------------+----------------------------+----------------------------+---------------------------=|
|newColumn|newColumn1                  |newColumn2                  |newColumn3                  |newColumn4                  |
|=--------+----------------------------+----------------------------+----------------------------+---------------------------=|
|1        |Tue Oct 01 09:30:00 JST 2019|Tue Oct 01 18:30:00 JST 2019|Sun Dec 31 09:00:00 JST 1899|Sun Dec 31 09:00:00 JST 1899|
|2        |Tue Oct 01 09:00:00 JST 2019|Tue Oct 01 19:00:00 JST 2019|Sun Dec 31 10:00:00 JST 1899|Sun Dec 31 19:00:00 JST 1899|
|3        |Tue Oct 01 09:00:00 JST 2019|Tue Oct 01 18:00:00 JST 2019|Sun Dec 31 09:00:00 JST 1899|Mon Jan 01 04:00:00 JST 1900|
|4        |Tue Oct 01 09:30:00 JST 2019|Tue Oct 01 19:30:00 JST 2019|Sun Dec 31 10:00:00 JST 1899|Mon Jan 01 14:00:00 JST 1900|
|5        |Tue Oct 01 13:30:00 JST 2019|Tue Oct 01 21:30:00 JST 2019|Sun Dec 31 08:00:00 JST 1899|Mon Jan 01 22:00:00 JST 1900|
|6        |Tue Oct 01 09:30:00 JST 2019|Tue Oct 01 18:30:00 JST 2019|Sun Dec 31 09:00:00 JST 1899|Tue Jan 02 07:00:00 JST 1900|
'---------+----------------------------+----------------------------+----------------------------+----------------------------'

[statistics] disconnected

ジョブ readExcel が 11:35 05/10/2019 に終了しました。 [終了コード=0]

日付の形式について

Excel2007の日付型をString型で読むと
「Tue Oct 01 09:30:00 JST 2019」
という形になりました。
※日付表示が文字になっているので、Localeを指定しないと
　文字列⇒日付の変換ができません。
　スキーマでは、パターンの指定しかできないので
　tFileInputExcelで直に日付型では読み込めないと思われます。
※「Excelは、日付情報をファイル内部では数値型として値を保持しており、値が0の場合は基準日として、通常は 「1899年12月31日（Excel表記上は1900年1月0日）」 を表します。」とのことです。
参考） ファイル内部の日付の基準日の設定

tJavaRowで変換

tFileInputExcelコンポーネントの後ろにtJavaRowコンポーネントを追加して
文字列を日付に変換します。

  • 基本設定
SimpleDateFormat sdfi = new SimpleDateFormat("EEE MMM dd HH:mm:ss z yyyy", Locale.US);
SimpleDateFormat sdfo = new SimpleDateFormat("yyyy/MM-dd HH:mm:ss");
// エクセル基準日
String zeroDate="Sun Dec 31 00:00:00 JST 1899";
long dateTimeZero = sdfi.parse(zeroDate).getTime();

output_row.newColumn = input_row.newColumn;
output_row.newColumn1 = sdfo.format( sdfi.parse( input_row.newColumn1 ) );
output_row.newColumn2 = sdfo.format( sdfi.parse( input_row.newColumn2 ) );

// 時間差
long dateTimeDiff = sdfi.parse( input_row.newColumn3 ).getTime();
long dateTimeAccm = sdfi.parse( input_row.newColumn4 ).getTime();

output_row.newColumn3 = String.valueOf((dateTimeDiff - dateTimeZero )/ (1000 * 60 * 60 ) );
output_row.newColumn4 = String.valueOf((dateTimeAccm - dateTimeZero )/ (1000 * 60 * 60 ) );

※エクセル基準日（1899-12-31 00:00:00）から、時刻差のミリ秒をlongでとり
　(1000 * 60 * 60 )で割ることで時間に変換します。　

  • 詳細設定
import java.util.Locale;

結果

tJavaRowに上記を設定して実行すると、下記の出力になりました。

ジョブ readExcel を 21:44 05/10/2019 に開始しました。
[statistics] connecting to socket on port 3829
[statistics] connected
.---------+-------------------+-------------------+----------+----------.
|                               tLogRow_1                               |
|=--------+-------------------+-------------------+----------+---------=|
|newColumn|newColumn1         |newColumn2         |newColumn3|newColumn4|
|=--------+-------------------+-------------------+----------+---------=|
|1        |2019/10-01 09:30:00|2019/10-01 18:30:00|9         |9         |
|2        |2019/10-01 09:00:00|2019/10-01 19:00:00|10        |19        |
|3        |2019/10-01 09:00:00|2019/10-01 18:00:00|9         |28        |
|4        |2019/10-01 09:30:00|2019/10-01 19:30:00|10        |38        |
|5        |2019/10-01 13:30:00|2019/10-01 21:30:00|8         |46        |
|6        |2019/10-01 09:30:00|2019/10-01 18:30:00|9         |55        |
'---------+-------------------+-------------------+----------+----------'

[statistics] disconnected

ジョブ readExcel が 21:44 05/10/2019 に終了しました。 [終了コード=0]

xlsファイルの場合

拡張子xlsファイルを対象として、同様の書式にして読み込んだ場合は、
下記の様になりました。
※tFileInputExcelコンポーネントの「Excel2007のファイル形式(xlsx)を読み取る」からチェックを外しました。

ジョブ readExcel_Copy を 22:12 05/10/2019 に開始しました。
[statistics] connecting to socket on port 3940
[statistics] connected
.---------+----------+----------+----------+----------.
|                      tLogRow_1                      |
|=--------+----------+----------+----------+---------=|
|newColumn|newColumn1|newColumn2|newColumn3|newColumn4|
|=--------+----------+----------+----------+---------=|
|1        |9:30      |6:30      |9:00      |:00       |
|2        |9:00      |7:00      |10:00     |:00       |
|3        |9:00      |6:00      |9:00      |:00       |
|4        |9:30      |7:30      |10:00     |:00       |
|5        |1:30      |9:30      |8:00      |:00       |
|6        |9:30      |6:30      |9:00      |:00       |
|null     |null      |null      |null      |null      |
'---------+----------+----------+----------+----------'

[statistics] disconnected

ジョブ readExcel_Copy が 22:12 05/10/2019 に終了しました。 [終了コード=0]

標準的な時刻の書式は、表示と同じ書式で読み込んでもらえるようですが、
newColumn4のカラムは、ファイルの書式が、ユーザ定義（[h]:mm）であったため
時間が読み込めなかったと思われます。
※エクセルの表示通りに読み込んでもらえるのは楽ですが、
　読み込めない場合があるのは、書式がいじれないファイルを扱う場合にはつらいですね。