【Excel VBA入門】基本的なCSVファイルの読み込みと書き出し方法

吉池昌貴 VBA 2019年8月9日 | 0

こんにちは、業務自動化ツール開発担当の吉池（@valmore_myoshi）です。

CSVは各フィールドがカンマ「,」で区切られたファイル形式です。システムからデータをエクスポートするときにCSV形式がよく使われます。

出力したCSVファイルはExcelで開き、データ編集する方が多いのではないでしょうか？Excel上でも読み書きできますが、VBAプログラムのなかでもCSVを取り込みたいといったニーズもあるはずです。

そこで今回はVBAを使ったCSVファイルの読み込み、書き出しを解説します。外部データを扱う基礎にもなるので、ぜひこの機会に習得しましょう！

1 VBAでCSVを読み込む方法
- 1.1 OpenステートメントでCSVを開く方法
- 1.2 QueryTablesで高速に開く方法
2 ExcelファイルをCSVに書き出す方法
3 まとめ

VBAでCSVを読み込む方法

CSVを簡単に開ける方法を２つ解説します。一つ目はOpenステートメントを使ってCSVデータを一行ずつ読み込む方法。二つ目はQueryTableオブジェクトを使ってCSVをワークシートに読み込む方法です。

CSVサンプルとして下記のような売上データを想定しています。ダウンロードはこちらから。

OpenステートメントでCSVを開く方法

CSVを読み込む方法一つ目はOpenステートメントです。

Dim filePath As String

# CSVファイルのパスを指定
filePath = ThisWorkbook.Path & "\Book1.csv"
    
Open filePath For Input As #1

Openステートメントでは、読み込みたいCSVファイルパスとアクセスモード、ファイル番号を指定します。アクセスモードはAppend（追記）やOutput（書き込み）など複数ありますが、今回はInput（読み込み）を指定します。ファイル番号は読み込むファイルを識別するもので、１から始まる番号を指定します。

CSVにアクセスできたらLine Inputステートメントで一行ずつ読み込みます。

Dim line As String
Dim lineItems As Variant, item As Variant

Do Until EOF(1)
    Line Input #1, line
    lineItems = Split(line, ",")
    For Each item In lineItems
        ‘ CSVの各フィールドに対する処理
        Debug.Print (item)
    Next
Loop

Close #1

Line Inputステートメントにファイル番号と変数（ここではline）を指定することで、読み込んだ一行分の文字列が変数に代入されます。そのままだとカンマ区切りの文字列なので、Split関数で区切り文字にカンマを指定して文字列を分割します。

ファイルの終端に到達したらループ処理から抜けるよう、EOF関数にファイル番号を指定します。終端に到達するとTrueを返してDo Until ~ Loopを抜け、Closeステートメントで読み込みを終了します。

全体のコードは下記の通り。

Dim filePath As String, line As String
Dim lineItems As Variant, item As Variant

# CSVファイルのパスを指定
filePath = ThisWorkbook.Path & "\Book1.csv"

Open filePath For Input As #1

Do Until EOF(1)
    Line Input #1, line
    lineItems = Split(line, ",")
    For Each item In lineItems
        ‘ CSVの各フィールドに対する処理
        Debug.Print (item)
    Next
Loop

Close #1

QueryTablesで高速に開く方法

QueryTableオブジェクトは外部データの接続やデータ形式を管理するオブジェクトです。今回はCSVに接続し、データ形式として文字コードや区切り文字を設定します。

Dim connection As String
Dim qt As QueryTable

connection = "TEXT;" & ThisWorkbook.Path & "\Book1.csv"

Set qt = ActiveSheet.QueryTables.Add( _
    Connection:=connection, Destination:=Range("B2") _
)

まずは接続先を指定する文字列の作成から始めます。DBやWebなどさまざまな外部データと接続できますが、今回はCSV、つまりテキストファイルと接続します。「TEXT;」のあとに続けてCSVファイルパスを指定します。

QueryTablesオブジェクトのAddメソッドの引数Connectionに接続先文字列、 DestinationにCSVデータの展開先を指定することでQueryTableオブジェクトを取得できます。ここで展開先として指定しているB2を基準にCSVが読み込まれます。

次にデータ形式を指定していきます。

Dim dataTypes As Variant

dataTypes = Array( _
    xlYMDFormat, _
    xlTextFormat, _
    xlTextFormat, _
    xlGeneralFormat, _
    xlGeneralFormat _
)

With qt
    .TextFilePlatform = 65001
    .TextFileParseType = xlDelimited
    .TextFileCommaDelimiter = True
    .TextFileColumnDataTypes = dataTypes
    .Refresh BackgroundQuery:=False
    .Delete
End With

取得したQueryTableオブジェクトのプロパティやメソッドを使っていくので、変数名の記述を省略するためにWithステートメントを使います。

QueryTableオブジェクトのTextFilePlatformプロパティでは文字コードを指定できます。区切り文字としてカンマを指定する場合、TextFileParseTypeプロパティにxlDelimited（カンマ区切り）、TextFileCommaDelimiterプロパティにTrueを指定します。

TextFileColumnDataTypesプロパティはCSVの各フィールドのデータ型を配列で指定します。今回想定しているCSVサンプルは下記のように５つのフィールドがあり、特に商品番号は普通に開くと先頭の0が消えてしまいます。そのため、データ型としてテキストを選択し、先頭の0が消えないようにしています。

TextFileColumnDataTypesでよく使う４つのデータ型を覚えておきましょう。

データ型	説明
xlGeneralFormat	一般形式
xlYMDFormat	YMD日付形式
xlTextFormat	テキスト
xlSkipColumn	フィールドをスキップ

データをワークシートに展開するときはRefreshメソッドを使います。引数のBackgroundQueryはバックグラウンドでのデータ更新を許可するものです。データ更新が終わってから次の処理に進みたいのでFalseを指定します。

最後のDeleteメソッドは外部データ、ここではCSVデータとの接続を削除しています。QueryTableは外部データとの接続設定を保存しているので、外部データに変更があったあとにRefreshするとワークシートにも変更が反映されます。継続的に更新する必要がないのなら接続を削除しましょう。

全体のコードは下記の通りです。

Dim filePath As String, connection As String
Dim qt As QueryTable
Dim dataTypes As Variant

connection = "TEXT;" & ThisWorkbook.Path & "\Book1.csv"

Set qt = ActiveSheet.QueryTables.Add( _
    Connection:=connection, Destination:=Range("B2") _
)

dataTypes = Array( _
    xlYMDFormat, _
    xlTextFormat, _
    xlTextFormat, _
    xlGeneralFormat, _
    xlGeneralFormat _
)
    
With qt
    .TextFilePlatform = 65001
    .TextFileParseType = xlDelimited
    .TextFileCommaDelimiter = True
    .TextFileColumnDataTypes = dataTypes
    .Refresh BackgroundQuery:=False
    .Delete
End With

ExcelファイルをCSVに書き出す方法

CSVをExcelファイルに読み込むときは長々とコードを書きましたが、ExcelファイルをCSVに書き出すのはとてもシンプル！WorkbookオブジェクトのSaveAsメソッドの引数FileFormatにCSV形式を表すxlCSVを指定して保存するだけです。

Dim filePath As String
Dim wb As Workbook

filePath = ThisWorkbook.Path & "\Book1.xlsx"

Set wb = Workbooks.Open(filePath)

‘ データの編集処理

wb.SaveAs Filename:=ThisWorkbook.Path & "\csv-test.csv", FileFormat:=xlCSV

まとめ

VBAを使ったCSVの読み込み、書き出しの方法を解説しました。CSVの読み込みはOpenステートメントを使った基本的な方法とデータ接続を管理するQueryTableオブジェクトの２つ紹介しました。

Openステートメントでは文字列として、QueryTableはワークシートにCSVデータが読み込まれるので、用途に応じて使い分けましょう。書き込みはSaveAsだけなので簡単ですね。

CSVでデータを加工したり、分析することは多々あるので、CSVの読み書きから習得していきましょう。

【Excel VBA入門】基本的なCSVファイルの読み込みと書き出し方法