Freigeben über

Arbeiten mit Excel-Dateien mit dem Skripttask

  • Artikel

Gilt für: SQL Server SSIS Integration Runtime in Azure Data Factory

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).

Wichtig

Ausführliche Informationen über das Herstellen einer Verbindung mit Excel-Dateien sowie Einschränkungen und bekannte Probleme beim Laden von Daten aus oder in Excel-Dateien finden Sie unter Load data from or to Excel with SQL Server Integration Services (SSIS) (Laden von Daten aus oder in Excel mit SQL Server Integration Services (SSIS)).

Tipp

Wenn Sie einen Task erstellen möchten, den Sie 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 Integration Services ein neues SQL Server Data Tools (SSDT)-Projekt, und öffnen Sie das Standardpaket für die Bearbeitung.

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

    • ExcelFile des Typs Zeichenfolge. Geben Sie den vollständigen Pfad zu einer vorhandenen Excel-Arbeitsmappe und den zugehörigen Dateinamen ein.

    • ExcelTable des Typs Zeichenfolge. 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 des Typs Boolesch.

    • ExcelTableExists des Typs Boolesch.

    • ExcelFolder des Typs Zeichenfolge. Geben Sie den vollständigen Pfad eines Ordners ein, der mindestens eine Excel-Arbeitsmappe enthält.

    • ExcelFiles des Typs Objekt.

    • ExcelTables des Typs Objekt.

  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. Verwenden Sie im Dialogfeld Optionen auf der Seite Allgemein die Option Skriptsprache, um die Standardskriptsprache für die Skriptkomponente festzulegen. Weitere Informationen finden Sie unter General Page.

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.

Codebeispiel 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 oben in der Skriptdatei die Imports-Anweisungen für die Namespaces System.IO und System.Data.OleDb hinzu.

  7. Fügen Sie den folgenden Code hinzu.

Codebeispiel 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.ACE.OLEDB.12.0;" & _  
        "Data Source=" & fileToTest & _  
        ";Extended Properties=Excel 12.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.ACE.OLEDB.12.0;" +  
                "Data Source=" + fileToTest + ";Extended Properties=Excel 12.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.

Codebeispiel 3

Public Class ScriptMain  
  Public Sub Main()  
    Const FILE_PATTERN As String = "*.xlsx"  
  
    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 = "*.xlsx";  
  
    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 Loop through Excel Files and Tables by Using a Foreach Loop Container (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.

Hinweis

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 „ExcelFiles“ 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 Eigenschaftsfeld, 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.ACE.OLEDB.12.0;" & _  
        "Data Source=" & excelFile & _  
        ";Extended Properties=Excel 12.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.ACE.OLEDB.12.0;" +  
                "Data Source=" + excelFile + ";Extended Properties=Excel 12.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 Loop through Excel Files and Tables by Using a Foreach Loop Container (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 Task DisplayResults 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 die Imports-Anweisungen für die Namespaces Microsoft.VisualBasic und System.Windows.Forms 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;  
        }  
}

Siehe auch

Laden von Daten aus oder in Excel mit SQL Server Integration Services (SSIS)
Schleife durch Excel-Dateien und Tabellen mit einem Foreach-Schleifencontainer