日々の業務を行う中で、テキストファイルを Excel で処理する機会は少なくありません。
システムの出力結果や、外部取引先からの提供データなど、CSV形式や、タブ区切り形式などのテキストファイル を入手することは多いものです。
テキストファイルを使うには多くの場合で データの加工が必要 です。ファイルが手に入るたびに同じデータ加工作業をするのはとても非効率です。
それに処理が必要なテキストファイルを大量に入手した場合は、いくら慣れていたとしても作業をするのはとても苦痛ではないでしょうか？
Power Query はそんな悩みを解決してくれます。データの加工は Power Query エディターでマウス操作で可能です。その上、一度データ加工手順を設定したら、Power Query が毎回自動で大量なデータでもあっという間に処理してくれます。
本記事では Power Query を使って効率的にテキストファイルを読み込む方法 を、実務でよくある課題とその対策も含めて解説させていただきます。
仕事でテキストファイルのデータを使っている方は是非この知識を身につけて、楽々仕事をこなせるようになって下さい。
では、早速いってみましょう！
⸻
テキストファイル読み込みの２つのパターン
Power Query でテキストファイルを読み込む基本操作は2つのパターンがあります。
- ファイルを１つ だけ読み込む
- フォルダ内のファイル をまとめて読み込む
ファイル読み込みのおおまかな流れは下の図のようになります。
フォルダ内のファイル読み込みの操作は少し複雑ですが、後からファイルが増えても Power Query が自動で処理をしてくれるので、こちらがオススメです。
１つのファイルを読み込み
ファイルを１つだけ読み込む場合は、まずメニューから図のように操作します。
続いて読み込むファイルを選択すると図のようなウィンドウが出てきます。
ここで次の処理を以下の３つの中から選択します。
- データの変換： ファイルを読み込んだ後にデータを編集します。通常はこれを選択することをオススメします。
- 読み込み： 読み込んだデータをそのまま Excel テーブルに出力します。
- 読み込み先…： 読み込んだデータを Excel テーブルに出力するか、何も出力せずクエリだけ作成するかなどを選択します。
通常ではデータを読み込んだままの状態では使えないものが多いので、上記の 1. データの変換 を選択してデータの編集を続けます。
ここでは 1. データの変換 を選択するケースで解説します。
次の「フォルダ内のファイルをまとめて読み込む場合」の項を飛ばして、「ファイルごとのデータ加工」に進んで下さい。
フォルダ内のファイルをまとめて読み込む
フォルダ内のファイルをまとめて読み込む場合は、まずメニューから図のように操作します。
上の図の操作の後で読み込むフォルダを選択すると、図のようなウィンドウが出てきます。
ここで次の処理を以下の５つの中から選択します。
選択肢が多く、慣れている人でも戸惑ってしまうポイントです。
次の編集操作のことを考えると、3番目の 結合および読み込み先… がオススメです。
- データの結合と変換： フォルダ内のデータを結合して、引き続きデータ加工を編集できます。
- 結合および読み込み： フォルダ内のデータを結合して、そのまま Excel のテーブルに出力します。
- 結合および読み込み先…： フォルダ内のデータを結合して、その後にExcel テーブルに出力するか、何も出力せずクエリだけ作成するかなどを選びます。
- 読み込み： ファイル一覧を Excel のテーブルに出力します。データを結合する処理は後で自分で編集しなければならず、上級者向けのためあまりオススメしません。
- 読み込み先…： ファイル一覧を Excel テーブルに出力するか、何も出力せずクエリだけ作成するかなどを選びます。こちらも上級者向けであまりオススメしません。
- データ変換： ファイル一覧を取得した状態からデータ加工を自分で編集をします。こちらも上級者向けであまりオススメしません。
フォルダからデータを読み込む機能はファイルがいくつあってもデータを結合できるのが大きなメリットです。
ですから、少なくともこの選択肢の中では最初から 結合 してくれる 1から3 の選択肢を選ぶべきです。
ここではオススメの 3. 結合および読み込み先… を選択するケースで説明を続けます。
3. 結合および読み込み先… を選択すると、「ファイルの結合」ウィンドウが表示されます。
ここで図に示したように 1～4 のファイル読み込みの設定ができます。
Power Query が自動で設定してくれますが、プレビューの内容が想定通りでは無い場合は設定を変更してみて下さい。
文字化けする場合
ファイルを読み込んだ状態で文字化けしている場合は、文字コードの設定を変更します。
図のように日本語だけでも７種類の文字コードを設定できますので、これらを試してみて下さい。
OKボタンを押すと続いて図のようなウィンドウが表示されます。
最初のうちは「テーブル」を選択したままOKボタンを押すことで問題ないです。
これでフォルダ内のファイルが全て読み込まれてテーブルにデータが出力されます。
しかし、テキストファイルは様々な形態があるため、ファイルごとにデータの加工が必要 な場合が多いです。
ファイルごとのデータ加工は、サンプル ファイルの変換 クエリで処理されていますので、これを編集します。
図のようにメニューを操作してサンプル ファイルの変換 をダブルクリックして Power Query エディターを開きます。
ファイルごとのデータ加工
ここからは １つのファイルを読み込む ケースと、フォルダ内のファイルをまとめて読み込む ケースと共通になります。
テキスト形式で提供されるデータは図のようにそのままでは使えないケースがありますので、これを Power Query のデータ加工機能で加工していきます。
不要なヘッダー行を削除
上の図では1行目は不要な行になっていましたのでこれを削除します。Power Query エディター で図のように操作します。
1行目の内容を列名にする
読み込んだデータはまだ列の名前が設定できていません。１行目の内容をそのまま列名にするには図のように操作します。
ファイル名もデータに変換
ファイル名に必要な情報が含まれている場合はデータを加工してデータを抽出します。
サンプルの例では図のようにすることで店名をファイル名の列から抽出することができます。
数値型をテキスト型に修正するケース
Power Query は自動的に数値や日付などを推定して型を設定します。しかし、自動判定がかえって困るケースもあります。
例えば 00102 のような数字が並んだデータがあると、Power Query は数値のデータ型に設定して、102 という数値に変換します。
しかし、ゼロも含めた文字列として扱いたい場合はこれでは困ります。
この場合は自動で設定されたデータ型を修正します。
Power Query エディターの右に表示はされているステップからデータ型を設定しているステップを選択してデータ型の設定を変更します。
こうすることで、無事 00102 はそのままの文字列として残すことができます。
日付と日時のデータ型を修正
日付と日時は似ていますが、データ型としては別のものです。
データ型が違ったまま扱っていると後工程でエラーになりかねません。
時間の部分が不要な場合は日付型にしておきましょう。
逆に時間の部分が必要なのであれば日時型にしておきましょう。
データ型の設定はなるべくデータ処理の早い段階で行うのがベストプラクティスです。
一定行数ごとにページになっているデータの抽出
システムの出力ファイルなどで、図のように一定行数ごとにページが構成されているようなケースがあります。
これは人間の目には理解しやすい形ですが、Power Query でデータを読み込むには工夫が必要です。
このような場合は図のようにしてデータを編集していきます。
「剰余」とは 割り算の余り のことです。
行番号を1ページの行数で割った余りは、各ページごとの行番号を求めることができます。
各ページごとの行番号があれば、各ページの中でデータがある行だけを抽出することができます。
しかし、その前にヘッダー（列名）の設定をしておきます。
ヘッダーを設定したらページごとの行番号の「剰余」列を使ってデータを抽出します。
このようにすることで、ページのようになっているテキストデータから必要なデータだけを抽出することが可能です。
⸻
実務での活用例
日次出力されるCSVファイルの処理
会社などでは様々なシステムが動いていて、システムから日次で出力されるテキストファイルを処理して内容を確認するような作業があると思います。
このようなケースでは Power Query は非常に便利です。ここで紹介したような方法で一度クエリを作成してしまえば、図のようにしてデータを更新するだけで最新ファイルも含めてデータを全て読み込み、データ加工までを Power Query が自動で処理してくれます。
図
人の手を介さずにデータ処理をする仕組みを構築できるのが Power Query の大きなメリットです。
システム出力ログの整理
システムのログも通常ではテキストファイルで出力されます。これも Power Query でデータを取り込んで、特に注目すべき情報だけに絞ってデータをアウトプットすることで人間によるログチェックの負担が大きく削減できます。
⸻
まとめ
この記事では Power Query でテキストファイルの読み込む方法についてご紹介しました。
業務の中で活用する機会が多いテキストファイル、毎回同じようなデータ加工をしているのであれば Power Query で作業を大幅に効率化できます。
また、テキストファイル形式のデータは様々な形をしている場合が多いですが、この記事で紹介したようなテクニックでデータ部分を抽出することが可能です。
データ処理の業務があるのであれば、是非 Power Query を活用して業務の効率化を図ってみて下さい。
