Artículo
03/30/2012

Cómo crear bucles entre archivos y tablas de Excel usando un contenedor de bucles Foreach

Los procedimientos de este tema explican cómo recorrer en bucle los libros de Excel de una carpeta o las tablas de un libro de Excel mediante el contenedor de bucles Foreach con el enumerador correspondiente.

Para crear un bucle entre libros de Excel con el enumerador de archivos para Foreach

Cree una variable de cadena que recibirá la ruta de acceso y nombre de archivo de Excel actuales en cada iteración del bucle. Para evitar problemas de validación, asigne una ruta de acceso y un nombre de archivo de Excel válidos como valor inicial de la variable (la expresión de ejemplo que se muestra más adelante en este procedimiento utiliza el nombre de variable ExcelFile).
También puede crear otra variable de cadena que mantendrá el valor del argumento Propiedades extendidas de la cadena de conexión de Excel. Este argumento contiene una serie de valores que especifican la versión de Excel y determinan si la primera fila contiene nombres de columna, y si se utiliza un modo de importación (la expresión de ejemplo que se muestra más adelante en este procedimiento usa el nombre de variable ExtProperties, con un valor inicial de "Excel 8.0;HDR=Yes").

Si no usa una variable para el argumento Propiedades extendidas, debe agregarlo manualmente a la expresión que contiene la cadena de conexión.
Agregue un contenedor de bucles Foreach a la pestaña Flujo de control. Para obtener más información acerca de cómo configurar el contenedor de bucles Foreach, vea Cómo configurar un contenedor de bucles Foreach.
En la página Colección del Editor de bucles Foreach, seleccione el enumerador de archivos para Foreach, y especifique la carpeta en la que se encuentran los libros de Excel y el filtro de archivos (generalmente *.xls).
En la página Asignaciones de variables, asigne Índice 0 a una variable de cadena definida por el usuario que recibirá la ruta de acceso y el nombre de archivo de Excel actuales en cada iteración del bucle. (La expresión de ejemplo que se muestra más adelante en este procedimiento utiliza el nombre de variable ExcelFile.)
Cierre el Editor de bucles Foreach.

Agregue un administrador de conexión con Excel al paquete como se describe en Cómo agregar o eliminar un administrador de conexiones en un paquete. Seleccione un libro de Excel existente para la conexión a fin de evitar que se produzcan errores de validación.

Importante
Para evitar que se produzcan errores de validación al configurar tareas y componentes de flujo de datos que utilizan este administrador de conexión con Excel, seleccione un libro de Excel existente en el Editor del Administrador de conexión con Excel. El administrador de conexión no utilizará ese libro en tiempo de ejecución después de configurar una expresión para la propiedad ConnectionString como se describe en los pasos siguientes. Tras crear y configurar el paquete, puede borrar el valor de la propiedad ConnectionString en la ventana Propiedades. Sin embargo, si borra este valor, la propiedad de cadena de conexión del administrador de conexiones de Excel deja de ser válido hasta que se ejecuta el bucle Foreach. Por lo tanto, debe establecer la propiedad DelayValidation en True en las tareas en las que se utiliza el administrador de conexiones, o bien en el paquete, para evitar errores de validación. También debe utilizar el valor predeterminado de False para la propiedad RetainSameConnection del administrador de conexiones de Excel. Si cambia este valor a True, cada iteración del bucle continuará abriendo el primer libro de Excel.

Para evitar que se produzcan errores de validación al configurar tareas y componentes de flujo de datos que utilizan este administrador de conexión con Excel, seleccione un libro de Excel existente en el Editor del Administrador de conexión con Excel. El administrador de conexión no utilizará ese libro en tiempo de ejecución después de configurar una expresión para la propiedad ConnectionString como se describe en los pasos siguientes. Tras crear y configurar el paquete, puede borrar el valor de la propiedad ConnectionString en la ventana Propiedades. Sin embargo, si borra este valor, la propiedad de cadena de conexión del administrador de conexiones de Excel deja de ser válido hasta que se ejecuta el bucle Foreach. Por lo tanto, debe establecer la propiedad DelayValidation en True en las tareas en las que se utiliza el administrador de conexiones, o bien en el paquete, para evitar errores de validación.

También debe utilizar el valor predeterminado de False para la propiedad RetainSameConnection del administrador de conexiones de Excel. Si cambia este valor a True, cada iteración del bucle continuará abriendo el primer libro de Excel.

Seleccione el nuevo administrador de conexiones de Excel, haga clic en la propiedad Expresiones en la ventana Propiedades y luego haga clic en los puntos suspensivos.
En el Editor de expresiones de propiedad, seleccione la propiedad ConnectionString y haga clic en los puntos suspensivos.
En el Generador de expresiones, escriba la siguiente expresión:
```
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +  @[User::ExcelFile] + ";Extended Properties=\"" + @[User::ExtProperties] + "\""
```
Observe el uso del carácter de escape "\" para las comillas internas necesarias alrededor del valor del argumento Propiedades extendidas.

El argumento Propiedades extendidas no es opcional. Si no usa una variable para contener su valor, debe agregarlo manualmente a la expresión, como en el ejemplo siguiente correspondiente a un archivo de Excel 2003:
```
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +  @[User::ExcelFile] + ";Extended Properties=Excel 8.0"
```
Cree tareas en el contenedor de bucles Foreach que usen el administrador de conexiones con Excel para realizar las mismas operaciones en cada libro de Excel que coincida con la ubicación y el patrón de archivo especificados.

Para crear un bucle entre tablas de Excel con el enumerador de conjunto de filas del esquema para Foreach de ADO.NET

Cree un administrador de conexión de ADO.NET que utilice el proveedor OLE DB para Microsoft Jet para conectarse al libro de Excel. En la página Todas del cuadro de diálogo Administrador de conexión, asegúrese de especificar Excel 8.0 como valor para la propiedad Propiedades extendidas. Para obtener más información, vea Cómo agregar o eliminar un administrador de conexiones en un paquete.
Cree una variable de cadena que recibirá el nombre de la tabla actual en cada iteración del bucle.
Agregue un contenedor de bucles Foreach a la ficha Flujo de control. Para obtener más información acerca de cómo configurar el contenedor de bucles Foreach, vea Cómo configurar un contenedor de bucles Foreach.
En la página Colección del Editor de bucles Foreach, seleccione el enumerador de conjunto de filas del esquema para Foreach de ADO.NET.
Como valor de Conexión, seleccione el administrador de conexión de ADO.NET que creó previamente.
Como valor de Esquema, seleccione Tablas.

Nota

La lista de tablas de un libro de Excel incluye tanto hojas (que tienen el sufijo $) como rangos con nombre. Si tiene que filtrar la lista para obtener solo las hojas o solo los rangos con nombre, escriba código personalizado en una tarea Script para este fin. Para obtener más información, vea Trabajar con archivos de Excel con la tarea Script.
En la página Asignaciones de variables, asigne Índice 2 a la variable de cadena que creó antes para albergar el nombre de la tabla actual.
Cierre el Editor de bucles Foreach.
Cree tareas en el contenedor de bucles Foreach que utilicen el administrador de conexión con Excel para realizar las mismas operaciones en cada tabla de Excel en el libro especificado. Si utiliza una tarea Script para examinar el nombre de la tabla enumerada o para trabajar con cada tabla, recuerde agregar la variable de cadena a la propiedad ReadOnlyVariables de la tarea Script.