振込データをExcelで全銀フォーマットに変換するには

従業員の給与から天引きする特別徴収の個人住民税について、その代行納付を銀行のEBサービスで依頼するため、振込データを全銀フォーマットで作成した。
これは複数の振込を一括で行う際に利用できる形式で、FBデータとも呼ばれる。

【参考】メガバンクのEBサービス

銀行名	サービス名
三菱UFJ銀行	BizSTATION
みずほ銀行	みずほe-ビジネスサイト
三井住友銀行	Web21

全銀フォーマットはExcelでも簡単に作れるので、そのやり方を紹介しよう。

全銀フォーマットとは

全銀プロトコル（企業・銀行間のデータ交換手順）でデータ伝送を行うために全国銀行協会が定めたファイルフォーマットのこと。
ヘッダレコード、データレコード、トレーラレコード、エンドレコードで構成される固定長120バイトのレコード集合からなる。
金融機関によってはCSV形式でも受け付けてくれるが正式にはテキスト形式である。

レコードフォーマット	出力順序	出力回数
ヘッダレコード	1	1回
データレコード	2	複数回（入力件数の数だけ繰り返す）
トレーラレコード	3	1回
エンドレコード	4	1回

例えば大分銀行の場合（今回使うのは３.住民税納付）
https://www.oitabank.co.jp/business/businessdirect/pdf/zengin_format.pdf

日付は和暦なので注意。この辺がいかにも銀行らしいが、元データが西暦であれば変換を要するということだ。

文字コードは JIS8単位コード（JIS X 0201）といって、8ビットで半角英数字と半角カタカナを表す大昔から存在する文字コードだ。Shift-JIS の半角カナとコードエリアを共有するので、全角文字が使えないことだけ意識していれば普通に使うことができる。EBCDIC という文字コードも指定できるが、これはパソコンが普及する以前のコンピュータ（ホストコンピュータとか汎用機とかメインフレームなどと呼ばれる）が使う文字コードだ。パソコンで使うことは無い。

パソコン（個人向け小型コンピュータ）だって汎用的なのに、なぜ大型コンピュータを汎用機と呼ぶのか？それを理解するにはさらに歴史を遡る必要がある。汎用機が登場する以前、コンピュータは商用計算（十進演算）や科学技術計算（浮動小数点演算）など、目的別に専用機を作って利用していた。それが、ソフトウェアを書き換えるだけで、あらゆる業務をひとつのコンピュータで処理できるようになる。これが汎用機の始まりであり、現在でも汎用機と呼ばれる所以である。今となっては違和感があるが、そういうものだ。

Excelツールの仕様

のようなシートに作成されたExcelファイルを、

のような固定長120バイトのテキストファイルに変換する。

要は、インターフェース仕様書で『C（文字）』と定義された項目は左詰めのスペースパディング、『N（数字）』と定義された項目は右詰めのゼロパディングで出力すれば良い。なお、IT業界では、○○パディングは埋めることを意味し、逆に抜くことは○○サプレスと表現する。「社員番号はゼロサプレスで入力してね」と言われたら「あー、先頭のゼロを抜くんだな」と思えば良い。

実装

Excelのマクロ言語（VBA）で実装する。

全角文字が含まれていないかチェック

全角文字は使えない仕様なので、まず、全角文字が含まれていないかチェックする関数を準備する。
先頭から１文字ずつ文字コードをチェックしても良いが、もっと簡単に、UnicodeからShift-JISに変換した文字列の総バイト数が、元の文字数と等しくなければ、全角文字を含むと判断することにした。

' 全角文字が含まれていたらTrueを返す
Private Function 全角チェック(s As String) As Boolean
    全角チェック = (Len(s) <> LenB(StrConv(s, vbFromUnicode)))
End Function

これとは逆に、半角文字が含まれていないかをチェックするなら、総バイト数が、元の文字数の２倍と等しいかで判断すれば良い。

テキストファイルに出力

VBAでテキストファイルに出力するならScripting.FileSystemObjectを使うのが分かり易いと思うが、ここではBASIC言語が古くから備えるレガシーなステートメント、Open ～ For Outputで出力する。

VBAにはfinallyも無ければcontinueも無いので、エレガントなコードにならないのは仕方がない。

Open sフォルダ & sファイル名 For Output As #1

' 【ヘッダレコード】の組み立て
' ヘッダ情報は別途定義されているものとする。
' 三井住友銀行などでは "1990" で始まるが、みずほ銀行では "1970" で始まる。
' 全銀フォーマット（レイアウト）は共通だが、種別コードは金融機関によって異なるので確認すること。
sLine = "1990" & s委託 & s支店番号 & s期日 & s年月 & s特徴 & Space(3)
If Len(sLine) <> 120 Then
    Close #1
    MsgBox "総桁数異常 1", vbCritical
    Exit Sub
End If

' 【ヘッダレコード】出力
Print #1, sLine

i = 2: sSpc = Space(50)
Do Until ws住民税.Cells(i, 1) = "合計"  ' ws住民税は住民税データのあるWorksheetオブジェクトである

    ' 【データレコード】の組み立て
    s市町村コード = Format(ws住民税.Cells(i, 1), "000000")
    s市町村名 = Left(ws住民税.Cells(i, 2).Text & sSpc, 15)
    s指定番号 = Left(ws住民税.Cells(i, 3).Text & sSpc, 15) '指定番号は数字とは限らない
    s異動有無 = Format(ws住民税.Cells(i, 4), "0")
    s給与件数 = Format(ws住民税.Cells(i, 5), "00000")
    s給与金額 = Format(ws住民税.Cells(i, 6), "000000000")
    s退職件数 = Format(0, "00000")  '今回はゼロ固定
    s退職金額 = Format(0, "000000000")  '今回はゼロ固定
    s合計件数 = Format(ws住民税.Cells(i, 5) + 0, "00000")
    s合計金額 = Format(ws住民税.Cells(i, 6) + 0, "000000000")
    s退職人員 = Format(0, "000")  '今回はゼロ固定
    s支払金額 = Format(ws住民税.Cells(i, 7), "0000000000")
    s市町村税 = Format(ws住民税.Cells(i, 8), "000000000")
    s都府県税 = Format(ws住民税.Cells(i, 9), "000000000")

    If 全角チェック(s市町村名) Then
        MsgBox "市町村名に全角文字が含まれています", vbCritical
        Exit Sub
    End If
    If 全角チェック(s指定番号) Then
        MsgBox "指定番号に全角文字が含まれています", vbCritical
        Exit Sub
    End If

    sLine = "2" & s市町村コード & s市町村名 & s指定番号 & s異動有無 & s給与件数 & s給与金額 & s退職件数 & s退職金額 _
                & s合計件数 & s合計金額 & s退職人員 & s支払金額 & s市町村税 & s都府県税 & Space(9)

    If Len(sLine) <> 120 Then
        Close #1
        MsgBox "総桁数異常 2", vbCritical
        Exit Sub
    End If

    ' 【データレコード】出力
    Print #1, sLine

    i = i + 1
Loop

' 【トレーラレコード】の組み立て
s給与件数T = Format(ws住民税.Cells(i, 5), "0000000")
s給与金額T = Format(ws住民税.Cells(i, 6), "00000000000")
s退職件数T = Format(0, "0000000")
s退職金額T = Format(0, "00000000000")
s合計件数T = Format(ws住民税.Cells(i, 5) + 0, "0000000")
s合計金額T = Format(ws住民税.Cells(i, 6) + 0, "00000000000")

sLine = "8" & s給与件数T & s給与金額T & s退職件数T & s退職金額T & s合計件数T & s合計金額T & Space(65)

If Len(sLine) <> 120 Then
    Close #1
    MsgBox "総桁数異常 3", vbCritical
    Exit Sub
End If

' 【トレーラレコード】出力
Print #1, sLine

' 【エンドレコード】の組み立て
sLine = "9" & Space(119)

' 【エンドレコード】出力
Print #1, sLine

Close #1

文字列を左詰めで表示する際にLeft関数を使っているが、Format関数を使っても良い。

CRLFではなくLFで改行する場合

金融機関によっては改行コードが指定されている場合がある。
CRはCarriage Returnの略で、コードは0x0D、
LFはLine Feedの略で、コードは0x0Aである。
WindowsならCRLFが既定で、MacOSのバージョン9までならCRが既定だ。
Windowsで改行コードにLFを使うなら次のようにする。

Print #1, sLine & vbLf;

ちなみに、UNIX系のOSだと改行コードはLFになる。

改行コードが要らない場合

改行コードは、有っても無くても、受け入れてくれる場合が多いが、改行コードを付けるとダメな金融機関もある。
その場合、最後にセミコロンを付ければOKだ。

Print #1, sLine;

ファイルの最後にEOFを付ける場合

EOFとはEnd Of Fileの略で、ファイルの終わりを示す制御コードのこと。
ファイルサイズを管理しない古いシステムでは必要なので、金融機関によっては指定される場合がある。
その場合、最後に0x1Aを出力すれば良い。

Print #1, Chr(&H1A);

市町村名について

給与ソフトによっては、市町村名を漢字で出力するものがあるが、全銀フォーマットは漢字が使えない。
この場合は、総務省のデータを使って、市区町村コードから半角カナの市町村名を求める必要がある。