miércoles, 22 de agosto de 2007

Exportar e Importar datos de SQL a Excel y Viceversa sin DTS

Bueno hace poco, tuve que realizar una importación de datos de Excel a SQL Server, la primera vez la realice con el Wizard de importación, pero hice una búsqueda en Google para ver si había una manera mas fácil y rápida, y si existe solo que todas las referencias que encontré por desgracia están en ingles, así que también uno de los propósitos de este blog es brindar recursos a la comunidad de desarrolladores mexicanos y latinoamericanos, pero en español, así que en mi primer entrada les explicare como se realiza este proceso.

Importación

Aquí tenemos una vista del libro de Excel del cual queremos leer los datos.

Free Image Hosting at www.ImageShack.us

Para mayor facilidad en la lectura de los datos yo guarde el archivo en raíz, para que su ruta sea esta: C:\prueba.xls.

Entonces ahora lo que procede es escribir el query en el manager de Sql Server, el cual queda así:

SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:\libro.xls',
'SELECT * FROM [exportar$]')

Si revisamos un poco el query veremos que no tiene gran ciencia a no ser por la instrucción OPENROWSET, la cual sirve para conectarnos a diversas fuentes de datos, y el ultimo select sirve para decirle a la función OPENROWSET de que hoja de Excel es de la que queremos traer los datos, si revisan la imagen de arriba, podrán ver que la hoja de mi libro de Excel se llama exportar.

El primer problema que me encontré al tratar de ejecutar este query fue que necesita tener habilitada la siguiente opción:

'Ad Hoc Distributed Queries', y para habilitar dicha opción necesitaremos ejecutar la siguiente consulta:

sp_configure 'show advanced options',1
reconfigure with override
go
sp_configure 'Ad Hoc Distributed Queries',1
reconfigure with override
go

Con las cuales modificamos su valor de default que es 0 por 1, si desean regresar al valor predefinido, necesitan ejecutar la consulta solo que en lugar de 1 le deberán poner 0.

Ahora si, al momento de ejecutar el query el resultado es el siguiente:

Free Image Hosting at www.ImageShack.us

Una vez que ya pudimos leer los datos del libro podremos hacer lo que queramos con ellos con mas consultas SQL, como insertarlos en una tabla existente, crear una tabla exclusiva para estos datos, etc, etc.

EXPORTAR

Para exportar datos a un libro de Excel, es igual de sencillo, solo cambia un poco la estructura del query, y un par de detalles que hay que cuidar.

El query queda así:

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:\prueba1.xls;',
'SELECT * FROM [Sheet1$]') select * from GASTOS

Lo que estamos diciendo aquí es que queremos que inserte dentro de nuestra hoja de Excel “C:\prueba1.xls”, dentro de la hoja1 “["Sheet11$"]”, todo lo que encuentre dentro de la tabla GASTOS.

Los dos pequeños detalles a cuidar aquí son:

  1. El libro de Excel ya debe de existir en la ruta que le especifiquemos, esta instrucción no tiene la habilidad de crearlo.
  2. Dentro del libro que ya debe de estar creado, deben de existir en la primer fila el nombre de las columnas que se exportaran desde la(s) tablas de SQL Server.

Y eso es todo para mi primer entrada, espero les sea de utilidad.

Saludos,

.:.:AXY:.:.

8 comentarios:

Unknown dijo...

Increible que no este post no tenga comentarios. Un comentario algo tardío, pero amigo, muchas gracias, la verdad que una explicación muy sencilla y suficiente. La documentación de Microsoft en español es desastrosa (por lo menos la que aparece con al primera búsqueda en google) ya que mencionan la sentencia de exportación, pero no como cambiar la configuración para que sea posible.

Unknown dijo...
Este comentario ha sido eliminado por el autor.
Unknown dijo...

Estupendo post! De gran utilidad. Gracias :)

cj dijo...

No lo he probado aún, pero estoy deseando hacerlo. Hace poco que estamos trabajando con SQL_SERVER y toda ayuda es poca.

!! muchas gracias !!

Unknown dijo...

Te felicito amigo, muy util tu explicacion...

Fermin Moreno

Unknown dijo...

Buena la explicación, pero me podrian decir como hacerlo con excel 2013 y sql server 2014

Unknown dijo...

Felicitaciones, de gran utilidad,
muchas gracias...

Pablo RI dijo...

buenas tardes al ejecutar las lineas me arroja este error gracias **** Cannot create an instance of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".