Utilisation de fichiers Excel avec la tâche de script
Integration Services fournit le gestionnaire de connexions Excel, la source Excel et la destination Excel pour utiliser des données stockées dans des feuilles de calcul au format de fichier Microsoft Excel. Les techniques décrites dans cette rubrique utilisent la tâche de script pour obtenir des informations sur les bases de données (fichiers de classeur) et tables (feuilles de calcul et plages nommées) Excel disponibles. Ces exemples peuvent être modifiés facilement afin d'utiliser l'une des autres sources de données basées sur des fichiers prises en charge par le fournisseur OLE DB Microsoft Jet.
Configuration d'un package pour tester les exemples
Exemple 1 : Vérifier si un fichier Excel existe
Exemple 2 : Vérifier si une table Excel existe
Exemple 3 : Obtenir la liste des fichiers Excel contenus dans un dossier
Exemple 4 : Obtenir la liste des tables contenues dans un fichier Excel
Affichage des résultats des exemples
[!REMARQUE]
Si vous souhaitez créer une tâche que vous pouvez réutiliser plus facilement dans plusieurs packages, envisagez d'utiliser le code indiqué dans cet exemple de tâche de script comme point de départ pour une tâche personnalisée. Pour plus d'informations, consultez Développement d'une tâche personnalisée.
Configuration d'un package pour tester les exemples
Vous pouvez configurer un package unique pour tester tous les exemples de cette rubrique. Les exemples utilisent de nombreuses variables de package et classes .NET Framework identiques.
Pour configurer un package à utiliser avec les exemples de cette rubrique
Créez un projet Integration Services dans Business Intelligence Development Studio et ouvrez le package par défaut afin de le modifier.
Variables. Ouvrez la fenêtre Variables et définissez les variables suivantes :
ExcelFile, de type String. Entrez le chemin d'accès complet et le nom de fichier d'un classeur Excel existant.
ExcelTable, de type String. Entrez le nom d'une feuille de calcul ou d'une plage nommée existante dans le classeur nommé dans la valeur de la variable ExcelFile. Cette valeur respecte la casse.
ExcelFileExists, de type Boolean.
ExcelTableExists, de type Boolean.
ExcelFolder, de type String. Entrez le chemin d'accès complet d'un dossier qui contient au moins un classeur Excel.
ExcelFiles, de type Object.
ExcelTables, de type Object.
Instructions Imports. La plupart des exemples de code impliquent que vous importiez l'un des espaces de noms .NET Framework suivants ou les deux au début de votre fichier de script :
System.IO, pour les opérations du système de fichiers.
System.Data.OleDb, pour ouvrir des fichiers Excel en tant que sources de données.
Références. Les exemples de code qui lisent des informations de schéma à partir de fichiers Excel requièrent une référence supplémentaire, dans le projet de script, à l'espace de noms System.Xml.
Définissez le langage de script par défaut du composant Script en utilisant l'option Langage de script de la page Général de la boîte de dialogue Options. Pour plus d'informations, consultez Page Général.
Description de l'exemple 1 : Vérifier si un fichier Excel existe
Cet exemple détermine si le fichier de classeur Excel spécifié dans la variable ExcelFile existe, puis définit la valeur booléenne de la variable ExcelFileExists sur le résultat. Vous pouvez utiliser cette valeur booléenne pour créer une branche dans le flux de travail du package.
Pour configurer cet exemple de tâche de script
Ajoutez une nouvelle tâche de script au package et remplacez son nom par ExcelFileExists.
Dans l'Éditeur de tâche de script, sous l'onglet Script, cliquez sur ReadOnlyVariables et entrez la valeur de propriété à l'aide de l'une des méthodes suivantes :
Tapez ExcelFile.
-ou-
Cliquez sur le bouton de sélection (...) en regard du champ de propriété, puis dans la boîte de dialogue Sélectionner des variables, sélectionnez la variable ExcelFile.
Cliquez sur ReadWriteVariables et entrez la valeur de propriété à l'aide de l'une des méthodes suivantes :
Tapez ExcelFileExists.
-ou-
Cliquez sur le bouton de sélection (...) en regard du champ de propriété, puis dans la boîte de dialogue Sélectionner des variables, sélectionnez la variable ExcelFileExists.
Cliquez sur Modifier le script pour ouvrir l'éditeur de scripts.
Ajoutez une instruction Imports pour l'espace de noms System.IO au début du fichier de script.
Ajoutez le code ci-dessous.
Code de l'exemple 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;
}
}
Description de l'exemple 2 : Vérifier si une table Excel existe
Cet exemple détermine si la feuille de calcul ou la plage nommée Excel spécifiée dans la variable ExcelTable existe dans le fichier de classeur Excel spécifié dans la variable ExcelFile, puis définit la valeur booléenne de la variable ExcelTableExists sur le résultat. Vous pouvez utiliser cette valeur booléenne pour créer une branche dans le flux de travail du package.
Pour configurer cet exemple de tâche de script
Ajoutez une nouvelle tâche de script au package et remplacez son nom par ExcelTableExists.
Dans l'Éditeur de tâche de script, sous l'onglet Script, cliquez sur ReadOnlyVariables et entrez la valeur de propriété à l'aide de l'une des méthodes suivantes :
Tapez ExcelTable et ExcelFile en les séparant par des virgules.
-ou-
Cliquez sur le bouton de sélection (...) en regard du champ de propriété, puis dans la boîte de dialogue Sélectionner des variables, sélectionnez les variables ExcelTable et ExcelFile.
Cliquez sur ReadWriteVariables et entrez la valeur de propriété à l'aide de l'une des méthodes suivantes :
Tapez ExcelTableExists.
-ou-
Cliquez sur le bouton de sélection (...) en regard du champ de propriété, puis dans la boîte de dialogue Sélectionner des variables, sélectionnez la variable ExcelTableExists.
Cliquez sur Modifier le script pour ouvrir l'éditeur de scripts.
Ajoutez une référence à l'assembly System.Xml dans le projet de script.
Ajoutez des instructions Imports pour les espaces de noms System.IO et System.Data.OleDb au début du fichier de script.
Ajoutez le code ci-dessous.
Code de l'exemple 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;
}
}
Description de l'exemple 3 : Obtenir la liste des fichiers Excel contenus dans un dossier
Cet exemple remplit un tableau à l'aide de la liste des fichiers Excel détectés dans le dossier spécifié dans la valeur de la variable ExcelFolder, puis copie le tableau dans la variable ExcelFiles. Vous pouvez utiliser l'énumérateur Foreach à partir d'une variable pour parcourir les fichiers inclus dans le tableau.
Pour configurer cet exemple de tâche de script
Ajoutez une nouvelle tâche de script au package et remplacez son nom par GetExcelFiles.
Ouvrez l'Éditeur de tâche de script, puis sous l'onglet Script, cliquez sur ReadOnlyVariables et entrez la valeur de propriété à l'aide de l'une des méthodes suivantes :
Tapez ExcelFolder
-ou-
Cliquez sur le bouton de sélection (...) en regard du champ de propriété, puis dans la boîte de dialogue Sélectionner des variables, sélectionnez la variable ExcelFolder.
Cliquez sur ReadWriteVariables et entrez la valeur de propriété à l'aide de l'une des méthodes suivantes :
Tapez ExcelFiles.
-ou-
Cliquez sur le bouton de sélection (...) en regard du champ de propriété, puis dans la boîte de dialogue Sélectionner des variables, sélectionnez la variable ExcelFiles.
Cliquez sur Modifier le script pour ouvrir l'éditeur de scripts.
Ajoutez une instruction Imports pour l'espace de noms System.IO au début du fichier de script.
Ajoutez le code ci-dessous.
Code de l'exemple 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;
}
}
Autre solution
Au lieu d'utiliser une tâche de script pour dresser la liste des fichiers Excel dans un tableau, vous pouvez également utiliser l'énumérateur ForEach File pour parcourir tous les fichiers Excel inclus dans un dossier. Pour plus d'informations, consultez Procédure : effectuer une boucle dans des fichiers et des tables Excel en utilisant un conteneur de boucles Foreach.
Description de l'exemple 4 : Obtenir la liste des tables contenues dans un fichier Excel
Cet exemple remplit un tableau à l'aide de la liste des feuilles de calcul et plages nommées détectées dans le fichier de classeur Excel spécifié par la valeur de la variable ExcelFile, puis copie le tableau dans la variable ExcelTables. Vous pouvez utiliser l'énumérateur Foreach à partir d'une variable pour parcourir les tables incluses dans le tableau.
[!REMARQUE]
La liste des tableaux d'un classeur Excel comprend à la fois les feuilles de calcul (affectées du suffixe $) et les plages nommées. Si vous devez filtrer la liste uniquement pour obtenir uniquement les feuilles de calcul ou les plages nommées, vous pouvez être amené à ajouter du code supplémentaire.
Pour configurer cet exemple de tâche de script
Ajoutez une nouvelle tâche de script au package et remplacez son nom par GetExcelTables.
Ouvrez l'Éditeur de tâche de script, puis sous l'onglet Script, cliquez sur ReadOnlyVariables et entrez la valeur de propriété à l'aide de l'une des méthodes suivantes :
Tapez ExcelFile.
-ou-
Cliquez sur le bouton de sélection (...) en regard du champ de propriété, puis dans la boîte de dialogue Sélectionner des variables, sélectionnez la variable ExcelFile.
Cliquez sur ReadWriteVariables et entrez la valeur de propriété à l'aide de l'une des méthodes suivantes :
Tapez ExcelTables.
-ou-
Cliquez sur le bouton de sélection (...) en regard du champ de propriété, puis dans la boîte de dialogue Sélectionner des variables, sélectionnez la variableExcelTables.
Cliquez sur Modifier le script pour ouvrir l'éditeur de scripts.
Ajoutez une référence à l'espace de noms System.Xml dans le projet de script.
Ajoutez une instruction Imports pour l'espace de noms System.Data.OleDb au début du fichier de script.
Ajoutez le code ci-dessous.
Code de l'exemple 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;
}
}
Autre solution
Au lieu d'utiliser une tâche de script pour dresser la liste des tables Excel dans un tableau, vous pouvez également utiliser l'énumérateur d'ensemble de lignes du schéma ADO.NET Foreach pour parcourir toutes les tables (autrement dit, les feuilles de calcul et les plages nommées) contenues dans un fichier de classeur Excel. Pour plus d'informations, consultez Procédure : effectuer une boucle dans des fichiers et des tables Excel en utilisant un conteneur de boucles Foreach.
Affichage des résultats des exemples
Si vous avez configuré chacun des exemples de cette rubrique dans le même package, vous pouvez connecter toutes les tâches de script à une tâche de script supplémentaire qui affiche la sortie de tous les exemples.
Pour configurer une tâche de script afin d'afficher la sortie des exemples de cette rubrique
Ajoutez une nouvelle tâche de script au package et remplacez son nom par DisplayResults.
Connectez les quatre exemples de tâche de script les uns aux autres, afin que chaque tâche s'exécute une fois que la tâche précédente s'est correctement achevée, puis connectez les quatre exemples de tâche à la tâche DisplayResults.
Ouvrez la tâche DisplayResults dans l'Éditeur de tâche de script.
Sous l'onglet Script, cliquez sur ReadOnlyVariables et utilisez l'une des méthodes suivantes pour ajouter les sept variables répertoriées dans Configuration d'un package pour tester les exemples :
Tapez le nom de chaque variable en les séparant par des virgules.
-ou-
Cliquez sur le bouton de sélection (...) en regard du champ de propriété, puis dans la boîte de dialogue Sélectionner des variables, sélectionnez les variables.
Cliquez sur Modifier le script pour ouvrir l'éditeur de scripts.
Ajoutez des instructions Imports pour les espaces de noms Microsoft.VisualBasic et System.Windows.Forms au début du fichier de script.
Ajoutez le code ci-dessous.
Exécutez le package et examinez les résultats qui s'affichent dans un message.
Code permettant d'afficher les résultats
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;
}
}
|