年や月ごとに分かれたExcelファイルをまとめて取り込む方法

年や月ごとに分かれたExcelファイルをまとめて取り込む方法

Pocket

Excelでデータを管理している場合、年や月ごとにファイルが分かれていることがあると思います。
QlikViewを使用すれば、複数のExcelファイルから一括でデータを取り込み、一つに統合できます。
ここではQlikViewを使用して、複数のExcelファイルを一つにまとめる方法を紹介いたします。

まだQlikViewをお持ちでない方は、ぜひこれを機にダウンロードしてみてください。
QlikViewは以下のページから無料でダウンロードできます。(要ユーザー登録)
QlikView Personal Editionのダウンロード

まず、処理のイメージを明確にしておきましょう。
手書きですみませんが下図をご覧ください。
concatenate-figureこのように、複数のExcelファイルを読み込み、読み込んだテーブルを縦に連結するイメージです。
これは、SQLで言うところのUNION(ユニオン)に相当する処理ですが、QlikViewではConcatenate(コンカチネート)と呼びます。

前述のとおり、年や月ごとに分かれたExcelファイルをまとめるために使用されることが多い機能ですが、この例に限らず複数のテーブルを一つにまとめる際に使用できます。
たとえば、2010年以前のデータがAccessにあり、2011年以降のデータがSQL Serverにあるときに、それらを一つのテーブルにまとめるときにも使用できます。

複数のテーブルを取り込んだ場合、QlikViewは同じ構造のテーブルを自動で判別し、一つのテーブルにまとめてくれます。
そのため、多くの場合はファイルが分かれていても単純にデータを取り込むだけですみます。
しかし、テーブルを一つにまとめるかどうかは、項目数と項目名によって判断されるため、データによっては手動で調整が必要な場合があります。

それでは、QlikViewが自動的にテーブルを連結する際の動作について、詳細を見ていきましょう。
なお、QlikViewをはじめてお使いになるという方には、事前に以下の記事をご一読いただくことをお勧めいたします。
ウィザードを使わないデータの取り込み
データの関連付け



ファイルのダウンロード

ここでは以下のファイルを使用しますので、ダウンロードしてください。
concatenate.zip
Zip形式ですので、ダウンロード後解凍してください。
以下ファイルが含まれています。
concatenate-101
売上実績.xls
一つ目のチュートリアルで使用するデータです。
2008年と2009年のデータが、二つのシートに分かれて格納されています。データの件数はそれぞれ60件ずつです。(12ヶ月分 * 商品分類が5つずつ。)
また、三つ目のシートには商品分類のデータが格納されています。
concatenate-102concatenate-103concatenate-104


テーブルの連結1(単純な連結)

それではデータを取り込んでみましょう。
まずはファイルを新規に作成し、[ロードスクリプトの編集]画面を起動します。
concatenate-105
ロードスクリプトの最下行にカーソルをあわせて、[テーブルファイル]ボタンをクリックします。
concatenate-106
「売上実績.xls」を選択します。
concatenate-107
[テーブル]が「売上2008$」であることを確認して、[終了]ボタンをクリックします。
concatenate-108
同様にして「売上2009」シートと、「商品分類」シートのデータも取り込みます。
concatenate-109concatenate-110
ロードスクリプトは以上で完成です。[OK]ボタンをクリックします。
concatenate-111
データを取り込んでみましょう。
[リロード]ボタンをクリックします。
concatenate-113
ファイルを保存してください。
concatenate-112
[ロードスクリプトの進捗]画面を確認してください。
下図の赤枠の表示は「売上2008$」テーブルに、「売上2009$」テーブルが連結されたことをあらわしています。
データの件数も二つのテーブルをあわせた件数(120件)となっています。
concatenate-114
テーブルビューアーでも確認してみましょう。
concatenate-115
売上関連のテーブルが一つしかないことが確認できます。
concatenate-116
テーブル名にカーソルをあわせると、合計120件になっていることが、ここでも確認できます。
concatenate-117また、テーブルビューアーを見て分かるとおり、テーブル名は一つ目に取り込んだテーブルの名前になるため、今回は「売上2008$」という名前になっています。

より適切なテーブル名に変更してみましょう。
[ロードスクリプトの編集]画面を起動し、テーブル名を指定します。
concatenate-118
リロードしてください。テーブル名が「売上」に変更されました。
concatenate-119concatenate-120
テーブルボックスを使用して、データを確認してみましょう。
シートの余白を右クリックし[シートオブジェクトの追加]→[テーブルボックス]を選択します。
concatenate-121
[すべて追加]ボタンをクリックして、すべての項目を選択します。
concatenate-122
[ロード順]ボタンをクリックして、項目の順序を調整します。
[OK]ボタンをクリックします。
concatenate-123
テーブルボックスが作成されました。
2008年と2009年、両方のデータが取り込まれていることが確認できます。
concatenate-124
データの取り込みは以上で完了ですが、どのようなときにテーブルが連結されて、どのようなときに連結されないのか、あらためて確認してみましょう。
まずはじめのポイントですが、前述のとおり重要なのは項目の”名前”であり、項目の指定順序は関係ありません。
このことを確認してみましょう。

[ロードスクリプトの編集]画面を起動します。
下図のように「商品分類番号」と「月」の指定順序を入れ替えます。
concatenate-125
リロードしてください。
項目の指定順序を変更しても、さきほどと同じようにテーブルが連結されます。
concatenate-126

つぎのポイントです。
項目数が異なる場合、テーブルは連結されません。
[ロードスクリプトの編集]画面を起動します。
下図のように「商品分類番号」をコメントアウトします。
concatenate-127
リロードしてください。
60件のテーブルが二つできていることが分かります。また、同じ名前の項目がいくつかあるため、Syntheticテーブルができています。
concatenate-128concatenate-129

さいごのポイントです。
(項目数が同じでも)項目名が異なる場合、テーブルは連結されません。
[ロードスクリプトの編集]画面を起動します。
「商品分類番号」のコメントアウトを解除し、「売上金額」の項目名を変更します。
concatenate-130
リロードしてください。
さきほどと同様、60件のテーブルが二つできています。
concatenate-131concatenate-132
ここまでの内容をまとめておきます。
項目数と項目名がすべて一致しているテーブルがあった場合、QlikViewは同じ構造のテーブルとみなして、テーブルを連結します。
このとき、項目の順序は関係ありません。


テーブルの連結2(項目数が異なるテーブルの連結)

こんどはもう少し複雑なデータを使用してみましょう。
ここでは、売上実績2.xlsを使用します。

売上実績2.xlsでも、2008年と2009年のデータが二つのシートに分かれて格納されていますが、前回よりもデータが多少複雑になっています。
とくに右端の二列(更新日と更新日時)は、2009年の方にしかないという点を認識しておいてください。
concatenate-201concatenate-202
それでは、ドキュメントを作成してみましょう。
まずはファイルを新規に作成し、[ロードスクリプトの編集]画面を起動します。
ロードスクリプトの最下行にカーソルをあわせて、[テーブルファイル]ボタンをクリックします。
concatenate-203
「売上実績2.xls」を選択し、「売上2008」シートを取り込んでください。
concatenate-204concatenate-205
さらに、「売上実績2.xls」の「売上2009」シートも取り込んでください。
concatenate-206concatenate-207
前回と同じようにテーブル名を指定してください。
concatenate-208
以上でロードスクリプトは完成です。
リロードを実行してください。
concatenate-209
ファイルを保存してください。
concatenate-210
二つのシートで項目が食い違っているため(更新日と更新日時は売上2009シートにしかない)、テーブルは連結されません。
concatenate-211concatenate-212
テーブルが連結されるようにしてみましょう。
単純に足りない項目を追加し、項目を揃えることでテーブルが連結できます。
つまり、ここでは「売上2008$」テーブルに更新日と更新日時の項目を追加します。

[ロードスクリプトの編集]画面を起動します。
一つ目のLOAD文を以下のように変更します。
変更前
     売上金額
変更後
     売上金額,
     Null() as 更新日,
     Null() as 更新日時
「売上金額」の末尾に,(カンマ)を追加するのを忘れないでください。
concatenate-213適当な日付や時刻を指定してもテーブルは連結できますが、データが無いということを明らかにするために、ここではNULL値を指定しています。
QlikViewでNULL値を指定したい場合はNull関数を使用します。QlikViewには「NULL」という定数や、キーワードの類はありません。Null関数は関数であるため末尾の()が必要であることに注意してください。

再度リロードを実行してみましょう。
テーブルが連結されました。
concatenate-214concatenate-215
テーブルボックスを使用して、データを確認してみましょう。
シートの余白を右クリックし[シートオブジェクトの追加]→[テーブルボックス]を選択します。
concatenate-216
[すべて追加]ボタンをクリックして、すべての項目を選択します。
concatenate-217
[ロード順]ボタンをクリックして、項目の順序を調整します。
[OK]ボタンをクリックします。
concatenate-218
テーブルボックスが作成されました。
テーブルは連結されていますが、更新日と更新日時の書式がおかしくなっています。
concatenate-219
はじめに取り込んだデータがNULL値であったため、書式がうまく反映されずこのような表示になっています。言い換えると、QlikViewが日付かどうか判断できなかったために、数値で表示されているということです。

ロードスクリプトでデータの型を明示するには、Date関数(日付)やTimestamp関数(日付時刻)を使用します。
LOAD文を以下のように編集します。
変更前
     Null() as 更新日,
     Null() as 更新日時
変更後
     Date(Null()) as 更新日,
     Timestamp(Null()) as 更新日時
concatenate-220
リロードを実行してください。
下図のように列の横幅が足りない場合は、右クリック→[データに列幅を合わせる]を選択してください。
concatenate-221concatenate-222
日付や日付時刻の書式が設定できました。
concatenate-223


追加の設定


静的な値を持つ項目の追加

ここからは、さらに追加の設定をしてみましょう。
あらためてもとのデータを確認すると、シートの中には年のデータがありません。
このファイルでは、年ごとにシートが分かれており、各シートには月日のデータしかありません。
concatenate-224
年のデータをロードスクリプトで追加してみましょう。

[ロードスクリプトの編集]画面を起動してください。
まず一つ目のLOAD文に2008年の値を追加します。
以下のように単純に値を指定すれば、静的な値を持つ項目を作成できます。
LOAD 2008 as 年,
concatenate-225文字の値を追加したい場合は、文字列を’(シングルコーテーション)で囲んでください。
'2008年' as 年,

同様にして二つ目のLOAD文に2009年の値を追加します。
LOAD 2009 as 年,
concatenate-226
リロードを実行してください。
テーブルボックスのプロパティを開き、[基本設定]タブで「年」の項目を追加します。
concatenate-227
[上へ][下へ]ボタンを使用して、「年」の項目を一番上に移動します。
[OK]ボタンをクリックしてください。
concatenate-228
テーブルボックスに「年」の項目が追加できました。
concatenate-229


テーブルを強制的に連結する

さきほどはNULL値を追加することでテーブルを連結しましたが、「Concatenate」というコマンドを使用することで、構造が違うテーブルを強制的に連結できます。

[ロードスクリプトの編集]画面を起動します。
まず、一つ目のLOAD文をもとの状態に戻します。以下のように変更してください。
変更前
     売上金額,
     Date(Null()) as 更新日,
     Timestamp(Null()) as 更新日時
変更後
     売上金額
//     Date(Null()) as 更新日,
//     Timestamp(Null()) as 更新日時
「売上金額」末尾の,(カンマ)を削除するのを忘れないでください。
concatenate-230
二つのLOAD文の間に「Concatenate」というコマンドを指定します。
LOAD ...

Concatenate

LOAD ...
concatenate-231
リロードするとテーブルが連結されていることが分かります。
結果はNULL値を手動で追加したときと同じです。
concatenate-232このように足りない項目を手動で追加しなくても、「Concatenate」というコマンドだけで強制的にテーブルを連結できます。


補足説明


データ型をGUIから明示する

さきほどはロードスクリプトの中でデータ型を明示しましたが、ユーザーインターフェース側から指定することもできます。
もとの表示
concatenate-301
テーブルボックスのプロパティを開きます。
[数値書式]タブで[ドキュメント初期設定を上書きする]にチェックを入れます。
項目を選択して、書式を設定します。
concatenate-302concatenate-303
書式が設定できました。
concatenate-304
オブジェクトのプロパティは、そのオブジェクトのみの設定です。
ドキュメント全体で書式設定をしたい場合は、ドキュメントプロパティから同様の設定をしてください。
[設定]メニューから[ドキュメントプロパティ]を選択します。
concatenate-305
[数値書式]タブから同様に設定できます。
concatenate-306


テーブルを強制的に分割する

「Concatenate」コマンドを使用することで、構造が異なっていても強制的にテーブルを連結できました。
逆に「NoConcatenate」というコマンドを使用することで、構造が同じでも強制的にテーブルを分割できます。

ロードスクリプトを以下のように編集します。
まず、一つ目のLOAD文の指定をもとに戻します。
二つのLOAD文の間に「NoConcatenate」というコマンドを指定します。
LOAD ...

NoConcatenate

LOAD ...
concatenate-307
リロードを実行してください。
テーブルは連結されません。
concatenate-308concatenate-309
「NoConcatenate」の使い道が少し想像しにくいと思いますので、補足しておきます。
まず、QlikViewでは「Resident」というコマンドで読み込み済みのテーブルを再読み込みできます。また「Drop Table」というコマンドでテーブルを削除できます。これらを使用することで、一回のLOAD文ではできない複雑な処理ができます。

たとえば、以下のような処理ができます。
テーブル1を読み込み→テーブル1を再読み込みしてデータを加工し、テーブル2とする→テーブル1は削除する
このような処理の流れのときに、途中でテーブル1とテーブル2が連結されるのを防ぐために、「NoConcatenate」を使用することがあります。


データ型を明示する関数

チュートリアルの中では、Date関数とTimestamp関数を使用してデータ型を明示しました。
データ型を明示する関数には、他にも以下のようなものがあります。
  • Text関数 文字型
  • Num関数 数値型
  • Date関数 日付型
  • Timestamp関数 日付時刻型
  • Time関数 時刻型


振り返り

今回は複数のテーブルを連結する方法についてご覧いただきました。以下に重要なポイントをまとめます。
  • 基本動作
    • テーブルの構造が同じ場合、自動的にテーブルが一つに連結される。
    • テーブルが連結されるかどうかは、項目数と項目名が一致しているかどうかによって判断される。項目の順序は関係ない。
  • ConcatenateとNoConcatenate
    • 「Concatenate」コマンドで、構造が異なるテーブルを強制的に連結できる。
    • 「NoConcatenate」コマンドで、構造が同じテーブルを強制的に分割できる。
  • 項目の追加
    • NULL値の項目を追加するには、Null関数を使う。
      Null() as 項目名
    • 静的な値を持つ項目を追加するには、以下のように指定する。
      数値 as 項目名
      '文字列' as 項目名
  • データ型の明示
    テーブルを連結した際、データ型がうまく認識されない場合は、手動で設定する。
    • ロードスクリプト側
      Date関数(日付型)、Timestamp関数(日付時刻型)などを指定する。
      Date(Null()) as 項目名
    • ユーザーインターフェース側
      • ドキュメント全体の設定
        [設定]→[ドキュメントプロパティ]→[数値書式]タブ
      • オブジェクトごとの設定
        オブジェクトのプロパティ→[数値書式]タブ
        [ドキュメント初期設定を上書きする]にチェックを入れてから書式を設定する。


最後に…

今回は複数のExcelファイルを一つにまとめる例を紹介しましたが、この方法だとファイルの数だけLOAD文を指定しなければならないため、ファイルが大量にある場合は少し手間です。

QlikViewのロードスクリプトでは繰り返し処理(FOR文)が使えるため、これを使えば複数のファイルを一括で読み込むこともできます。
たとえば、あるディレクトリ以下にあるExcelファイルをすべて読み込むといった指定ができます。
この方法については、次回説明いたします。

お疲れ様でした。


追記 2013年10月10日 10:00

ファイルを一括で取り込む方法について、記事を公開しました。こちらもぜひご覧ください。
大量のExcelファイルを一つにまとめる方法