続　全銀固定長データ～複数件データを読む（Power Query）

9539 ワード

PowerBI Excel PowerQuery Excel テキストリンク

前回の記事では単データのみに対応していました。結局、複数件のデータにも挑戦しましたので、書き残しておきます。

前回の記事：全銀固定長データを1件だけ読んでみた

複数件の固定長データとは（筆者の理解）

全銀固定長データは下記の4区分で構成されています。最初の1文字目でどの区分か判別できるように設計され、それぞれ120字を1単位とします。

ヘッダーレコード（1始まり）
データレコード（2始まり）
トレーラーレコード（8始まり）
エンドレコード（9始まり）

調べたところ、複数件となる場合でも1ファイルの中に納めるものの、その納め方について下記の2タイプが存在することが分かりました。

データレコードだけを繰り返すタイプ
ヘッダレコードからトレーラーレコードまでの3区分のセットを繰り返すタイプ（通称：マルチファイル）

本記事では、読もうとしているデータが2タイプのうち、どちらなのか判別し、どちらでも読めるよう、作ってみました。

クエリ全体の構成

材料1：固定長データのテキスト
材料2：桁指定マスタ（前回と同じ）
カスタム関数（サブ）：桁指定マスタに従ってテキストのリストをテーブルで返すもの（ファイルパーサ）
カスタム関数（メイン）：固定長データが要件どおりか、マルチファイルか判別の上、ファイルパーサを実行する（fx_ファイル読取）

コード

固定長データ

前回の記事と異なり、複数件のデータを入れています。テスト用に各タイプ1個ずつ作りみました。
また、作成の都合上、改行コードを入れてます。したがって、変換する過程で改行を除去してから変換していきます。
※詳細エディタに貼ると文字列になるはずです。

固定長データ_1

let
    ソース = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNFCAg/d797zfN+/93jXv93W93zf//b6O9/sa3u+b/X7vZgX8wMDCwNDAwNgYXdgAXcTQ0sDAyARDoYKCEVHaEUZYmLzf1/x+7973+1re7933ft/c9/umAd2p8X7vNuwuBANDEIHXJ0qxOtFKo44ZdQwJjrFAqIMx8KqnFrCkiy2YQCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [列1 = _t]),
    カスタム1 = Text.Combine(ソース[列1])
in
    カスタム1

固定長データ_2

let
    ソース = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("7ZExCgIxEEWvElJZWEyyW2TPEnIXQQvxADaiXsBOsHLmSv8KroG4sBuChVjNK0IYJjM/vBit847MB/ATcgHfIAfIFbKHbCAn8N20oUCOqOvmZZpX3EDk+0WjMTato/VfzZjmhB6yBTNkBxbIGXIcw67Aj3rMjHsfze/kMGFqLpfmo1+hTqqok4w6KaiTKuoko04Kw1+2LLEpvQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [列1 = _t]),
    変更された型 = Table.TransformColumnTypes(ソース,{{"列1", type text}}),
    カスタム1 = Text.Combine(変更された型[列1])
in
    カスタム1

桁指定シート（前回の記事の再掲）

桁指定シート

let
    ソース = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZTbToNAEIbfhWsuoPUQn4X0XQgbBZSG1thSbUxKbS0apU1MNFmxPswyHK76Ci6tAZp2YbkggeX7d2bnn1lFEQjSCQqI9gsWBuNKEAWZPgSNCEIEqdk/9Ea0j+2LKXRERYg9H4x5sSYKrTpJ/sUfJbKWyToI14/pJAgxLoeTpYZi6HWp4oRTFjnzjOaD4RKl12riWuUEm2p3+cmn3LLobgnYKYdsN9Q2DplOeqnep97HY5/fPyoE+wnwIh68gv1FRed1whAboN1nIWpRVvP+rx7wLKeq+b1SsVCWI9U839bmDfxMQzyL7HFkqnkla3I/5hcLPuoRG3bAHuZj1Zaqs9/2AU0ldbvF7DLp4Sp5tveGvfqU7gL8ab62Cfr1MQ40txx57W4GSwf/gbPZkvcRvSYT14tnuBbOm7669KyGNzIyw16OjQn0jMQzwu/PaLCi+Bm3pHCtxaEpBlfiyYpxFs0jiGJmxfbyBRvs/AE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [項目名 = _t, 桁数 = _t, 区分 = _t]),
    変更された型 = Table.TransformColumnTypes(ソース,{{"項目名", type text}, {"桁数", Int64.Type}, {"区分", type text}})
in
    変更された型

カスタム関数（サブ）

読み取った結果をテーブルで返します。
引数の「対象リスト」というのは、読み取る区分名のテキストをリストにしたもので、M言語なら、

{"ヘッダーレコード","データレコード"}

というようなものです。複数件データを読む場合、4区分をまとめて使う場面がないので、区分を指定し、その区分の桁マスタで分割します。

ファイルパーサ

(対象リスト as list,固定長のリスト as list)=>
let
    桁指定 =Table.SelectRows( 桁指定シート, each List.Contains(対象リスト,[区分]) ),
    分割引数セット =[分け方指示 = List.Generate(()=>[index=0,累計桁数=0],
                                           each [index]<=List.Count(桁指定[桁数]),
                                           each [index=[index]+1,累計桁数=[累計桁数]+桁指定[桁数]{[index]}],
                                           each [累計桁数]
                             ),

                   //分け方指示とぴったり同じ数の列で入るのだが、最後に空列ができないとエラーになる。
                   名前リスト = List.Transform(Table.ToRecords(桁指定),
                                              each Text.Range(_[区分],0,3)&"_"&_[項目名]
                               )&{"ダミー"}
                   ],
    リストの各要素の分割 = Table.FromList(固定長のリスト,
                                        Splitter.SplitTextByPositions(分割引数セット[分け方指示],false),
                                        分割引数セット[名前リスト]),
    ダミー列を削除 = Table.RemoveColumns(リストの各要素の分割,{"ダミー"})
in
    ダミー列を削除

カスタム関数（メイン）（fx_ファイル読取）

既述のサブ関数「ファイルパーサ」が作成されていることを前提にしています。

fx_ファイル読取

(Source as text)=>
let
    //改行の入れ方は数パターンはあれど、cr,lfの2種をそれぞれ除去すれば、全パターンを消せる。
    改行削除 = Text.Remove(Source,{"#(cr)","#(lf)"}),
    ファイルパターン = List.Alternate( Text.ToList(改行削除),120-1,1,1),//ファイル種類の判別用。

    ファイル評価 = if Number.Mod(Text.Length(改行削除),120) > 0 then "文字数エラー：パターン不正です"
        else
        //3区分目の最初の文字でファイル型を識別する
        if ファイルパターン{2}="8" then 
            //マルチファイルパターンとの一致を確認
            if ファイルパターン =List.Repeat( List.Range({"1","2","8"},0,3),
                                             (List.Count(ファイルパターン)-1)/3 )
                               &{"9"} then
                let
                マルチリスト =Splitter.SplitTextByRepeatedLengths(360)(改行削除),
                マルチ完成物 =[主データ =ファイルパーサ({"ヘッダーレコード","データレコード","トレーラーレコード"},
                                                     List.RemoveLastN(マルチリスト,1)
                                        ),
                              エンド =ファイルパーサ({"エンドレコード"},{List.Last(マルチリスト)})
                             ]
                in
                マルチ完成物
            else
                "マルチファイル：パターン不正です"
        else
            //非マルチファイルパターンとの一致を確認
            if ファイルパターン ={"1"}
                                &List.Repeat( {"2"},(List.Count(ファイルパターン)-3) )
                                &{"8","9"} then
                let
                その他リスト =Splitter.SplitTextByRepeatedLengths(120)(改行削除),
                その他完成物=[ヘッダ   =ファイルパーサ( {"ヘッダーレコード"},  {その他リスト{0}} ),
                             データ   =ファイルパーサ( {"データレコード"},  List.Range(その他リスト,1,List.Count(その他リスト)-3) ),
                             トレーラ =ファイルパーサ( {"トレーラーレコード"},List.Range(その他リスト,List.Count(その他リスト)-2,1) ),
                             エンド   =ファイルパーサ( {"エンドレコード"},{List.Last(その他リスト)})
                            ]
                in
                その他完成物
            else
                "非マルチファイル：パターン不正です。"
in
    ファイル評価

実行

実行方法

数式バーに下記のようなコードを貼ります。

=fx_ファイル読取(固定長データ_1)

結果

引数を「固定長データ_1」にした場合（タイプ1の結果）
区分別の1フィールドを割当、それぞれにテーブルが格納されています。データレコードのフィールドには無事、複数件データが入りました。（1行1データ）

引数を「固定長データ_2」にした場合（タイプ2：マルチファイルの結果）
主データには、ヘッダレコード、データレコード、トレーラーレコードの3つをまとめてパースして得たテーブルが入ります。こちらも、複数件データが入ています。

おまけ：関数解説など

Splitter.SplitTextByRepeatedLengths()

今回取り組む際に、@PowerBIxyz さんにSplitter関数の使い方を教わりましたので、早速使いました。

例

Splitter.SplitTextByRepeatedLengths(4)(Text.Combine({"A".."Z"}))

結果

引数を入れて、ようやく関数になるものです。試してはいないですが、他のSplitter関数群や、Combiner関数群、Comparer関数群でも同様の使い方ができると思われます。

List.Alternate()

なかなか使いづらいので、念のため。

例

List.Alternate({1..1000},119,1,1)

結果

リストを0始まりで数えた1番目の要素から、「119個飛ばして、その次の1個は残す」――というのをリストの終わりまで繰り返しているわけです。

Author And Source

この問題について(続　全銀固定長データ～複数件データを読む（Power Query）), 我々は、より多くの情報をここで見つけました https://qiita.com/tanuki_phoenix/items/d15a16d7eca96a059f5a

著者帰属：元の著者の情報は、元のURLに含まれています。著作権は原作者に属する。

Content is automatically searched and collected through network algorithms . If there is a violation . Please contact us . We will adjust (correct author information ,or delete content ) as soon as possible .