Arbeiten mit Excel-Dateien mit dem Skripttask
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
Erstellen Sie in Integration Services ein neues SQL Server Data Tools (SSDT)-Projekt, und öffnen Sie das Standardpaket für die Bearbeitung.
Variablen: Öffnen Sie das Fenster Variablen, und definieren Sie die folgenden Variablen:
ExcelFiledes Typs Zeichenfolge. Geben Sie den vollständigen Pfad zu einer vorhandenen Excel-Arbeitsmappe und den zugehörigen Dateinamen ein.ExcelTabledes Typs Zeichenfolge. Geben Sie den Namen eines vorhandenen Arbeitsblatts oder eines benannten Bereichs in der Arbeitsmappe ein, der im Wert derExcelFile-Variablen genannt wird. Bei diesem Wert wird die Groß-/Kleinschreibung beachtet.ExcelFileExistsdes Typs Boolesch.ExcelTableExistsdes Typs Boolesch.ExcelFolderdes Typs Zeichenfolge. Geben Sie den vollständigen Pfad eines Ordners ein, der mindestens eine Excel-Arbeitsmappe enthält.ExcelFilesdes Typs Objekt.ExcelTablesdes Typs Objekt.
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.
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.
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
Fügen Sie dem Paket einen neuen Skripttask hinzu, und ändern Sie den Namen in ExcelFileExists.
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.
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.
Klicken Sie zum Öffnen des Skript-Editors auf Skript bearbeiten.
Fügen Sie am Anfang der Skriptdatei eine Imports-Anweisung für den System.IO-Namespace hinzu.
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
Fügen Sie dem Paket einen neuen Skripttask hinzu, und ändern Sie den Namen in ExcelTableExists.
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.
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.
Klicken Sie zum Öffnen des Skript-Editors auf Skript bearbeiten.
Fügen Sie der Assembly System.Xml im Skriptprojekt einen Verweis hinzu.
Fügen Sie oben in der Skriptdatei die Imports-Anweisungen für die Namespaces System.IO und System.Data.OleDb hinzu.
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
Fügen Sie dem Paket einen neuen Skripttask hinzu, und ändern Sie den Namen in GetExcelFiles.
Ö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.
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.
Klicken Sie zum Öffnen des Skript-Editors auf Skript bearbeiten.
Fügen Sie am Anfang der Skriptdatei eine Imports-Anweisung für den System.IO-Namespace hinzu.
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
Fügen Sie dem Paket einen neuen Skripttask hinzu, und ändern Sie den Namen in GetExcelTables.
Ö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.
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.
Klicken Sie zum Öffnen des Skript-Editors auf Skript bearbeiten.
Fügen Sie dem System.Xml-Namespace einen Verweis im Skriptprojekt hinzu.
Fügen Sie am Anfang der Skriptdatei eine Imports-Anweisung für den System.Data.OleDb-Namespace hinzu.
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
Fügen Sie dem Paket einen neuen Skripttask hinzu, und ändern Sie den Namen in DisplayResults.
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.
Öffnen Sie den Task DisplayResults im Skripttask-Editor.
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.
Klicken Sie zum Öffnen des Skript-Editors auf Skript bearbeiten.
Fügen Sie am Anfang der Skriptdatei die Imports-Anweisungen für die Namespaces Microsoft.VisualBasic und System.Windows.Forms hinzu.
Fügen Sie den folgenden Code hinzu.
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
Feedback
Bald verfügbar: Im Laufe des Jahres 2024 werden wir GitHub-Issues stufenweise als Feedbackmechanismus für Inhalte abbauen und durch ein neues Feedbacksystem ersetzen. Weitere Informationen finden Sie unter https://aka.ms/ContentUserFeedback.
Feedback senden und anzeigen für