Freigeben über

  • Artikel

Arbeiten mit Excel-Dateien mit dem Skripttask

Integration Services stellt den Excel-Verbindungs-Manager, die Excel-Quelle und das Excel-Ziel zum Arbeiten mit den in Kalkulationstabellen gespeicherten Daten im Microsoft Excel-Dateiformat bereit. Die in diesem Thema beschriebenen Verfahren verwenden den Skripttask zum Abrufen von Informationen über verfügbare Excel-Datenbanken (Arbeitsmappendateien) und -Tabellen (Arbeitsmappen und benannte Bereiche). Diese Beispiele können leicht geändert werden, um mit einer der anderen vom Microsoft Jet OLE DB-Anbieter unterstützten dateibasierten Datenquellen zu arbeiten.

Konfigurieren eines Pakets zum Testen der Beispiele

Beispiel 1: Überprüfen, ob eine Excel-Datei vorhanden ist

Beispiel 2: Überprüfen, ob eine Excel-Tabelle vorhanden ist

Beispiel 3: Abrufen einer Liste der Excel-Dateien in einem Ordner

Beispiel 4: Abrufen einer Liste der Tabellen in einer Excel-Datei

Anzeigen der Ergebnisse dieser Beispiele

HinweisHinweis

Wenn Sie einen Task erstellen möchten, den Sie einfacher in mehreren Paketen wiederverwenden können, empfiehlt es sich, den Code in diesem Skripttaskbeispiel als Ausgangspunkt für einen benutzerdefinierten Task zu verwenden. Weitere Informationen finden Sie unter Entwickeln eines benutzerdefinierten Tasks.

Konfigurieren eines Pakets zum Testen der Beispiele

Sie können ein einzelnes Paket konfigurieren, um alle Beispiele in diesem Thema zu testen. In den Beispielen werden oft die gleichen Paketvariablen und die gleichen .NET Framework-Klassen verwendet.

So konfigurieren Sie ein Paket zur Verwendung mit den in diesem Thema beschriebenen Beispielen

  1. Erstellen Sie in Business Intelligence Development Studio ein neues Integration Services-Projekt, und öffnen Sie das Standardpaket für die Bearbeitung.

  2. Variablen. Öffnen Sie das Fenster Variablen, und definieren Sie die folgenden Variablen:

    • ExcelFile vom Typ String. Geben Sie den vollständigen Pfad zu einer vorhandenen Excel-Arbeitsmappe und den zugehörigen Dateinamen ein.

    • ExcelTable vom Typ String. Geben Sie den Namen eines vorhandenen Arbeitsblatts oder eines benannten Bereichs in der Arbeitsmappe ein, der im Wert der ExcelFile-Variablen genannt wird. Bei diesem Wert wird die Groß-/Kleinschreibung beachtet.

    • ExcelFileExists vom Typ Boolean.

    • ExcelTableExists vom Typ Boolean.

    • ExcelFolder vom Typ String. Geben Sie den vollständigen Pfad eines Ordners ein, der mindestens eine Excel-Arbeitsmappe enthält.

    • ExcelFiles vom Typ Object.

    • ExcelTables vom Typ Object.

  3. Imports-Anweisungen. Für die meisten Codebeispiele müssen am Anfang der Skriptdatei einer oder beide der folgenden .NET Framework-Namespaces importiert werden:

    • System.IO für Dateisystemvorgänge.

    • System.Data.OleDb zum Öffnen von Excel-Dateien als Datenquellen.

  4. Verweise. Für die Codebeispiele, die Schemainformationen in Excel-Dateien lesen, ist ein zusätzlicher Verweis im Skriptprojekt für den System.Xml-Namespace erforderlich.

  5. Um die Standardskriptsprache für die Skriptkomponente festzulegen, verwenden Sie im Dialogfeld Optionen auf der Seite Allgemein die Option Skriptsprache. Weitere Informationen finden Sie unter Seite Allgemein.

Beschreibung zu Beispiel 1: Überprüfen, ob eine Excel-Datei vorhanden ist

In diesem Beispiel wird überprüft, ob die von der ExcelFile-Variable angegebene Excel-Arbeitsmappendatei vorhanden ist. Daraufhin wird der boolesche Wert der ExcelFileExists-Variable auf das Ergebnis festgelegt. Sie können diesen booleschen Wert im Workflow des Pakets zur Verzweigung verwenden.

So konfigurieren Sie dieses Skripttaskbeispiel

  1. Fügen Sie dem Paket einen neuen Skripttask hinzu, und ändern Sie den Namen in ExcelFileExists.

  2. Klicken Sie im Skripttask-Editor auf der Registerkarte Skript auf ReadOnlyVariables, und geben Sie den Eigenschaftswert mit einer der folgenden Methoden ein:

    • Geben Sie ExcelFile ein.

      - oder -

    • Klicken Sie auf die Schaltfläche mit den Auslassungspunkten (…) neben dem Eigenschaftsfeld, und wählen Sie im Dialogfeld Variablen auswählen die Variable ExcelFile aus.

  3. Klicken Sie auf ReadWriteVariables, und geben Sie den Eigenschaftswert mit einer der folgenden Methoden ein:

    • Geben Sie ExcelFileExists ein.

      - oder -

    • Klicken Sie auf die Schaltfläche mit den Auslassungspunkten (…) neben dem Eigenschaftsfeld, und wählen Sie im Dialogfeld Variablen auswählen die Variable ExcelFileExists aus.

  4. Klicken Sie zum Öffnen des Skript-Editors auf Skript bearbeiten.

  5. Fügen Sie am Anfang der Skriptdatei eine Imports-Anweisung für den System.IO-Namespace hinzu.

  6. Fügen Sie den folgenden Code hinzu.

Code zu Beispiel 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;
  }
}

Beschreibung zu Beispiel 2: Überprüfen, ob eine Excel-Tabelle vorhanden ist

In diesem Beispiel wird überprüft, ob das in der ExcelTable-Variable angegebene Excel-Arbeitsblatt bzw. der benannte Bereich in der Excel-Arbeitsmappendatei vorhanden ist, die in der ExcelFile-Variable angegeben wurde. Daraufhin wird der boolesche Wert der ExcelTableExists-Variable auf das Ergebnis festgelegt. Sie können diesen booleschen Wert im Workflow des Pakets zur Verzweigung verwenden.

So konfigurieren Sie dieses Skripttaskbeispiel

  1. Fügen Sie dem Paket einen neuen Skripttask hinzu, und ändern Sie den Namen in ExcelTableExists.

  2. Klicken Sie im Skripttask-Editor auf der Registerkarte Skript auf ReadOnlyVariables, und geben Sie den Eigenschaftswert mit einer der folgenden Methoden ein:

    • Geben Sie durch Trennzeichen getrennt ExcelTable und ExcelFile ein.

      - oder -

    • Klicken Sie auf die Schaltfläche mit den Auslassungspunkten (…) neben dem Eigenschaftsfeld, und wählen Sie im Dialogfeld Variablen auswählen die Variablen ExcelTable und ExcelFile aus.

  3. Klicken Sie auf ReadWriteVariables, und geben Sie den Eigenschaftswert mit einer der folgenden Methoden ein:

    • Geben Sie ExcelTableExists ein.

      - oder -

    • Klicken Sie auf die Schaltfläche mit den Auslassungspunkten (…) neben dem Eigenschaftsfeld, und wählen Sie im Dialogfeld Variablen auswählen die Variable ExcelTableExists aus.

  4. Klicken Sie zum Öffnen des Skript-Editors auf Skript bearbeiten.

  5. Fügen Sie der Assembly System.Xml im Skriptprojekt einen Verweis hinzu.

  6. Fügen Sie am Anfang der Skriptdatei Imports-Anweisungen für den System.IO-Namespace und den System.Data.OleDb-Namespace hinzu.

  7. Fügen Sie den folgenden Code hinzu.

Code zu Beispiel 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;

        }
}

Beschreibung zu Beispiel 3: Abrufen einer Liste der Excel-Dateien in einem Ordner

In diesem Beispiel wird ein Array mit der Liste der Excel-Dateien aus dem Ordner gefüllt, der im Wert der ExcelFolder-Variable angegeben wurde. Das Array wird daraufhin in die ExcelFiles-Variable kopiert. Mithilfe des Foreach-Enumerators für Daten aus Variablen können die Dateien in dem Array durchlaufen werden.

So konfigurieren Sie dieses Skripttaskbeispiel

  1. Fügen Sie dem Paket einen neuen Skripttask hinzu, und ändern Sie den Namen in GetExcelFiles.

  2. Öffnen Sie den Skripttask-Editor, klicken Sie auf der Registerkarte Skript auf ReadOnlyVariables, und geben Sie den Eigenschaftswert mit einer der folgenden Methoden ein:

    • Geben Sie ExcelFolder ein.

      - oder -

    • Klicken Sie auf die Schaltfläche mit den Auslassungspunkten (…) neben dem Eigenschaftsfeld, und wählen Sie im Dialogfeld Variablen auswählen die Variable ExcelFolder aus.

  3. Klicken Sie auf ReadWriteVariables, und geben Sie den Eigenschaftswert mit einer der folgenden Methoden ein:

    • Geben Sie ExcelFiles ein.

      - oder -

    • Klicken Sie auf die Schaltfläche mit den Auslassungspunkten (…) neben dem Eigenschaftsfeld, und wählen Sie im Dialogfeld Variablen auswählen die Variable ExcelFiles aus.

  4. Klicken Sie zum Öffnen des Skript-Editors auf Skript bearbeiten.

  5. Fügen Sie am Anfang der Skriptdatei eine Imports-Anweisung für den System.IO-Namespace hinzu.

  6. Fügen Sie den folgenden Code hinzu.

Code zu Beispiel 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;
  }
}

Alternative Lösung

Anstelle eines Skripttasks können Sie zum Sammeln einer Liste von Excel-Arbeitsmappen in einem Array auch den Foreach-Dateienumerator verwenden, um alle Excel-Dateien in einem Ordner zu durchlaufen. Weitere Informationen finden Sie unter Vorgehensweise: Schleife durch Excel-Dateien und Tabellen mit einem Foreach-Schleifencontainer.

Beschreibung zu Beispiel 4: Abrufen einer Liste der Tabellen in einer Excel-Datei

In diesem Beispiel wird ein Array mit der Liste der Arbeitsmappen und benannten Bereiche in der Excel-Arbeitsmappendatei gefüllt, der im Wert der ExcelFile-Variable angegeben wurde. Das Array wird daraufhin in die ExcelTables-Variable kopiert. Mithilfe des Foreach-Enumerators für Daten aus Variablen können die Tabellen in dem Array durchlaufen werden.

HinweisHinweis

Die Liste der Tabellen in einer Excel-Arbeitsmappe schließt sowohl Arbeitsmappen (diese weisen das Suffix $ auf) als auch benannte Bereiche ein. Wenn Sie die Liste nach nur Arbeitsmappen oder nach nur benannten Bereichen filtern müssen, müssen Sie zu diesem Zweck möglicherweise zusätzlichen Code hinzufügen.

So konfigurieren Sie dieses Skripttaskbeispiel

  1. Fügen Sie dem Paket einen neuen Skripttask hinzu, und ändern Sie den Namen in GetExcelTables.

  2. Öffnen Sie den Skripttask-Editor, klicken Sie auf der Registerkarte Skript auf ReadOnlyVariables, und geben Sie den Eigenschaftswert mit einer der folgenden Methoden ein:

    • Geben Sie ExcelFile ein.

      - oder -

    • Klicken Sie auf die Schaltfläche mit den Auslassungspunkten (…) neben dem Eigenschaftsfeld, und wählen Sie im Dialogfeld Variablen auswählen die Variable ExcelFile aus.

  3. Klicken Sie auf ReadWriteVariables, und geben Sie den Eigenschaftswert mit einer der folgenden Methoden ein:

    • Geben Sie ExcelTables ein.

      - oder -

    • Klicken Sie auf die Schaltfläche mit den Auslassungspunkten (…) neben dem Eigenschaftenfeld, und wählen Sie im Dialogfeld Variablen auswählen die Variable ExcelTables aus.

  4. Klicken Sie zum Öffnen des Skript-Editors auf Skript bearbeiten.

  5. Fügen Sie dem System.Xml-Namespace einen Verweis im Skriptprojekt hinzu.

  6. Fügen Sie am Anfang der Skriptdatei eine Imports-Anweisung für den System.Data.OleDb-Namespace hinzu.

  7. Fügen Sie den folgenden Code hinzu.

Code zu Beispiel 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;
        }
}

Alternative Lösung

Anstelle eines Skripttasks können Sie zum Sammeln einer Liste von Excel-Arbeitsmappen in einem Array auch den Enumerator für das Foreach-ADO.NET-Schemarowset verwenden, um alle Tabellen (d. h. Arbeitsmappen und benannte Bereiche) in einer Excel-Arbeitsmappendatei zu durchlaufen. Weitere Informationen finden Sie unter Vorgehensweise: Schleife durch Excel-Dateien und Tabellen mit einem Foreach-Schleifencontainer.

Anzeigen der Ergebnisse dieser Beispiele

Wenn Sie alle Beispiele dieses Themas im selben Paket konfiguriert haben, können Sie alle Skripttasks mit einem zusätzlichen Skripttask verbinden, der die Ausgaben aller Beispiele anzeigt.

So konfigurieren Sie einen Skripttask zum Anzeigen der Ausgabe der in diesem Thema behandelten Beispiele

  1. Fügen Sie dem Paket einen neuen Skripttask hinzu, und ändern Sie den Namen in DisplayResults.

  2. Verbinden Sie alle vier Beispielskripttasks miteinander, sodass nach dem erfolgreichen Abschluss des vorhergehenden Tasks der jeweils nächste Task ausgeführt wird, und verbinden Sie den vierten Beispieltask mit dem DisplayResults-Task.

  3. Öffnen Sie den DisplayResults-Task im Skripttask-Editor.

  4. Klicken Sie auf der Registerkarte Skript auf ReadOnlyVariables, und fügen Sie mithilfe einer der folgenden Methoden alle sieben unter Konfigurieren eines Pakets zum Testen der Beispiele aufgeführten Variablen hinzu:

    • Geben Sie den Namen jeder Variable durch Trennzeichen getrennt ein.

      - oder -

    • Klicken Sie auf die Schaltfläche mit den Auslassungspunkten (…) neben dem Eigenschaftsfeld, und wählen Sie im Dialogfeld Variablen auswählen die Variablen aus.

  5. Klicken Sie zum Öffnen des Skript-Editors auf Skript bearbeiten.

  6. Fügen Sie am Anfang der Skriptdatei Imports-Anweisungen für den Microsoft.VisualBasic-Namespace und den System.Windows.Forms-Namespace hinzu.

  7. Fügen Sie den folgenden Code hinzu.

  8. Führen Sie das Paket aus, und überprüfen Sie die in dem Meldungsfeld angezeigten Ergebnisse.

Code zum Anzeigen der Ergebnisse

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 (kleines Symbol) Bleiben Sie mit Integration Services auf dem neuesten Stand

Die neuesten Downloads, Artikel, Beispiele und Videos von Microsoft sowie ausgewählte Lösungen aus der Community finden Sie auf der Integration Services-Seite von MSDN oder TechNet:

Abonnieren Sie die auf der Seite verfügbaren RSS-Newsfeeds, um automatische Benachrichtigungen zu diesen Aktualisierungen zu erhalten.