El Blog de Fede

Migrar 365 Archivos Excel a SQL Server 2005 sin perder tiempo…

 

Hola estimados.

Se nos presentó el siguiente escenario: había que migrar 365 archivos Excel, todos con la misma estructura a una tabla en el SQL Server.

Los nombres de los 365 archivos eran correlativos: 1.xls, 2.xls, 3.xls, …, 365.xls.
Todos los archivos tenían en la primer Hoja los datos a migrar.

Se necesitaban los datos en el SQL Server lo antes posible!!!
La versión del SQL Server era SQL Server 2005 Standard (En Ingles).

Bien, inmediatamente dijimos:

a) “hagamos un DTS con SQL Server 2000 que tenemos para pruebas, lo guardamos como un modulo en Visual Basic, y mediante una pequeña aplicación en VB6, vamos ejecutando el DTS pasándole como parámetro el nombre del archivo excel a importar….”

b) “hagamos un proyecto con SSIS (SQL Server Integration Services el sucesor del DTS) en SQL 2005, mediante una pequeña aplicación en VB.NET, vamos ejecutándolo pasándole como parámetro el nombre del archivo excel a importar….”

Bien, luego de unas pequeñas pruebas, surgieron unos pequeños dramitas con la APP en VB6 (que eran solucionables) pero dada la urgencia se decidió en ese momento avanzar paralelamente con la aplicación en VB.NET… justo ahí apareció ese instante en que uno se ilumina (por supuesto dado el escenario) y se nos ocurrió lo siguiente…

Hagámoslo con Linked Server…pero bueno como evitamos crear los 365 Linked Server??? (ya que al crear un Linked Server se vincula 1 Linked a 1 Archivo Excel)

Bien, ahí es donde surge “la magia” y nos iluminamos y decidimos utilizar OPENDATASOURCE y acceder mediante OLE DB a los archivos Excel de una manera mucho más sencilla, y fundamentalmente más rápida!!!

Utilizando un EXEC y generando el Transact-SQL dinámicamente, pudimos ir abriendo uno a uno los 365 archivos e ir insertándolos en nuestra tabla en el SQL Server.

Acá va el Transact-SQL utilizado para importar los 365 archivos:

DECLARE @Indice smallint
DECLARE @IndiceString varchar(3)

SET @Indice = 1
DELETE FROM MyTabla

WHILE @Indice <= 365
BEGIN

    SET @IndiceString = CONVERT(varchar(3),@Indice)

    EXEC('INSERT INTO MyTabla ' +
            'SELECT * FROM OPENDATASOURCE(''Microsoft.Jet.OLEDB.4.0'', ' +
            '''Data Source=C:\PathArchivos\' + @IndiceString + '.xls;Extended Properties="Excel 8.0;HDR=NO"'')...Sheet1$')

    SET @Indice = @Indice + 1
END

El WHILE se ejecutaba 365 veces, y por cada vez iba generando un EXEC que hacía en INSERT en nuestra tabla.

Un dato no menor:
Para que funcione el OPENDATASOURCE, tiene que tener el SQL Server habilitado ‘Ad Hoc Distributed Queries’. Para ello, hay que hacerlo mediante el SQL Server Surface Area Configuration Tool, o bien, mediante Transact-SQL utilizando sp_configure:

SQL Server Surface Area Configuration Tool (Surface Area Configuration for Features)

Captura

sp_configure:

sp_configure 'show advanced options', 1
reconfigure

sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure

Espero les sirva.
Saludos