====== SQL SERVER Excel =====

===== Configuraciones =====

<code sql>

-- Ver todos los Proveedores:

EXEC master.dbo.sp_MSset_oledb_prop

-- Otras configuraciones:

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE
GO
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO

</code>



===== Formas de conectarlo =====

<code sql>



 SELECT * 
FROM OPENROWSET(

   'MSDASQL',

   'Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=C:\Users\Nakankari\Desktop\import_manif_guia_Temp.xls',

   'SELECT * FROM [Guias$]')


</code>

==== Microsoft.ACE.OLEDB.12.0 ====

  * HDR=NO Sin cabecera
  * IMEX=1 No formatea los campos

Este ejemplo solo coje la cabecera
<code sql>

	SELECT TOP 1 
		*
	FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 
		   'Excel 12.0;Database=C:\Users\Nakankari\Desktop\import_manif_guia_Temp.xls;HDR=NO;IMEX=1', [Guias$])

</code>

<code sql>
	SELECT 
		*
	FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 
		   'Excel 12.0;Database=C:\Users\Nakankari\Desktop\import_manif_guia_Temp.xls;HDR=NO;IMEX=1', [Guias$]) 
		   AS W
</code>

====== XLSB =====

<code sql>

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
  'Excel 12.0;HDR=NO;Database=\\WS2008-PHP\temporales\import_manif_guia_TempFile_20201144_150944_2.xlsb', [shipment2$])

</code>


===== Mas ejemplos ======

<code sql>


DECLARE @startnum INT=1
DECLARE @endnum INT=19
DECLARE @cabecera VARCHAR(1000)
;
WITH gen AS (
    SELECT @startnum AS num
    UNION ALL
    SELECT num+1 FROM gen WHERE num+1<=@endnum
)
SELECT @cabecera = REPLACE( REPLACE( REPLACE(
(	SELECT num FROM gen
	FOR XML PATH('')
),'</num><num>', ', Excel.F')
 ,'</num>','')
 ,'<num>','Excel.F')

 --SELECT @cabecera

 DECLARE @SQL NVARCHAR(MAX) = ' SELECT '+
 @cabecera+' FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', 
	   ''Excel 12.0 Xml;HDR=NO;IMEX=1;Database=\\WS2008-PHP\temporales\import_terceros_guia_TempFile_20210334_104234.xlsx'',
	   [EstadoManifiesto_234$]) AS Excel '

--SELECT @SQL

EXEC sp_executesql @SQL


</code>