次の方法で共有


スクリプト タスクを使用した Excel ファイルの操作

Integration Services には Excel 接続マネージャ、Excel ソース、Excel 変換先が用意されており、Microsoft Excel ファイル形式のスプレッドシートに保存されているデータを操作できます。このトピックで説明する方法では、スクリプト タスクを使用して、使用可能な Excel のデータベース (ワークブック ファイル) およびテーブル (ワークシートおよび名前付き範囲) に関する情報を取得します。これらのサンプルに簡単な変更を加えて、Microsoft Jet OLE DB プロバイダによってサポートされる他のすべてのファイルベース データ ソースを操作することができます。

サンプルをテストするためのパッケージの構成

例 1 : Excel ファイルが存在するかどうかを確認する

例 2 : Excel テーブルが存在するかどうかを確認する

例 3 : フォルダ内の Excel ファイルの一覧を取得する

例 4 : Excel ファイル内のテーブルの一覧を取得する

サンプルの結果の表示

注意注意

複数のパッケージでより簡単に再利用できるタスクを作成する場合は、このスクリプト タスク サンプルのコードを基にした、カスタム タスクの作成を検討してください。詳細については、「カスタム タスクの開発」を参照してください。

サンプルをテストするためのパッケージの構成

このトピックのすべてのサンプルをテストする単一のパッケージを構成することができます。これらのサンプルでは、同じパッケージ変数と同じ .NET Framework クラスを数多く使用します。

このトピックの例で使用するパッケージを構成するには

  1. Business Intelligence Development Studio で新しい Integration Services プロジェクトを作成し、編集のために既定のパッケージを開きます。

  2. 変数[変数] ウィンドウを開き、次の変数を定義します。

    • String 型の ExcelFile。既存の Excel ワークブックの完全なパスとファイル名を入力します。

    • String 型の ExcelTable。ExcelFile 変数の値で指定されたワークブック内の既存のワークシートまたは名前付き範囲の名前を入力します。この値では大文字と小文字が区別されます。

    • Boolean 型の ExcelFileExists。

    • Boolean 型の ExcelTableExists。

    • String 型の ExcelFolder。少なくとも 1 つの Excel ワークブックを含むフォルダの完全なパスを入力します。

    • Object 型の ExcelFiles。

    • Object 型の ExcelTables。

  3. Imports ステートメント。ほとんどのコード サンプルでは、スクリプト ファイルの先頭で次の .NET Framework 名前空間のいずれかまたは両方をインポートする必要があります。

    • ファイル システム操作用の System.IO。

    • Excel ファイルをデータ ソースとして開くための System.Data.OleDb。

  4. 参照。Excel ファイルからスキーマ情報を読み取るコード サンプルでは、スクリプト プロジェクトで System.Xml 名前空間への追加の参照が必要です。

  5. [オプション] ダイアログ ボックスの [全般] ページにある [スクリプト言語] オプションを使用して、スクリプト コンポーネントの既定のスクリプト言語を設定します。詳細については、「[全般] ページ」を参照してください。

例 1 の説明 : Excel ファイルが存在するかどうかを確認する

この例では、ExcelFile 変数で指定された Excel ワークブック ファイルが存在するかどうかを判断し、その結果を ExcelFileExists 変数のブール値に設定します。このブール値は、パッケージのワークフローを分岐させるために使用することができます。

このスクリプト タスクの例を構成するには

  1. パッケージに新しいスクリプト タスクを追加し、名前を ExcelFileExists に変更します。

  2. [スクリプト タスク エディタ][スクリプト] タブで [ReadOnlyVariables] をクリックし、次のいずれかの方法でプロパティ値を入力します。

    • 「ExcelFile」と入力します。

      - または -

    • プロパティ フィールドの横にある参照ボタン ([...]) をクリックし、[変数の選択] ダイアログ ボックスで [ExcelFile] 変数を選択します。

  3. [ReadWriteVariables] をクリックし、次のいずれかの方法でプロパティ値を入力します。

    • 「ExcelFileExists」と入力します。

      - または -

    • プロパティ フィールドの横にある参照ボタン ([...]) をクリックし、[変数の選択] ダイアログ ボックスで [ExcelFileExists] 変数を選択します。

  4. [スクリプトの編集] をクリックして、スクリプト エディタを開きます。

  5. スクリプト ファイルの先頭に、System.IO 名前空間の Imports ステートメントを追加します。

  6. 次のコードを追加します。

例 1 のコード

Public Class ScriptMain
  Public Sub Main()
    Dim fileToTest As String

    fileToTest = Dts.Variables("ExcelFile").Value.ToString
    If File.Exists(fileToTest) Then
      Dts.Variables("ExcelFileExists").Value = True
    Else
      Dts.Variables("ExcelFileExists").Value = False
    End If

    Dts.TaskResult = ScriptResults.Success
  End Sub
End Class
public class ScriptMain
{
  public void Main()
  {
    string fileToTest;

    fileToTest = Dts.Variables["ExcelFile"].Value.ToString();
    if (File.Exists(fileToTest))
    {
      Dts.Variables["ExcelFileExists"].Value = true;
    }
    else
    {
      Dts.Variables["ExcelFileExists"].Value = false;
    }

    Dts.TaskResult = (int)ScriptResults.Success;
  }
}

例 2 の説明 : Excel テーブルが存在するかどうかを確認する

この例では、ExcelTable 変数で指定された Excel ワークシートまたは名前付き範囲が ExcelFile 変数で指定された Excel ワークブック ファイル内に存在するかどうかを判断し、その結果を ExcelTableExists 変数のブール値に設定します。このブール値は、パッケージのワークフローを分岐させるために使用することができます。

このスクリプト タスクの例を構成するには

  1. パッケージに新しいスクリプト タスクを追加し、名前を ExcelTableExists に変更します。

  2. [スクリプト タスク エディタ][スクリプト] タブで [ReadOnlyVariables] をクリックし、次のいずれかの方法でプロパティ値を入力します。

    • 「ExcelTable」および「ExcelFile」をコンマで区切って入力します。

      - または -

    • プロパティ フィールドの横にある参照ボタン ([...]) をクリックし、[変数の選択] ダイアログ ボックスで [ExcelTable][ExcelFile] の変数を選択します。

  3. [ReadWriteVariables] をクリックし、次のいずれかの方法でプロパティ値を入力します。

    • 「ExcelTableExists」と入力します。

      - または -

    • プロパティ フィールドの横にある参照ボタン ([...]) をクリックし、[変数の選択] ダイアログ ボックスで [ExcelTableExists] 変数を選択します。

  4. [スクリプトの編集] をクリックして、スクリプト エディタを開きます。

  5. スクリプト プロジェクトに System.Xml アセンブリへの参照を追加します。

  6. スクリプト ファイルの先頭に、System.IO 名前空間と System.Data.OleDb 名前空間の Imports ステートメントを追加します。

  7. 次のコードを追加します。

例 2 のコード

Public Class ScriptMain
  Public Sub Main()
    Dim fileToTest As String
    Dim tableToTest As String
    Dim connectionString As String
    Dim excelConnection As OleDbConnection
    Dim excelTables As DataTable
    Dim excelTable As DataRow
    Dim currentTable As String

    fileToTest = Dts.Variables("ExcelFile").Value.ToString
    tableToTest = Dts.Variables("ExcelTable").Value.ToString

    Dts.Variables("ExcelTableExists").Value = False
    If File.Exists(fileToTest) Then
      connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & fileToTest & _
        ";Extended Properties=Excel 8.0"
      excelConnection = New OleDbConnection(connectionString)
      excelConnection.Open()
      excelTables = excelConnection.GetSchema("Tables")
      For Each excelTable In excelTables.Rows
        currentTable = excelTable.Item("TABLE_NAME").ToString
        If currentTable = tableToTest Then
          Dts.Variables("ExcelTableExists").Value = True
        End If
      Next
    End If

    Dts.TaskResult = ScriptResults.Success
  End Sub
End Class
public class ScriptMain
{
    public void Main()
        {
            string fileToTest;
            string tableToTest;
            string connectionString;
            OleDbConnection excelConnection;
            DataTable excelTables;
            string currentTable;

            fileToTest = Dts.Variables["ExcelFile"].Value.ToString();
            tableToTest = Dts.Variables["ExcelTable"].Value.ToString();

            Dts.Variables["ExcelTableExists"].Value = false;
            if (File.Exists(fileToTest))
            {
                connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                "Data Source=" + fileToTest + ";Extended Properties=Excel 8.0";
                excelConnection = new OleDbConnection(connectionString);
                excelConnection.Open();
                excelTables = excelConnection.GetSchema("Tables");
                foreach (DataRow excelTable in excelTables.Rows)
                {
                    currentTable = excelTable["TABLE_NAME"].ToString();
                    if (currentTable == tableToTest)
                    {
                        Dts.Variables["ExcelTableExists"].Value = true;
                    }
                }
            }

            Dts.TaskResult = (int)ScriptResults.Success;

        }
}

例 3 の説明 : フォルダ内の Excel ファイルの一覧を取得する

この例では、ExcelFolder 変数の値で指定されたフォルダ内で検索された Excel ファイルの一覧を配列に代入し、その配列を ExcelFiles 変数にコピーします。Foreach from Variable 列挙子を使用して、配列内のファイルを繰り返し処理することができます。

このスクリプト タスクの例を構成するには

  1. パッケージに新しいスクリプト タスクを追加し、名前を GetExcelFiles に変更します。

  2. [スクリプト タスク エディタ] を開き、[スクリプト] タブで [ReadOnlyVariables] をクリックし、次のいずれかの方法でプロパティ値を入力します。

    • 「ExcelFolder」と入力します。

      - または -

    • プロパティ フィールドの横にある参照ボタン ([...]) をクリックし、[変数の選択] ダイアログ ボックスで [ExcelFolder] 変数を選択します。

  3. [ReadWriteVariables] をクリックし、次のいずれかの方法でプロパティ値を入力します。

    • 「ExcelFiles」と入力します。

      - または -

    • プロパティ フィールドの横にある参照ボタン ([...]) をクリックし、[変数の選択] ダイアログ ボックスで [ExcelFiles] 変数を選択します。

  4. [スクリプトの編集] をクリックして、スクリプト エディタを開きます。

  5. スクリプト ファイルの先頭に、System.IO 名前空間の Imports ステートメントを追加します。

  6. 次のコードを追加します。

例 3 のコード

Public Class ScriptMain
  Public Sub Main()
    Const FILE_PATTERN As String = "*.xls"

    Dim excelFolder As String
    Dim excelFiles As String()

    excelFolder = Dts.Variables("ExcelFolder").Value.ToString
    excelFiles = Directory.GetFiles(excelFolder, FILE_PATTERN)

    Dts.Variables("ExcelFiles").Value = excelFiles

    Dts.TaskResult = ScriptResults.Success
  End Sub
End Class
public class ScriptMain
{
  public void Main()
  {
    const string FILE_PATTERN = "*.xls";

    string excelFolder;
    string[] excelFiles;

    excelFolder = Dts.Variables["ExcelFolder"].Value.ToString();
    excelFiles = Directory.GetFiles(excelFolder, FILE_PATTERN);

    Dts.Variables["ExcelFiles"].Value = excelFiles;

    Dts.TaskResult = (int)ScriptResults.Success;
  }
}

代替ソリューション

スクリプト タスクを使用して Excel ファイルの一覧を配列に集める代わりに、ForEach File 列挙子を使用してフォルダ内のすべての Excel ファイルを繰り返し処理することもできます。詳細については、「Foreach ループ コンテナを使用して Excel のファイルおよびテーブルをループ処理する方法」を参照してください。

例 4 の説明 : Excel ファイル内のテーブルの一覧を取得する

この例では、ExcelFile 変数の値で指定された Excel ワークブック ファイル内で検索されたワークシートまたは名前付き範囲の一覧を配列に代入し、その配列を ExcelTables 変数にコピーします。Foreach from Variable 列挙子を使用して、配列内のテーブルを繰り返し処理することができます。

注意注意

Excel ワークブック内のテーブルの一覧には、ワークシート ($ というサフィックスが付きます) と名前付き範囲の両方が含まれます。ワークシートまたは名前付き範囲のみを一覧からフィルタ選択する必要がある場合は、そのためのコードを追加する必要があります。

このスクリプト タスクの例を構成するには

  1. パッケージに新しいスクリプト タスクを追加し、名前を GetExcelTables に変更します。

  2. [スクリプト タスク エディタ] を開き、[スクリプト] タブで [ReadOnlyVariables] をクリックし、次のいずれかの方法でプロパティ値を入力します。

    • 「ExcelFile」と入力します。

      - または -

    • プロパティ フィールドの横にある参照ボタン ([...]) をクリックし、[変数の選択] ダイアログ ボックスで [ExcelFile] 変数を選択します。

  3. [ReadWriteVariables] をクリックし、次のいずれかの方法でプロパティ値を入力します。

    • 「ExcelTables」と入力します。

      - または -

    • プロパティ フィールドの横にある参照ボタン ([...]) をクリックし、[変数の選択] ダイアログ ボックスで [ExcelTables] 変数を選択します。

  4. [スクリプトの編集] をクリックして、スクリプト エディタを開きます。

  5. スクリプト プロジェクトに System.Xml 名前空間への参照を追加します。

  6. スクリプト ファイルの先頭に、System.Data.OleDb 名前空間の Imports ステートメントを追加します。

  7. 次のコードを追加します。

例 4 のコード

Public Class ScriptMain
  Public Sub Main()
    Dim excelFile As String
    Dim connectionString As String
    Dim excelConnection As OleDbConnection
    Dim tablesInFile As DataTable
    Dim tableCount As Integer = 0
    Dim tableInFile As DataRow
    Dim currentTable As String
    Dim tableIndex As Integer = 0

    Dim excelTables As String()

    excelFile = Dts.Variables("ExcelFile").Value.ToString
    connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & excelFile & _
        ";Extended Properties=Excel 8.0"
    excelConnection = New OleDbConnection(connectionString)
    excelConnection.Open()
    tablesInFile = excelConnection.GetSchema("Tables")
    tableCount = tablesInFile.Rows.Count
    ReDim excelTables(tableCount - 1)
    For Each tableInFile In tablesInFile.Rows
      currentTable = tableInFile.Item("TABLE_NAME").ToString
      excelTables(tableIndex) = currentTable
      tableIndex += 1
    Next

    Dts.Variables("ExcelTables").Value = excelTables

    Dts.TaskResult = ScriptResults.Success
  End Sub
End Class
public class ScriptMain
{
  public void Main()
        {
            string excelFile;
            string connectionString;
            OleDbConnection excelConnection;
            DataTable tablesInFile;
            int tableCount = 0;
            string currentTable;
            int tableIndex = 0;

            string[] excelTables = new string[5];

            excelFile = Dts.Variables["ExcelFile"].Value.ToString();
            connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                "Data Source=" + excelFile + ";Extended Properties=Excel 8.0";
            excelConnection = new OleDbConnection(connectionString);
            excelConnection.Open();
            tablesInFile = excelConnection.GetSchema("Tables");
            tableCount = tablesInFile.Rows.Count;

            foreach (DataRow tableInFile in tablesInFile.Rows)
            {
                currentTable = tableInFile["TABLE_NAME"].ToString();
                excelTables[tableIndex] = currentTable;
                tableIndex += 1;
            }

            Dts.Variables["ExcelTables"].Value = excelTables;

            Dts.TaskResult = (int)ScriptResults.Success;
        }
}

代替ソリューション

スクリプト タスクを使用して Excel テーブルの一覧を配列に集める代わりに、ForEach ADO.NET Schema Rowset 列挙子を使用して Excel ワークブック ファイル内のすべてのテーブル (つまり、ワークシートと名前付き範囲) を繰り返し処理することもできます。詳細については、「Foreach ループ コンテナを使用して Excel のファイルおよびテーブルをループ処理する方法」を参照してください。

サンプルの結果の表示

このトピックの各例を同じパッケージで構成した場合は、すべてのスクリプト タスクを、すべての例の出力を表示する追加のスクリプト タスクに接続することができます。

このトピックの例の出力を表示するスクリプト タスクを構成するには

  1. パッケージに新しいスクリプト タスクを追加し、名前を DisplayResults に変更します。

  2. 4 つのスクリプト タスク例のそれぞれを互いに接続し、各タスクが、前のタスクが正常に完了した後に実行されるようにして、4 番目のタスク例を DisplayResults タスクに接続します。

  3. [スクリプト タスク エディタ] で DisplayResults タスクを開きます。

  4. [スクリプト] タブで [ReadOnlyVariables] をクリックし、次のいずれかの方法で、「サンプルをテストするためのパッケージの構成」で示した 7 つの変数のすべてを追加します。

    • 各変数の名前をコンマで区切って入力します。

      - または -

    • プロパティ フィールドの横にある参照ボタン ([...]) をクリックし、[変数の選択] ダイアログ ボックスで変数を選択します。

  5. [スクリプトの編集] をクリックして、スクリプト エディタを開きます。

  6. スクリプト ファイルの先頭に、Microsoft.VisualBasic 名前空間と System.Windows.Forms 名前空間の Imports ステートメントを追加します。

  7. 次のコードを追加します。

  8. パッケージを実行し、メッセージ ボックスに表示される結果を調べます。

結果を表示するコード

Public Class ScriptMain
  Public Sub Main()
    Const EOL As String = ControlChars.CrLf

    Dim results As String
    Dim filesInFolder As String()
    Dim fileInFolder As String
    Dim tablesInFile As String()
    Dim tableInFile As String

    results = _
      "Final values of variables:" & EOL & _
      "ExcelFile: " & Dts.Variables("ExcelFile").Value.ToString & EOL & _
      "ExcelFileExists: " & Dts.Variables("ExcelFileExists").Value.ToString & EOL & _
      "ExcelTable: " & Dts.Variables("ExcelTable").Value.ToString & EOL & _
      "ExcelTableExists: " & Dts.Variables("ExcelTableExists").Value.ToString & EOL & _
      "ExcelFolder: " & Dts.Variables("ExcelFolder").Value.ToString & EOL & _
      EOL

    results &= "Excel files in folder: " & EOL
    filesInFolder = DirectCast(Dts.Variables("ExcelFiles").Value, String())
    For Each fileInFolder In filesInFolder
      results &= " " & fileInFolder & EOL
    Next
    results &= EOL

    results &= "Excel tables in file: " & EOL
    tablesInFile = DirectCast(Dts.Variables("ExcelTables").Value, String())
    For Each tableInFile In tablesInFile
      results &= " " & tableInFile & EOL
    Next

    MessageBox.Show(results, "Results", MessageBoxButtons.OK, MessageBoxIcon.Information)

    Dts.TaskResult = ScriptResults.Success
  End Sub
End Class
public class ScriptMain
{
  public void Main()
        {
            const string EOL = "\r";

            string results;
            string[] filesInFolder;
            //string fileInFolder;
            string[] tablesInFile;
            //string tableInFile;

            results = "Final values of variables:" + EOL + "ExcelFile: " + Dts.Variables["ExcelFile"].Value.ToString() + EOL + "ExcelFileExists: " + Dts.Variables["ExcelFileExists"].Value.ToString() + EOL + "ExcelTable: " + Dts.Variables["ExcelTable"].Value.ToString() + EOL + "ExcelTableExists: " + Dts.Variables["ExcelTableExists"].Value.ToString() + EOL + "ExcelFolder: " + Dts.Variables["ExcelFolder"].Value.ToString() + EOL + EOL;

            results += "Excel files in folder: " + EOL;
            filesInFolder = (string[])(Dts.Variables["ExcelFiles"].Value);
            foreach (string fileInFolder in filesInFolder)
            {
                results += " " + fileInFolder + EOL;
            }
            results += EOL;

            results += "Excel tables in file: " + EOL;
            tablesInFile = (string[])(Dts.Variables["ExcelTables"].Value);
            foreach (string tableInFile in tablesInFile)
            {
                results += " " + tableInFile + EOL;
            }

            MessageBox.Show(results, "Results", MessageBoxButtons.OK, MessageBoxIcon.Information);

            Dts.TaskResult = (int)ScriptResults.Success;
        }
}
Integration Services のアイコン (小) 最新の Integration Services の入手

マイクロソフトが提供する最新のダウンロード、アーティクル、サンプル、ビデオ、およびコミュニティで選択されたソリューションについては、MSDN または TechNet の Integration Services のページを参照してください。

これらの更新が自動で通知されるようにするには、ページの RSS フィードを購読します。