Effectuer une boucle dans des fichiers et des tables Excel en utilisant un conteneur de boucles Foreach

Article
05/07/2013

Les procédures de cette rubrique expliquent comment effectuer une boucle dans les classeurs Excel d'un dossier, ou dans les tableaux d'un classeur Excel, à l'aide du conteneur de boucles Foreach et de l'énumérateur approprié.

Pour effectuer une boucle dans des fichiers Excel à l'aide de l'énumérateur Foreach File

Créez une variable de chaîne qui recevra le chemin d'accès et le nom de fichier Excel actuel à chaque itération de la boucle. Pour éviter des problèmes de validation, assignez un chemin d'accès Excel et un nom de fichier valides comme valeur initiale de la variable. (L'exemple d'expression présenté ultérieurement dans cette procédure utilise le nom de variable ExcelFile.)
En option, créez une autre variable de chaîne qui contiendra la valeur de l'argument Propriétés étendues de la chaîne de connexion Excel. Cet argument contient une série de valeurs qui spécifient la version d'Excel et déterminent si la première ligne contient les noms de colonnes, et si le mode d'importation est utilisé. (L'exemple d'expression présenté ultérieurement dans cette procédure utilise le nom de variable ExtProperties, avec une valeur initiale « Excel 8.0;HDR=Yes ».)

Si vous n'utilisez pas une variable pour l'argument de propriétés étendues, vous devez l'ajouter manuellement à l'expression qui contient la chaîne de connexion.
Ajoutez un conteneur de boucles Foreach à l'onglet Flux de contrôle. Pour plus d'informations sur la configuration du conteneur de boucles Foreach, consultez Configurer un conteneur de boucles Foreach.
Dans la page Collection de l'Éditeur de boucle Foreach, sélectionnez l'énumérateur Foreach File, spécifiez le dossier contenant les classeurs Excel, puis spécifiez le filtre de fichiers (généralement *.xls).
Dans la page Mappage de variables, mappez l'index 0 à une variable de chaîne définie par l'utilisateur qui recevra le chemin et le nom de fichier Excel à chaque itération de la boucle. L'exemple d'expression présenté plus loin dans cette procédure utilise le nom de variable ExcelFile.
Fermez l'Éditeur de boucle Foreach.
Ajoutez un gestionnaire de connexions Excel au package, comme décrit dans la rubrique Ajouter, supprimer ou partager un gestionnaire de connexions dans un package. Pour éviter toute erreur de validation, sélectionnez un fichier de classeur Excel pour la connexion.

Important

Pour éviter des erreurs de validation à mesure que vous configurez des tâches et des composants de flux de données qui utilisent ce gestionnaire de connexions Excel, sélectionnez un classeur Excel existant dans l'Éditeur du gestionnaire de connexions Excel. Le gestionnaire de connexions n'utilise pas ce classeur au moment de l'exécution après que vous ayez configuré une expression pour la propriété ConnectionString comme décrit dans la procédure suivante. Après avoir créé et configuré le package, vous pouvez supprimer la valeur de la propriété ConnectionString dans la fenêtre Propriétés. Néanmoins, si vous supprimez cette valeur, la propriété de chaîne de connexion du gestionnaire de connexions Excel n'est plus valide tant que la boucle Foreach n'est pas exécutée. Vous devez donc définir la propriété DelayValidation à True dans les tâches où le gestionnaire de connexions est utilisé, ou bien dans le package, pour éviter des erreurs de validation.

Vous devez également utiliser la valeur par défaut False pour la propriété RetainSameConnection du gestionnaire de connexions Excel. Si vous remplacez cette valeur par True, chaque itération de la boucle continue d'ouvrir le premier classeur Excel.
Sélectionnez le nouveau gestionnaire de connexions Excel, cliquez sur la propriété Expressions dans la fenêtre Propriétés, puis cliquez sur les points de suspension.
Dans l'Éditeur d'expressions de la propriété, sélectionnez la propriété ConnectionString, puis cliquez sur les points de suspension.
Dans le Générateur d'expressions, entrez l'expression suivante :
```
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +  @[User::ExcelFile] + ";Extended Properties=\"" + @[User::ExtProperties] + "\""
```
L'utilisation du caractère d'échappement « \ » permet d'isoler les guillemets internes requis autour de la valeur de l'argument de propriétés étendues (ExtProperties).

L'argument de propriétés étendues n'est pas facultatif. Si vous n'utilisez pas une variable pour contenir sa valeur, vous devez l'ajouter manuellement à l'expression, comme dans l'exemple suivant pour un fichier Excel 2003 :
```
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +  @[User::ExcelFile] + ";Extended Properties=Excel 8.0"
```
Dans le conteneur de boucles Foreach, créez des tâches qui utilisent le gestionnaire de connexions Excel pour effectuer les mêmes opérations sur chaque classeur Excel correspondant à l'emplacement et au modèle des fichiers spécifiés.

Pour effectuer une boucle dans des tableaux Excel à l'aide de l'énumérateur d'ensemble de lignes du schéma ADO.NET Foreach

Créez un gestionnaire de connexions ADO.NET qui utilise le fournisseur OLE DB pour Microsoft Jet afin d'établir une connexion à un classeur Excel. Dans la page Tout de la boîte de dialogue Gestionnaire de connexions, veillez à entrer Excel 8.0 comme valeur de la propriété Extended Properties. Pour plus d'informations, consultez Ajouter, supprimer ou partager un gestionnaire de connexions dans un package.
Créez une variable de chaîne qui recevra le nom du tableau actuel à chaque itération de la boucle.
Ajoutez un conteneur de boucles Foreach à l'onglet Flux de contrôle. Pour plus d'informations sur la configuration du conteneur de boucles Foreach, consultez Configurer un conteneur de boucles Foreach.
Dans la page Collection de l'Éditeur de boucle Foreach, sélectionnez l'énumérateur d'ensemble de lignes du schéma ADO.NET Foreach
En guise de valeur pour Connexion, sélectionnez le gestionnaire de connexions ADO.NET que vous avez précédemment créé.
En guise de valeur pour Schéma, sélectionnez Tables.

[!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 à partir des feuilles de calcul ou des plages nommées, vous pouvez être amené à écrire du code personnalisé dans une tâche de script. Pour plus d'informations, consultez Utilisation de fichiers Excel avec la tâche de script.
Dans la page Mappage de variables, mappez Index 2 avec la variable de chaîne créée précédemment pour inclure le nom du tableau actuel.
Fermez l'Éditeur de boucle Foreach.
Dans le conteneur de boucles Foreach, créez des tâches qui utilisent le gestionnaire de connexions Excel pour effectuer les mêmes opérations sur chaque tableau Excel du classeur spécifié. Si vous utilisez une tâche de script dans le but d'examiner le nom de tableau énuméré ou à utiliser avec chaque tableau, pensez à ajouter la variable chaîne à la propriété ReadOnlyVariables de la tâche de script.