Procédure : effectuer une boucle dans des fichiers et tableaux Excel

Mis à jour : 17 juillet 2006

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

1. 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. (L'exemple d'expression présenté plus loin dans cette procédure utilise le nom de variable ExcelFile.)

   Remarque :
   Pour éviter tout problème de validation, affectez un nom de fichier et de chemin d'accès Excel valide comme valeur initiale de la variable.

2. 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é plus loin dans cette procédure utilise le nom de variable ExtProperties, avec une valeur initiale de "Excel 8.0;HDR=Yes``".

3. 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 Procédure : configurer un conteneur de boucles Foreach.

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

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

6. Fermez l'Éditeur de boucle Foreach.

7. Ajoutez un gestionnaire de connexions Excel au package, comme décrit dans la rubrique Procédure : ajouter un gestionnaire de connexions à 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, après avoir supprimé 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.

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

9. Dans l'Éditeur d'expressions de la propriété, sélectionnez la propriété ConnectionString, puis cliquez sur les points de suspension.

10. 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 Extended Properties.

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

1. 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 Procédure : ajouter un gestionnaire de connexions à un package.

2. Créez une variable de chaîne qui recevra le nom du tableau actuel à chaque itération de la boucle.

3. 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 Procédure : configurer un conteneur de boucles Foreach.

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

5. En guise de valeur pour Connexion, sélectionnez le gestionnaire de connexions ADO.NET que vous avez précédemment créé.

6. 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 Working with Excel Files with the Script Task.

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

8. Fermez l'Éditeur de boucle Foreach.

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

Voir aussi

Tâches

Procédure : configurer un conteneur de boucles Foreach
Procédure : créer une expression de propriété

Concepts

Gestionnaire de connexions Excel
Source Excel
Destination Excel

Autres ressources

Working with Excel Files with the Script Task

Aide et Informations

Assistance sur SQL Server 2005