Excel Power Query フォルダーから複数データ結合

Power Query 第4回目です。今回は複数のCSVファイルを保存しているフォルダーからまとめてデータをインポートして結合する方法を勉強します。

※「フォルダーから」のデータ取得で「予期せぬエラー」が発生したため中断していました。
(詳細はリンクの記事をご覧ください)

くるみこ
くるみこ

「予期せぬエラー」発生で中断していましたが再開します(^^)
今回も前回と同じCSVファイルを使用します。フォルダー内にまとめて保存してあるCSVファイルのデータをまとめて取得・結合する方法を勉強しましょう。

ダウンロードしたCSVファイルは、ほとんどの場合まとめてフォルダーに格納しています。それをまとめてインポートして結合までしちゃうということですね!
よろしくお願いしますm(__)m

【この記事でわかることは】
・フォルダーから複数ファイルのデータをまとめて取得・結合する方法
・CSVデータとExcelブックを取り込む際の違いについて

前回記事のおさらいは、下のカードをクリックすれば開きます(^^ゞ

・前回の内容は単独のCSVファイルからデータをインポートする方法でした。

記事作成にあたっては次の Microsoft office のサポートの情報などを参照しています。
Power Query for Excel のヘルプ
Excel の Power Query について

フォルダーから複数ファイルをインポートする方法

はじめに、この記事の画像は Microsoft 365 で取得した画像です。Excel2016 のリボンやメニューなどバージョンによって違うようなので、適宜読み替をお願いします(^^)/(下図は一例です)

Microsofr 365
Excel 2016 のリボン

「フォルダから」取得する場合の条件を確認します

・フォルダを指定することでフォルダ内の複数ファイルを同時にインポートすることができます。
・今回この記事で使用するCSVファイルだけでなく、Excelブック等もインポートできます。
・ただし次のような条件があるので確認しておきましょう。
・条件に合わなければ「エラー」が発生します。(壊れることはありませんが)

読み込みできる条件

・ファイルの「スキーマ」(構造)が同じである必要があります。
・CSVファイルならばファイル内のデータは「同じ列名」で構成されている必要があります。
・データの「区切り記号」(コンマとかタブとか)も同じでなくてはいけません。
・Excelブックなら「シート」があるので、さらに「シート名」も同じでなくてはいけません。
・ファイルの種類「拡張子」も統一されていないとエラーになります。
・パスワード付きファイルも当然エラーで読み込みできません。

その他の留意事項

同じフォルダ内のファイルは全て読み込む仕様になっています。
・フォルダには下位のフォルダ(フォルダ内のフォルダ)も対象に入ります。
・なので、対象にしたくないファイルやフォルダは別の場所に移しておく必要があります。
 ※読み込みたくないファイルは読み込む際に指定することもできますが除外しておくほうがよいでしょう。

フォルダ内の全CSVファイルからデータを取得します

[データ] >[データの取得]>[ファイルから]>[フォルダから]>[フォルダー]ダイアログ(下図)が表示されるので「フォルダー パス」に入力します。[参照]ボタンを押してフォルダー選択ダイアログを表示させ、選択指定するか手入力後に「開く」をクリックします。

・実行しているGIF画像を下に貼り付けました。
※「フォルダーから」のデータ取得で「予期せぬエラー」が発生したため中断していました。
(詳細はリンクの記事をご覧ください)
・発生していたエラー?のため本来表示される「フォルダー パス」のダイアログが表示されず「参照」ダイアログが直接表示されていることが確認できると思います。(仕様が変わった?)

インポートするデータの情報が取得できました

Excelブックの場合は「ナビゲーター」ダイアログが表示され、シートを選択してインポートしましたがいきなり下の画像のように内容がプレビューされました。

・「結合」「読み込み」「データの変換」と3種類のボタンがあります。
・以下それぞれについて説明していきますが「結合」については別段で詳しく説明します。

それぞれの方法でインポートしてみます

「データの変換」を選択した場合

・「データの変換」→「Power Quryエディター」が起動され、編集できるようになります

・取り込んだフォルダー内データの一覧が表示されます。
・フォルダ内に取り込む必要のないファイルが一緒に保存されていたりした場合などや、取り込みするデータのフィルタリングして絞る場合などの作業を行うことができます。
・設定完了後、ここから結合を行うには[ホーム]→[結合]→[File の結合]選択で結合を開始します。

「読み込み」を選択した場合

・先ほどの「データの変換」と同じクエリが作成されますが、新規シートが作成されてテーブルにデータが表示されます。
・この状態から編集するには「クエリと接続」内に作成されている「CSV」クエリをダブルクリックして「Power Query エディター」を起動させて編集します。

「読み込み先…」を選択した場合

・右画像「データのインポート」ダイアログが表示されます。
・インポートするデータをどのように表示するかが選択できます。
・「テーブル」を選択してデータを表示できます。

・ここでは「接続の作成のみ」で接続専用クエリを作成しておきましょう。

・作成した「クエリ」を編集するには「Power Query エディター」を起動させて編集します。

「結合」を選択した場合

・「結合」の動作は「データの変換」→「読み込み」→そして対象データの結合という流れです。
・「結合」は、「データの結合と変換」「結合および読み込み」「結合および読み込み…」という3つの選択肢に分かれています。
・次の項で詳しく解説していきます。

フォルダー内のファイルデータを結合する

・ここからは「結合」について詳しく見ていきます。

「結合」を選択した場合の動作

・「結合」は次の3つのオプションを選択して実行できます。
・①[データの結合と変換] ②[結合および読み込み] ③[結合および読み込み…]です。
・動作の違いは次のとおりです。

①[データの結合と変換]→[Fileの結合]→[PowerQueryエディタ起動]→[クエリ表示]
 →※[閉じて読み込む]→[完了]
 ※[閉じて次に読み込む…]を選択した場合[データのインポート]ダイアログが表示されるので読み込み先等を指定することができます。

②[結合および読み込み]→[Fileの結合]→[完了]
 ※読み込み先はデフォルトで「新規ワークシート」のテーブルに読み込まれます。
 ※出来上がったシートを削除してしまえば「読み込み専用」にできます。

③[結合および読み込み…]→[Fileの結合]→[データのインポート]→[完了]
 ※[データのインポート]ダイアログで読み込み先等を指定することができます。

・私のおすすめは、①[データの結合と変換]です。
・理由は、[Power Query エディタ]で表示されたデータの型変換や不要な列の削除などの編集が作成時に行えるからです。
・何も編集シュル必要がないのであれば、②と③のほうがお手軽で簡単です。編集が必要になった場合は後からでも可能です。

・では、①[データの結合と変換]を詳しく見ていきましょう。

[データの結合と変換]選択時の動作

[Fileの結合]

・「サンプルファイル」で最初のファイルが表示されています(ファイルは変更できます)
・「元のファイル」では、表示データが文字化けしている場合にエンコードを調整できます。
・「区切り記号」も、読み込みがおかしい場合、元ファイルに合わせて変更します。
・「データ型検出」で検出しない設定にも変更できます(タイトルがあるなど場合)
・「エラーのあるファイルをスキップする」は表記のとおり動作たい場合チェックします。
・設定完了後[OK]ボタンを押下します。

PowerQuery エディタ が起動します

・読み込むでクエリを作成する前に必要な編集を行うことができます。
・不要な列や行を削除したり、列の位置を入れ替えたり、列毎のデータ型変更や文字列の置き換えなどの様々な編集ができます。
・「ホーム」タブの他「変換」「列の追加」「表示」タブがあり色々な編集ができます。
・前回記事中の「Power Query エディターで編集」も参照してみてください。
・編集を行うと、右側の「クエリの設定」内の「適用したステップ」にすべて記録されていきます。
・「ステップ」を削除するとその編集は無効となり、編集前の状態に戻すことができます。
・編集終了後は一番左にある「閉じて読み込む」(下の画像)でデータを指定のとおりに読み込みます。

・「閉じて次に読み込む…」を選択すると「読み込み先…」の時と同じく右画像の「データのインポート」ダイアログが表示されます。

・ここでは、「接続の作成のみ」を選択し、「このデータをデータモデルに追加する」をチェックします。設定後に「OK」押下で結合が完了します。

完了後の「クエリと接続」を確認してみます

・「フォルダーから」フォルダー内のデータを結合できましたが、「クエリと接続」を見ると、どんな仕組みなのかをザックリと確認することができます。

・「CSVからファイルを変換する」というグループの中に、さらに「ヘルパークエリ」と「その他のクエリ」という2つのグループが作成されているのが確認できます。

・結合されて出来上がったクエリは一番下の「CSV」です。
・「その他のクエリ」というグループ内にあります。
・「データモデル」に15行読み込またことがわかります。

「ヘルパークエリ」グループには4つのクエリが作成されています。
・中身についてここでは詳しく解説しませんが、4つのクエリは相互に関連を持って作用しています。ソースデータのあるフォルダーの指定やファイルの変換方法などがすべて細かく記録されています。

「フォルダーから」の利用方法

「フォルダーから」取得する方法は、ファイルを指定するのではなく「フォルダー」内にあるファイルのデータを取得する機能です。

「フォルダー」内のファイルは「スキーマ」(構造)が同じであれば、ファイル名が違っていても、ファイル数が増減してもいいんです。

「フォルダー」内にファイルを追加しすれば、追加されたファイルを読み込んでデータを更新してくれるのです。すごく便利ですよね(^^♪

ファイルを増減して確認してみます

・GIF画像でご確認ください。

・ファイルが増減させる毎に「すべて更新」をクリックしています。
・シートの「テーブル」データが更新されるのがお分かりいただけると思います。
※このGIF作成のためにシートにデータを表示させています。

Excelブックでも結合できます

・複数のCSVファイルの結合についてみてきましたが、CSVファイルだけでなく、複数のExcelブックでも同じようにを結合することが可能です。

・Excelブックのデータは、テーブルだけではなく、普通のワークシートのセル範囲データでもOKです。

・Excelブックの場合は、次の画面のような「Fileの結合」ダイアログが表示されます。CSVのときとは違って、左側にブック内のシート一覧が表示されています。

・シート名を選択するとプレビューが表示されます。

・該当の読み込むシート名またはテーブル名を選択指定します。

・あとは、CSVファイルと同じ手順で結合することができます。

Excelブックの場合に注意しなければならないのは、読み込むシート名がすべて統一されていなければならない点です。

・シート名が異なっていると、エラーになって結合できませんので注意してくださいね(^^;

スポンサーリンク

まとめ(おわりに)

・以上で、「フォルダから」データ取得・結合する方法の勉強は終了です。いかがでしたでしょうか?
・今回は、記事内で使ったCSVファイルのサンプルファイルを登録しておきます!
・是非サンプルを使って実際にファイルの結合を試してみてください。

今までの記事のサンプルも登録していますのでよろしければお使いください(^^)

まとめと感想など

フォルダに保存したCSVファイルの結合法については何となくですが理解できました。もっと実際に自分で動かしてみて修得したいと思いました。
実際に今後の仕事で確実に役に立つと思いました(^^♪

くるみこ
くるみこ

そうですね! 一番使う頻度が高いかもしれませんね。

でも、「フォルダから」の取得がもっと沢山設定したとすると、その分の「ヘルパー クエリ」が作成されることになるので、クエリが増殖してしまうのが気になるところです。そのあたりのことについて別途調べておきますね。
次回は その点について検証しながらもう少し深く勉強しましょう(^^)/

【今回わかったことは】
・フォルダーから複数ファイルのデータをまとめて取得・結合する方法
・CSVデータとExcelブックを取り込む際の違いについて

★★★ ランキング参加中! クリックしてね(^^)/ ★★★

【今後の記事について】

今回の記事はいかがだったでしょうか。皆さまのお役に立てたなら幸いです(^^;
「汎用でだれでも使えて活用できるように考えてる」というポリシーで、記事を継続して書いていきたいと思っています。どうぞよろしくお願いしますm(_ _)m

【検討中の今後の記事内容は・・・・・】
・実務に役立つものを提供できるよう常に検討しています(^^ゞ
・その他雑記的に「プチネタなど」もいろいろ考えていきたいと思います・・・・・
・今後の記事にご期待ください(^^)/

過去記事のサンプルファイルをダウンロードできます

今回の記事で使用したCSVサンプルファイルを登録しています
過去の記事で使用したサンプルファイルもダウンロードできるようにページを設置していますので、こちら(このリンク先)からご利用ください