ExcelのVBAでCSVをシートに読み込む方法｜QueryTablesで文字コードと列の型を指定する

VBAでCSVをシートに取り込む方法はいくつかあります。文字コード・区切り文字・列ごとのデータ型をまとめて指定したい場合は、QueryTables.Add が扱いやすい方法です。テキストファイルウィザードで手作業で指定する内容を、コードで固定できます。ここでは、指定したシートにCSVを読み込み、取り込んだ行数を確認するところまでを作ります。

ほかの方法との違いも押さえておきます。

• Workbooks.Open：CSVを手軽に開けますが、文字コードや列の型を細かく指定できません。
• Workbooks.OpenText：文字コードや列の型を指定できますが、新しいブックとして開きます。既存のシートに入れるには転記が必要です。

完成形のコード

標準モジュールに貼り付け、先頭の定数を自分の環境に合わせて書き換えます。例として、「顧客コード, 顧客名, 取引日, 金額」の4列を持つCSVを「取込」シートに読み込みます。

Option Explicit

Sub ImportCsvToSheet()
    Const CSV_PATH As String = "C:\data\sales.csv"
    Const SHEET_NAME As String = "取込"
    Const CODE_PAGE As Long = 65001   ' UTF-8。Shift_JISなら932

    Dim ws As Worksheet
    Dim qt As QueryTable
    Dim expected As Variant
    Dim importedRows As Long
    Dim fileLines As Long
    Dim i As Long

    If Dir(CSV_PATH) = "" Then
        MsgBox "ファイルが見つかりません：" & CSV_PATH, vbExclamation
        Exit Sub
    End If

    Set ws = ThisWorkbook.Worksheets(SHEET_NAME)

    ' 前回の取り込みで残ったクエリテーブルと値を消す
    Do While ws.QueryTables.Count > 0
        ws.QueryTables(1).Delete
    Loop
    ws.Cells.Clear

    Set qt = ws.QueryTables.Add( _
        Connection:="TEXT;" & CSV_PATH, _
        Destination:=ws.Range("A1"))

    With qt
        .TextFilePlatform = CODE_PAGE
        .TextFileParseType = xlDelimited
        .TextFileCommaDelimiter = True
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileConsecutiveDelimiter = False
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileStartRow = 1
        ' 左の列から順に：顧客コード、顧客名、取引日、金額
        .TextFileColumnDataTypes = Array(xlTextFormat, xlTextFormat, xlYMDFormat, xlGeneralFormat)
        .RefreshStyle = xlOverwriteCells
        .AdjustColumnWidth = True
        .Refresh BackgroundQuery:=False
        importedRows = .ResultRange.Rows.Count
        .Delete   ' 値は残し、ファイルとのつながりを外す
    End With

    ' 見出しが想定どおりか（列の順番が変わっていないか）
    expected = Array("顧客コード", "顧客名", "取引日", "金額")
    For i = 0 To UBound(expected)
        If CStr(ws.Cells(1, i + 1).Value) <> expected(i) Then
            MsgBox i + 1 & "列目の見出しが想定と違います：" & ws.Cells(1, i + 1).Value, vbExclamation
            Exit Sub
        End If
    Next i

    ' シートの行数とファイルの行数を比べる
    fileLines = CountFileLines(CSV_PATH)
    If importedRows = fileLines Then
        MsgBox "取り込み完了：見出しを除いて " & (importedRows - 1) & " 件", vbInformation
    Else
        MsgBox "行数が一致しません。" & vbCrLf & _
               "シート：" & importedRows & " 行／ファイル：" & fileLines & " 行", vbExclamation
    End If
End Sub

' ファイルの物理行数を数える（LFの数。最終行の末尾に改行がなければ1行足す）
Function CountFileLines(ByVal path As String) As Long
    Dim f As Integer
    Dim buf() As Byte
    Dim i As Long
    Dim n As Long

    f = FreeFile
    Open path For Binary Access Read As #f
    If LOF(f) = 0 Then
        Close #f
        Exit Function
    End If
    ReDim buf(0 To LOF(f) - 1)
    Get #f, , buf
    Close #f

    For i = 0 To UBound(buf)
        If buf(i) = 10 Then n = n + 1
    Next i
    If buf(UBound(buf)) <> 10 Then n = n + 1

    CountFileLines = n
End Function

指定している項目の意味

特に重要なのが BackgroundQuery:=False です。指定しないと、読み込みが終わる前に行数の確認や後続の処理が動き出します。その結果、空のシートを相手に処理してしまうことがあります。

列ごとのデータ型を指定する

TextFileColumnDataTypes には、左の列から順に型を並べた配列を渡します。

注意点は3つあります。

• 配列の要素は、先頭の列から順に対応します。要素数より列が多い場合、残りの列は標準として扱われます。先頭0を残したい列が後ろの方にあるときは、その列までの型をすべて並べます。
• CSVの列の順番が変わると、型の指定もずれます。完成形のコードには見出しのチェックを入れてあり、出力元の仕様変更に気づけるようにしています。
• xlTextFormat を指定した列は、数値に見える値も文字列として入ります。その列で計算したい場合は、標準にするか、取り込んだ後に数値へ変換します。

取り込んだ行数と見出しを確かめる

完成形のコードでは、シートに入った行数（ResultRange.Rows.Count）とCSVファイルの行数を比べています。CountFileLines はファイルをバイト列として読み、改行（LF）の数を数えるので、UTF-8でもシフトJISでも同じように使えます。ただしファイル全体をメモリに読み込むため、非常に大きなファイルでは時間がかかります。

行数が一致しない場合は、次のような状態が考えられます。

• ファイルの末尾に空行がある
• 値の中に改行を含むセルがある（ファイル上の行数がレコード数より多くなる）
• 改行コードにLFが使われていない

改行が原因で行がずれる場合の対処は、VBAでCSVを読み込むと行がずれるときの記事で扱っています。

見出しのチェックは、列の順番が変わったCSVを、ずれた型の指定のまま取り込む事故を防ぐためのものです。不一致に気づいた時点で処理を止めますが、取り込んだデータはシートに残っています。後続の処理を自動で動かす場合は、見出しと行数の両方の確認を通過したときだけ進むようにしておきます。

よくあるつまずき

• 日本語が文字化けする：TextFilePlatform の値がファイルの文字コードと合っていません。65001 と 932 を入れ替えて試します。
• すべてA列に入る：区切り文字の指定を確認します。セミコロン区切りやタブ区切りのファイルなら、該当する区切り文字を True にします。
• 先頭の0が消える：該当の列が xlTextFormat になっているか、配列の位置が列の順番と合っているかを確認します。
• 実行するたびに列が右へずれる：前回のクエリテーブルが残ったまま追加している可能性があります。コードのように、既存のクエリテーブルを削除してシートをクリアしてから取り込み、RefreshStyle を xlOverwriteCells にします。
• 「クエリと接続」や「名前の管理」に項目が増えていく：Excelのバージョンによっては、クエリテーブルを削除しても接続や名前が残ることがあります。不要なら、「データ」タブの「クエリと接続」や、「数式」タブの「名前の管理」から削除します。
• 取り込み時にエラーになる：取り込み先の範囲にExcelのテーブル（ListObject）が重なっていると、うまく追加できないことがあります。取り込み専用のシートを用意し、そこから別のシートへ転記する形にすると安定します。