【セル内改行 ダブルクォーテーション対応】ExcelVBAのCSV読み込み方法7つ

VBA Excel

Excel VBAでのCSVの読み込み方法を検索するとたくさん出てきますが，それぞれ一長一短があります。この記事でまとめてみます。

結論は「ADODBで読み込むといい」です．そこだけ見たい人はまとめをご覧ください．

以下，VBAを書いたExcelブックのカレントディレクトリのemployee.csvを読み込むとします．

Workbooks.Openで開く

Workbooks.OpenでCSVを開くのは、一番簡単なCSVの読み込み方法でしょう。

Sub LoadCSV1()
    Dim csvBook As Workbook
    Set csvBook = Workbooks.Open("employee.csv")
End Sub

ただし、Excelによって勝手にデータが解釈されて変換されてしまうので、数値の頭のゼロが落ちるなどの問題が起きることも多いです。

例えば、このデータが、

f:id:kamocyc:20191211184240p:plain

こうなります。A列の頭の0が無くなっています。

f:id:kamocyc:20191211190135p:plain

他にも，1-2-3➡2001/2/3，○月○日➡×年○月○日（勝手に開いた年が入る），(1)➡-1などと勝手に変わります．

f:id:kamocyc:20191211190253p:plain

なので，読み込むデータ形式が不定の場合はあまりこの方法を使わないほうが良いです．

テキストファイルとして読み込む（単純Split版）

また、よくCSV読み込みサンプルとしてあるのが、テキストファイルとしてCSVを読み込んで、改行とカンマで分割するというものです。

Sub LoadCsv2()
    Open "employee.csv" For Input As #1

    Dim r As Long: r = 1
    Do Until EOF(1)
        Dim line As String
        Line Input #1, line
        
        Dim items As Variant
        items = Split(line, ",")
        Cells(r, 1).Resize(1, UBound(items) + 1).Value = items
        r = r + 1
    Loop

    Close #1
End Sub

テキストファイルとして読み込むので、セルに入れるデータ形式などの融通が利くのがメリットです。

文字コードがUTF8のテキストファイルを読み込むには、下記のサイトのようにADODB.Streamを使用します。

Office TANAKA - ファイルの操作[UTF-8形式のテキストファイルから読み込む]

CSV内のデータにカンマや改行などが絶対に入ってこないのであればこの方法で問題無いです。

しかし、実際にはそうでない場合が多いので、実業務ではあまり使えないです。 もしデータにカンマや改行が入ると、読み込むセルがズレたりします。

テキストファイルとして読み込む（独自にパース）

単純にSplitでカンマで分割せずに、1文字ずつ見ていって解析すれば、理論上データに改行やカンマなど含まれていても正確にCSVを読み込めます。

ただ、独自で実装するのはなかなか大変です。

検索すると、下記のサイトでコードが見つかりました。 データに改行、カンマ、ダブルクォーテーションが含まれるCSVも取り込めました。 このサイトには，下記ページからのリンク先にもCSV取り込みの他のバリエーションなどが解説されているので参考になります．

CSVの読み込み方法（改の改）｜VBAサンプル集

なお，1セルずつデータを出力すると，大きなCSVを読み込んだときに非常に時間がかかるので， 配列に入れてまとめて書き出すなど工夫が必要です (上記サイトにも解説がありました)．

ADODB.Connectionで読み込む

ADODBを使って、CSVファイルに接続して読み込む方法もあります。

詳しくは、「adodb csv 読み込み」などで検索すると出てきます。

Sub LoadCsv3()
    Dim conn As Object
    Set conn = CreateObject("ADODB.Connection")
    
    ' * UTF-8なら CharacterSet=65001
    ' * 実際使う際は、Data Source=C:\hoge のようにCSVの親フォルダパスを指定します
    Dim connectionString As String
    connectionString = _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=.;" & _
        "Extended Properties=""Text;HDR=Yes;CharacterSet=932;FMT=Delimited"";"
    
    conn.Open connectionString
    
    Dim rec As Object
    Set rec = CreateObject("ADODB.Recordset")
    ' ファイル名がテーブル名になります。ピリオドは#になります。
    rec.Open "SELECT * FROM [employee#csv];", conn
    
    ' 列名出力
    Dim c As Long
    For c = 1 To rec.Fields.Count
        Cells(1, c).Value = rec.Fields(c - 1).Name
    Next
    
    ' データを出力
    Range("A2").CopyFromRecordset rec
    
    rec.Close
    conn.Close
End Sub

この方法はかなり優秀で、カンマや改行がデータに含まれていてもきちんと読み込むことができます。

デフォルト（上記のコード）では列のデータ型が自動判別されて、Workbooks.Openで開くときと同様に数値の頭の0が落ちたりします。これを防ぐには、下記のようなschema.iniファイルをCSVと同じフォルダに置き、データ型（ここではtext）を指定します。

[employee.csv]
Format=CSVDelimited
ColNameHeader=True
Col1=No text
Col2=氏名 text
Col3=入社日 text

列ごとに，Col連番=列名 データ型のように指定します．列名は重複しないなら任意に付けられます．実際のCSVと同じ列名でも大丈夫です．

データ型の代表例は下記です．

  • 整数: Long
  • 小数: Double
  • 日付: DateTime
  • 文字列: Text

公式リファレンスは下記です（日本語版は型名が自動翻訳されて意味不明になっています）．

Schema.ini File (Text File Driver) - SQL Server | Microsoft Docs

日本語の解説は下記が見つかりました．

テキスト ファイルのフィールドを定義する方法 | ArcGISブログ

実装の手間はかかりますが、schema.iniもプログラムで自動生成すれば、任意のCSVファイルを頭の0を落としたりせずに取り込めます。

ただ注意点もあり、列数は255までしか取り込めません。

また、同じ列名があると、Fieldsで列名を取得したときに勝手に変更されています。例えば、「氏名」列を2つにすると、下記のようになります。

f:id:kamocyc:20191211185248p:plain

列名を取得したいけど，列名の重複があるような場合は，別途ファイルの先頭行から列名を取り込むなどの工夫が要ります．

Workbooks.OpenTextで開く

Workbooks.OpenTextは、テキストファイルやCSVファイルを開くことに特化したメソッドです。

下記ページにまとまっていました。

VBA CSV ファイルの読み込み (Workbooks.OpenText 関数を使う)

サンプル:

Sub LoadCsv4()
    ' UTF-8なら Origin:=65001
    ' FieldInfo でデータ型指定
    Workbooks.OpenText "employee.txt", _
        StartRow:=1, _
        Origin:=932, _
        DataType:=xlDelimited, _
        TextQualifier:=xlTextQualifierDoubleQuote, _
        ConsecutiveDelimiter:=False, _
        Tab:=False, _
        Semicolon:=False, _
        Comma:=True, _
        Space:=False, _
        Other:=False, _
        FieldInfo:=Array(Array(1, xlTextFormat), Array(2, xlTextFormat), Array(1, xlTextFormat))
End Sub

使う上での注意点は、ファイルの拡張子によって挙動が変わることです。

ファイルの拡張子がtxtだと、Originによる文字コードの指定、FieldInfoによるデータ型の指定がともに効きます。しかし、セル内改行に対応していません。（カンマやダブルクォーテーションは対応しています。）

一方、ファイルの拡張子がcsvだと、Originによる文字コードの指定、FieldInfoによるデータ型の指定が効きません。（正確には、UTF8ファイルを開くときは、ファイル先頭にBOMがついているときのみOriginによる指定が効きます。）しかし、セル内改行に対応しています。

まとめると以下のようになります。

拡張子 文字コード指定 データ型指定 セル内改行
txt ○ ○ ×
csv △ (BOM必要) × ○

データに応じて拡張子を返る必要があり，使いづらいです．

クエリーテーブルで読み込む

Excelのクエリーテーブルの機能を使ってCSVを読み込めます。

こちらのページにまとまっていました:

VBA CSV ファイルの読み込み (QueryTables.Add 関数を使う)

Sub LoadCsv5()
    Dim ws As Worksheet: Set ws = ActiveSheet
    
    ' ws.Range("A1") に出力
    Dim qt As QueryTable
    Set qt = ws.QueryTables.Add( _
        Connection:="text;employee.csv", _
        destination:=ws.Range("A1"))
        
    With qt
        ' 型指定
        .TextFileColumnDataTypes = Array(xlTextFormat, xlTextFormat, xlTextFormat)
        ' UTF8の場合、 .TextFilePlatform = 65001
        .TextFilePlatform = 932
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileTrailingMinusNumbers = False
    End With
        
    ' BackgroundQuery:=False によって、同期して更新
    qt.Refresh BackgroundQuery:=False
    
    ' Deleteすることで、ファイル側の更新が反映されるのを防ぐ
    qt.Delete
End Sub

TextFileColumnDataTypesで指定する定数値は下記に記載があります。

XlColumnDataType enumeration (Excel) | Microsoft Docs

データ型の指定ができ、セル内のカンマやダブルクォーテーションに対応していますが、セル内改行に未対応です。

※蛇足ですが、「CSVが空のときにエラーになる」と記載があるページがありましたが、データが空でも取り込めました。（Excel 2016で確認） ヘッダすらない0バイトのファイルだとエラーになります。

PowerQuery (取得と変換) を使う

PowerQuery (Excel 2016では「取得と変換」と呼ばれる) を使ってCSVを取り込めます。

PowerQueryをVBAから使う方法はネット上に情報が少ないですが、マクロの記録を使って，VBAから呼び出すメソッドなどが特定できます。 下記にサイトなどにやり方が書いてありました。

PowerQueryの強力な機能をVBAから利用する方法｜VBA技術解説

Sub LoadCsv6()
    Const kQueryName As String = "temp_csv_import_01"
    ' UTF8の場合、65001
    Const codePage As Long = 932
    
    ' CSVは絶対パスの指定が必要
    Dim csvPath As String
    csvPath = ThisWorkbook.Path & "\employee.csv"
    
    Dim targetSheet As Worksheet: Set targetSheet = ActiveSheet
    
    ' 一時ブックを作ってインポートし、そこから目的のセルへコピーする
    Dim wb As Workbook: Set wb = Application.Workbooks.Add()
    Dim ws As Worksheet: Set ws = wb.Worksheets(1)
    Dim tempDestination As Range: Set tempDestination = ws.Cells(1, 1)
    
    Dim mScript As String
    mScript = "Table.PromoteHeaders(Csv.Document(File.Contents(""" & csvPath & """), [Delimiter="","", Encoding=" & codePage & ", QuoteStyle=QuoteStyle.Csv]), [PromoteAllScalars=true])"
    
    Dim wQuery As WorkbookQuery
    Set wQuery = wb.Queries.Add(Name:=kQueryName, Formula:=mScript)
    
    Dim qTable As QueryTable
    Set qTable = _
        ws.ListObjects.Add( _
            SourceType:=xlSrcExternal, _
            Source:="OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""" & kQueryName & """;Extended Properties=""""", _
            destination:=tempDestination _
        ).QueryTable
    
    With qTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [" & kQueryName & "]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = False
        .ListObject.DisplayName = "q" & kQueryName
        .Refresh BackgroundQuery:=False
    End With
    
    ' 目的のセルにコピー
    ws.UsedRange.Copy
    targetSheet.Range("A1").PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    
    Application.DisplayAlerts = False
    wb.Close False
    Application.DisplayAlerts = True
End Sub

このコードは、以下の流れで実行しています。

  1. CSVの一時読み込み先の新たな空のワークブックを作成
  2. wb.Queries.Add(で、新たな取得と変換クエリを追加。ここで、引数Formula:=に、CSVを読み込むM言語のコードを指定しています。
  3. ws.ListObjects.Add(でListObjectsコレクションにクエリを追加し、QueryTableオブジェクトを取得
  4. QueryTableの各種パラメータを指定し、Refreshメソッドによってパラメータを反映させて、CSVのデータを取得
  5. 取得したCSVのデータをtargetSheet.Range("A1")にコピー
  6. 一時作成したワークブックを閉じる

セル内のカンマやダブルクォーテーション、改行に対応しています。

注意点は、以下の2つがあります。

  • Excel 2016以降でないと標準で使えない
  • 同じ列名があるときに、勝手に末尾に_1付くように列名が変わってしまう

なお，M言語とはPowerQueryによるデータの処理を記述する言語です． 情報はまだ少ないですが，下記に公式リファレンスがあります．

Power Query M 数式言語のリファレンス - PowerQuery M | Microsoft Docs

まとめ

まとめると以下になります．

実際に，多様なCSVデータに対して「使える」方法は太字にしてあります．

方法 UTF8 対応 セル内「,」「"」 セル内改行 勝手なデータ変換防止 255を超える列数 列名重複OK
Workbooks.Open △*1 O O X O O
テキストを単純Split X X O O O
テキストを独自にパース O*2 O O O O O
ADODBで読み込む O O O O*3 X X
Workbooks.OpenText (.csv) △*4 O O X O O
Workbooks.OpenText (.txt) O O X X O O
クエリーテーブル O O X O O O
PowerQuery (取得と変換) *5 O O O O O X

私の場合は，ADODB.Connectionを使うことが多いです．schema.ini作成，接続オープンを行うクラスを作って再利用しています． 理由は，列数が255を超えることはあまり無いことし，SQLを使って容易にデータを編集できるからです．

また，単純なCSV読み込みのみで，Excel 2016以降で動かすことが決まっているなら，PowerQueryを使うのが安心感がある気がします．

最後に，一口にCSVと言っても，セル内改行の有無やダブルクォーテーションの有無，文字コードや改行コードの種類など多様なものが存在するので，必ず仕様の確認が必要です．

*1:BOM必要

*2:ADODB.Streamで可能

*3:schema.iniで可能

*4:BOM必要

*5:Excel2016以降