Cargar hoja de excel a SQL SERVER

"El Cote" wrote in message
news:
Mostrar la cita

¿Conocen alguna forma óptima de cargar una hoja de excel
con muchísimos registros a SQL SERVER?

Si tienes una de las versiones "grandes" de Sql Server, podrías usar SQL
Server Integration Services (SSIS), que entre los múltiples orígenes de
datos que soporta incluye Excel. En ese caso, la importación la realizaría
el propio servidor, y te ahorrarías el tráfico de datos que se ocasionaría
si hicieras la importación con un programa en una estación de trabajo.

"Fernando Gómez" escribió:

Mostrar la cita

Jet agrega más capas de procesamiento. No dudo que sea rápido, pero
pudiese ser más rápido. Además, no necesitas ordenar tu hoja de Excel
para que luzca como base de datos. Esto último, quizás, puede ser lo
más interesante para el OP.

Pues no sabría decirte quién de las dos agrega más capas de procesamiento.

Jet, o mejor dicho, la biblioteca correspondiente al ISAM de Excel,
únicamente se utiliza para conectarse con el origen de datos y ejecutar la
consulta SQL: punto. Con las bibliotecas de Office, hay que utilizar el
modelo de objetos propio de la biblioteca que se vaya a utilizar, y digo yo
que alguna que otra capa añadirá al procesamiento. Aparte, salvo que
utilicemos el espacio de nombres System.Reflection, estamos vinculando
nuestra aplicación a una versión concreta de la biblioteca de Excel, la
cual, necesariamente tiene que estar también instalada en el equipo cliente.
La versión 4.0 del motor Microsoft Jet viene incluido con los últimos
sistemas operativos Microsoft Windows, cosa que no sucede con las
bibliotecas de Office.

Ten en cuenta que sólo me estoy refiriendo a exportar/importar datos de
Excel o hacia Excel. Si desde nuestra aplicación vamos a utilizar Excel para
otras cuestiones, entonces sí sería necesario trabajar directamente con el
modelo de objetos de las bibliotecas Microsoft Office y Microsoft Excel.

También podemos utilizar Microsoft SQL Server para realizar la importación
de datos, tal y como bien ha comentado Alberto Población, pero si la
importación /exportación se efectúa una vez al més, pienso que se puede
utilizar perfectamente el ISAM para Excel que nos proporciona tanto el motor
Microsoft Jet como el motor de Microsoft ACE (Office 2007).

Enrique Martínez
[MS MVP - VB]

Nota informativa: La información contenida en este mensaje, así como el
código fuente incluido en el mismo, se proporciona «COMO ESTÁ», sin
garantías de ninguna clase, y no otorga derecho alguno. Usted asume
cualquier riesgo al poner en práctica, utilizar o ejecutar lo recomendado o
sugerido en el presente mensaje.

On May 21, 7:10 am, SoftJaén wrote:
Mostrar la cita

"Fernando Gómez" escribió:

> Jet agrega más capas de procesamiento. No dudo que sea rápido, pero
> pudiese ser más rápido. Además, no necesitas ordenar tu hoja de Excel
> para que luzca como base de datos. Esto último, quizás, puede ser lo
> más interesante para el OP.

Pues no sabría decirte quién de las dos agrega más capas de procesamiento.

Jet, o mejor dicho, la biblioteca correspondiente al ISAM de Excel,
únicamente se utiliza para conectarse con el origen de datos y ejecutar la
consulta SQL: punto.

Claro, que corre bajo ODBC y el driver para ODBC de Excel.

Mostrar la cita

Con las bibliotecas de Office, hay que utilizar el
modelo de objetos propio de la biblioteca que se vaya a utilizar, y digo yo
que alguna que otra capa añadirá al procesamiento.

Sí, al emplear COM Interop le agregas la capa asociada a COM.
Pero .NET Framework está construido sobre ésta, así que esa capa ya la
tiene que emplear de cualquier forma.

Mostrar la cita

Aparte, salvo que
utilicemos el espacio de nombres System.Reflection, estamos vinculando
nuestra aplicación a una versión concreta de la biblioteca de Excel,

No exactamente. Estás vinculándote contra interfases COM, cuyo ID
(IID, CLSID y GUID) no cambiará nunca. La interfaz IApplication, por
ejemplo, tiene un GUID == x, entonces cuando se solicita la interfaz
el API de COM va en busca de la librería que contenga esta interfaz
(la localiza a través del GUID), carga la DLL correspondiente y manda
ejecutar a GetClassObject, que será la que regresará el puntero a la
interfaz seleccionada. Todo esto es COM normalito, estándar. Si
después cambio de versión de Office, la librería que venga
necesariamente tendrá la interfaz IApplication con los mismos GUIDs.
Lo único que tiene que hacer es redireccionar el GUID a la nueva DLL
que contenga el componente COM. Así, puedo cambiar de versiones y
asegurarme de que no haya problemas de compatibilidad, siempre que
Office no cometa una idiotez como no incluir IApplication, etc.

Un programa que emplee COM, pués, no se une ni estática ni
dinámicamente con una librería. De hecho, el soporte Interop que
tiene .NET es precísamente para esto. Te unes con respecto a la
interfaz (o co-clases que utilices como ApplicationClass), pero no hay
una dependencia de la librería.

Mostrar la cita

la
cual, necesariamente tiene que estar también instalada en el equipo cliente.
La versión 4.0 del motor Microsoft Jet viene incluido con los últimos
sistemas operativos Microsoft Windows, cosa que no sucede con las
bibliotecas de Office.

Aquí sí no tengo argumento en contra. Empero, existen los componentes
redistribuibles que bien se pueden distribuir junto con la aplicación,
y que no cuestan un céntimo más, pero lo mejor es que no habría que
pagar por ellos.

Mostrar la cita

Ten en cuenta que sólo me estoy refiriendo a exportar/importar datos de
Excel o hacia Excel. Si desde nuestra aplicación vamos a utilizar Excel para
otras cuestiones, entonces sí sería necesario trabajar directamente con el
modelo de objetos de las bibliotecas Microsoft Office y Microsoft Excel.

También podemos utilizar Microsoft SQL Server para realizar la importación
de datos, tal y como bien ha comentado Alberto Población, pero si la
importación /exportación se efectúa una vez al més, pienso que se puede
utilizar perfectamente el ISAM para Excel que nos proporciona tanto el motor
Microsoft Jet como el motor de Microsoft ACE (Office 2007).

Sí, de acuerdo. Lo que alego en esencia es que para emplear ISAM et.
al., necesitas mantener un formato para la hoja, lo cuál no
necesariamente es bueno, sobre todo si es una hoja que emplean, por
ejemplo, como formatos de algo. Entonces habría que cambiar el layout
de la hoja, lo cuál no sería una buena idea desde el punto de vista
del negocio. Por eso, me inclino más por emplear la Office Object
Library.

"Fernando Gómez" escribió:

Mostrar la cita

Claro, que corre bajo ODBC y el driver para ODBC de Excel.

El ejemplo que he puesto, SÓLO utiliza el driver ODBC de SQL Server para
conectarse a una instancia de Microsoft SQL Server, pero el driver de ODBC
de Excel no se utiliza, de hecho, estoy utilizando un objeto
«OleDbConnection» en cuya cadena de conexión indico expresamente que voy a
utilizar el proveedor de datos Ole Db del motor Microsoft Jet 4.0. Si
utilizara el driver ODBC de Excel, entonces en la cadena de conexión habría
que especificarlo expresamente, así como utilizar el proveedor .net para
Odbc

También se puede hacer la importación sin utilizar el driver ODBC de SQL
Server, porque lo podemos hacer mediante Ole Db ejecutando las funciones
OPENDATASOURCE u OPENROWSET del propio lenguaje Transact-SQL de Microsoft
SQL Server. En este caso, habría que utilizar entonces los objetos incluidos
en el espacio de nombres «System.Data.SqlClient». Y por supuesto, también se
puede utilizar un «servidor vinculado» para efectuar la importación. ¡Vamos!
¡Que existen varias maneras de hacerlo! Pero éstas, también utilizan el ISAM
de Excel, ya que en la cadena de conexión Ole Db hay que especificar el
proveedor de datos Microsoft Jet o Microsoft ACE (si los datos pertenecen a
la versión 2007 de Excel).

Mostrar la cita

Si después cambio de versión de Office, la librería que venga
necesariamente tendrá la interfaz IApplication con los mismos GUIDs.

Totalmente de acuerdo.

Mostrar la cita

Lo único que tiene que hacer es redireccionar el GUID a la nueva DLL
que contenga el componente COM. Así, puedo cambiar de versiones y
asegurarme de que no haya problemas de compatibilidad, siempre que
Office no cometa una idiotez como no incluir IApplication, etc.

En los proyectos de C# ignoro por completo lo que puede suceder. En los
proyectos de Visual Basic .net, como en los anteriores de Visual Basic 6.0,
cuando referencias en el proyecto una versión en concreto de la biblioteca
de Excel o de Word, ésta versión es la que deberá estar instalada en los
equipos cliente.

Si posteriormente deseas cambiar a una nueva versión, tienes que editar
nuevamente la aplicación, quitar la referencia a la antigua versión y añadir
la referencia a la nueva versión, por tanto, hay que volver a generar la
aplicación y distribuirla, aunque los GUIDs del objeto Excel.Application o
Word.Application sean los mismos.

Con el ISAM de Excel, no existen estos pequeños o grandes inconvenientes,
según como se mire. ;-)

Mostrar la cita

Empero, existen los componentes
redistribuibles que bien se pueden distribuir junto con la aplicación,
y que no cuestan un céntimo más, pero lo mejor es que no habría que
pagar por ellos.

¿Que Office tiene componentes redistribuibles que no cuestan ni un céntimo?
¿Me estás diciendo que se puede redistribuir libremente la biblioteca de
Excel o de Word sin pagar nada a cambio? Si es así, ahora me estoy
enterando. :-(

¿Por casualidad no tendrás un enlace a la página de descarga de dichos
componentes redistribuibles?

Mostrar la cita

Lo que alego en esencia es que para emplear ISAM et.
al., necesitas mantener un formato para la hoja, lo cuál no
necesariamente es bueno, sobre todo si es una hoja que emplean, por
ejemplo, como formatos de algo. Entonces habría que cambiar el layout
de la hoja, lo cuál no sería una buena idea desde el punto de vista
del negocio. Por eso, me inclino más por emplear la Office Object
Library.

¡Coño! Tampoco una tabla de una base de datos Microsoft SQL Server tiene un
formato específico; tiene una estructura tabular compuesta de filas y
columnas; el formato, posteriormente se lo daremos, si así lo creemos
oportuno, pero lo que se guarda en la base son datos, no formatos. :-)

Si tú quieres pasar datos de Excel a SQL Server, o a cualquier otro formato
de base de datos de las existentes en el mercado, digo yo que tendrás que
respectar la estructura de la base de datos receptora.

Ya comenté en un principio que si en la hoja de cálculo existen celdas
combinadas, o aquella no presenta la típica estructura tabular, se puede
tener problemas a la hora de exportar los datos utilizando el ISAM para
Excel. Pero estos mismos problemas también se pueden tener utilizando los
objetos propios de la biblioteca de Excel, por lo que a lo mejor, antes de
llevar a cabo la exportación, lo mismo hay que preparar los datos para que
se puedan exportar correctamente a SQL Server o a Microsoft Access.

Enrique Martínez
[MS MVP - VB]

Nota informativa: La información contenida en este mensaje, así como el
código fuente incluido en el mismo, se proporciona «COMO ESTÁ», sin
garantías de ninguna clase, y no otorga derecho alguno. Usted asume
cualquier riesgo al poner en práctica, utilizar o ejecutar lo recomendado o
sugerido en el presente mensaje.

On May 21, 12:04 pm, "SoftJaén" wrote:
Mostrar la cita

"Fernando Gómez" escribió:

> Claro, que corre bajo ODBC y el driver para ODBC de Excel.

El ejemplo que he puesto, SÓLO utiliza el driver ODBC de SQL Server para
conectarse a una instancia de Microsoft SQL Server, pero el driver de ODBC
de Excel no se utiliza, de hecho, estoy utilizando un objeto
«OleDbConnection» en cuya cadena de conexión indico expresamente que voy a
utilizar el proveedor de datos Ole Db del motor Microsoft Jet 4.0. Si
utilizara el driver ODBC de Excel, entonces en la cadena de conexión habría
que especificarlo expresamente, así como utilizar el proveedor .net para
Odbc

Sí, en tu ejemplo solo usas OleDB, pero el ISAM (que es llamado por
éste) a su vez emplea ODBC y el driver de Excel.

Mostrar la cita

También se puede hacer la importación sin utilizar el driver ODBC de SQL
Server, porque lo podemos hacer mediante Ole Db ejecutando las funciones
OPENDATASOURCE u OPENROWSET del propio lenguaje Transact-SQL de Microsoft
SQL Server. En este caso, habría que utilizar entonces los objetos incluidos
en el espacio de nombres «System.Data.SqlClient». Y por supuesto, también se
puede utilizar un «servidor vinculado» para efectuar la importación. ¡Vamos!
¡Que existen varias maneras de hacerlo! Pero éstas, también utilizan el ISAM
de Excel, ya que en la cadena de conexión Ole Db hay que especificar el
proveedor de datos Microsoft Jet o Microsoft ACE (si los datos pertenecen a
la versión 2007 de Excel).

> Si después cambio de versión de Office, la librería que venga
> necesariamente tendrá la interfaz IApplication con los mismos GUIDs.

Totalmente de acuerdo.

> Lo único que tiene que hacer es redireccionar el GUID a la nueva DLL
> que contenga el componente COM. Así, puedo cambiar de versiones y
> asegurarme de que no haya problemas de compatibilidad, siempre que
> Office no cometa una idiotez como no incluir IApplication, etc.

En los proyectos de C# ignoro por completo lo que puede suceder. En los
proyectos de Visual Basic .net, como en los anteriores de Visual Basic 6.0,
cuando referencias en el proyecto una versión en concreto de la biblioteca
de Excel o de Word, ésta versión es la que deberá estar instalada en los
equipos cliente.

De ser eso cierto para VB.NET, entonces no sigue las especificaciones
del CLR/CLI para la interoperabilidad con COM. De VB6, no me
sorprendería en absoluto.

Mostrar la cita

Si posteriormente deseas cambiar a una nueva versión, tienes que editar
nuevamente la aplicación, quitar la referencia a la antigua versión y añadir
la referencia a la nueva versión, por tanto, hay que volver a generar la
aplicación y distribuirla, aunque los GUIDs del objeto Excel.Application o
Word.Application sean los mismos.

Con el ISAM de Excel, no existen estos pequeños o grandes inconvenientes,
según como se mire. ;-)

> Empero, existen los componentes
> redistribuibles que bien se pueden distribuir junto con la aplicación,
> y que no cuestan un céntimo más, pero lo mejor es que no habría que
> pagar por ellos.

¿Que Office tiene componentes redistribuibles que no cuestan ni un céntimo?
¿Me estás diciendo que se puede redistribuir libremente la biblioteca de
Excel o de Word sin pagar nada a cambio? Si es así, ahora me estoy
enterando. :-(

Sí, a través de los diferentes SDKs y los puedes bajar de la página de
downloads, de Microsoft. Obvio solo vienen librerías para desarrollo,
y no tienes acceso a la GUI de Excel (et. al.), pero sirven
precísamente para eso, para hacer los desarrollos.
http://www.microsoft.com/downloads/...layLang=en

Hay otros componentes que también te los puedes descargar aparte, como
los Office Web Components.
http://www.microsoft.com/downloads/...layLang=en

etc.

Mostrar la cita

¿Por casualidad no tendrás un enlace a la página de descarga de dichos
componentes redistribuibles?

> Lo que alego en esencia es que para emplear ISAM et.
> al., necesitas mantener un formato para la hoja, lo cuál no
> necesariamente es bueno, sobre todo si es una hoja que emplean, por
> ejemplo, como formatos de algo. Entonces habría que cambiar el layout
> de la hoja, lo cuál no sería una buena idea desde el punto de vista
> del negocio. Por eso, me inclino más por emplear la Office Object
> Library.

¡Coño! Tampoco una tabla de una base de datos Microsoft SQL Server tiene un
formato específico; tiene una estructura tabular compuesta de filas y
columnas; el formato, posteriormente se lo daremos, si así lo creemos
oportuno, pero lo que se guarda en la base son datos, no formatos. :-)

SQL Server guarda datos, no formatos, en efecto. Excel guarda ambos.
Para una hoja de cálculo en Excel que se emplee en una empresa para
llevar controles, emplear ISAM forzaría a que, y cito, "los datos de
la hoja de cálculo se encuentre en un formato de tabla, es decir, la
típica estructura de filas y columnas". Es decir, según tu primer
correo, si la hoja de cálculo no se encuentra en un "formato de
tabla", no se puede emplear ISAM (u ODBC u OLEDB). Si el OP tiene una
hoja de cálculo que no siga este formato, tendría que cambiarlo para
hacerlo funcionar. Mi respuesta iba en ese sentido: empleando las
Office Object Libraries no necesita cambiar el formato de su hoja,
pues puede accesar a las celdas de forma directa.

Mostrar la cita

Si tú quieres pasar datos de Excel a SQL Server, o a cualquier otro formato
de base de datos de las existentes en el mercado, digo yo que tendrás que
respectar la estructura de la base de datos receptora.

Ya comenté en un principio que si en la hoja de cálculo existen celdas
combinadas, o aquella no presenta la típica estructura tabular, se puede
tener problemas a la hora de exportar los datos utilizando el ISAM para
Excel. Pero estos mismos problemas también se pueden tener utilizando los
objetos propios de la biblioteca de Excel, por lo que a lo mejor, antes de
llevar a cabo la exportación, lo mismo hay que preparar los datos para que
se puedan exportar correctamente a SQL Server o a Microsoft Access.

Nope, con los objetos de la biblioteca de Excel puedes acceder a las
celdas dada sus coordenadas (i.e. H8). Sí tendrías que saber en qué
celda está qué información, pero no tendrías que cambiar el formato.

Saludos,
Fernando.