Excel・CSV

複数のCSVをExcelのシートごとに取り込む方法｜1ファイル1シートで読み込み、シート名にファイル名を付ける

店舗別や月別に出力されたCSVを、1つの表に縦にまとめるのではなく、ファイルごとに別のシートとして1つのブックに並べたい場面があります。1つずつ開いてコピーする方法だと、貼り付け先のシートを間違えたり、先頭の0が消えたり、文字化けしたりといったミスが起きやすくなります。

この記事では、次の2つの方法を説明します。

  • VBAで、フォルダ内のCSVを順にシートへ取り込む方法
  • Power Queryで、ファイルごとに読み込む方法

あわせて、シート名にファイル名を付けるときの制限と、取り込み後の件数の確認方法も扱います。同じ形式のCSVを1つの表に縦に結合したい場合は、Power Queryのフォルダからの結合など、別の方法が向いています。

どちらの方法を選ぶか

状況 向いている方法
ファイル数が多い、毎回ファイルの数や名前が変わる VBA
ファイルが数個で固定、同じ場所のファイルを差し替えて更新したい Power Query
マクロ付きブック（.xlsm）が使えない環境 Power Query

取り込む前に確認すること

  • 文字コード：UTF-8か、Shift_JIS（CP932）か。文字コードの違うファイルが混ざっていると、一部のシートだけ文字化けします。
  • 見出し行の有無：件数を確認するときに、見出しの1行を引くかどうかが変わります。
  • 先頭の0を残す列：社員番号、郵便番号、商品コードなど。
  • ファイル名の長さ：シート名は31文字までなので、長いファイル名は切り詰める必要があります。
  • 元ファイルのコピー：作業の前に、元のCSVを別のフォルダへコピーしておきます。

シート名に付けるときのルール

Excelのシート名には、次の制限があります。

  • 31文字まで
  • : \ / ? * [ ] は使えない
  • 空にはできない
  • 先頭と末尾にアポストロフィ（'）は使えない
  • 同じブック内で重複できない（大文字と小文字は区別されない）
  • 「History」は予約されていて使えない

ファイル名をそのままシート名にすると、この制限に引っかかることがあります。そこで、次の処理を入れておきます。

  1. 拡張子を外す
  2. 使えない文字を置き換える
  3. 31文字に切り詰める
  4. 名前が重複したら番号を付ける

切り詰めると、先頭31文字が同じファイル同士はシート名だけでは区別できなくなります。そのため、元のファイル名とシート名の対応を一覧に残しておきます。

方法1：VBAで1ファイル1シートに取り込む

手順

  1. 新しいブックを作り、Excelマクロ有効ブック（.xlsm）として保存します。
  2. Alt + F11 でVBEを開き、［挿入］→［標準モジュール］を選びます。
  3. 下のコードを貼り付けます。
  4. 先頭の CSV_CODEPAGE を、CSVの文字コードに合わせます（UTF-8なら65001、Shift_JISなら932）。
  5. Excelに戻り、Alt + F8 で ImportCsvFilesToSheets を実行し、CSVのあるフォルダを選びます。

コード

Option Explicit

Const CSV_CODEPAGE As Long = 65001   ' UTF-8。Shift_JISなら932
Const HEADER_ROWS As Long = 1        ' 見出し行がないCSVなら0

Sub ImportCsvFilesToSheets()
    Dim folderPath As String
    Dim fileName As String
    Dim ws As Worksheet
    Dim logWs As Worksheet
    Dim qt As QueryTable
    Dim colTypes() As Variant
    Dim logRow As Long
    Dim dataRows As Long
    Dim i As Long

    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "CSVファイルのあるフォルダを選択"
        If .Show <> -1 Then Exit Sub
        folderPath = .SelectedItems(1)
    End With
    If Right(folderPath, 1) <> "\" Then folderPath = folderPath & "\"

    ' すべての列を文字列として取り込む（先頭の0や日付への自動変換を防ぐ）
    ReDim colTypes(0 To 99)
    For i = 0 To 99
        colTypes(i) = xlTextFormat
    Next i

    ' ファイル名とシート名の対応、取り込んだ件数を残すシート
    Set logWs = ThisWorkbook.Worksheets.Add(Before:=ThisWorkbook.Worksheets(1))
    logWs.Name = SafeSheetName("取込記録")
    logWs.Columns("A:B").NumberFormat = "@"
    logWs.Range("A1:C1").Value = Array("ファイル名", "シート名", "データ行数")
    logRow = 2

    fileName = Dir(folderPath & "*.csv")
    Do While fileName <> ""
        Set ws = ThisWorkbook.Worksheets.Add( _
            After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
        ws.Name = SafeSheetName(fileName)

        Set qt = ws.QueryTables.Add( _
            Connection:="TEXT;" & folderPath & fileName, _
            Destination:=ws.Range("A1"))
        With qt
            .TextFilePlatform = CSV_CODEPAGE
            .TextFileParseType = xlDelimited
            .TextFileCommaDelimiter = True
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileColumnDataTypes = colTypes
            .Refresh BackgroundQuery:=False
        End With

        dataRows = 0
        On Error Resume Next   ' 空のCSVではResultRangeを取得できない
        dataRows = qt.ResultRange.Rows.Count - HEADER_ROWS
        On Error GoTo 0
        qt.Delete              ' 接続だけを削除し、取り込んだ値は残す

        logWs.Cells(logRow, 1).Value = fileName
        logWs.Cells(logRow, 2).Value = ws.Name
        logWs.Cells(logRow, 3).Value = dataRows
        logRow = logRow + 1

        fileName = Dir()
    Loop

    MsgBox logRow - 2 & "件のCSVを取り込みました。"
End Sub

' ファイル名からExcelで使えるシート名を作る
Function SafeSheetName(ByVal baseName As String) As String
    Dim badChars As Variant
    Dim c As Variant
    Dim candidate As String
    Dim n As Long

    If LCase(Right(baseName, 4)) = ".csv" Then
        baseName = Left(baseName, Len(baseName) - 4)
    End If

    badChars = Array(":", "\", "/", "?", "*", "[", "]")
    For Each c In badChars
        baseName = Replace(baseName, c, "_")
    Next c

    baseName = Left(baseName, 31)
    Do While Left(baseName, 1) = "'"
        baseName = Mid(baseName, 2)
    Loop
    Do While Right(baseName, 1) = "'"
        baseName = Left(baseName, Len(baseName) - 1)
    Loop
    If baseName = "" Then baseName = "csv"
    If LCase(baseName) = "history" Then baseName = baseName & "_csv"

    candidate = baseName
    n = 1
    Do While SheetExists(candidate)
        n = n + 1
        candidate = Left(baseName, 31 - Len("_" & n)) & "_" & n
    Loop
    SafeSheetName = candidate
End Function

Function SheetExists(ByVal sheetName As String) As Boolean
    Dim sh As Object
    For Each sh In ThisWorkbook.Sheets
        If StrComp(sh.Name, sheetName, vbTextCompare) = 0 Then
            SheetExists = True
            Exit Function
        End If
    Next sh
End Function

コードのポイント

  • すべての列を文字列で取り込む：先頭の0が消えたり、コードが日付に変わったりするのを防ぎます。その代わり、金額などの数値も文字列になります。計算に使う列は、取り込んだ後で数値に変換するか、colTypes の該当する位置（0始まりで、1列目は0）を xlGeneralFormat に変えてください。
  • 100列分を指定している：100列を超えるCSVを扱う場合は、ReDim colTypes(0 To 99) と For i = 0 To 99 の数を増やします。
  • 取り込み後に接続を削除する：qt.Delete で外部ファイルへの接続を外します。シートの値は残ります。
  • 取込記録シート：ファイル名、シート名、データ行数を1行ずつ記録します。マクロを2回実行すると、シート名に番号が付いたシートが追加されます。やり直すときは、前回作られたシートを削除してから実行してください。
  • ファイルの順番：Dir が返す順番は、エクスプローラーの表示順と同じとは限りません。シートの並び順が重要な場合は、取り込んだ後に並べ替えます。

方法2：Power Queryでファイルごとに読み込む

ファイルが数個で、毎回同じ場所の同じファイル名で差し替える運用なら、Power Queryでファイルごとにクエリを作る方法も使えます。メニューの名前や位置はExcelのバージョンによって異なるため、画面の表記に合わせて読み替えてください。

  1. ［データ］タブ→［データの取得］→［ファイルから］→［テキストまたはCSVから］を選びます。
  2. 取り込むCSVを選びます。
  3. プレビュー画面で、［元のファイル］をCSVの文字コードに合わせます（65001: Unicode (UTF-8) または 932: 日本語 (シフト JIS)）。先頭の0を残したい場合は、［データ型検出］を［データ型を検出しない］にします。
  4. ［データの変換］を押してPower Queryエディターを開き、右側のクエリ名を確認します。通常はファイル名が入ります。必要なら分かりやすい名前に変えます。
  5. 計算に使う列があれば、その列だけデータ型を数値に変更します。
  6. ［閉じて次に読み込む］→［テーブル］→［新規ワークシート］を選んで読み込みます。
  7. 追加されたシートの名前を確認し、必要ならファイル名に合わせて変更します。自動で付くシート名はバージョンなどによって異なるため、読み込むたびに確認します。
  8. 残りのファイルについても、1〜7を繰り返します。

CSVを同じ場所・同じ名前で差し替えた後は、［データ］→［すべて更新］で読み込み直せます。ファイル名や保存場所が変わった場合は、［データソースの設定］から参照先を変更します。

ファイル数が多い場合や、毎回ファイル名が変わる場合は、クエリを1つずつ作る手間が大きくなります。その場合は方法1のVBAのほうが向いています。

取り込み後に件数を確かめる

シートが作られただけで安心せず、ファイルごとに件数と中身を確認します。

  1. 元のCSVの件数を把握する：出力したシステムの画面や出力ログに件数が出ていれば、それを使います。テキストエディタの行数で確かめる場合は、見出し行と末尾の空行を数えないようにします。セル内改行を含むCSVでは、エディタの行数とデータの件数は一致しません。
  2. 取込記録と比べる：VBAの場合は、取込記録シートのデータ行数と元の件数を比べます。Power Queryの場合は、［クエリと接続］ウィンドウに表示される読み込み行数で確認できます。
  3. シートごとに数え直す：取込記録シートのD2に次の式を入れて下にコピーすると、各シートのA列の件数を数え直せます。
=COUNTA(INDIRECT("'"&B2&"'!A:A"))-1

A列に空白のセルがあると少なく数えられるため、必ず値が入っている列（キー列など）に合わせて A:A を変えてください。シート名にアポストロフィ（'）が含まれる場合、この式ではエラーになります。

  1. 中身を目で確認する：各シートの先頭と最終行（Ctrl + End）を見て、次の点を確かめます。
    • 先頭の0が残っているか
    • 日付やコードが別の値に変わっていないか
    • 日本語が文字化けしていないか
    • 最終列の右側に値がはみ出していないか（列ずれ）
  2. 合計を比べる：金額の列があれば、元のシステムで出した合計とシートの合計を比べます。文字列で取り込んだ場合は、数値に変換してから合計します。

件数が合わないシートがあれば、そのCSVだけを開いて見直します。原因として多いのは、文字コードの違い、引用符の閉じ忘れ、見出し行の有無の違いです。

この記事を書いた人
ゆきむら
ゆきむら
ひとり仕事・小さな事業の実務を軽くする記事や、小さなツールを作っています。 会計・データ整理・Excel・CSV・文書・連絡・記録・業務改善などを扱っています。 制度・税務に関する記事では、国税庁などの一次資料を確認し、制度上のルールと実務上の工夫を分けて整理することを基本方針にしています。 姉妹サイト「あたまの道具箱」も運営しています。
おすすめの記事