(旧版)大量のExcelファイルをひとつにまとめる方法

(旧版)大量のExcelファイルをひとつにまとめる方法

Pocket



前のページでは、ディレクトリが一つのときの例を紹介しましたが、このページではディレクトリが複数ある場合の例を紹介します。



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

ここでは「例2」のフォルダを使用しますので、フォルダを開いてみてください。
二つ目の例は、ファイルが複数のフォルダに分かれているというものです。

年ごとにフォルダが分かれています。
combine-many-excel-files-201
そして、月ごとにファイルが分かれています。
combine-many-excel-files-202
ドキュメントはさきほど作成したものをコピーして使用します。
「例1」フォルダにあるドキュメントを、「例2」フォルダにコピーしてください。
combine-many-excel-files-203
ドキュメントを開き、[ロードスクリプトの編集]画面を開きます。
FOR文を以下のように変更します。
変更前
FOR Each fileName in FileList *.xls
変更後
FOR Each fileName in FileList 2008\*.xls, FileList 2009\*.xls, FileList 2010\*.xls
combine-many-excel-files-204「in」の後ろには、複数の値を,(カンマ)区切りで指定できましたが、「FileList」もおなじように複数指定できます。

初期設定では、\(円マーク)がバックスラッシュで表示されますが、これはフォントの問題です。
フォントを「MS ゴシック」などの円マークが表示できるものに変更すれば、円マークで表示されます。
フォントを変更するには[ツール]メニュー→[エディタ設定]を選択します。
combine-many-excel-files-205
フォントを指定します。
combine-many-excel-files-206combine-many-excel-files-207以降の説明では初期設定のフォントである「Courier New」を使用します。フォントの設定はもとに戻しておいてください。

リロードを実行してください。
計三つのフォルダから36ファイルが読み込まれます。
combine-many-excel-files-208
統計ボックスでデータの件数を確認すると、約1万3千件になっています。
combine-many-excel-files-209
ここまでで全ファイルが取り込めました。しかし、テーブルボックスで「年」と「月」の項目を確認すると、値がおかしくなっています。
ファイル名などが前回とは変わっているため、「年」と「月」の指定も変更する必要があります。

[ロードスクリプトの編集]画面を開きます。
「年」と「月」の指定を以下のように変更してください。
「年」を作成するには、FileDir関数でフォルダ名を取得してください。FileDir関数はフォルダ名をフルパスで取得する関数です。
また、フォルダのパスから一部を抜き出すには、SubField関数を使用するとよいでしょう。
変更前
LOAD Mid(FileBaseName(), 3, 4) as 年,
     Mid(FileBaseName(), 7, 2) as 月,
変更後
LOAD SubField(FileDir(), '\', -1) as 年,
     Mid(FileBaseName(), 3, 2) as 月,
combine-many-excel-files-210月の指定は文字数を変更しているだけなので、説明は省きます。

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
リロードを実行します。
「年」と「月」が取得できました。
combine-many-excel-files-211
あらためてFOR文を確認してみましょう。
FOR Each fileName in FileList 2008\*.xls, FileList 2009\*.xls, FileList 2010\*.xls
「2008」「2009」「2010」というフォルダ名を固定で指定していますが、フォルダが大量にある場合すべて指定するのは手間です。
この指定をやめて、全フォルダを自動で取り込むように変更してみましょう。

「FileList」でファイルの一覧が取得できましたが、おなじように「DirList」でフォルダの一覧を取得できます。
ロードスクリプトに、フォルダの一覧を取得するFOR文を新たに追加します。
FOR Each dirName in DirList *
:
NEXT
combine-many-excel-files-212
つづいて、固定されているフォルダ名の指定を$(dirName)の指定に置き換えます。
変更前
FOR Each fileName in FileList 2008\*.xls, FileList 2009\*.xls, FileList 2010\*.xls
変更後
FOR Each fileName in FileList $(dirName)\*.xls
combine-many-excel-files-213一つ目のFOR文でフォルダ一覧を取得して、二つ目のFOR文でフォルダごとにその中のファイル一覧を取得します。
言い換えると、一つ目のFOR文によりフォルダごと(年ごと)に3回処理が繰り返され、二つ目のFOR文によりファイルごと(月ごと)に12回処理が繰り返されます。
結果として3回x12回で36回処理が繰り返され、36ファイルが読み込まれます。

以上で完成です。
リロードすると結果が確認できます。

繰り返し処理の動作が分かりにくい場合は、デバッグ機能を使用してみてください。
[ロードスクリプトの編集]画面で、[デバッグ]ボタンをクリックします。
combine-many-excel-files-214
[デバッガ]画面で[ステップ]ボタンをクリックすると、ロードスクリプトを一文ずつ実行できます。
combine-many-excel-files-215
画面下部でそのときの変数の値(fileNameとdirNameの値)が確認できます。
combine-many-excel-files-216
LOAD文が実行され、データが取り込まれると画面左下に結果が表示されます。
combine-many-excel-files-217残りのロードスクリプトをすべて実行したい場合は、[開始]ボタンをクリックしてください。

以上で二つ目の例も終了です。


補足説明


複数ファイルの選択

非常に単純な機能なのですが、意外と知られていないようですので、ここで説明しておきたいと思います。
ファイルを選択する際、複数のファイルを選択できます。

[テーブルファイル]ボタンをクリックします。
combine-many-excel-files-301
下図のように複数のファイルを選択できます。
combine-many-excel-files-302

通常どおりファイルウィザードが起動しますが、[終了]ボタンをクリックすると…
combine-many-excel-files-304
ファイル数の分だけ、立て続けにファイルウィザードが起動します。
combine-many-excel-files-305
さいごまで終了するとLOAD文が一括で生成されます。
combine-many-excel-files-306つまり[終了]ボタンを36回連打すれば、全ファイルを一括で取り込むことは可能です。
しかし、その場合は「年」や「月」の項目の指定も、すべてのLOAD文に記述する必要があります。
そのため、ファイル数が多い場合は、やはりFOR文を使用した方が簡単です。
ただし、意味の異なるデータ(たとえば、地域、商品、部門のデータ)を一括で取り込むときは、この方法が便利です。


文字列関数

本編ではMid関数とSubField関数をご覧いただきましたが、文字列から一部を抜き出す関数には、他にも以下のようなものがあります。
  • Left関数
    文字列の左端から、指定した文字数分を抜き出します。
    Left(文字列, 文字数)
  • Right関数
    文字列の右端から、指定した文字数分を抜き出します。
    Right(文字列, 文字数)
  • TextBetween関数
    文字列と文字列に挟まれた、あいだの文字列を抜き出します。
    TextBetween(文字列, 文字列1, 文字列2)
    文字列1と文字列2のあいだの文字列を抜き出します。たとえば以下の指定の結果は「2008」になります。
    TextBetween('売上実績2008年度', '売上実績', '年度')


ファイル関数

本編ではFileBaseName関数とFileDir関数をご覧いただきましたが、ファイル関数には他にも以下のようなものがあります。
各関数の説明の下に記載しているのは、読み込んだファイルが以下のファイルだったときの結果です。
C:\Users\qlikview\Desktop\combine-many-excel-files\例1\売上200801.xls
  • FileBaseName関数
    ファイル名(拡張子なし)
    売上200801
  • FileName関数
    ファイル名(拡張子あり)
    売上200801.xls
  • FileExtension関数
    ファイルの拡張子(.ドットは含まない)
    xls
  • FileDir関数
    フォルダのフルパス
    C:\Users\qlikview\Desktop\combine-many-excel-files\例1
  • FilePath関数
    ファイルのフルパス
    C:\Users\qlikview\Desktop\combine-many-excel-files\例1\売上200801.xls
  • FileSize関数
    ファイルサイズ(単位はバイト)
    45056
  • FileTime関数
    ファイルの更新日時
    2013/10/09 13:00:00


振り返り

今回は大量のExcelファイルを一つにまとめる方法についてご覧いただきました。以下に重要なポイントをまとめます。
  • [相対パス]
    初期設定:無効
    有効にすると、読み込むファイルの指定がファイル名のみの指定になる。
    無効にすると、読み込むファイルの指定がフルパスの指定になる。
    つまり、有効にした場合はドキュメント(qvwファイル)とおなじディレクトリにあるファイルが検索される。
  • 繰り返し処理(FOR文)
    • FORとNEXTで囲んだ範囲が繰り返し実行される。
      「in」の後ろに指定した値の数だけ処理を繰り返す。
      FOR Each 変数名 in 値1, 値2, ..., 値n
    • FileListでファイルの一覧を取得できる。
      FOR Each 変数名 in FileList ファイル名
    • DirListでフォルダの一覧を取得できる。
      FOR Each 変数名 in DirList フォルダ名
  • ファイル名やフォルダ名の取得には、ファイル関数を使用する。
    FileBaseName関数(拡張子なしのファイル名)、FileDir関数(フォルダのフルパス)など。
  • 文字列から一部を抜き出すには、以下のような関数を使用する。
    Mid関数(文字数を指定)、SubField関数(区切り文字で区切られた一部を取得)など。


    作成したロードスクリプト

    今回作成したロードスクリプトは以下のとおりです。
    ※前半の「SET」文は省略しています。

    Excelファイルの統合1(あるディレクトリ中のファイルをすべて取り込む)
    FOR Each fileName in FileList *.xls
    
    LOAD Mid(FileBaseName(), 3, 4) as 年,
         Mid(FileBaseName(), 7, 2) as 月,
         日, 
         商品名, 
         販売単価, 
         納品数量, 
         売上金額
    FROM
    $(fileName)
    (biff, embedded labels, table is [売上$]);
    
    NEXT
    
    Excelファイルの統合2(ディレクトリが複数ある場合)
    FOR Each dirName in DirList *
    
    FOR Each fileName in FileList $(dirName)\*.xls
    
    LOAD SubField(FileDir(), '\', -1) as 年,
         Mid(FileBaseName(), 3, 2) as 月,
         日, 
         商品名, 
         販売単価, 
         納品数量, 
         売上金額
    FROM
    $(fileName)
    (biff, embedded labels, table is [売上$]);
    
    NEXT
    
    NEXT
    


    最後に…

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

    繰り返し処理のFOR文は、他のツールや言語にもよくある機能だと思いますが、ファイルの一覧を取得する「FileList」の記述は、他ではあまり見かけない機能だと思います。
    また、補足説明に掲載したTextBetween関数なども、ありそうでなかった便利な関数ではないでしょうか。

    QlikViewにはこういった、他ではあまり見かけない機能、ありそうでなかった機能がいくつもあり、深く学ぶほどに驚かされます。
    わたしがとくにそう感じる機能をいくつか挙げると、デュアル値とDual関数、並列ステート、リストボックスのANDモードなどが挙げられます。
    これらの機能についても、いつかこのブログで紹介したいと思っています。

    お疲れ様でした。