VBA: Power Queryを使ってCSVファイルを読み込む

VBAからPower Queryを使ってCSVを読み込んでみました。

CSVファイル

元になるCSVファイルは次の通りです。

ダブルクリックしてExcelで開くと次のように表示されます。

VBAを実行すると次のように読み込むことができます。

コード

コードは次の通りです。

コピペ用です。

Public Sub uImport()

    Dim uWB As Workbook

    Dim uWS As Worksheet

    Dim uFormula As String

    Dim uQuery As WorkbookQuery

    Dim uList As ListObject

    Const uCSVPath As String = "C:\NKTemp\Sample.csv"

    Const uType As String = "{""購入日"", type date}, {""金額"", Int64.Type}"

    Const uQueryName As String = "任意のクエリー名"

    Const uListName As String = "任意のテーブル名"

    Const uSheet As String = "Sheet1"

    Const uDestination  As String = "A3"

    

    Set uWB = ThisWorkbook

    Set uWS = uWB.Worksheets(uSheet)  'テーブルを作成するワークシート

    

    uDeleteTableAndQuery uWS, uListName, uQueryName

    

    uFormula = _

        "let uSource = Csv.Document(File.Contents(""" & uCSVPath & """), " & _

            "[Delimiter="","", Encoding=932, QuoteStyle=QuoteStyle.Csv]), " & _

        "uPromotedHeaders = Table.PromoteHeaders(uSource, [PromoteAllScalars=true]), " & _

        "uChangedType = Table.TransformColumnTypes(uPromotedHeaders, " & _

            "{" & uType & "}) " & _

        "in uChangedType"   ' M言語によるクエリー

    

    Set uQuery = uWB.Queries.Add(Name:=uQueryName, Formula:=uFormula)   'クエリーを作成

    

    Set uList = uWS.ListObjects.Add( _

        SourceType:=xlSrcExternal, _

        Source:="OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;" & _

            "Location=" & uQuery.Name, _

        Destination:=uWS.Range(uDestination)) 'テーブルを作成（クエリーテーブル付き）

    

    With uList.QueryTable

        .CommandType = xlCmdSql

        .CommandText = Array("SELECT * FROM [" & uQuery.Name & "]")

        

        .ListObject.DisplayName = uListName

        

        .Refresh BackgroundQuery:=False '読込実行

    End With

End Sub

'指定されたーテーブルとクエリーを削除

Private Sub uDeleteTableAndQuery( _

    ByVal uWS As Worksheet, _

    ByVal uListName As String, _

    ByVal uQueryName As String)

    Dim uWB As Workbook

    Dim uQuery As WorkbookQuery

    Dim uList As ListObject

    

    For Each uList In uWS.ListObjects

        If uList.Name = uListName Then

            uList.Delete

            Exit For

        End If

    Next

    

    Set uWB = uWS.Parent

    

    For Each uQuery In uWB.Queries

        If uQuery.Name = uQueryName Then

            uQuery.Delete

            Exit For

        End If

    Next

End Sub

解説

「マクロの記録」を使ってCSVを読み込んだ際に生成されるコードを元に作り直しています。

最初の Constant はパラメーターです。

Const uCSVPath As String = "C:\NKTemp\Sample.csv"
Const uType As String = "{""購入日"", type date}, {""金額"", Int64.Type}"
Const uQueryName As String = "任意のクエリー名"
Const uListName As String = "任意のテーブル名"
Const uSheet As String = "Sheet1"
Const uDestination  As String = "A3"

CSVファイルの場所
型を変更する列と型指定
クエリー名
テーブル名
シート名
そして、テーブルを配置する開始セルです。

次の行では、ワークブックとワークシートを指定しています。必要に応じて、任意のものを指定するとよいでしょう。

Set uWB = ThisWorkbook
Set uWS = uWB.Worksheets(uSheet)  'テーブルを作成するワークシート

繰り返し読み込む場合に備えて、最初にクエリーとテーブルの削除を行っています。

uDeleteTableAndQuery uWS, uListName, uQueryName

M言語部分

クエリー(Power Query)の命令部分です。M言語で記述されています。

uSourceでCSVファイルを開き、uPromotedHeadersでCSVファイルの一行目を見出しにして、uChangedTypeで列のデータ型を指定しています。

uFormula = _
    "let uSource = Csv.Document(File.Contents(""" & uCSVPath & """), " & _
        "[Delimiter="","", Encoding=932, QuoteStyle=QuoteStyle.Csv]), " & _
    "uPromotedHeaders = Table.PromoteHeaders(uSource, [PromoteAllScalars=true]), " & _
    "uChangedType = Table.TransformColumnTypes(uPromotedHeaders, " & _
        "{" & uType & "}) " & _
    "in uChangedType"   ' M言語によるクエリー

Csv.Document

Delmiterは区切り文字なので、必要に応じてタブなどを指定するとよいでしょう。

Encodingは、シフトJISなので932が指定されています。これも適宜指定するとよいでしょう。省略するとUTF-8になるようです。

QuoteStyleは""の中に改行が現れた場合の処理です。セル内改行されているようなデータを読み込む際に必要だと思います。通常はQuoteStyle.Csvのままが良いでしょう。

詳細については、Microsoftのリファレンスを見た方が良いでしょう。

Csv.Document 

Table.PromoteHeaders

ファイルの一行目が見出しではなくデータであれば、この行は不要だと思います。

Table.TransformColumnTypes

列の型の変換、という事になるでしょう。

試した限りでは、Csv.Documentで読み込んだだけだとすべての列が文字として読み込まれていました。

文字以外に、数値や、日付を指定したい場合には、上の購入日や金額の例のようにデータタイプを指定します。

データ型を指定しなくても良い場合には、この行も不要だと思います。削除する場合には、最後の in uChangeTypeをuPromotedHeadersや、uSourceに変更する必要があります。

より汎用的にするには、ここで変換せず、読み込んだ後にセルの書式指定やクエリーテーブルのSQL文などで変更してしまっても良いのかもしれません。

クエリーの作成

Queries.Addを使ってワークブックにクエリーを追加しています。

Set uQuery = uWB.Queries.Add(Name:=uQueryName, Formula:=uFormula) 

追加されたクエリーはExcelの「データ」にある「クエリと接続」をクリックすると確認することができます。

テーブルの作成

ListObject.Addを使って、クエリーにリンクしたテーブルを作成します。

Set uList = uWS.ListObjects.Add( _
    SourceType:=xlSrcExternal, _
    Source:="OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;" & _
        "Location=" & uQuery.Name, _
    Destination:=uWS.Range(uDestination))

xlSrcExternalは次のページを参照するとよいでしょう。

XlListObjectSourceType 列挙 (Excel)

Sourceにデータを読み込むための文字列を指定します。Locationにクエリー名を指定することにより、指定したクエリーにリンクできるようです。

Destinationはテーブルを配置する場所を示します。

クエリーテーブルの設定と実行

テーブル（ListObject）に接続されたクエリーテーブルを設定し、クエリーを実行します。

クエリーとクエリーテーブルは別物なので注意が必要です。

With uList.QueryTable
    .CommandType = xlCmdSql
    .CommandText = Array("SELECT * FROM [" & uQuery.Name & "]")
    .ListObject.DisplayName = uListName
    .Refresh BackgroundQuery:=False '読込実行
End With

CommandTypeはxlCmdSqlで固定だと思います。

CommandTextにSQL文を指定できます。

ListObject.DisplayNameで、クエリーテーブルに接続されているテーブルの名前を変更しています。必須ではありません。

Refleshメソッドを実行することにより、クエリーが実行され、CSVファイルからの読み込みが実行されます。直接クエリーを実行しても同じ結果になると思います。

uDeleteTableAndQuery

指定されたテーブルとクエリーを削除しているだけです。

クエリーはワークブックにリンクし、テーブルはワークシートにリンクすることを理解しておいた方が良いでしょう。

クエリーテーブルの確認方法

クエリーテーブルは、テーブルクリック→「テーブルデザイン」「プロパティ」「クエリプロパティ」「定義」で確認できました。ただし、変更はできないようです。

クエリーテーブルで絞り込む

クエリーテーブルに指定したSQL文を変更することにより、絞り込みを行うことができます。

.CommandText = Array("SELECT * FROM [" & uQuery.Name & "]")

次のように指定することにより、指定した列のみをテーブルに表示することが可能です。

.CommandText = Array("SELECT モデル,金額 FROM [" & uQuery.Name & "]")

WHERE句も使えます。

.CommandText = Array("SELECT * FROM [" & uQuery.Name & "] WHERE モデル='LOLO'")

CommandTextを書き換えることにより、このようにフィルタリングを行うことが可能になります。

再読み込み

単純な再読み込み

データをファイルから再読み込みするだけであれば、次のようにクエリーテーブルの Reflesh メソッドを実行するだけで済みます。

'再読み込み
Public Sub uReflesh()
    Dim uWS As Worksheet
    Dim uList As ListObject
    
    Set uWS = Worksheets("Sheet1")
    Set uList = uWS.ListObjects("任意のテーブル名")
    
    uList.QueryTable.Refresh BackgroundQuery:=False
End Sub

CSVファイルに行を追加したあと実行すると、次のように読み込まれます。

CSVファイルを変更して再読み込み

CSVファイルのパスを変更する必要があるのなら、Formula プロパティの再設定が必要になるでしょう。

'CSVファイル変更
Public Sub uChangeCSV()
    Dim uQuery As WorkbookQuery
    Dim uCSVPath
    
    Set uQuery = ThisWorkbook.Queries("任意のクエリー名")
    
    uCSVPath = "C:\NKTemp\Sample2.csv"
    
    uQuery.Formula = _
        "let uSource = Csv.Document(File.Contents(""" & uCSVPath & """), " & _
            "[Delimiter="","", Encoding=932, QuoteStyle=QuoteStyle.Csv]), " & _
        "uPromotedHeaders = Table.PromoteHeaders(uSource, [PromoteAllScalars=true]), " & _
        "uChangedType = Table.TransformColumnTypes(uPromotedHeaders, " & _
            "{{""購入日"", type date}, {""金額"", Int64.Type}}) " & _
        "in uChangedType"
    
    uReflesh
End Sub

Sample.csvをSample2.csvとしてコピーして、LOLOを「変更後」と書き直し、実行した結果。

Sample2.csv の内容が読み込まれています。

このように、初回にクエリーとテーブルを作った後は、比較的短いコードでテーブルのアップデートが可能です。

Formula("uSource") = のように、Csv.Documentの部分だけ指定して変更できれば、もっとシンプルに書けるのですが、そのような方法は見当たりませんでした。

クエリーの編集画面ではできていますが、あれはM言語の構文解析を行っているのではないかと思います。

リンクに注意

上の例では、CSVファイルとテーブルはリンクされています。

そのため、ファイル内容を更新すれば、テーブルの内容まで更新される場合があります。

特に「すべての更新」を行うと、VBAで作成したクエリーもアップデートされてしまうかもしれません。

クエリーや、クエリーテーブルを削除することにより、更新を停止できるのではないかと思います。まだ、試していませんが。

誤訳

以前から、クエリーの編集画面に表示される「昇格されたヘッダー数」が意味不明でした。コードを読んでも、これ数値じゃないんじゃね？と思っていました。

今回、調べたところ、元の英語は PromotedHeaders でした。なので、昇格されたヘッダー、もしくは昇格された見出し、と訳すのが正しいでしょう。

おわり

今回説明したような方法を使うと、VBAからCSVをいかようにでも読み込むことが可能になります。

ただ、定型業務で、CSVファイルだけその都度他のファイルに変更したいという場合には、もっと簡単な方法もあります。

VBA クエリーで読み込むCSVファイルを簡単に切り替える

これまで、QueryTableを使ってCSVを読み込んでからテーブルに変換したり、PowerQueryの読み込みをマクロ記録したコードを手直しして対応してきたため、CSVファイルの処理は憂鬱でした。

今回、一念発起してクエリーとQueryTableとListObjectを調べてコードを整理してみました。ようやく容易にCSVを処理できそうです。

2022/10/23 追記　使いやすいようにパラメーターをConstantにしてまとめました。