固定長テキストを読み込んで列に分割する（全角を含むバイト数での切り出しと文字コード）

どんな場面で使うか

銀行や保険、製造業などの基幹システムでは、データが CSV ではなく「固定長」のテキストで出力されることがあります。固定長とは、「1〜6バイト目が社員コード、7〜26バイト目が氏名」のように、項目ごとの位置と長さが決められている形式です。区切り文字がないため、そのまま Excel で開くと1行が1つのセルに入ってしまいます。

Excel のテキストファイルウィザードでも区切り位置を指定できますが、毎月同じ作業をするならマクロにしておく方が確実です。この記事では、全角文字を含む固定長データを正しく分割する方法を説明します。

全角文字で位置がずれる理由

固定長のレイアウトは、多くの場合 Shift_JIS での「バイト数」で決められています。Shift_JIS では半角文字が1バイト、全角文字が2バイトです。一方、VBA の Mid 関数は「文字数」で数えるため、氏名に全角文字が入っていると、それ以降の項目の位置がずれてしまいます。

では MidB を使えばよいかというと、そのままでは解決しません。VBA の文字列は内部で Unicode（UTF-16）として扱われ、半角も全角も1文字2バイトになるため、MidB のバイト数も Shift_JIS とは一致しません。そこで、StrConv で一度 Shift_JIS のバイト列に変換してから MidB で切り出し、再び Unicode に戻すという手順を取ります。

コード

次のレイアウトの固定長ファイル（1行40バイト）を読み込み、「取込」シートに書き出します。先頭1バイトのレコード区分が「2」（データ行）の行だけを対象にし、ヘッダー行やトレーラー行は読み飛ばします。長さが合わない行は件数を数えて最後に知らせます。

• 1バイト目：レコード区分（1=ヘッダー、2=データ、9=トレーラー）
• 2〜7バイト目：社員コード（6バイト）
• 8〜27バイト目：氏名（20バイト、全角を含む、後ろは空白埋め）
• 28〜30バイト目：部署コード（3バイト）
• 31〜40バイト目：金額（10バイト、前ゼロ埋め）

Option Explicit

Sub ImportFixedLength()
    Const REC_LEN As Long = 40
    Dim filePath As Variant, fn As Long
    Dim rowText As String, sjis As String
    Dim results As Collection, outArr() As Variant, rec As Variant
    Dim ws As Worksheet, i As Long, badCnt As Long

    filePath = Application.GetOpenFilename("テキストファイル (*.txt;*.dat),*.txt;*.dat")
    If VarType(filePath) = vbBoolean Then Exit Sub

    Set results = New Collection
    fn = FreeFile
    Open filePath For Input As #fn
    Do Until EOF(fn)
        Line Input #fn, rowText
        sjis = StrConv(rowText, vbFromUnicode)             ' Shift_JIS のバイト列にする
        If LenB(sjis) = REC_LEN Then
            If SjisPart(sjis, 1, 1) = "2" Then             ' データ行だけを対象にする
                results.Add Array( _
                    SjisPart(sjis, 2, 6), _
                    Trim(SjisPart(sjis, 8, 20)), _
                    SjisPart(sjis, 28, 3), _
                    CCur(Val(SjisPart(sjis, 31, 10))))
            End If
        ElseIf Len(rowText) > 0 Then
            badCnt = badCnt + 1                            ' 長さが合わない行
        End If
    Loop
    Close #fn

    Set ws = ThisWorkbook.Worksheets("取込")
    ws.Cells.Clear
    ws.Range("A1:D1").Value = Array("社員コード", "氏名", "部署コード", "金額")
    ws.Range("A:A,C:C").NumberFormat = "@"                 ' 先頭の0を残すため文字列にする
    If results.Count > 0 Then
        ReDim outArr(1 To results.Count, 1 To 4)
        For i = 1 To results.Count
            rec = results(i)
            outArr(i, 1) = rec(0): outArr(i, 2) = rec(1)
            outArr(i, 3) = rec(2): outArr(i, 4) = rec(3)
        Next i
        ws.Range("A2").Resize(results.Count, 4).Value = outArr
    End If
    MsgBox results.Count & " 件を取り込みました（長さ不一致：" & badCnt & " 行）"
End Sub

' Shift_JIS のバイト列から、開始バイト・バイト数を指定して切り出す
Private Function SjisPart(ByVal sjis As String, ByVal startByte As Long, ByVal byteLen As Long) As String
    SjisPart = StrConv(MidB(sjis, startByte, byteLen), vbUnicode)
End Function

文字コードと改行の注意

Line Input は、Windows の既定の文字コード（日本語環境では Shift_JIS）としてファイルを読み込みます。ファイルが UTF-8 の場合や、改行が LF だけの場合は正しく読めません。そのような場合は ADODB.Stream で文字コードを指定して全体を読み込み、改行で分割します。読み込んだ後は、各行を上のコードと同じように StrConv で Shift_JIS のバイト列にして切り出します。ただし、UTF-8 では全角文字が3バイトになるため、レイアウトが UTF-8 のバイト数で定義されている場合は、切り出し方も別に考える必要があります。

Dim stm As Object, allText As String, recTexts As Variant
Set stm = CreateObject("ADODB.Stream")
stm.Type = 2                       ' テキストモード
stm.Charset = "shift_jis"          ' UTF-8 の場合は "utf-8"
stm.Open
stm.LoadFromFile filePath
allText = stm.ReadText
stm.Close
allText = Replace(allText, vbCrLf, vbLf)
recTexts = Split(allText, vbLf)    ' 0 始まりの配列。各要素が1行

取り込みでつまずきやすい点

社員コードや部署コードは「000123」のように先頭に0が付くことが多く、そのままセルに書き込むと数値の 123 になってしまいます。コードの列は、書き込む前に表示形式を文字列にしておきます。金額は前ゼロ埋めの文字列なので、Val で数値に変換しています。システムによっては符号を末尾に付けたり、ゾーン形式で表したりするため、レイアウト定義書を確認して変換方法を合わせてください。

また、ファイルの末尾に終端を表す制御文字が入っているシステムもあります。この記事のコードでは長さが合わない行として数えるだけで、取り込みは続けます。長さ不一致の件数が想定より多い場合は、レイアウトの理解や文字コードの指定が誤っている可能性が高いため、元のファイルを確認してください。

• 切り出しは Shift_JIS のバイト列にしてから MidB で行う
• コード列は文字列として書き込み、先頭の0を残す
• レコード区分でヘッダー・トレーラーを読み飛ばす

ほかの記事