Leer archivos .xlsx desde SQL Server es una tarea sencilla, aunque hay veces que puede requerir de cierta configuración inicial. En ester artículo vamos a ver cómo preparar una instancia de SQL Server para leer este tipo de archivos de forma nativa.

Para empezar, podemos comprobar que tenemos el OLE DB provider necesario:

 USE [master];
 EXEC sys.sp_enum_oledb_providers;

Si no vemos un ProviderName con formato Microsoft.ACE.OLEDB.XX.0, donde XX suele ser 12 o 16. Entonces nos hará falta descargar el Microsoft Access Database Engine 2016 Redistributable, puedes usar este link.

Una consulta usando OPENROWSET es lo que nos permitirá acceder al contenido del archivo .xlsx como si fuera una tabla. Ejemplo:

SELECT *
FROM OPENROWSET(
    'Microsoft.ACE.OLEDB.16.0',
    'Excel 8.0;HDR=NO;Database=C:\Users\Data\Survey.xlsx',
    'select * from [sheet1$]');

Es posible que la primera vez que ejecutemos este tipo de consulta nos encontremos con este error:

Msg 15281, Level 16, State 1, Line 10
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’, search for ‘Ad Hoc Distributed Queries’ in SQL Server Books Online.

La solución es bastante sencilla, pero debes consultar con tu administrador de base de datos en caso de que necesites hacer esto en un servidor que no sea tu local:

USE [master];
EXEC sys.sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sys.sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;

Otro problema que podemos encontrarnos es el siguiente:

Msg 7399, Level 16, State 1, Line 10
The OLE DB provider «Microsoft.ACE.OLEDB.16.0» for linked server «(null)» reported an error. Access denied.
Msg 7350, Level 16, State 2, Line 10
Cannot get the column information from OLE DB provider «Microsoft.ACE.OLEDB.16.0» for linked server «(null)».

Puede que haga falta habilitar un par de opciones más para Microsoft.ACE.OLEDB.16.0:

USE master;
EXEC dbo.sp_MSset_oledb_prop; -- Comprobar si AllowInProcess y DynamicParameters están habilitados
-- Si no lo están, ejecutar lo siguiente:
EXEC dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.16.0', N'AllowInProcess', 1;
EXEC dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.16.0', N'DynamicParameters', 1;

Si aún siguieramos teniendo problema de permisos, pude ser porque el usuario con el que estamos ejecutando nuestro cliente de SQL, típicamente SSMS, no tiene permisos para leer el fichero .xlsx, por lo que se resuelve asignando los permisos necesarios en el fichero que queremos leer, ej: usando el Windows explorer: Botón derecho sobre nuestro archivo > Propiedades > Seguridad > Editar, y añadimos el usuario y permiso de lectura. A veces también puedes resolverlo ejecutando tu cliente como administrador.

Con todo lo anterior, ya no deberías tener problema para leer el archivo .xlsx. ¡Espero que te haya servido de ayuda!