2

【Excel】色々な方法でCSVファイルの読み込みを試してみよう【VBA】

どうも、ほっちるです。みなさん、いかがお過ごしでしょうか?
私は最近になってWindowsマシンを手に入れたので、Officeを導入しました。
自宅でVBAを書ける環境ができたので、VBAをテーマに記事を書いてみます。

基本的な内容はこちらの記事を確認してください。

当記事で扱う題材はCSVファイルの読み込みです。
事前準備からVBAでCSVをインポートする方法を4種類わかりやすくご紹介します。
それではいってみましょう。

事前準備(Excel, Windows)

実装を始める前に以下の参照設定を準備しましょう。
分かる方は読み飛ばしていただいて結構です。
エクセルVBAでCSVを読み込む

項番参照設定ライブラリ
1Microsoft Scripting RuntimeFileSystemObject
2Microsoft ActiveX Data ObjectsADO関連
3mscorlibArrayList

必要なライブラリの有効化

参照設定をするためには、まずExcelを表示しましょう。

  1. Excelが表示されている状態で、「Alt + F11」を押下してVBEを展開
  2. VBE上で「Alt」キーを押しながら「T → R」の順にキーを押下
  3. 表示された参照設定画面にて、必要なライブラリにチェックを入れて有効化

以下のように設定できていれば参照設定は完了です。

VBEの参照設定画面で、必要なライブラリへチェックを入れた状態

.Net Framework 3.5の有効化

ArrayListを事前バインディングするには
・「mscorlib.dll」の参照
・「.Net Framework 3.5」の有効化
の2つの工程が必要です。

有効化していない方は以下の手順を実行してください。

  1. 「コントロール パネル\プログラム\プログラムと機能」を展開
  2. 「Windowsの機能の有効化または無効化」を押下
  3. 「.Net Framework 3.5 ( .Net 2.0 および 3.0 を含む)」をチェック
  4. PCを再起動

CSVファイルの用意

なんちゃって個人情報」からデータを用意しましょう。

お好みのオプションを設定してみてください。
私はこの記事を書くにあたり、出力数を5000件に設定しました。
出力形式をCSVにして「なんちゃって生成」を押下するとダウンロードが開始されます。
ダウンロード完了後、ファイルの拡張子を「.csv」に変更しておきましょう。

以上で事前準備は終了です。それでは、実装に移っていきましょう。

Excel VBAでCSVをインポートする

CSVファイルを読み込んで、エクセルのシート上に出力するマクロを幾つかご紹介いたします。
ソースコード内に含まれる以下の文字列については、ご自身で任意のパラメータを設定していただくようにお願いいたします。

項目説明
[anyPath]CSVファイルの絶対パスを入力してください。
[sheetIndex]出力先のシート名を入力してください。

※当記事で掲載しているソースコードの使用は自由ですが何らかの問題が発生してもご対応致しかねますので、ご了承ください。

QueryTableオブジェクト

まずはQueryTableを使ってCSVを読み込んでみたいと思います。
QueryTableオブジェクトは参照設定が必要なく、最初から利用可能です。
他にも文字コードを指定して読み込んだり、プロパティの設定を変更するだけでTSVも読み込めたりする点が特徴で使い勝手が良い印象です。
以下のソースコードを参考にして試してみましょう。

Public Enum QtCharset
    SJIS = 932
    UTF8 = 65001
    UTF16 = 1200
End Enum

Public Sub ReadCSVByQueryTable()
    Dim ws As Worksheet
    Dim conn As String

    conn = "TEXT;" & "[anyPath]"                        ' -> ファイルパス
    Set ws = ThisWorkbook.Worksheets("[sheetIndex]")    ' -> 出力先シート

    ' CSVに接続
    With ws.QueryTables.Add(conn, ws.Range("A1"))
    .TextFilePlatform = QtCharset.SJIS                  ' -> 文字コード
    .TextFileCommaDelimiter = True                      ' -> カンマ区切り
    .RefreshStyle = xlInsertEntireRows                  ' -> 上書き出力
    .Refresh                                            ' -> 出力実行
    .Delete                                             ' -> 閉局
    End With
End Sub

ハイライトされている行について、任意のパラメータを入力してから実行してみてください。
実行するとCSVファイルの中身が出力先シートのA1セルを起点に出力されるかと思います。

それでは、QueryTableオブジェクトの各プロパティについて解説していきます。

Addメソッド

項目説明
Connection“Text;FilePath”のように接続文字列を指定します。
FilePathの部分にはCSVファイルの絶対パスを入れましょう。
Destination出力先を指定します。
上記のコードでは任意のシート上のA1セルを指定しています。

TextFilePlatformプロパティ
このプロパティでは対象となるCSVファイルの文字コードを指定可能です。
VBAではShift_JIS以外の文字コードで読み込むと文字化けしてしまいますが、当該プロパティで任意の文字コードを指定することでそれを防ぐことが出来ます。
文字化けする場合は、上記のソースに含まれる列挙(QtCharset)の中から文字コードを選択してみましょう。

TextFileCommaDelimiterプロパティ
CSVファイルを読み込む場合は、値を「True」に設定しましょう。
また、以下のプロパティを設定することでTSVファイルも読み込めます。

.TextFileTabDelimiter = True

RefreshStyleプロパティ
出力形式を変更したい場合はこちらのプロパティを設定してください。

定数説明
xlOverwriteCells0セルの値を上書きして出力する。
xlInsertDeleteCells1出力する行数に応じて行の追加/削除をする。
xlInsertEntireRows2行を追加して挿入する。(列が右シフトする)

Refreshメソッド
設定したプロパティの内容を反映して出力を実行します。

Deleteメソッド
接続を終了してオブジェクトを削除します。
上記のソースの場合、CSVファイルとの接続を終了します。

FileSystemObject

次はFileSystemObject(FSO)を使った抽出も試してみましょう。
CSVを1行ずつ読み込みたい場合はこちらの処理がおすすめです。
比較的シンプルな書き方となりますので、分かりやすくて使いやすいです。

Public Sub ReadCSVByFSO()
    Dim fso As FileSystemObject
    Dim lineList As mscorlib.ArrayList
    Dim wsf As WorksheetFunction
    Dim filePath As String

    ' 各クラスのインスタンスを生成
    Set fso = New FileSystemObject                      ' -> CSV抽出用
    Set lineList = New ArrayList                        ' -> 抽出結果格納用
    Set wsf = Application.WorksheetFunction             ' -> Excel関数

    filePath = "[anyPath]"                              ' -> CSVファイルパス
    With fso.OpenTextFile(filePath)                     ' -> ファイル展開
    ' 各行をカンマで分割してリストに格納
    Do Until .AtEndOfStream                             ' -> 最終行までループ
        lineList.Add Split(.ReadLine, ",")              ' -> 1行ずつ読む
    Loop
    .Close                                              ' -> 閉じる
    End With

    ' 配列を変換
    csvLines = lineList.ToArray                         ' -> リスト配列化
    csvLines = wsf.Transpose(csvLines)                  ' -> 二次元配列化
    csvLines = wsf.Transpose(csvLines)                  ' -> 次元転換

    ' 抽出結果を出力
    With ThisWorkbook.Worksheets("[sheetIndex]")
    .Range( _
        .Cells(1, 1), _
        .Cells(UBound(csvLines), UBound(csvLines, 2)) _
    ).Value = csvLines
    .Columns.AutoFit
    End With
End Sub

簡単に処理についての解説をしていきます。

対象行処理内容
13テキストファイル(CSVファイル)を展開しています。
16開いたテキストファイルを1行ずつ読み込み、結果をコレクションの
末尾に追加しています。
※ReadAllメソッドによって全行をまとめて読み込むことも可能です。
22 ToArrayメソッドによってArrayListクラスの要素を新しい配列に
コピーします。
23Transpose関数によって配列を2次元配列にしています。
24Transpose関数によって配列の次元(縦横)が転換しているので、
再度Transpose関数を実行することで元に戻します。
28-31 テキストファイルの読み込み結果が格納された配列をシート上に
出力します。

Doループの中で任意の条件を書いて抽出条件を絞ったりすることも可能です。
QueryTableと違い、Shift_JIS以外の文字コードで読み込んでしまうと文字化けする点には注意してください。

Recordsetオブジェクト(ADO)

上記の2つよりは使う機会が少ないかもしれませんが、ADOを使ってCSVを読み込む方法もあります。
こちらはSQLを使って抽出をするので、抽出したい列を指定したり任意の条件を指定することができます。
VBAのコード量が減るので個人的にはよく使う処理となります。

Public Sub ReadCSVByADORecordset()
    Dim con As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim filePath As String, folderPath As String
    Dim csvName As String, strSQL As String

    filePath = "[anyPath]"                              ' ファイルパス
    csvName = Dir(filePath)                             ' ファイル名
    folderPath = Replace(filePath, csvName, "")         ' フォルダパス

    ' CSV抽出用SQL
    strSQL = vbNullString
    strSQL = strSQL & "SELECT *"
    strSQL = strSQL & "  FROM [" & csvName & "]"

    ' コネクションオブジェクトを設定
    Set con = New Connection
    With con
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .Properties("Extended Properties").Value = "Text;HDR=Yes;FMT=Delimited"
    .Open folderPath
    End With
    ' 抽出を実行
    Set rs = New Recordset
    rs.Open strSQL, con, adOpenStatic, adLockReadOnly

    ' 結果出力
    With ThisWorkbook.Worksheets("[sheetIndex]")
    For Each f In rs.Fields                             ' -> 列名を出力
        ixCol = ixCol + 1
        .Cells(1, ixCol).Value = f.Name
    Next
    .Range("A2").CopyFromRecordset rs                   ' -> 値を出力
    .Columns.AutoFit
    End With
End Sub

ADOを使わない方にとっては見慣れないオブジェクトが使われていて面食らうかもしれませんが、やっていることはいたってシンプルです。
では、下表にて処理の解説をしていきます。

対象行処理内容
8Dir関数によってファイルパスからファイル名を取得しています。
97行目のファイルパスから8行目で取得したファイル名を除去して、
フォルダのパスを取得しています。
12-14実行するSQL文(“SELECT * FROM [ファイル名]”)を書いています。
結果としては、CSVファイルの中の文字列を全件取得します。
17Connectionオブジェクトを生成しています。
19Providerプロパティに接続文字列を設定しています。
20PropertyオブジェクトのValueプロパティにRecordsetの
Field情報を設定しています。
規定のプロパティがValueのため、この場合は省略可能です。

Text: テキストファイル
HDR: Yes->ヘッダーあり, No->ヘッダーなし
FMT: 区切り文字設定(省略可能)
21第1引数に指定したデータソースに対して接続を開局します。
データソースに格納先フォルダのパスを指定する点に注意です。
Excelに接続する場合はExcelファイルのフルパスで問題ないです。
24Recordsetオブジェクトを生成しています。
25OpenメソッドによってRecordsetを取得します。

第1引数: 実行するSQL(12~14行目)
第2引数: 対象のConnectionオブジェクト(17行目)
第3引数: カーソルの種類を列挙(CursorTypeEnum)の中から選択
第4引数: 並行性の設定を列挙(LockTypeEnum)の中から選択

カーソルの種類は、既定ですと順方向専用となります。
並行性については、既定では読み込み専用となっております。
29-32FieldオブジェクトのName要素(ヘッダー名)を出力しています。
RecordsetオブジェクトのValueプロパティ内にはCSVファイルの
ヘッダー名が含まれないため、このような処理をしています。
33RecordsetオブジェクトのValueプロパティの要素を任意のシートの
A1セルから出力しています。

12~14行目に記述しているSQL文を変更することで抽出条件が変更可能です。
SQL文を書ける方はぜひ、他の条件での抽出も試してみましょう。
また、この処理についても文字コードがShift_JISであることが前提となります。

Streamオブジェクト(ADO)

最後に、同じくADOを使った処理でStreamオブジェクトでの読み込み方法をご紹介いたします。QueryTableのように読み込み対象となるファイルの文字コードを指定することができます。
また、FSOのように1行ずつ読み込むことも出来るので、処理の中でデータ加工をしたい場合は試してみるといいでしょう。

Public Const CHARSET_SJIS As String = "Shift_JIS"
Public Const CHARSET_UTF8 As String = "UTF-8"

Public Sub ReadCSVByADODBStream()
    Dim adoStream As ADODB.Stream
    Dim lineList As mscorlib.ArrayList
    Dim wsf As WorksheetFunction
    Dim filePath As String

    ' 各インスタンス生成
    Set adoStream = New Stream                          ' -> ADODB.Stream
    Set lineList = New ArrayList                        ' -> 抽出結果格納用
    Set wsf = Application.WorksheetFunction             ' -> Excel関数
    filePath = "[anyPath]"                              ' -> CSVファイルパス

    ' CSV読み込み処理
    With adoStream
    .Charset = CHARSET_SJIS                             ' -> 文字コード設定
    .LineSeparator = adLF                               ' -> 改行コード設定
    .Open
    .LoadFromFile fileName:=filePath                    ' -> 読み込み対象
    Do Until .EOS                                       ' -> 最終行までループ
        textLine = Split(.ReadText(adReadLine), ",")    ' -> 1行ずつ読み込む
        lineList.Add textLine                           ' -> リストへ追加
    Loop
    .Close
    End With

    ' 配列を変換
    csvLines = lineList.ToArray                         ' -> 配列化
    csvLines = wsf.Transpose(csvLines)                  ' -> 二次元配列化
    csvLines = wsf.Transpose(csvLines)                  ' -> 次元転換

    ' 抽出結果を出力
    With ThisWorkbook.Worksheets("[sheetIndex]")
    .Range( _
        .Cells(1, 1), _
        .Cells(UBound(csvLines), UBound(csvLines, 2)) _
    ).Value = csvLines
    .Columns.AutoFit
    End With
End Sub

書き方としてはFileSystemObjectを使った形式と似ていますね。
主な違いはCharsetプロパティにて文字コードを設定できる点です。
処理の概要を見ていきましょう。

対象行処理内容
18テキストファイルの文字コードを設定します。
※規定値: Unicode
19テキストファイルの改行コードを列挙(LineSeparatorsEnum)の中
から選択します。
※規定値: adCrLf
20Streamオブジェクトを展開します。
21設定したファイルの内容をStreamオブジェクトに読み込みます。
22テキストファイルの最終行までループします。
231行単位の読み込み結果をカンマで分割して配列化します。
24上記の配列をArrayListクラスの末尾に追加します。
26Streamオブジェクトを閉じます。
30ToArrayメソッドによってArrayListクラスの要素を新しい配列に
コピーします。
31Transpose関数によって配列を2次元配列に転換します。
32Transpose関数によって配列の次元(縦横)が転換しているので、
再度Transpose関数を実行することで元に戻します。
36-39テキストファイルの読み込み結果が格納された配列をシート上
に出力します。

多くの場合、18行目のCharsetプロパティと19行目のLineSeparatorプロパティのパラメータを明示的に指定する必要がある点に注意しましょう。
VBEではSJISを採用しておりますが、Windowsのメモ帳ではUTF-8が既定に設定されていたりとややこしい状況です。
上記の点を意識できていれば汎用性が高く使いやすい処理に感じると思います。

まとめ

みなさんはどの書き方が使いやすかったでしょうか?
CSVファイルの読み込み方法の中から4つの書き方を紹介させていただきましたが、同じ要件でも様々な書き方があって面白いですね。

要件に合わせて必要なライブラリを選定することや、なぜその書き方をしたのか、といった部分はエンジニアとして意識したいポイントです。
単純にCSVファイルを読み込んで結果をシートに出力したいといった場合にはQueryTableオブジェクトを採用したり、複雑なデータ加工が必要な場合にはRecordsetやStreamオブジェクトを検討してみたり、といったように意識してみると良いでしょう。

日々のルーチンワークの中で、CSVファイルなどのテキストファイルの読み込みや、データ加工はよくある作業だと思います。
そのようなルーチンワークはプログラムによって解決をして、生産性を高めていきましょう!

ご紹介した方法の中で、みなさんが参考になる点がありましたら幸いです。

作者情報

工場オペレータからエンジニアにジョブチェンジ!
白い巨匠の弟子として日々邁進中!
尊敬する先輩に奢ってもらうご飯やお酒ってとっても美味しいですよね!!