Foreach ループ コンテナを使用して Excel のファイルおよびテーブルをループ処理する方法

ループの反復ごとに現在の Excel のパスとファイル名を受け取る文字列変数を作成します。検証で問題が発生しないようにするには、変数の初期値として Excel の有効なパスとファイル名を割り当てます (この手順の後半で示すサンプル式では、ExcelFile という変数名を使用します)。

必要に応じて、Excel 接続文字列の Extended Properties 引数の値を保持する別の文字列変数を作成します。この引数には、Excel のバージョンを指定したり、最初の行に列名が含まれているかどうか、およびインポート モードが使用されるかどうかを判断する一連の値が格納されます (この手順の後半で示すサンプル式では、初期値が "Excel 8.0;HDR=Yes" である ExtProperties という名前の変数を使用しています)。

Foreach ループ コンテナを [制御フロー] タブに追加します。Foreach ループ コンテナの構成方法の詳細については、「Foreach ループ コンテナの構成方法」を参照してください。

[Foreach ループ エディタ] の [コレクション] ページで [Foreach File 列挙子] を選択し、Excel ブックが存在するフォルダを指定して、ファイル フィルタ (通常は *.xls) を指定します。

[変数のマッピング] ページで、ループの反復ごとに現在の Excel のパスとファイル名を受け取るユーザー定義文字列変数に、インデックス 0 をマップします (この手順の後半で示すサンプル式では、ExcelFile という変数名を使用します)。

[Foreach ループ エディタ] を閉じます。

「パッケージの接続マネージャを追加または削除する方法」の説明に従って、Excel 接続マネージャをパッケージに追加します。接続時に検証エラーが発生しないように、既存の Excel ブック ファイルを選択してください。

重要

この Excel 接続マネージャを使用するデータ フロー コンポーネントとタスクを構成する際、検証エラーが発生しないようにするには、[Excel 接続マネージャ] で既存の Excel ブックを選択します。以下の手順に従って ConnectionString プロパティ用の式を構成した後は、接続マネージャは実行時にこのブックを使用しなくなります。パッケージを作成して構成したら、[プロパティ] ウィンドウで ConnectionString プロパティの値を削除することができます。ただし、この値を削除すると、Excel 接続マネージャの接続文字列プロパティは Foreach ループが実行されるまで無効になります。したがって、接続マネージャを使用するタスクまたはパッケージでは、検証エラーが発生しないように、DelayValidation プロパティを True に設定してください。 また、Excel 接続マネージャの RetainSameConnection プロパティでは既定値の False を使用する必要があります。この値を True に変更すると、ループの各反復処理で最初の Excel ブックが繰り返し開かれるようになります。

新しい Excel 接続マネージャを選択し、[プロパティ] ウィンドウで [式] プロパティをクリックして、参照ボタンをクリックします。

[プロパティ式エディタ] で、[ConnectionString] プロパティを選択し、参照ボタンをクリックします。

式ビルダで、次の式を入力します。

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +  @[User::ExcelFile] + ";Extended Properties=\"" + @[User::ExtProperties] + "\""

拡張プロパティ引数の値の前後に必要な内側の引用符をエスケープするために、エスケープ文字 "\" を使用していることに注意してください。

Foreach ループ コンテナ内で、Excel 接続マネージャを使用して、指定したファイルの場所とパターンに一致した各 Excel ブックに対して同じ操作を実行するタスクを作成します。

Microsoft Jet OLE DB Provider を使用して Excel ブックに接続する ADO.NET 接続マネージャを作成します。[接続マネージャ] ダイアログ ボックスの [すべて] ページで、Extended Properties プロパティの値として「Excel 8.0」と入力します。詳細については、「パッケージの接続マネージャを追加または削除する方法」を参照してください。

ループの反復ごとに現在のテーブルの名前を受け取る文字列変数を作成します。

Foreach ループ コンテナを [制御フロー] タブに追加します。Foreach ループ コンテナの構成方法については、「Foreach ループ コンテナの構成方法」を参照してください。

[Foreach ループ エディタ] の [コレクション] ページで、Foreach ADO.NET Schema Rowset 列挙子を選択します。

[コレクション] の値として、以前に作成した ADO.NET 接続マネージャを選択します。

[スキーマ] の値として、[テーブル] を選択します。

注意
Excel ブック内のテーブルの一覧には、ワークシート ($ サフィックスが付きます) と名前付き範囲が含まれます。ワークシートのみ、または名前付き範囲のみを一覧からフィルタ選択する場合は、そのためのカスタム コードをスクリプト タスクで記述する必要があります。詳細については、「スクリプト タスクを使用した Excel ファイルの操作」を参照してください。

[変数のマッピング] ページで、以前に作成した文字列変数にインデックス 2 をマップし、現在のテーブルの名前を保持します。

[Foreach ループ エディタ] を閉じます。

Foreach ループ コンテナ内で、Excel 接続マネージャを使用して、指定したブック内の各 Excel テーブルに対して同じ操作を実行するタスクを作成します。スクリプト タスクを使用して、列挙されるテーブル名を調べたり各テーブルを操作したりする場合、スクリプト タスクの ReadOnlyVariables プロパティに文字列変数を追加することを忘れないでください。