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 WithEnd 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 NextEnd Sub
解説
「マクロの記録」を使ってCSVを読み込んだ際に生成されるコードを元に作り直しています。
最初の Constant はパラメーターです。
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 uWS = uWB.Worksheets(uSheet) 'テーブルを作成するワークシート
繰り返し読み込む場合に備えて、最初にクエリーとテーブルの削除を行っています。
uDeleteTableAndQuery uWS, uListName, uQueryName
M言語部分
クエリー(Power Query)の命令部分です。M言語で記述されています。
uSourceでCSVファイルを開き、uPromotedHeadersでCSVファイルの一行目を見出しにして、uChangedTypeで列のデータ型を指定しています。
"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のリファレンスを見た方が良いでしょう。
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を使って、クエリーにリンクしたテーブルを作成します。
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）に接続されたクエリーテーブルを設定し、クエリーを実行します。
クエリーとクエリーテーブルは別物なので注意が必要です。
.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 プロパティの再設定が必要になるでしょう。
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ファイルだけその都度他のファイルに変更したいという場合には、もっと簡単な方法もあります。
これまで、QueryTableを使ってCSVを読み込んでからテーブルに変換したり、PowerQueryの読み込みをマクロ記録したコードを手直しして対応してきたため、CSVファイルの処理は憂鬱でした。
今回、一念発起してクエリーとQueryTableとListObjectを調べてコードを整理してみました。ようやく容易にCSVを処理できそうです。
2022/10/23 追記 使いやすいようにパラメーターをConstantにしてまとめました。
コメント
コメントを投稿