Qlik Training Blog

開発・スクリプト

大量のExcelファイルを一つにまとめる方法

公開日：
更新日：
開発・スクリプト
#QlikView
#Qlik Sense
#Qlik Cloud
#開発者
#データロード

QlikViewを使用すれば、社内に散在する大量のExcelファイルから一括でデータを取り込み、一つに統合できます。さらに、一つにまとめたデータをQlikViewで分析したり、Excel形式やCSV形式で出力したりできます。

前回の記事では、二つのExcelファイルを一つに統合する例をご紹介しましたが、ファイルが大量にある場合に一つ一つ指定していくのは手間です。このような場合は、ファイル名にワイルドカード（?、*）を指定すれば、あるディレクトリ中のファイルを簡単に一括で取り込めます。

ファイルのダウンロード

ここでは以下のファイルを使用しますので、ダウンロードしてください。
combine-many-excel-files.zip
Zip形式ですので、ダウンロード後解凍してください。
今回は使用する例ごとに、フォルダを分けてあります。

Excelファイルの統合1（あるディレクトリ中のファイルをすべて取り込む）

それではまず、一番よくありそうな例として、”あるディレクトリ中のファイルをすべて取り込む”という処理をおこなってみましょう。

ここでは「例1」のフォルダを使用しますので、フォルダを開いてみてください。
下図のようにExcelファイルが大量にあります。

これらのファイルは年月ごとに分かれており、12ヶ月ｘ3年分で計36ファイルあります。
ファイル名の末尾が年月をあらわします。たとえば「売上200801.xls」は2008年1月のデータです。
各ファイルには約350件ずつデータが格納されており、全ファイルあわせると約1万3千件（13,353件）になります。

Excelファイルの内容はすべておなじ形式です。下図のように「日」「商品名」「売上金額」といったデータが格納されています。

下準備（一つのファイルを取り込む）

それではデータを取り込んでみましょう。
ファイルを新規に作成し、［ロードスクリプトの編集］画面を起動します。

まずはファイルを一つだけ取り込んでみます。
ロードスクリプトの最下行にカーソルをあわせます。
［相対パス］にチェックを入れて、［テーブルファイル］ボタンをクリックします。

［相対パス］については後ほど説明します。

「例1」フォルダの「売上200801.xls」を選択します。

今回は単純にデータを取り込むだけなので、そのまま［終了］ボタンをクリックします。

［相対パス］にチェックを入れると、読み込むファイルの指定がファイル名のみになります。
今回の例では、ドキュメント（qvwファイル）とおなじディレクトリにある「売上200801.xls」という意味になります。

［相対パス］にチェックを入れなかった場合は、下図のようにフルパスの指定になります。
どちらでも問題はないのですが、今回は相対パスの方が指定しやすいため相対パスにしました。

さらに［相対パス］にチェックを入れた場合、LOAD文の上に「Directory」という文も挿入されます。
しかし、Directory文はここでは不要なので削除します。

Directory文の詳細はヘルプなどでご確認ください。検索するファイルのパスを指定するためのもので、Excelファイルの統合とは直接関係ない機能です。

ワイルドカードの指定（すべてのファイルを取り込む）

このままだとファイル名が固定で指定されているため、一つのファイルしか取り込まれません。
ファイル名の指定をワイルドカードの指定に置き換えます。
変更前

売上200801.xls

変更後

*.xls

この指定により、ドキュメント（qvwファイル）とおなじディレクトリにある、すべてのExcelファイル（拡張子が.xlsのファイル）が取り込まれます。
ロードスクリプトはひとまず完成です。［OK］ボタンをクリックします。

データを取り込んでみましょう。
［リロード］ボタンをクリックします。

ファイルを保存してください。

今回は相対パスの指定にしたため、ドキュメントはExcelファイルとおなじ場所に保存してください。
「例1」フォルダを開き、名前をつけて保存します。

ファイルが立て続けに読み込まれ、「例1」フォルダにあるすべてのExcelファイルが取り込まれました。

ファイル名から年月の項目を作成する

では、今回読み込んだExcelファイルをあらためて確認してみましょう。
年月のデータがExcelファイル中にないため、単純にデータを取り込んだだけでは、どの年月のデータか分からなくなってしまいます。

このような場合は、ファイル名をもとに項目を作成できます。

［ロードスクリプトの編集］画面を起動してください。
ファイル名を取得するには、ファイル関数を使用します。
ファイル関数にはいくつか種類がありますが、ここではFileBaseName関数を使用するとよいでしょう。FileBaseName関数はファイル名を拡張子なしで取得します。
また、文字列の一部を抜き出すには、Mid関数を使用するとよいでしょう。

LOAD文に年と月の項目を追加してください。

LOAD Mid(FileBaseName(), 3, 4) as 年,
Mid(FileBaseName(), 7, 2) as 月,
日,
:

関数の指定はそれぞれ以下の意味になります。
Mid(FileBaseName(), 3, 4) ファイル名の3文字目から4文字分を抜き出す。
Mid(FileBaseName(), 7, 2) ファイル名の7文字目から2文字分を抜き出す。

1  2  3  4  5  6  7  8
売 上 2  0  0  8  0  1
      |--------|  |--|
          年       月

リロードを実行してください。

取り込んだデータを確認するために、テーブルボックスを作成してみましょう。 シートの余白を右クリックし［シートオブジェクトの追加］→［テーブルボックス］を選択します。

画面中央の［すべて追加］をクリックします。

画面右の［ロード順］ボタンをクリックして、項目の並び順をデータを取り込んだ順（Excelファイルとおなじ順番）にします。
［OK］ボタンをクリックします。

テーブルボックスが作成されました。
また、ファイル名から「年」と「月」の項目も作成できました。

一つにまとめたデータをExcelで使用したい場合は、テーブルボックスの機能でExcel出力すると簡単です。
テーブルボックスの右上にある［Excel出力］をクリックします。

Excelファイルに出力できました。

データが大量にある場合、Excel出力に時間がかかる場合があります。その場合は、ロードスクリプトにSTORE文を追加して、CSVファイルに出力してください。

STORE テーブル名 into ファイル名.csv(txt)

今回の例であれば、ロードスクリプトの末尾に以下の指定を追加します。

:
NEXT
STORE 売上$ into 売上.csv(txt)

以上で一つ目の例は終了です。
複数のExcelファイルを、簡単に一つにまとめられることが、お分かりいただけたのではないでしょうか。

Excelファイルの統合2（ディレクトリが複数ある場合）

繰り返し処理（FOR文）の指定

一つ目の例では、一つのディレクトリ中のファイルをすべて取り込む例を紹介しましたが、ここからはディレクトリが複数に分かれている場合の例を紹介します。

ディレクトリが複数に分かれているときは少し指定が複雑になり、繰り返し処理（FOR文）が必要になります。
繰り返し処理は、プログラミング（Excelで言うマクロ）の一種ですが、コマンドを数行記述するだけですので、プログラミングに苦手意識のある方でも問題なく理解できると思います。

ここでは「例2」のフォルダを使用しますので、フォルダを開いてみてください。
年ごとのフォルダがあります。

各フォルダの中に月ごとに分かれたファイルがあります。

ドキュメントはさきほど作成したものをコピーして使用します。
「例1」フォルダにあるドキュメントを、「例2」フォルダにコピーしてください。

ドキュメントを開き、［ロードスクリプトの編集］画面を開きます。
複数のフォルダから一括でファイルを読み込みたい場合は、繰り返し処理（FOR文）が必要です。
まず、LOAD文の前後に以下のように「FOR」と「NEXT」の指定を追加します。

FOR Each DirName in DirList *

LOAD ...

NEXT

つぎに、ファイル名の指定を以下のように変更します。

FOR Each DirName in DirList *

$(DirName)\*.xls

ロードスクリプトの全文は以下のようになります。

初期設定では、\（円マーク）がバックスラッシュで表示されますが、これはフォントの問題です。
フォントを「ＭＳ ゴシック」などの円マークが表示できるものに変更すれば、円マークで表示されます。
フォントを変更したい場合は、［ツール］メニュー→［エディタ設定］から変更できます。

繰り返し処理（FOR文）の解説

まず、繰り返し処理では「FOR」と「NEXT」で囲んだ範囲が繰り返し実行されます。
このとき、指定した変数に値が繰り返し代入されていきます。

FOR Each DirName in DirList *

この指定により、フォルダの数だけ処理が繰り返し実行されます。
「DirName」は変数名の指定です。フォルダのパスがこの変数に順次格納されていきます。
末尾の「*」は読み込むフォルダ名の指定です。ここでもワイルドカードが使用できます。
今回は「*」と指定してすべてのフォルダを対象としています。

$(DirName)\*.xls

$(DirName)は変数の指定です。QlikViewでは変数の値を呼び出す際「$(変数名)」と指定します。

つまり、今回の繰り返し処理は、以下のようにLOAD文を三回指定したのとおなじ意味になります。
各LOAD文の中では、読み込むファイル名の指定（FROM句の後ろ）だけが書き換わっています。

LOAD ...
FROM
C:\Users\qlikview\Desktop\combine-many-excel-files\例2\2008\*.xls
(biff, embedded labels, table is [売上$]);

LOAD ...
FROM
C:\Users\qlikview\Desktop\combine-many-excel-files\例2\2009\*.xls
(biff, embedded labels, table is [売上$]);

LOAD ...
FROM
C:\Users\qlikview\Desktop\combine-many-excel-files\例2\2010\*.xls
(biff, embedded labels, table is [売上$]);

リロードを実行してください。
計三つのフォルダから36ファイルが読み込まれます。

年月の項目の修正

ここまでで全ファイルが取り込めました。しかし、テーブルボックスで「年」と「月」の項目を確認すると、値がおかしくなっています。
ファイル名などが前回とは変わっているため、「年」と「月」の指定も変更する必要があります。

［ロードスクリプトの編集］画面を開きます。
「年」と「月」の指定を以下のように変更してください。
「年」を作成するには、FileDir関数でフォルダ名を取得してください。FileDir関数はフォルダ名をフルパスで取得する関数です。
また、フォルダのパスから一部を抜き出すには、SubField関数を使用するとよいでしょう。
変更前

LOAD Mid(FileBaseName(), 3, 4) as 年,
Mid(FileBaseName(), 7, 2) as 月,

変更後

LOAD SubField(FileDir(), '\', -1) as 年,
Mid(FileBaseName(), 3, 2) as 月,

月の指定は文字数を変更しているだけなので、説明は省きます。

SubField関数は、文字列がある文字で区切られているときに、区切られた部分を取得する関数です。
今回の指定は、フォルダのフルパスを\（円マーク）で区切って、一番後ろの部分を取得します。「-1」が一番後ろ（後ろから一つ目）という意味です。
今回の例で言えば、「-1」と指定するのと「7」と指定するのはおなじ結果となります。

C: \ Users \ qlikview \ Desktop \ combine-many-excel-files \ 例2 \ 2008
| 1 |   2   |    3     |    4    |            5             |  6  |  7  |
指定 結果
SubField(FileDir(), '\', -1) 2008
SubField(FileDir(), '\', 1) C:
SubField(FileDir(), '\', 2) Users
SubField(FileDir(), '\', 6) 例2
SubField(FileDir(), '\', 7) 2008

リロードを実行します。
「年」と「月」が取得できました。

以上で二つ目の例も終了です。
二つ目の例では、ディレクトリが複数に分かれている場合の例をご覧いただきました。

振り返り

今回は大量のExcelファイルを一つにまとめる方法についてご覧いただきました。以下に重要なポイントをまとめます。

・［相対パス］
初期設定：無効
有効にすると、読み込むファイルの指定がファイル名のみの指定になる。
無効にすると、読み込むファイルの指定がフルパスの指定になる。
つまり、有効にした場合はドキュメント（qvwファイル）とおなじディレクトリにあるファイルが検索される。
・ワイルドカード
ファイル名やフォルダ名を指定する箇所では、ワイルドカードが使用できる。
?：任意の一文字
*：任意の複数文字
・繰り返し処理（FOR文）
複数のディレクトリから一括でデータを取り込む場合は、繰り返し処理（FOR文）が必要。
FORとNEXTで囲んだ範囲が繰り返し実行される。
以下の指定で複数のフォルダを一括で処理できる。

FOR Each 変数名 in DirList フォルダ名

・ファイル名やフォルダ名の取得には、ファイル関数を使用する。
FileBaseName関数（拡張子なしのファイル名）、FileDir関数（フォルダのフルパス）など。
・文字列から一部を抜き出すには、以下のような関数を使用する。
Mid関数（文字数を指定）、SubField関数（区切り文字で区切られた一部を取得）など。

最後に…

今回は大量のExcelファイルを一つにまとめる方法を紹介しました。
この記事を書くにあたり、そもそもExcelだけで簡単にファイルを統合できないのか、あらためて調べてみましたが、やはりマクロ（VBA）を記述する必要があるようです。
マクロを一から記述するのに比べれば、QlikViewを使用した方が簡単だと思いますが、いかがだったでしょうか。

さらにQlikViewでは、複数列に分かれたデータを一列にまとめたり、重複するデータを一意にするようなことも簡単にできます。以下の二つの記事もぜひ見てみてください。
複数列に分かれたデータを一列にまとめる方法（と空白のセルを埋める方法）
重複するデータを削除して一意にする

お疲れ様でした。

この記事をシェアする