はじめに
エクセルファイルを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）であったため
時間が読み込めなかったと思われます。
※エクセルの表示通りに読み込んでもらえるのは楽ですが、
読み込めない場合があるのは、書式がいじれないファイルを扱う場合にはつらいですね。