固定長レイアウトから MIDB式/M言語を作るツール
COBOL帳票などの固定長データを、レイアウト定義書の「開始位置・バイト数」からExcelの式にするツールです。MIDB式とPower QueryのM言語コードを同時に作り、サンプル行で結果を確認できます。
🧮 できること
固定長データのレイアウト(項目名・開始位置・バイト数・型)を入れると、ExcelのMIDB式と、Power QueryのM言語のコードを作ります。サンプルの1行を貼ると、切り出し結果もその場で確認できます。
入力したデータは外部に送信しません。すべてこのページの中(お使いのブラウザ内)で計算します。それでも、本番データではなくテスト用のデータで試してください。
ツール
Excelの式(項目ごとに、右隣の列へ貼ります)
| 項目 | 式 | サンプルの結果 |
|---|
Power Query(M言語)
先に、下の fx_MIDB を空のクエリとして作り、クエリ名を fx_MIDB にします。次に、データのクエリの詳細エディターで、最後のステップの後に「列の追加」のコードを続けます。
作られる式の解説
Excelの式(MIDB)
基本の形は =TRIM(MIDB(A2,開始位置,バイト数)) です。
- MIDB:文字列の指定したバイト位置から、指定したバイト数だけ取り出します。日本語版のExcelでは、全角は2バイト、半角は1バイトとして数えます。レイアウト定義書がバイトで書かれている固定長データと相性がよい関数です。
- TRIM:前後の余白を取ります。固定長データは桁をそろえるために空白で埋められていることが多いためです。全角スペースが残る場合は
SUBSTITUTE(…," "," ")を挟みます(MID / MIDB 関数の記事)。 - 数値:
=VALUE(MIDB(…))で、ゼロ埋めの「000012345」を数値の 12345 にします。 - 日付8(YYYYMMDD):
=DATE(MIDB(…,1,4),MIDB(…,5,2),MIDB(…,7,2))の形で、年・月・日を取り出して日付にします。セルの表示形式を日付にしてください。
MIDBは非推奨です。Microsoftは MIDB を非推奨としています。今すぐ使えなくなるわけではありませんが、新しく作る仕組みでは、下のPower Query版も検討してください。
Power Queryのコード(fx_MIDB)
- Text.ToBinary(文字列, 932):文字列をShift_JIS(コードページ932)のバイト列に変えます。
- Binary.Range(バイト列, 開始, 長さ):バイト単位で切り出します。開始は0から数えるため、関数の中で1を引いてMIDBと同じ数え方に合わせています。
- Text.FromBinary(バイト列, 932):切り出したバイト列を文字列に戻します。
- 列の追加は
Table.AddColumnで、数値はNumber.FromText、日付は#dateで型を変えています。
詳しい仕組みは M言語でMIDB相当の自作関数を作る で解説しています。
このツールのバイト数の数え方(全角2・半角1)は、CP932の多くの文字に合わせた目安です。外字・機種依存文字・絵文字などは数え方が変わることがあります。環境により結果が異なる場合があります。必ず実データのコピーで結果を照合してください。