ExcelのVBAでCSVを読み込むとダブルクォーテーション内のカンマで列がずれるときの対処｜Splitを使わず引用符を考慮して分割する

VBAで Line Input を使ってCSVを1行ずつ読み、Split(行, ",") で列に分ける方法はよく使われます。ところが、金額の「"1,000"」や住所の「"東京都港区1-2-3, ABCビル"」のように、ダブルクォーテーションで囲まれた値の中にカンマがあると、そこで列が分かれて右側の列が全部ずれます。

この記事では、列がずれる原因を確かめたうえで、引用符の内側かどうかを判定しながら1行を分割する関数を作り、列数のチェックで取り込み結果を確認するところまでを説明します。

列がずれる原因

CSVでは、値の中にカンマを含めたいとき、その値全体をダブルクォーテーションで囲みます。値の中にダブルクォーテーション自体を含めたいときは、2つ重ねて "" と書きます。

顧客名,住所,金額
山田商店,"東京都港区1-2-3, ABCビル","1,000"

このファイルの2行目は、本来3列です。しかし Split は引用符を区別せず、すべてのカンマで区切ります。

列が増えるうえに、値に引用符も残ります。見出し行には引用符付きのカンマがないことが多いため、「見出しは正しいのに、一部のデータ行だけ右にずれる」という形で表れます。

まず、読み込みに使っているCSVをメモ帳などで開き、列がずれる行に "...,..." の形の値があるかを確認してください。該当すれば、この記事の方法で直せます。

引用符を考慮して1行を分割する関数

1文字ずつ読み進め、「いま引用符の内側にいるか」を覚えながら分割します。内側にいる間のカンマは区切りとして扱いません。

Public Function ParseCsvLine(ByVal lineText As String, _
                             Optional ByVal delimiter As String = ",") As Variant
    Dim fields() As String
    Dim fieldCount As Long
    Dim buf As String
    Dim inQuotes As Boolean
    Dim i As Long
    Dim ch As String

    ReDim fields(0 To 0)
    i = 1

    Do While i <= Len(lineText)
        ch = Mid$(lineText, i, 1)

        If inQuotes Then
            If ch = """" Then
                If Mid$(lineText, i + 1, 1) = """" Then
                    ' 引用符の中の "" は、1つの " として値に含める
                    buf = buf & """"
                    i = i + 1
                Else
                    ' 閉じの引用符
                    inQuotes = False
                End If
            Else
                buf = buf & ch
            End If
        Else
            If ch = """" Then
                inQuotes = True
            ElseIf ch = delimiter Then
                ReDim Preserve fields(0 To fieldCount)
                fields(fieldCount) = buf
                fieldCount = fieldCount + 1
                buf = ""
            Else
                buf = buf & ch
            End If
        End If

        i = i + 1
    Loop

    ReDim Preserve fields(0 To fieldCount)
    fields(fieldCount) = buf
    ParseCsvLine = fields
End Function

動きを整理すると次のとおりです。

• 引用符の外で " が来たら、内側に入る
• 内側で "" が来たら、値に " を1つ加えて、内側にとどまる
• 内側で " が1つだけ来たら、外側に戻る
• 外側でカンマが来たら、そこまでを1列として確定する
• 行末に来たら、残りを最後の列として確定する

戻り値は0始まりの配列です。囲みの引用符は取り除かれるため、"1,000" は 1,000 という文字列になります。

タブ区切りのファイルなら、ParseCsvLine(lineText, vbTab) のように区切り文字を指定して使えます。

引用符が閉じていない行を見つける関数

1行の中のダブルクォーテーションの数が奇数なら、その行の中で引用符が閉じていません。値の中に改行が含まれていて、1件のデータが複数行に分かれて読まれている可能性があります。この場合は分割の問題ではなく行の読み方の問題なので、まず見つけて記録できるようにしておきます。

Private Function HasUnclosedQuote(ByVal lineText As String) As Boolean
    HasUnclosedQuote = ((Len(lineText) - Len(Replace(lineText, """", ""))) Mod 2 = 1)
End Function

値の中の "" は2個として数えられるため、正しく閉じている行では合計が偶数になります。

読み込みと列数チェックを組み合わせる

見出し行の列数を基準にして、各行の列数が一致するかを確かめながらシートに書き込みます。列数が違う行と、引用符が閉じていない行は「列数確認」シートに一覧で記録します。

事前に「取込」と「列数確認」という名前のシートを用意しておきます。

Sub ImportCsvWithQuotes()
    Dim filePath As String
    Dim f As Integer
    Dim lineText As String
    Dim fields As Variant
    Dim colCount As Long
    Dim expectedCols As Long
    Dim lineNo As Long
    Dim c As Long
    Dim ws As Worksheet
    Dim wsChk As Worksheet
    Dim chkRow As Long

    filePath = "C:\data\customers.csv"

    Set ws = ThisWorkbook.Worksheets("取込")
    Set wsChk = ThisWorkbook.Worksheets("列数確認")

    ws.Cells.Clear
    ws.Cells.NumberFormat = "@"
    wsChk.Cells.Clear
    wsChk.Columns("C").NumberFormat = "@"
    wsChk.Range("A1:D1").Value = Array("行番号", "列数", "引用符未閉じ", "元の行")
    chkRow = 1

    f = FreeFile
    Open filePath For Input As #f

    Do While Not EOF(f)
        Line Input #f, lineText
        lineNo = lineNo + 1

        fields = ParseCsvLine(lineText)
        colCount = UBound(fields) + 1
        If lineNo = 1 Then expectedCols = colCount

        If colCount <> expectedCols Or HasUnclosedQuote(lineText) Then
            chkRow = chkRow + 1
            wsChk.Cells(chkRow, 1).Value = lineNo
            wsChk.Cells(chkRow, 2).Value = colCount
            wsChk.Cells(chkRow, 3).Value = HasUnclosedQuote(lineText)
            wsChk.Cells(chkRow, 4).NumberFormat = "@"
            wsChk.Cells(chkRow, 4).Value = lineText
        End If

        For c = 0 To UBound(fields)
            ws.Cells(lineNo, c + 1).Value = fields(c)
        Next c
    Loop

    Close #f

    MsgBox "読み込んだ行数：" & lineNo & vbCrLf & _
           "見出しの列数：" & expectedCols & vbCrLf & _
           "確認が必要な行：" & (chkRow - 1)
End Sub

取込シート全体の表示形式を文字列（@）にしてから書き込んでいるのは、商品コードや郵便番号の先頭の0が消えたり、日付に変換されたりするのを防ぐためです。

このコードは1セルずつ書き込むため、行数が多いファイルでは時間がかかります。大きなファイルでは、配列にまとめてからシートへ一括で貼り付ける方法に切り替えてください。

金額を数値として扱いたい場合

分割後の 1,000 は桁区切りのカンマを含む文字列です。金額の列だけ数値にしたい場合は、カンマを取り除いてから変換します。たとえば金額が3列目（配列の添字2）なら、書き込みのループの後に次のように上書きします。

Dim amountText As String
If lineNo > 1 Then
    amountText = Replace(fields(2), ",", "")
    If IsNumeric(amountText) Then
        ws.Cells(lineNo, 3).NumberFormat = "#,##0"
        ws.Cells(lineNo, 3).Value = CDbl(amountText)
    End If
End If

Dim はプロシージャの先頭にまとめて書いても構いません。コードや番号のように先頭の0に意味がある列は、数値に変換せず文字列のままにしておきます。

取り込み結果を確認する

マクロを実行したら、次の順で確認します。

1. 列数確認シート：見出し行だけで、データ行がなければ、すべての行が見出しと同じ列数で分割されています。
2. 読み込んだ行数：メッセージの行数が、CSVの行数と一致するか。メモ帳などで最終行の行番号を確認して比べます。
3. 引用符付きの値があった行：住所の列に「, ABCビル」まで1つのセルに入っているか、金額の列に「1,000」が入っているかを目で確かめます。
4. 引用符が残っていないか：値の先頭や末尾に " が残っているセルがあれば、その行の元データを確認します。

列数確認シートに行が出た場合

値の中の改行や改行コードによる行のずれは、行の分割ではなく行の読み方の問題で、この記事の関数だけでは直りません。また、UTF-8のファイルを Open ... For Input で読むと文字化けすることがあります。これらは文字コードや改行の扱いを指定できる別の読み込み方法で対処してください。

Splitを使い続けてよい場合

次の条件がすべてそろっているなら、Split でも列はずれません。

• 出力元の仕様で、値にカンマが入らないことが決まっている
• 引用符で囲まれた値がない
• 区切り文字が値に含まれないことを、取り込み前に確認できる

条件がそろわない場合や、相手から受け取るCSVの中身を管理できない場合は、ここで紹介した分割関数と列数チェックを使うほうが安全です。