Trabajar con Excel en SQL Server

Muchas veces necesitamos obtener datos desde un Excel e importarlos en una tabla de nuestra base de datos o sencillamente utilizarlos con alguna consulta, si este es tu caso este tutorial te puede ayudar a trabajar con archivos Excel como si fueran una base de datos y cada una de sus hojas como una tabla.

Lo primero que tienes que tener instado en el servidor donde está SQL Server (no vale en la máquina remota donde haces las consultas) es el Office o en su defecto 2007 Office System Driver Data Connectivity Components (solo 32 bits) o Microsoft Access Database Engine 2010 Redistributable (versiones de 32 y 64 bits). Es muy muy importante tener esto instalado para evitar el siguiente error The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered. Más importante aún es tener la versión correcta, si tu SQL Server es de 64 bits solo puedes instalar de 64 bits, si es de 32 bits lo mismo, en caso contrario tendrías el siguiente error OLE DB provider 'Microsoft.ACE.OLEDB.12.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

Nota: para trabajar con archivos XLS se necesita Microsoft.Jet.OLEDB.4.0 pero este creo que solo viene en versiones de 32 bits, yo no lo he probado porque siempre utilizo archivos XLSX.

Una vez instalado el Office o el correspondiente driver de Microsoft debemos permitir este tipo de consultas en la configuración de SQL o de lo contrario tendremos el error siguiente SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

Para activar la configuración ejecutamos como administradores las siguientes consultas:

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE
GO

Si todos va bien deberíamos obtener esta respuesta:

Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
Configuration option 'Ad Hoc Distributed Queries' changed from 0 to 1. Run the RECONFIGURE statement to install.

Una vez configurado SQL Server debemos modificar el registro para poder crear instancias de estos proveedores OLE por suerte lo podemos hacer desde SQL Server.

USE [master]
GO
EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1
GO
EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1
GO

De no hacerlo tendremos un error como este Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Con esto ya podremos ejecutar la consulta sobre nuestro archivo Excel (que debe estar accesible al servidor de SQL Server)

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\Libro2.xlsx', 'SELECT * FROM [Hoja1$]')

Nota: muchas veces cuando queremos utilizar estos datos dentro de una tabla las codificaciones de los datos no son las mismas, en mi caso tenía el siguiente error al intentar hacer un INNER JOIN Cannot resolve the collation conflict between "Modern_Spanish_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

Para solucionarlo al lado de cada campo del Excel debemos especificar la codificación, esto lo podemos hacer con el comando COLLATE.

Os dejo un enlace que os puede ayudar a completar la información. Import/Export Excel (.Xlsx) or (.Xls) File into SQL Server.

¿Alguna sugerencia?