NEWS
IBM i 技術解説 IBM i 技術解説
2024.09.06
SHARE
  • twitter
  • facebook
  • hatena

【IBM Power Salon】株式会社 電業様
「根っからのエンジニアが語る IBM i 内製化のリアル」実践編~IBM i CSVファイルからExcelファイルへの変換 その1

【IBM Power Salon】株式会社 電業様<br /> 「根っからのエンジニアが語る IBM i 内製化のリアル」実践編~IBM i CSVファイルからExcelファイルへの変換 その1

日本アイ・ビー・エム株式会社が毎月開催しているIBM Powerユーザーのための自由な語り場「IBM Power Salon」(月1回、第二水曜日の朝9時から開催)をご存じでしょうか?

2023年1月11日に開催された第14回の、株式会社 電業様による「惜しみなく共有しちゃいます、根っからのエンジニアが語る、IBM i 内製化のリアル」では、既存のIBM i 環境を見事に活用し、自作でIBM i と自動倉庫をつなげDXを実現された素晴らしい事例が披露されました。
ビデオはこちら→https://video.ibm.com/recorded/132452956

とはいえ、1時間の講演時間では語りきれなかった詳細は、きっと他のIBM i ユーザーの方にも参考になるはず!ということで、株式会社電業 総務部 竹本伸明様に“内製DX”について具体的にご説明いただきます。読めばきっと、「IBM i でここまでできるんだ!」と目から鱗が落ちるはずです。

1. はじめに

みなさん初めまして。株式会社電業でシステム担当をしている竹本です。
2023年1月にIBM Powerサロンで講演させて頂きましたので、会社紹介や私のプロファイルについてはそちらをご覧下さい。
中小企業ではご存じの通り、システムへの投資のための予算を捻出することがなかなか難しいという現場があります。それでも私は、システムを活用して現場の負担を少なくして働きやすい環境を作りたいと、今まで培った自分の知っている限りのIT知識でいろんな取り組みをしてきました。サロンでお話した自動倉庫とIBMiの連携もそのひとつです。ところが、その他の事例についてもぜひ紹介してほしいとの要望を頂き、ブログでの連載という形で発信していく事になりました。

この記事を読んで頂くとおわかりになると思いますが、ソリューションを購入しなくても、ちょっとした工夫を行う事で自分たちでいろんなことができるはずです。
私は中小企業のシステム担当として自分でできることはやれば良い、という信条があります。従いまして私の持っている知識、今までやってきた事例を公開して、是非参考にして頂ければと思っております。この連載はみなさんのお役にたてばと思い記載しています。
かなり技術寄りの記事になりますが、ご興味がありましたら一読頂ければ幸いです。

今回はIBMiで作成されたcsvのデータをExcelファイルに変換するpythonスプリクトの紹介になります。
きっかけはIBMiのデータをExcelで利用したいという声がありツールを検討しましたが、いずれも導入コスト+年間保守料金が高価である為、コストメリットが合わなかったことです。月数回レベルの利用回数ではツールを導入するメリットがありませんし、一部のデータはプログラミング処理が必要で、ツールでは対応できない内容もありました。

しかしながら、現場の声を聞いて検討した結果、高機能版は不要で限定されたものでも十分に活用できるのではないかと判断し内作する事にしました。検証版ではcsv作成まではRPG, excelへの変換はvb.netで構築しましたが最低限の機能しか実装できず諦めた機能もありました。(目的は達成しました)
時間が経過するにつれ.NetFrameWork4.6の保守サポート停止があり、本稼働環境で使用するとなるとVisual Studioが高価であるため、別の手段を検討していました。
2022年からIBMiでpythonが稼働する情報が多くなり調べた結果、pythonでも構築可能ではないかと判断しました。Pythonは過去にシステム開発で経験がありましたので、Excel出力にチャレンジする事にしました。
尚、開発にあたって下記項目を要件としました。

  1. 全てフリーソフトを利用する(ランニングコストを含めて費用が掛からないにように考慮する)
  2. Excelで表示される項目を見易くするため、エンドユーザーが見易い形にする。(1行置きに編みかけ表示及び列幅自動調整及び表示編集形式)
  3. Excelのピボット集計を可能とする(定型化できる集計は人が操作しなくても良い:テスト実装)
  4. Excelのピボット集計されたデータのグラフ化を可能とする(より視覚性を向上させる:テスト実装)
  5. 複数のCSVファイルを1つの.xlsxファイル(複数シート)にまとめることを可能とする。(ファイルを複数作成せず1つのブックにまとめる)
  6. 生成したxlsxファイルの取得方法をメール及びftpで送信可能とする。(ftp送信の場合、出力ファイル名に日本語を使用しないで下さい。)

今回IBM i 上で稼働する事を目指しPASE上のpython3.9で作成しました。
尚、データ抽出部分は固定フォーマットのSQL-RPGⅢもしくはILE-SQLRPGで作成しています。(RPGで作成する事で長期間ソースコードの変更なしで運用可)
また、ピボット集計とグラフはテスト実装(動くかの確認)ですので、本番で使用すると機能不足があるかもしれません。この点は今後の検討内容です。

(コラム)
ここまで作っていて、今更ですがコードページに関する問題などがありWebアプリとして作成した方が楽だったと思います。
過去からIBMiを使用されている企業はWeb環境設定をしなくても動作する環境が一番便利ではないかと思い開発を継続しました。
ただし、Excel変換のメインはpythonで動作しますので、pythonのWebフレームワークに取り込む事は問題ないかと思います。

2. 環境

No. 項目 内容 備考
1 ハードウェア Power10 (9105-41B) 1 Core
2 OS IBMi V7.5 TR 3 CCSID 5026 : 日本語カタカナ
3 Python 3.9 Windows環境ではPython3.10でも動作します。
4 openpyxl Pythonのパッケージ:Excelファイル操作
5 Pandas Pythonのパッケージ:CSVファイル操作
6 5250エミュレータ ACS 1.1.9.3 ホストコードページ=930

※pythonスプリクト関連はzipファイルに圧縮していますので、解凍する為にツールが必要です。

  1. Windowsでzipファイルを解凍しWindowsからIBMiへftpで転送
  2. zipファイルをIFSにFTPで転送後に解凍する場合はunzipが必要です。

※PythonスプリクトはWindows環境でも動作します。(動作確認バージョンは上記の通り、3.9です。)

3. 概要

3.1 全体構成

Pythonを用いたExcel出力の全体構成図
  1. 5250画面にて選択条件入力
    抽出条件から、SQL文をQTEMP/SQLWORK作成し、CLのパラメータとしてIDと画面に表示した出力件数を渡します。
  2. DEF出力
    Excelの書式を設定する情報をフィールド選択からカラム出力順に読込み、DEFファイル(QTEMP/CSVTXT)を出力します。 また、CLに渡されたIDよりExcel設定ファイルを参照してテーブルスタイル情報をDEFに出力します。
  3. CSV
    QTEMP/SQLWORKに出力されたSQL文を読込み、SQLを実行します。
    SQLから商品コードを取出し、商品マスターデータを取得します。取得した商品マスターデータはフィールド選択ファイルに従ってCSVデータを出力します。フィールド選択ファイルはカラム(フィールド)出力順でCSVデータ(QTEMP/CSVTXT2)を出力します。 CSVヘッダー(タイトル)はフィールド選択ファイルの情報を使用してCSVの1行目に出力します。
  4. IFSへのデータ転送
    今回のサンプルでは、FTPを使用してIFS上にDEF及びCSVをテキスト転送します。
    ※コード変換はFTPが行ってくれます。
  5. Python実行するシェルスクリプトを実行
    QP2SHELLにてPASE環境のシェルスクリプトを実行します。このシェルからPythonスクリプトを実行し.xlsxファイルを生成します。

以上が概要の流れです。

(サンプルプログラム)
以下で説明するサンプルプログラム(ibm_i_EXCEL_LIBRARY_V7R3M0.savf、Pythonスクリプト、サンプルデータ)をこちらからダウンロードいただけます。


・1~4まではEXCELLIBライブラリーに作成しておりソース及びプログラムはSAVLIBでibm_i_EXCEL_LIBRARY_V7R3M0.savfに保管しています。

No ファイル名 タイプ 属性 ファイル内容 備考
1 QRPGLESRC *FILE PF-SRC ILERPG
ソースファイル
2 QCLLESRC *FILE PF-SRC ILECL
ソースファイル
3 QDSPSRC *FILE PF-SRC 画面
ソースファイル
4 QDDSSRC *FILE PF-SRC 物理・論理
ソースファイル
5 QCMDSRC *FILE PF-SRC コマンド
ソースファイル
6 XSNDFTP *PGM RPGLE FTP送信プログラム(本体) 実態はSTRTCPFTPでFTP転送
7 XSNDFTPC *PGM CLLE FTP送信プログラム(CL)
8 XSNDFTP *CMD FTP送信プログラム(コマンド)
9 CHKHIMEDR3 *PGM CLLE /home/<USER>/excelディレクトリーチェック ディレクトリーが存在しなければ作成する
10 CVTCSVEXCL *PGM CLLE DEF及びCSVファイル転送及びCSV->EXCEL変換実行
11 CSVEXCELC *PGM CLLE PASE環境pythonスクリプトの実行 run_csvExcel2.shを実行
12 CSVEXCEL *CMD CSVEXCELCのコマンド
13 SAMPLE01 *PGM RPGLE サンプル01プログラム
14 SAMPLE01C *PGM CLLE サンプル01プログラム起動
15 SAMPLE01EX *PGM CLLE サンプル01プログラムからCALLされるCL このCLでIFSへの転送及びEXCEL変換を起動
16 UTLCATKNJ *PGM RPGLE 2つの漢字フィールド結合プログラム
17 UTLCHKSTS *PGM RPGLE IFS上のステータスファイルチェック パラメータで結果を返す。
18 UTLCNVEBC *PGM RPGLE 5026<->5035間のコード変換
19 UTLGETEXCL *PGM RPGLE EXCEL用パラメータ取得 EXCELのみ
20 UTLGETPRM *PGM RPGLE パラメータ取得 EXCEL以外
21 UTLHSTLOG *PGM RPGLE 実行履歴出力 実行履歴をCSVHSTLOGに出力
22 UTLOUTDEF *PGM RPGLE DEFファイルデータ出力
23 UTLSEPKNJ *PGM RPGLE 漢字フィールド分割 前述しています。
24 UTLTRMKNJ *PGM RPGLE 漢字フィールド後ろ空白削除
25 UTLXMLESC *PGM RPGLE XML文字エスケープ処理
26 XCHKFTP *PGM RPGLE FTP実行結果チェック
27 XCSVMSGF *FILE CSV->EXCELメッセージファイル
28 CSVCOL *FILE PF-DTA EXCELフィールド選択ファイル
29 CSVCOLL1 *FILE LF EXCELフィールド選択論理ファイル
30 CSVEDT *FILE PF-DTA EXCEL表示編集形式設定ファイル
31 CSVEXCLPRM *FILE PF-DTA パラメータファイル(EXCEL用)
32 CSVHSTLOG *FILE PF-DTA 実行履歴ファイル
33 CSVPARM *FILE PF-DTA パラメータファイル(EXCEL以外)
34 CSVTXT *FILE PF-DTA XMLテキストファイル用(DEF) レコード長:256バイト
35 CSVTXT2 *FILE PF-DTA CSVテキストファイル用 レコード長:450バイト
36 CSVTXT3 *FILE PF-DTA XMLテキストファイル用(BND) レコード長:256バイト
37 CXHNMP *FILE PF-DTA サンプル用商品マスター
38 SAMPLE01D *FILE DSPF サンプル01用表示装置ファイル
39 SQLWORK *FILE PF-DTA SQL文ワークファイル
40 CSVHSTLOG *CMD 実行履歴コマンド
41 UTLCSVESC *PGM RPGLE CSVエスケープ処理
2024.04.08追加
ダブルクォート文字エスケープ処理
42 UTLCSVESCD *CMD CSVエスケープ処理コマンド
2024.04.08追加
ダブルクォート文字エスケープ処理
43 TSTCSVESCD *FILE DSPF CSVエスケープ処理テスト画面
2024.04.08追加
ダブルクォート文字エスケープ処理テスト
44 TSTCSVESC *PGM RPGLE CSVエスケープ処理テスト
2024.04.08追加
ダブルクォート文字エスケープ処理テスト

・5のシェルスクリプト及びpythonスクリプトはIBM i_pythonスクリプト.zipに圧縮しています。

No ファイル名 ファイル内容 配置先
1 config.ini CSV→Excelファイル変換設定ファイル プログラムディレクトリー(例:/opt/excel)
2 csvExcel_log_conf.json ログ設定ファイル(pythonの実行ログ) 同上
3 mail_body.txt メールテンプレート 同上
4 csvExcel.py メインプログラム 同上
5 csvExceConst.py 定数定義 同上
6 csvExcelImportXml.py CSV及びXMLインポート 同上
7 csvExcelUtil.py ユーティリティ 同上
8 csvPivot.py ピボットテーブル及びグラフ作成 同上
9 chk_home_dir.sh /homeディレクトリーチェック 同上
10 run_csvExcel2.sh CSV->Excel実行シェル(内部でrun_csvExcel.shを実行) 同上
11 run_csvExcel.sh CSV->Excel実行シェル(実行本体) 同上

3-2.実行結果画面

3-2-1.ピボット集計・グラフ付き変換

3-2-2.複数のCSVを1つのEXCELファイルに変換

3-2-3.拡大図(複数のCSVファイルを1つのExcelファイルにまとめたもの)

3-2-4.拡大図(ピボット集計・グラフ描画)

3-3.プログラム概要

  • 1つのCSVファイルをExcel(.xlsx)ファイルに変換もしくは、複数のCSVファイルをExcel(.xlsx)ファイルに変換します。
  • CSVファイルが1つの場合は、Excelファイル変換時にピボット集計およびグラフ描画ができます。(※CSVファイルを複数まとめる場合はピボットおよびグラフは不可)
  • 変換したExcel(.xlsx)ファイルはメール送信もしくはftpサーバーへ転送します。
  • 図中の.DEFファイルは、CSVファイル変換時のExcelの表示形式やピボット集計及びグラフ生成方法が記述されたファイルです。
  • 1つのCSVファイルに対し1つのDEFファイルが必要です。
  • BNDファイルは、複数のCSVファイルを1つのExcelファイルにまとめる場合に使用します。(複数CSVファイルはシートで分かれます。)

弊社では、SQL-RPGⅢ、ILE-SQLRPGⅣで開発したプログラムでDEF及びCSVファイルを作成しています。下記画面がそのプログラム画面の一例になります。
下記画面は弊社で使用している得意先マスターEXCEL作成ですが他に商品、在庫等々があります。

データ(DEFファイルおよびCSVファイル)をQTEMPに出力し、XSNDFTPコマンドでIFS上のファイルへ転送します。詳細は後述します。(EBCDIC->JISコード変換および漢字コード変換もFTPで行います。この為、XSNDFTPコマンドで転送したファイルのコード変換は不要です。)

4. 機能

CSVファイル(シフトJIS)からExcel(.xlsx)ファイルへの変換

No 機能 内容 備考
1 ピボット集計・グラフ描画 集計する縦軸、横軸、集計項目を記述する事でピボット集計が可能。
集計した結果のグラフ表示が可能。
※別シートに作成します。
複数のCSVをExcelファイル変換する場合はピボット集計、グラフ描画不可
※シート数が多くなるため制約とした。
2 書式設定 テーブルスタイル及び数字項目表示形式を自動設定 1行置きに網掛けが行われる。データが見やすい。
3 列幅・フォント設定 データ変換時にフォントやセル列幅を設定 変換後のデータは見やすい。
4 複数CSVファイルを変換 複数のCSVファイルを1つのExcelファイルに変換 CSVファイルはシートで分かれます。
5 生成したExcelの送信 SMTPもしくはFTP送信 config,iniに記述します。
6 エラーログファイル(python) 指定した場所にログファイルを生成 エラー時対応に使用します。

※Excelプログラムは不要です。 ※文字コード: ANK:JIS, 漢字:Shift-JIS

※IBM i (Power10、1コア)Python3のExcelファイル変換の実行速度は52列×12,648行で約3分です。(ピボット集計あり、グラフ出力あり)

※SMBでのファイル送信も組込み可能(環境依存が大きい為、今回は組込まず。Pythonにはpysmbモジュールがあります。)

※PASE環境はAIX互換です。この為、文字コードはSJISがデフォルトです。(シェルを作成する場合に注意)

【重視した事】既存環境で動作する事を重視しました。これはCCSID=5026で動作する事を意味します。

サンプルプログラムの流れ

この動画は一番シンプル(簡単)な機能のみで作成したものです。ピボット集計やグラフ描画は組込んでいません。
また、本来の仕様では実装する予定はなかったのですが、見て分かりやすくするため、変換したExcelファイルを起動するところまで行っています。

5.1.(STEP1)5250画面から検索条件を入力

条件入力は2項目あります。

①商品コード範囲検索 通常の範囲検索です。入力された値からSQLのWHERE句にBETWEENで条件を生成します。

②商品名漢字検索 この漢字検索は空白で文字列を最大5つまで分割しWHERE句のLIKE 条件をANDで生成します。分割した文字列の末尾に%を自動付与します。2番目以降の分割した文字には文字列の先頭に%を自動付与します。

これは商品名に寸法や仕様が含まれている場合に使用すると便利です。この機能をサンプルとして実装したのは、弊社社内システムで実装し評価が良かったからです。

上記2つの条件からSQL文を生成し実行します。(→STEP2へ)

5.2. (STEP2) 検索結果画面

STEP1で入力された条件でSQLを生成し、出力したデータを1データ1行で画面に表示します。

▲ 1画面目

▲ 2画面目

①キーボードのF11を押すと、1画面で表示できなかったフィールドが表示されます。F14を押すと生成されたSQLが画面に表示されます。(デバッグに便利でした)

②F9を押すとExcel生成を開始します。

  • プログラムで生成したSQLはCSV生成プログラムで使用しますのでQTEMP/SQLWORKに保存しています。
  • CALL命令でSAMPLE01EXを実行し、実行に必要なパラメータを渡します。(ID, ユーザーID、WS番号、日付、時刻、BNDファイル名、DEFファイル名、CSVファイル名)
  • このCLでDEF及びCSVファイルを生成し、さらにCSVCVTEXCELをCALLします。
  • CSVCVTEXCLにてIFSへのファイル転送及びpythonでのCSV->EXCELファイル変換を実行します。

上記②は、甘く見ていていた事を非常に後悔しました。
PASE環境はAIX(いわゆるUNIX環境)ですので、英大文字小文字を区別します。この英大文字小文字は5035ベースのものですので、5026環境で使える英小文字ではありません。また5250のホストコードページの設定や実行しているジョブのCCCSIDにも影響されます。
最初、簡単に作成できると思い込んでおり、英小文字が文字化けした原因と解決方法に非常に悩み、時間もかかりました。
本来は、CCSIDを5035かその発展系である1399にすべきですが、変更する労力に比べると得られるメリットが少ない事と、5577エミュレーションプリンターで1399で新たに使用可能となった漢字が正しく印刷できるかの検証があるため見送ったのです。
2024年現在、プリンターメーカーに問い合せても5577関連では不明な事が多い状態です。5577エミュレーションの場合、旧JIS(1978)、新JIS(1980)の設定しかない事が多く1980以降に追加された漢字がどうなるか不明です。

③ EXCELファイルの起動

  • サンプル01では、PC (Windows10,11)にバッチファイルを導入する必要があります。(CSVEXCELFTP.BAT)
  • このバッチファイルをSTRPCCMDで呼ぶ事により生成したEXCELファイルをFTPにてPCにダウンロードし、EXCELを実行します。
  • この機能は、本来、実装を予定していませんでしたが、前述した通り変換後のEXCELファイルが最後に起動されると、分かりやすいと考えたからです。

上記③も、甘く見ていていた事を後悔しました。
CSV->EXCEL変換は、PASE環境でPythonを実行しますので、実行エラーは当然ありえる話です。
では、Pythonで発生したエラーをどうやってRPGやCLで把握するかになります。当初はRTVJOBAでRTNCDEがあるから使えるのではと思い、深く調査せずにCLでPASE環境のPythonを実行するシェルを、QP2SHELLでCALLした後に発覚しました。シェルでは正しくリターンコードを返しているのですが、このリターンコードはRPG及びCLでは取得できません。QP2SHELLのAPIも調査しましたが取得できませんでした。リターンコードはRPGやCLに戻してよ、と本当に言いたくなりました。結局、Pythonスクリプト終了時に成功・不成功情報をステータスファイル(テキストファイル)に出力するように変更しました。ILERPGでこのファイルを直接読込み、ASCII->EBCDIC変換後、*ENTRYのパラメータにセットしCLでエラー処理を実行するようにしました。
※「IBM i 7.5 プログラミング」の「IBM PASE for i」の最後に、PASE環境のsystemコマンドでリターンコードをCL変数に設定できると記述がありました。今回は、IBM i →PASEの流れで動作しています。systemコマンドPASE環境からCLを呼び出しますので、欲しい仕様ではありませんでした。

次回からは、実際のサンプルプログラムの流れについて、考慮点も交えてご説明してまいります。



(サンプルプログラムと注意事項)
当記事のサンプルプログラムをダウンロードいただけます。本サンプルプログラムは、学習・参考目的で提供されております。ダウンロードは可能ですが、動作保証は致しかねます。環境や条件により、正常に動作しない場合がありますので、予めご了承ください。また、使用に際しては自己責任で行っていただきますようお願い申し上げます。

サンプルプログラムのダウンロードはこちらから!
dengyo_1_dl

いいねと思ったらシェア
twitter
facebook
hatena
関連記事
人手不足対応への「採用」以外の取り組みとは
人手不足対応への「採用」以外の取り組みとは
Power10 プロセッサー搭載スケールアウトモデル登場
Power10 プロセッサー搭載スケールアウトモデル登場
IBM i 用の新しいODBCドライバー
IBM i 用の新しいODBCドライバー
あなたにオススメの連載
できるIBM i 温故知新編
9記事
できるIBM i 温故知新編
IBM i の”新”必須言語 〜FFRPG入門〜
14記事
IBM i の”新”必須言語 〜FFRPG入門〜
IBM i アプリの第二の柱 OSS
15記事
IBM i アプリの第二の柱 OSS
PAGE TOP