Ejecutar consultas SQL en archivos de Excel

  • Artículo

Aunque las acciones de Excel permiten afrontar la mayoría de los escenarios de automatización de Excel, las consultas SQL permiten recuperar y manipular grandes cantidades de datos de Excel de manera más eficiente.

Suponga que un flujo tiene que modificar solo los registros de Excel que contengan un valor concreto. Para lograr esta funcionalidad sin consultas SQL, necesita bucles, condicionales y múltiples acciones de Excel.

Alternativamente, puede implementar esta funcionalidad con consultas SQL utilizando solo dos acciones, Abrir conexión SQL y Ejecutar instrucciones SQL.

Abrir una conexión SQL con un archivo de Excel

Antes de ejecutar una consulta SQL, debe abrir una conexión con el archivo de Excel al que desea acceder.

Para establecer la conexión, cree una nueva variable llamada %Excel_File_Path% e inicialícela con la ruta del archivo de Excel. Opcionalmente, puede omitir este paso y utilizar más adelante la ruta codificada del archivo en el flujo.

Captura de pantalla de la acción Establecer variable completada con la ruta del archivo de Excel.

Ahora, implemente la acción Abrir conexión SQL action y rellene la siguiente cadena de conexión en sus propiedades.

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=%Excel_File_Path%;Extended Properties="Excel 12.0 Xml;HDR=YES";

Nota

Para utilizar correctamente la cadena de conexión presentada, debe descargar e instalar el Motor de base de datos de Microsoft Access 2010 redistribuible.

Captura de pantalla de la acción de conexión Open SQL.

Abra una conexión SQL a un archivo de Excel protegido con contraseña

Se requiere un enfoque diferente para los escenarios en los que se ejecutan consultas SQL en archivos de Excel protegidos con contraseña. La acción Abrir conexión SQL no puede conectarse a archivos de Excel protegidos con contraseña, por lo que debe quitar la protección.

Para ello, inicie el archivo de Excel mediante la acción Iniciar Excel. El archivo está protegido por contraseña, por tanto, introduzca la contraseña adecuada en el campo Contraseña.

Captura de pantalla de la acción Iniciar Excel y el campo Contraseña.

A continuación, implemente las acciones de automatización de la interfaz de usuario adecuadas y vaya a Archivo>Información>Proteger libro>Cifrar con contraseña. Encontrará más información sobre la automatización de la interfaz de usuario y cómo utilizar las acciones correspondientes en Automatizar aplicaciones de escritorio.

Captura de pantalla de las acciones de la interfaz de usuario utilizadas para seleccionar la opción Cifrar con contraseña.

Después de seleccionar Cifrar con contraseña, rellene una cadena vacía en el cuadro de diálogo emergente con la acción Rellenar el campo de texto en la ventana. Para rellenar una cadena vacía, use la siguiente expresión: %""%.

Captura de pantalla de la acción Rellenar el campo de texto en la ventana.

Para presionar el botón Aceptar del cuadro de diálogo y aplicar los cambios, implemente la acción Presionar un botón en la ventana.

Captura de pantalla de la acción Presionar un botón en la ventana.

Por último, implemente la acción Cerrar Excel para guardar el libro no protegido como un nuevo archivo de Excel.

Captura de pantalla de la acción Cerrar Excel con la opción Guardar documento como seleccionada.

Después de guardar el archivo, siga las instrucciones de Abrir una conexión SQL a un archivo de Excel para abrir una conexión con él.

Cuando finalice la manipulación del archivo de Excel, utilice la acción Eliminar archivos para eliminar la copia no protegida del archivo de Excel.

Captura de pantalla que la acción Eliminar archivos.

Leer el contenido de una hoja de cálculo de Excel

Aunque la acción Leer en hoja de cálculo de Excel puede leer el contenido de una hoja de cálculo de Excel, se puede tardar un tiempo considerable en recorrer mediante bucles los datos recuperados.

Una forma más eficaz de recuperar valores específicos de las hojas de cálculo es tratar los archivos de Excel como bases de datos y ejecutar consultas SQL en ellos. Este enfoque es más rápido y aumenta el rendimiento del flujo.

Para recuperar todo el contenido de una hoja de cálculo, puede utilizar la siguiente consulta SQL en la acción Ejecutar instrucción SQL.

SELECT * FROM [SHEET$]

Captura de pantalla de instrucciones Ejecutar SQL rellenadas con una consulta SELECT.

Nota

Para aplicar esta consulta SQL a sus flujos, reemplace el marcador de posición HOJA con el nombre de la hoja de cálculo a la que desea acceder.

Para recuperar las filas que contienen un valor concreto en una columna específica, use la siguiente consulta SQL:

SELECT * FROM [SHEET$] WHERE [COLUMN NAME] = 'VALUE'

Nota

Para aplicar esta consulta SQL a sus flujos, reemplace:

  • HOJA con el nombre de la hoja de cálculo a la que desea acceder.
  • NOMBRE DE LA COLUMNA con la columna que contiene el valor que desea encontrar. Las columnas de la primera fila de la hoja de cálculo de Excel se identifican como nombres de columna de la tabla.
  • VALOR con el valor que desea encontrar.

Eliminar datos de una fila de Excel

Aunque Excel no admite la consulta SQL DELETE, puede utilizar la consulta UPDATE consulta para establecer como nulas todas las celdas de una fila concreta.

De forma más precisa, puede utilizar la siguiente consulta SQL:

UPDATE [SHEET$] SET [COLUMN1]=NULL, [COLUMN2]=NULL WHERE [COLUMN1]='VALUE'

Captura de la pantalla de instrucciones Ejecutar SQL rellenadas con una consulta UPDATE.

Mientras desarrolla el flujo, deberá reemplazar el marcador de posición HOJA con el nombre de la hoja de cálculo a la que desea acceder.

Los marcadores de posición COLUMN1 y COLUMN2 representan los nombres de las columnas para gestionar. Este ejemplo tiene dos columnas, pero en un escenario real, el número de columnas puede diferir. Las columnas de la primera fila de la hoja de cálculo de Excel se identifican como nombres de columna de la tabla.

La parte COLUMN1='VALOR' de la consulta define la fila que desea actualizar. En el flujo, use el nombre de la columna y el valor según la combinación que describe las filas de forma única.

Recuperar datos de Excel salvo para una fila concreta

En algunos escenarios, es posible que tenga que recuperar todo el contenido de una hoja de cálculo de Excel, salvo para una fila específica.

Una forma conveniente de lograr esto es establecer los valores de la fila no deseada en nulo y luego recuperar todos los valores excepto los nulos.

Para cambiar los valores de una fila específica de la hoja de cálculo, puede usar una consulta SQL UPDATE, como se presenta en Eliminar datos de una fila de Excel:

UPDATE [SHEET$] SET [COLUMN1]=NULL, [COLUMN2]=NULL WHERE [COLUMN1]='VALUE'

Captura de pantalla de las instrucciones Ejecutar SQL rellenadas con una consulta UPDATE.

A continuación, ejecute la siguiente consulta SQL para recuperar todas las filas de la hoja de cálculo que no contienen valores nulos:

SELECT * FROM [SHEET$] WHERE [COLUMN1] IS NOT NULL OR [COLUMN2] IS NOT NULL

Los marcadores de posición COLUMN1 y COLUMN2 representan los nombres de las columnas para gestionar. Este ejemplo tiene dos columnas, pero en una tabla real, el número de columnas puede diferir. Todas las columnas de la primera fila de la hoja de cálculo de Excel se identifican como nombres de columna de la tabla.