Consultar un fichero de Excel desde SQL Server

Hola a todos,
Muchas veces necesitamos realizar carga de datos en una BBDD desde ficheros externos, o bien solamente consultar datos, y a menudo puede ser desde ficheros de Office o similares. Vamos a ver cómo consultar un fichero de Excel desde SQL Server como ejemplo, utilizando un script de Transact-SQL.

Para realizar la lectura de datos desde un fichero debemos utilizar en nuestro código la función OPENROWSET, cuya sintaxis completa se puede revisar aquí.

Función OPENROWSET

Esta función es la encargada de acceder al contenido del fichero.La sintaxis básica del comando es la siguiente:

OPENROWSET
(  'provider_name'
    , { 'datasource' ; 'user_id' ; 'password' | 'provider_string' }
    , {  [ catalog. ] [ schema. ] object | 'query' }
)

Hay que especificar que tipo de fichero es, si es de Excel, Access, etc y versión del driver que vamos a usar. Además hay que especificar la ruta del fichero.

Esta función se utiliza en una query que se puede ejecutar en el código de nuestro procedure, o si se quiere recuperar un conjunto de datos se puede utilizar en la query del cursor

El ejemplo de código que se va a utilizar es:

FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=YES;Database=E:\prueba_excel\datos_excel.xlsx', [Hoja1$])

Esta es la explicación de cada uno de ellos:

• Microsoft.ACE.OLEDB.12.0 –> Es el driver que se utiliza para acceder al fichero.

• Excel 12.0 –> Versión de Excel del fichero que contiene los datos.

• HDR=YES –> Especifica si la tabla de Excel de donde se recuperan los datos tiene cabeceras. Si se especifica NO y tiene cabeceras devuelve error.

• Database=E:\prueba_excel\datos_excel.xlsx –> Ruta completa del fichero que va a ser origen de los datos.

• [Hoja1$] –> Hoja del fichero origen de los datos.

Ejemplo de uso de OPENROWSET

En este caso hemos creado unos datos de prueba inventados, para mostrar el funcionamiento en un cursor.

El cursor se declara como un cursor normal de Transact-Sql de SQL Server declarando la query, especificando la función OPENROWSET en el momento de FROM.

Declare cursor_nombres Cursor For
    	SELECT nombre, apellidos, fecha_nacimiento, salario
    	FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=YES;Database=E:\prueba_excel\datos_excel.xlsx', [Hoja1$])

Y luego el tratamiento es el mismo que cualquier cursor de Transact-SQL, hay que declarar las variables, abrir y recuperar del cursor con un bloque que recorra el cursor (WHILE por ejemplo) y posteriormente cerrar el cursor y liberar los recursos del mismo.

Con este cursor que hemos declarado , se podría escribir un bloque como este:

OPEN cursor_nombres
FETCH next From cursor_nombres Into @nombre,@apellidos,@fecha_nacimiento,@salario

    WHILE @@FETCH_STATUS = 0 -- Mientras haya datos
    BEGIN
		
		<Acciones a realizar con los datos recuperados por el cursor>

	    FETCH next From cursor_nombres Into @nombre,@apellidos,@fecha_nacimiento,@salario

    END

CLOSE cursor_nombres  
DEALLOCATE cursor_nombres

Veamos cómo consultar un fichero de Excel desde SQL Server

Una vez que hemos recuperado los datos y los vamos leyendo con el bloque del cursor, el tratamiento de los datos es el tratamiento normal de Transact-SQL.

Si por ejemplo queremos mostrar los datos por pantalla se puede usar el comando print con las variables declaradas que se van recuperando en el cursor.

print 'Persona: ' + convert(varchar(10),@contador)
print @nombre + ' ' + @apellidos + ' ' + convert(varchar(10),@fecha_nacimiento,103) + ' ' + convert(varchar(10),@salario) + '€'

set @contador = @contador + 1

El resultado es el siguiente:

Aunque se puede utilizar también para realizar un insert en una tabla (Tabla empleados creada para la prueba)

insert into empleados values (@nombre,@apellidos,@fecha_nacimiento,@salario);

El resultado es el siguiente:

Código utilizado para consultar un fichero de Excel desde SQL Server

El código completo que hemos utilizado para el ejemplo es:

BEGIN
	-- Variables del cursor
    Declare @nombre varchar(50)
	Declare @apellidos varchar(100)
    Declare @fecha_nacimiento date
    Declare @salario Numeric(12,2)
	-- Variable contador de iteraciones del cursor
	Declare @contador int = 1
    Declare cursor_nombres Cursor For
    	SELECT nombre, apellidos, fecha_nacimiento, salario
    	FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=YES;Database=E:\prueba_excel\datos_excel.xlsx', [Hoja1$])

    OPEN cursor_nombres
    FETCH next From cursor_nombres Into @nombre,@apellidos,@fecha_nacimiento,@salario
    While @@FETCH_STATUS = 0
    BEGIN
		-- Opción imprimir por pantalla
		print 'Persona: ' + convert(varchar(10),@contador)
	    print @nombre + ' ' + @apellidos + ' ' + convert(varchar(10),@fecha_nacimiento,103) + ' ' + convert(varchar(10),@salario) + '€'
	    set @contador = @contador + 1
		-- Opción insertar las filas en una tabla Empleados
		insert into empleados values (@nombre,@apellidos,@fecha_nacimiento,@salario);
	    FETCH next From cursor_nombres Into @nombre,@apellidos,@fecha_nacimiento,@salario
    END
   	CLOSE cursor_nombres  
	DEALLOCATE cursor_nombres
END
GO
print 'Fin del proceso.'

Esperamos que os haya resultado de utilidad. No dudéis en poneros en contacto con nosotros en caso de necesitar ayuda en la gestión de vuestras bases de datos. Échale un vistazo a nuestros servicios de soporte y mantenimiento Sql Server.

¿Aún no conoces Query Performance? Descubre cómo puede ayudarte en tu entorno Oracle. Más información en su página de LinkedIn.

Sígue a GPS en LinkedIn