102057967 Como Importar Datos de Excel a SQL Server
Transcript of 102057967 Como Importar Datos de Excel a SQL Server
Cómo importar datos de Excel a SQL Server Id. de artículo: 321686 - Ver los productos a los que se aplica este artículo
Advertencia: Artículo de Traducción Automática, vea la exención de responsabilidad.
Haga clic aquí para ver en paralelo el artículo de traducción automática y el artículo original en
inglés.
Expandir todo | Contraer todo
En esta página
Resumen
En este artículo paso a paso se muestra cómo importar datos de hojas de cálculo de Microsoft Excel
en bases de datos de Microsoft SQL Server mediante una variedad de métodos.
Descripción de la técnica
Para importar datos de Excel, los ejemplos de este artículo con:
SQL Server Data Transformation Services (DTS)
Microsoft SQL Server 2005 Integration Services (SSIS)
Servidores vinculados de SQL Server
Consultas distribuidas de SQL Server
ActiveX Data Objects (ADO) y el proveedor Microsoft OLE DB para SQL Server
ADO y el proveedor Microsoft OLE DB para Jet 4.0
Requisitos
La lista siguiente describe el hardware recomendado, software, infraestructura de red y los service
Pack que se necesitan:
Instancia disponible de Microsoft SQL Server 7.0 o 2000 de Microsoft SQL Server o
Microsoft SQL Server de 2005
Microsoft Visual Basic 6.0 de los ejemplos de ADO que utilice Visual Basic
Partes de este artículo se supone que está familiarizado con los temas siguientes:
Servicios de transformación de datos
Los servidores vinculados y las consultas distribuidas
Desarrollo de ADO en Visual Basic
Ejemplos
Importación frente a anexar
Las instrucciones de SQL de ejemplo que se utilizan en este artículo muestran las consultas de
Create Table que importación datos de Excel en una nueva tabla de SQL Server con el
seleccionar...EN...DE la sintaxis. Puede convertir estas instrucciones para consultas de datos
anexados mediante el uso de INSERT INTO...SELECCIONE...Continúen de sintaxis mientras haciendo
referencia a los objetos de origen y destino como se muestra en estos ejemplos de código.
Uso DTS o SSIS
Puede utilizar el Asistente para importación de servicios de transformación de datos (DTS) de SQL
Server o SQL Server de importación y el Asistente para exportación para importar datos de Excel en
las tablas de SQL Server. Cuando se recorre el asistente y seleccionar las tablas de origen de Excel,
recuerde que los nombres de objeto de Excel que se anexan con un signo de dólar ($) representan
hojas de cálculo (por ejemplo, Sheet1$) y que los nombres de objeto simple sin el signo de dólar
representan Excel rangos con nombre.
Utilizar un servidor vinculado
Para simplificar las consultas, puede configurar un libro de Excel como un servidor vinculado en SQL
Server. Para obtener información adicional, haga clic en el número de artículo siguiente para verlo
en Microsoft Knowledge Base:
306397 HOWTO: Usar Excel con SQL Server de servidores vinculados y consultas distribuidas
El código siguiente importa los datos de la hoja de cálculo de los clientes en el servidor vinculado
de Excel "EXCELLINK" en una nueva tabla de SQL Server, denominado XLImport1:
SELECT * INTO XLImport1 FROM EXCELLINK...[Customers$]
También puede ejecutar la consulta en el origen de una manera de atravesar mediante OPENQUERY
como sigue: SELECT * INTO XLImport2 FROM OPENQUERY(EXCELLINK, 'SELECT * FROM [Customers$]')
Utilizar consultas distribuidas
Si no desea configurar una conexión persistente al libro de Excel como un servidor vinculado, puede
importar datos para un propósito específico mediante el OPENDATASOURCE o la función
OPENROWSET. Ejemplos de código siguientes también importación los datos de la hoja de cálculo
de Excel clientes en nuevas tablas de SQL Server:
SELECT * INTO XLImport3 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=C:\test\xltest.xls;Extended Properties=Excel 8.0')...[Customers$] SELECT * INTO XLImport4 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\test\xltest.xls', [Customers$]) SELECT * INTO XLImport5 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\test\xltest.xls', 'SELECT * FROM [Customers$]')
Utilizar ADO y SQLOLEDB
Cuando está conectado a SQL Server en una aplicación de ADO con OLE DB de Microsoft para SQL
Server (SQLOLEDB), puede utilizar la misma sintaxis de "consulta distribuida" en la sección Utilizar
consultas distribuidas para importar datos de Excel a SQL Server.
En el siguiente ejemplo de código de Visual Basic 6.0 requiere que agregue una referencia de
proyecto ActiveX Data Objects (ADO). Este ejemplo de código también muestra cómo utilizar
OPENDATASOURCE y OPENROWSET en una conexión de SQLOLEDB.
Dim cn As ADODB.Connection Dim strSQL As String Dim lngRecsAff As Long Set cn = New ADODB.Connection cn.Open "Provider=SQLOLEDB;Data Source=<server>;" & _ "Initial Catalog=<database>;User ID=<user>;Password=<password>" 'Import by using OPENDATASOURCE. strSQL = "SELECT * INTO XLImport6 FROM " & _ "OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', " & _ "'Data Source=C:\test\xltest.xls;" & _ "Extended Properties=Excel 8.0')...[Customers$]" Debug.Print strSQL cn.Execute strSQL, lngRecsAff, adExecuteNoRecords Debug.Print "Records affected: " & lngRecsAff 'Import by using OPENROWSET and object name. strSQL = "SELECT * INTO XLImport7 FROM " & _ "OPENROWSET('Microsoft.Jet.OLEDB.4.0', " & _ "'Excel 8.0;Database=C:\test\xltest.xls', " & _ "[Customers$])" Debug.Print strSQL cn.Execute strSQL, lngRecsAff, adExecuteNoRecords Debug.Print "Records affected: " & lngRecsAff 'Import by using OPENROWSET and SELECT query. strSQL = "SELECT * INTO XLImport8 FROM " & _ "OPENROWSET('Microsoft.Jet.OLEDB.4.0', " & _ "'Excel 8.0;Database=C:\test\xltest.xls', " & _ "'SELECT * FROM [Customers$]')" Debug.Print strSQL cn.Execute strSQL, lngRecsAff, adExecuteNoRecords Debug.Print "Records affected: " & lngRecsAff cn.Close Set cn = Nothing
Utilizar ADO y el proveedor Jet
En el ejemplo de la sección anterior utiliza ADO con el proveedor SQLOLEDB para conectar al
destino de la importación de Excel a SQL. También puede utilizar el proveedor OLE DB para Jet 4.0
para conectarse al origen de Excel.
El motor de base de datos Jet puede hacer referencia a bases de datos externas en las instrucciones
de SQL mediante una sintaxis especial que tiene tres formatos diferentes:
[Ruta de acceso completa a la base de datos de Microsoft Access].[Nombre de la tabla]
[Nombre ISAM;Cadena de conexión de ISAM].[Nombre de la tabla]
[ODBC;Cadena de conexión ODBC].[Nombre de la tabla]
Esta sección utiliza el formato tercero para realizar una conexión ODBC a la base de datos de
destino de SQL Server. Puede utilizar un nombre de origen de datos ODBC (DSN) o una cadena de
conexión sin DSN: DSN: [odbc;DSN=<DSN name>;UID=<user>;PWD=<password>] DSN-less: [odbc;Driver={SQL Server};Server=<server>;Database=<database>; UID=<user>;PWD=<password>]
En el siguiente ejemplo de código de Visual Basic 6.0 requiere que agregue una referencia de
proyecto a ADO. Este ejemplo de código muestra cómo importar datos de Excel a SQL Server a
través de una conexión ADO a través del proveedor de Jet 4.0.
Dim cn As ADODB.Connection Dim strSQL As String Dim lngRecsAff As Long Set cn = New ADODB.Connection cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\test\xltestt.xls;" & _ "Extended Properties=Excel 8.0" 'Import by using Jet Provider. strSQL = "SELECT * INTO [odbc;Driver={SQL Server};" & _ "Server=<server>;Database=<database>;" & _ "UID=<user>;PWD=<password>].XLImport9 " & _ "FROM [Customers$]" Debug.Print strSQL cn.Execute strSQL, lngRecsAff, adExecuteNoRecords Debug.Print "Records affected: " & lngRecsAff cn.Close Set cn = Nothing
También puede utilizar esta sintaxis, que admite el proveedor Jet, para importar datos de Excel en
otras bases de datos de Microsoft Access, bases de datos de acceso secuencial indizado (ISAM) del
método ("escritorio") o bases de datos ODBC.
Solución de problemas
Recuerde que los nombres de objeto de Excel que se anexan con un signo de dólar
($) representan hojas de cálculo (por ejemplo, Sheet1$) y que los nombres de objeto
simple Excel rangos con nombre.
En algunas circunstancias, especialmente al designar los datos de origen de Excel
con el nombre de la tabla en lugar de una consulta de selección, se reorganizan las
columnas de la tabla de destino de SQL Server en orden alfabético.Para obtener
información adicional acerca de este problema con el proveedor Jet, haga clic en el
número de artículo siguiente para verlo en Microsoft Knowledge Base:
299484 PRB: Las columnas se ordenan alfabéticamente cuando se utiliza ADOX para
recuperar columnas de tabla de Access
Cuando el proveedor Jet determina que una columna de Excel contiene datos
numéricos y texto mixto, el proveedor Jet selecciona el tipo de datos "mayoría" y
devuelve valores no coincidentes como NULLs.For obtener información adicional
acerca de cómo evitar este problema, haga clic en el número de artículo siguiente
para verlo en Microsoft Knowledge Base:
194124 PRB: Valores de Excel devueltos como NULL utiliza OpenRecordset de DAO
Volver al principio | Propocionar comentarios
Referencias
Para obtener información adicional acerca de cómo utilizar Excel como un origen de datos, haga clic
en el número de artículo siguiente para verlo en Microsoft Knowledge Base:
257819 HOWTO: Usar ADO con datos de Excel desde Visual Basic o desde VBA
Para obtener información adicional acerca de cómo transferir los datos a Excel, haga clic en los
números de artículo siguientes para verlos en Microsoft Knowledge Base:
295646 HOWTO: Transferir datos de origen de datos de ADO a Excel con ADO
247412 INFO: Métodos para transferir datos a Excel desde Visual Basic
246335 HOWTO: Transferir datos desde un conjunto de registros ADO a Excel con automatización
319951 Cómo: Servicios de transferencia de datos a Excel utilizando la transformación de datos de
SQL Server
306125 Cómo: Importar datos desde SQL Server a Microsoft Excel
Volver al principio | Propocionar comentarios
Propiedades
Id. de artículo: 321686 - Última revisión: jueves, 05 de julio de 2012 - Versión: 5.0
La información de este artículo se refiere a:
Microsoft Excel 2000 Standard Edition
Microsoft SQL Server 2000 Standard Edition
Microsoft SQL Server 2000 64-bit Edition
Microsoft SQL Server 7.0 Standard Edition
Microsoft Excel 2002 Standard Edition
Microsoft Excel 97 Standard Edition
Microsoft SQL Server 2005 Developer Edition
Microsoft SQL Server 2005 Enterprise Edition
Microsoft SQL Server 2005 Standard Edition
Microsoft SQL Server 2005 Workgroup Edition
Palabras clave: kbhowtomaster kbjet kbmt KB321686 KbMtes
Traducción automática
IMPORTANTE: Este artículo ha sido traducido por un software de traducción automática de Microsoft
(http://support.microsoft.com/gp/mtdetails) en lugar de un traductor humano. Microsoft le ofrece artículos
traducidos por un traductor humano y artículos traducidos automáticamente para que tenga acceso en su propio
idioma a todos los artículos de nuestra base de conocimientos (Knowledge Base). Sin embargo, los artículos
traducidos automáticamente pueden contener errores en el vocabulario, la sintaxis o la gramática, como los que un
extranjero podría cometer al hablar el idioma. Microsoft no se hace responsable de cualquier imprecisión, error o
daño ocasionado por una mala traducción del contenido o como consecuencia de su utilización por nuestros clientes.
Microsoft suele actualizar el software de traducción frecuentemente.
Haga clic aquí para ver el artículo original (en inglés): 321686
Volver al principio | Propocionar comentarios
Propocionar comentarios
¿Fue útil esta información?
Sí
No
Un poco
¿Cuánto esfuerzo ha dedicado personalmente para usar este artículo?
Muy poco
Poco
Moderado
Mucho
Muchísimo
Típico de MS Excel Application Lifecycle
MS Excel proporciona muchas herramientas poderosas para diseñar, gestionar y analizar datos empresariales. Excel
también es una forma fácil y barata de prototipos de nuevas aplicaciones de negocio.Con demasiada frecuencia, sin
embargo, MS hojas de cálculo Excel que son útiles para un usuario el poder que construyó comienzan a ser
utilizados por más y más gente, crece la hoja de cálculo en las características y complejidad, y se copiado,
modificado y enviado por correo electrónico alrededor. Antes de que te des cuenta, el esfuerzo para apoyar la
aplicación MS Excel empieza a exceder a los beneficios de negocios!
Cuando llegue el momento en que su aplicación MS Excel está fuera de control, es el momento de migrar la
aplicación de base de datos de Excel en una aplicación de base de datos relacional. Esto eliminará todos los
problemas multi-usuario, proporciona funciones de negocios confiables y repetibles, y mejorar la calidad y la
consistencia de los datos subyacentes.
Convertir MS Excel a SQL Server
Convertir hojas de cálculo Excel base de datos a una base de datos MS SQL Server proporciona una mucho más
estructurada, repetible enfoque más a los procesos críticos de negocio. Hojas de cálculo Excel proporcionan una
flexibilidad infinita, pero a costa de los malos datos, y el coste de la gestión de una aplicación distribuida que está
destinado a sólo uno o unos pocos usuarios. Base de datos SQL Server es compatible con los datos bien
estructurados, y los datos repetibles y de alta calidad y procesos de flujo de trabajo, con número ilimitado de
usuarios finales simultáneos. La buena noticia es que la ACP puede migrar la hoja de cálculo Excel a MS SQL
Server, y hacer que su aplicación de Excel más estructurado, mientras que la preservación de la integridad funcional
de la original, original de hoja de cálculo MS Excel.
MS Excel SQL Server
Flujo de trabajo Ninguno (correo electrónico) Proceso de flujo definido
Actualizaciones
concurrentes No (usuario único) Sí (varios usuarios concurrentes)
Seguridad Una contraseña para todos Privilegios basados en funciones
Problemas de
versiones Alto (archivo de datos sello) Ninguno
Auditoría-capacidad de
Bajo (fecha y comparaciones de
correo electrónico) Alta (pista de auditoría completa)
Integridad de los datos Baja (algo limitado) Muy limitada
Los datos de capacidad Limitado Ilimitado
Estructuras de datos Piso: 2-dimensiones Relacional: 3 dimensiones
El uso de MS Excel con SQL Server
Una vez que una aplicación de Excel se ha convertido de una hoja de cálculo de la base de datos SQL Server, los
datos todavía se pueden ver, exportar, y se analizaron en MS Excel! Todos estos datos, ecuaciones, y la
manipulación de datos se convierte en parte de una aplicación estructurada, base de datos bien definido, que prevé
la mejora de la coherencia de entrada de datos, control de versiones mejorado como nuevas capacidades se
añaden, y por lo general más fáciles, más simples interfaces de usuario. Lo mejor de todo, los datos pueden ser
controladas y puestas a disposición a través de seguridad basada en roles de seguridad a través de Internet.
Lección 7: Seguimos aprendiendo
• Vamos a abrir de nuevo la hoja que tendremos debidamente guardada, para poder seguir haciendo
cambios en ella (ya sabes: Archivo-Abrir y encontrarla en el disco duro de nuestro PC), y una vez la tengamos en pantalla si habeis hecho todo correctamente la hoja deberá presentar este aspecto: -
-
- Lo primero que haremos será seguir adecentándola un poco para que al tiempo que vá tomando mejor aspecto vosotros veais cómo se hace y luego podais llevarlo a la práctica porque no olvides que es la única manera de aprender, Excel o cualquier otra aplicación, la práctica una y otra vez de lo que se vaya explicando.
- Vamos a hacer una división de las celdas de la hoja para que quede todo más presentable (en mi opinión), pues si tal como está picamos en el botón de Vista preliminar que debeis saber es este que está
arriba en la barra de herramientas:
- La hoja la veremos de esta manera:
- - (Para volver al área de trabajo, picamos en el botón Cerrar );así que lo que haremos será separar cada
celda, cada fila y cada columna con una línea, de manera que ya veremos como queda más presentable.-Para ello seleccionamos como siempre el rango al que le aplicaremos los cambios:
- Y acto seguido picaremos en la flechita negra del botón: para que nos muestre:
- Eligiendo como veis el apartado “Todos los bordes” (podeis probar con otros también como ejercicio, y a continuación volveis un paso atrás con Edición-Deshacer…. ), de tal manera que una vez picado, la hoja aprecerá así tras picar en cualquier celda vacía: -
Si ahora volvemos a picar en “ Vista preliminar ” y luego en “ Zoom ”, ya el aspecto es un poco más bonito como dijimos, podeis verlo: -
- Enfín, ahora podríamos ajustar ciertas filas y columnas y cambiarle el aspecto a nuestro gusto, yo por ejemplo os muestro como me parece a mí aceptable y dejo como ejercicio el que vosotros prepareis la vuestra de forma idéntica para que en futuros ejercicios todos tengamos la hoja similar, la muestro:
Ahora manos a la obra y hay que dejarla idéntica, y como pistas os diré que he utilizado los siguientes botones:
para seleccionar los colores de los números.
para el borde oscuro del total de la tabla.
para colorear fondos, y
para dar el atributo de Negrita en algunos lugares.
- Con lo apuntado, en poco tiempo deberías tener la hoja completada y con el aspecto que te he presentado arriba ¿todo correcto?, pues seguimos.
- Si eres observador, enseguida te habrás dado cuenta que cuando vamos a la vista preliminar , la columna del mes de Junio no aparece, y en cambio en la hoja donde trabajamos sí que la vemos pero…con una línea vertical punteada:
-
- Y lo que nos está diciendo esto es que el tamaño de la página nos llega justo hasta el mes de Junio, y que la columna de dicho mes, si fuésemos a imprimir la hoja por ejemplo, estaría en una página distinta (saldrían 2 páginas por la impresora). Para solucionar esto(pues resultaría desagradable que la hoja saliera partida) tenemos dos opciones: la primera sería decirle a Excel que nos cambie la página a Apaisado , lo que haríamos picando en Archivo-Configurar página…
- Picando ahora en el circulito de: y en botón Aceptar para que ya la hoja aparezca así, y el espacio sea suficiente (lo muestro en vista preliminar):
- Veis ya el mes de Junio incluído.(Volved a cambiarla a vertical para seguir).
- Y la segunda forma (suponiendo que no quereis que la página cambie a “Apaisada” sino que siga en “Vertical”) sería: seleccionar todas las columnas de los 6 meses: para ello colocais el puntera del ratón sobre la C de la primera de las columnas hasta que el puntero cambiase a:
En ese momento pulsais el botón izquierdo y sin soltarlo arrastrais a la derecha hasta que queden seleccionadas las columnas C hasta H y soltais:
- Acto seguido colocais el puntero del ratón justo en la línea que separa C de D y vereis que el puntero se
convierte en doble flecha:
así que pulsas botón izquierdo y sin soltar “estrechas” la celda C ,hasta un tamaño
adecuado que permita a Junio “entrar” en la página, y cuando esté correcto sueltas el botón, quedando así:
• Como vés, ahora incluso sobra espacio hasta la vertical de puntos.Y es todo por hoy amigos, así que proceded a Guardar la hoja tal como está y seguiremos.-Saludos
SolutionBase: Hacer Excel y
SQL Server hablar el uno al otro
Por Colaborador Invitado
31 de marzo 2004, 8:00 am PST
Muchas empresas hoy en día se utiliza SQL Server para manejar los datos sobre productos, servicios y
soporte de las llamadas-casi cualquier cosa que puedas imaginar. Por lo general, las empresas que tienen
SQL Server tiene aplicaciones de software muy grandes y costosos dedicados a introducir y recuperar
datos de las bases de datos. Si bien estas aplicaciones son útiles y necesarios para la obtención de datos
en el entorno de SQL Server en muchos casos, la información a veces puede ser problemático.
Para muchos usuarios, la generación de informes de SQL Server no es la primera cosa que pensamos que
es fácil de usar. Con un poco de trabajo, puede crear documentos que se presentan datos de SQL Server
de forma que los usuarios finales no se dan cuenta los datos que están jugando con la vida en una
enorme base de datos. En su lugar, usted puede presentar esos datos en un formato más familiar, como
una hoja de cálculo Excel. He aquí cómo se hace.
Nota del autor
El objetivo de esta obra será en dos programas de SQL Server y Excel. Este artículo le ayudará a mostrar
a sus usuarios cómo utilizar los dos programas a la perfección para ver los datos. Los informes y la
información que va a ser capaz de proporcionar los ayudará a hacer su trabajo más eficientemente y
reducir las llamadas al help desk o el DBA empresa.
A los efectos de este artículo, voy a utilizar SQL Server 2000 Evaluation Edition y Office XP. Usted
necesitará por lo menos la versión en tiempo de ejecución de Microsoft SQL Server y Microsoft Excel u
Office. Voy a estar utilizando la base de datos Neptuno, una base de datos de ejemplo que se incluye con
las versiones Standard y Enterprise de SQL Server. Esta base de datos también está disponible con
Microsoft Access.
Requisitos simples
La mayoría de los entornos de oficina utilizan productos de Microsoft Office, por lo que Excel fácil de
conseguir. Sin embargo, SQL Server puede ser un poco caro, y si su empresa no ha bombardeado a cabo
o no está dispuesto a pagar la tarifa de uso (derechos de licencia de Microsoft y otros gastos) para la
versión sobrealimentada de SQL Server, no preocuparse.Microsoft ha creado una edición de escritorio
que es libre de descarga . Se almacenan los datos tan bien como las versiones más grandes del producto,
pero no viene con todas las herramientas útiles para el diseño de bases de datos.
Su primer paso en la creación de informes en Excel es útil para sondear su comunidad de usuarios para
las cosas que usamos todos los días. Por ejemplo, usted puede tomar nota cuando los usuarios de un
determinado departamento vienen a hacer preguntas y obtener ayuda, y si están teniendo problemas con
un informe o conseguir algunos datos, a continuación, esta solución puede proporcionar a usted con una
solución rápida y fácil a un problema potencialmente en curso. En el siguiente escenario, voy a crear un
informe rápido para Phil, un empleado del departamento de marketing, que detalla que los clientes
compran Ale Sasquatch y las cantidades que han comprado.
Para elaborar el informe al cliente, hablar con los usuarios que podrían tener uso adicional de la
misma. Tal vez comienza como un informe para satisfacer una necesidad de Phil en el departamento de
marketing, pero en el largo plazo, un informe creado por un usuario probablemente se ajuste a las
necesidades de otros varios, con muy pocos cambios, si los hubiere.
La obtención de datos de forma nativa en SQL Server
Una vez que haya recogido alguna información, empezar a probar las ideas para extraer estos datos. A
través de mi propia experiencia, he encontrado que las mejores maneras de escribir un informe destinado
a Excel es crearlo en Microsoft Query Analyzer o utilizar la herramienta incluida en Office, Microsoft
Query, y poner a prueba su validez en lugar de confiar en el Asistente para consultas.
Obteniendo datos de SQL Server puede ser muy abrumadora si el informe se está generando se ve a
varias tablas y montones y montones de necesidades de datos. Si accede a varias tablas, a continuación,
el Asistente para consultas puede ser una mejor opción para crear el informe. Le permite incluir
comentarios en las instrucciones SQL para que pueda permitir que otra persona a recoger a donde lo
dejaste, o en algunos casos, seguir basándose en una consulta posterior.
El informe para mostrar que los clientes compraron Ale Sasquatch y la cantidad de este producto fue
comprado por cliente sólo debe contener una consulta y referencia cuatro tablas: Clientes, Pedidos,
Productos y Detalles de pedidos. Esta consulta de tres tablas puede parecer un poco complejo al
principio, especialmente si usted todavía no han creado su propia sentencia SQL, pero como usted verá,
en realidad no es tan malo.
Si nos fijamos en cada elemento individual y la pieza que con los otros elementos, que caerá en su lugar
rápidamente. Para recuperar la información del cliente, nombre de la empresa, y el nombre del contacto,
tendrás que seleccionar de la tabla Customers. Esta tabla también contiene la identificación del cliente,
lo que facilitará el acceso a los pedidos de ese cliente.
La tabla Orders, el cual contiene la identificación de la orden por cada pedido realizado y el ID de
cliente para el cliente que realizó el pedido, debe ser considerado como la puerta de acceso a la
información que desee seleccionar. Si bien no es necesario para mostrar alguno de los datos de la tabla
Pedidos, debe hacer una parada en boxes, no para obtener la identificación de la orden.Una vez hecho
esto a través de una simple declaración JOIN, puede seleccionar la cantidad solicitada para cada cliente,
así como la fecha de la orden de la tabla Detalles de pedidos con otro sencillo JOIN.
Una instrucción JOIN final que arroja en la tabla de Productos para el nombre del producto solicitado. El
código para esta instrucción SELECT se muestra a continuación con los comentarios:
- Consultas para seleccionar los clientes que ordenaron Sasquatch Ale, la cantidad que compra y la fecha
de la orden
- Se colocó
- Indicar a SQL Server de base de datos que debe utilizar para la consulta. Por defecto, la base de datos
principal se selecciona.
USE Northwind
- Crear una consulta de selección de las columnas necesarias en sus respectivas tablas
seleccione cust.companyname, cust.contactname, orddet.quantity, ord.orderdate, prod.productname
de los clientes cust inner join pedidos Ord en cust.customerid = interno ord.customerid unirse [Detalles
de pedidos] orddet de ord.orderid = orddet.orderid interiores se unen los productos de productos de
orddet.productid = prod.productid
donde prod.productname = 'Sasquatch Ale
El ejemplo de código anterior crea el resultado que se muestra en la Figura A.
La figura A
El conjunto de resultados de la consulta anterior, se muestra en formato de cuadrícula Analizador de
consultas, la muestra de 19 clientes que ordenaron Sasquatch Ale.
Las tablas se unen en una columna común. Por ejemplo, clientes y pedidos se unen, donde el cliente los
ID de ambas tablas son iguales con el comando:
las órdenes internas de combinación de ord = cust.customerid ord.customerid
Las tablas también tienen una sintaxis adicional después de su nombre. Esto se conoce como un alias. Se
permite hacer referencia a las tablas combinadas en formato abreviado para simplificar el código. He
utilizado el alias de cust para los clientes y los alias ord de los pedidos. Esto me permite utilizar la
sintaxis de cust.contactname y ord.orderdate cuando se hace referencia a la columna ContactName de la
tabla Clientes y la columna OrderDate en la tabla Pedidos. Si lo hace, hace que el tamaño de la consulta
un poco más pequeño. Al crear consultas de gran tamaño, un menor número de pulsaciones de teclas
puede ser muy útil.
Esta consulta no es la simple consulta a escribir usando solamente SQL transaccional. Se trata de unir
cuatro tablas para recuperar la información deseada. Además, en la declaración anterior después de la
cláusula Where, podría haber añadido ordenar por ord.orderdate para poner los resultados en orden por
la fecha en que se realizó el pedido, pero eso no es necesario hacer el trabajo comunicado.
¿Cómo hacerlo en Excel
Ahora voy a mirar en la misma consulta a través de los ojos de Excel. En Excel, vaya a datos | Importar
datos externos | Nueva consulta Seleccionar base de datos. Cuando se selecciona la Nueva consulta de
base de datos la opción de menú, se le presentará con la pantalla que se muestra en la Figura B, que le
pedirá que seleccione un DSN.
Figura B
La fuente de datos el nombre de ventana de selección con el nuevo origen de datos puso de relieve: Este es
el valor predeterminado.
¿Qué pasa si no hay un DSN de SQL Server creado? Simplemente haga clic en el botón OK en el lado
derecho de la ventana, con la selección por defecto resaltado.
DSN, DNS, ODBC: ¿Qué son todas estas siglas?
Cuando se conecta a SQL Server utilizando Excel, que está creando un Open Database Connectivity
(ODBC) a la base de datos. Estas conexiones se conocen como nombres de origen de datos (DSN), que a
veces se mal escritas como DNS, el sistema de nombres de dominio utilizados en Internet. Muchas veces
me he hecho de este error tipográfico, que pasa el corrector ortográfico de Word y puede ser pasado por
alto.
Haga clic en el botón OK en la parte derecha de la ventana. A continuación, verá el Asistente para crear
nuevo origen de datos de comenzar, como se muestra en la Figura C.
Figura C
La primera pantalla del asistente Crear nuevo origen de datos
Puede especificar un nombre personalizado para el DSN y, a continuación seleccionar el tipo de fuente
de datos que se está conectando. En este caso, haga clic en el cuadro desplegable, desplácese hasta el
último tipo de fuente de datos (SQL Server) y haga clic en el botón Conectar.
En el cuadro de sesión de SQL Server, tendrá que especificar el nombre del servidor de su equipo SQL
Server 2000, junto con las credenciales de acceso que permiten el DSN para conectarse a SQL Server
con éxito. Si el servidor de base de datos utiliza la autenticación de Windows, seleccione la casilla de
confianza caja de conexión y haga clic en Aceptar. Este método de conexión se muestra en la figura D.
Figura D
Introduzca las credenciales necesarias para la base de datos.
Si su SQL Server utiliza los inicios de sesión de SQL, o cuentas de usuario creadas en SQL Server, se
debe considerar la creación de una cuenta de sólo lectura de sesión de SQL para su uso por todas las
aplicaciones de informes. Si usted tiene una cuenta ya creada, introduzca el nombre de usuario y
contraseña para esa cuenta en esta página y haga clic en Aceptar.
Si desea asegurarse de que el origen de datos siempre se conecta a la base de datos a la derecha en el
cuadro de inicio de sesión se muestra en la figura D, puede hacer clic en el botón Opciones y seleccione
la base de datos predeterminada. En este caso, la base de datos sería Neptuno. A continuación, haga clic
en Aceptar y continúe con el resto del asistente. Aunque este paso no es necesario, se podría hacer el
proceso un poco más suave. La pantalla para especificar una base de datos se muestra en la Figura E.
Figura E
Elegir una base de datos predeterminada para este DSN
El asistente realizará una conexión a la base de datos usando las credenciales que ha especificado y le
preguntará si desea especificar una tabla para este DSN, como se muestra en la Figura C. Deje esta
casilla en blanco. Debido a que su consulta va a utilizar más de una mesa, no hay necesidad de una tabla
predeterminada. Guardar el nombre de usuario y contraseña como parte de la DSN no es una mala idea
si se ha creado un inicio de sesión de SQL Server para el uso de sólo lectura, de esta manera, no tendría
a las llamadas de campo sobre el nombre de usuario y una contraseña para el informe.
Una vez que tengas todo lo llena en la forma deseada, haga clic en Aceptar. Esto le devolverá a la página
Elija un cuadro de origen de datos, pero esta vez su DSN recién creado aparecerá en la lista. Haga clic en
el DSN para seleccionarlo y haga clic en Aceptar.
En la siguiente pantalla que aparece, verá el Asistente para consultas se muestra en la Figura F.Aquí,
usted puede elegir las columnas de las tablas de la base de datos por defecto (si se ha seleccionado una
previamente) mediante la ampliación de signo más de una tabla y poner de relieve la columna que desea
elegir. Haga clic en la flecha única en el centro de la pantalla para agregar la columna a la lista.
Figura F
Selección de columnas para su uso por el asistente
Después de seleccionar todas las columnas que desea utilizar, haga clic en Siguiente. A continuación se
le pedirá para filtrar el conjunto de resultados. Cuando se aplican filtros para el conjunto de resultados,
que son esencialmente la construcción de la cláusula WHERE de la consulta. Si desea ver sólo los
resultados en identificación de la orden es igual a 3, que completaría esta página como se muestra en
la Figura G.
Figura G
Elija el orden en que desea que los resultados ordenados.
Cuando se han filtrado los datos que desee, haga clic en Siguiente y decirle al Asistente para consultas
de la orden en el que desea ordenar los resultados. Esta pantalla muestra las columnas que ha
seleccionado en cada cuadro desplegable. Si se selecciona una columna, los resultados serán ordenados
por cada columna que haya seleccionado.
En la última pantalla del asistente, usted puede seguir para editar su consulta en Microsoft Query, lo que
le permite personalizar aún más los resultados que usted recibe, o puede crear una consulta OLAP o
devolver los resultados a Excel. Para esta parte del asistente, voy a añadir la consulta se muestra arriba a
la asistente y mostrar los resultados en Microsoft Query y luego en Excel (Figuras H e I).
Figura H
Los resultados aparecen en Microsoft Query
La figura I
Los resultados muestran en Microsoft Excel
Cuando en realidad lo devuelven resultados a Excel, ya sea desde el asistente o desde Microsoft Query,
se le pedirá que elija una celda para poner los datos in Si va a incluir el título del informe, es posible que
desee dejar un poco espacio extra y comienzan los datos alrededor de la fila 6. Si usted decide ver la
consulta y además editarlo en Microsoft Query, el icono en la barra de herramientas que muestra una
puerta con una flecha que entra es el botón para regresar los datos a Excel.
Una alternativa a veces más fácil
Cuando usted está utilizando una consulta compleja, como lo he hecho en este ejemplo, podría ser más
fácil para componer la consulta en el Analizador de consultas (ver imagen superior) o en Microsoft
Query en lugar de utilizar el asistente. Para ello, seleccione una columna cuando se le preguntó a hacerlo
por el asistente. A continuación, haga clic en Siguiente tres veces y seleccione Ver datos o modificar
consulta en Microsoft Query. Haga clic en Finalizar, y verá el registro que eligió en Microsoft
Query. Seleccione la tabla que se muestra gráficamente en la parte superior de la ventana de Microsoft
Query. A continuación, haga clic en Tabla | Eliminar tabla. Haga clic en el botón de la barra de
herramientas de SQL y escriba manualmente la consulta o pegar una consulta pretyped en este cuadro, y
haga clic en Aceptar.
4
Comentarios
Pon tu opinión
Únete a la conversación!
Siga a través de:
RSS
Envía esta página Sigue
Ver todos los comentarios
Selecciones del personal
Mejor Valoradas
Más recientes
Mis contactos
0votos
+-
Excel para bases de datos
brigzy 13 de junio
Excel to Database now supports MySQL as well as SQL Server and Access. Excel para bases de datos
ahora es compatible con MySQL, así como SQL Server y Access. The tool allows you to pre-validate
data... Read Whole Comment +
Ver en el hilo
0votos
+-
copiar los datos de hoja de Excel a SQL Server utilizando script T-SQL
ravi_maduri_mca @ ... 14 de noviembre 2007
I want to copy the Excel sheet data to Sqlserver table how can ido that please help me out not with
VB.net and C#.net devalopment,only with T-Sql Query. Quiero copiar los datos de la hoja de Excel
a... Read Whole Comment +
SQL Server y Microsoft Excel Integración
Última actualización 29 de enero 2010.
Si estás en busca de más arriba-información actualizada sobre este tema, por favor visite nuestro
SQL Server artículo , podcasts , y la tienda de páginas.
Como profesionales de la información, la mayoría de nosotros se concentran en el establecimiento
de un seguro, confiable, con buen desempeño servidor back-end para diferentes
aplicaciones. Rara vez te preocupes por las aplicaciones de front-end, a menos que causar
problemas con un clúster o servidor.
Es una cuestión diferente, sin embargo, cuando se trata de la integración. No importa cuál sea su
nivel de participación con las aplicaciones de front-end, es su deber de asistir con los esfuerzos
para acceder directamente a datos en una base de datos SQL Server. Nadie está más
familiarizado con los efectos que se producirán cuando los datos se importan, exportan, o
vinculado a una de sus bases de datos.
Entra en la oficina de Microsoft. Es la herramienta de la industria estándar de oficina, conocida por
la mayoría de los usuarios de negocio, y funciona en todo, desde Windows 95 y Windows XP hasta
el final a Crossover Office en Linux. En resumen, la Oficina está en todas partes. Y, en particular,
Microsoft Excel es invocado por la mayoría de cada empresa que lo ha instalado. De hecho,
algunos lo han llamado "el mundo el sistema de base de datos de uso más frecuente." Y eso no
puede estar lejos de la verdad.
Debido a su fácil disponibilidad, Excel es una elección natural para la manipulación de datos. Tiene
una increíble variedad de herramientas para las aplicaciones, de las estadísticas a la ingeniería, y
funciones de negocios, además. Excel se enseña en la mayoría de las escuelas de negocios y
universidades, como parte de un plan de estudios estándar.
Otra de las razones para integrar los datos de SQL Server con Microsoft Excel es que los libros,
que se componen de una o más hojas de cálculo, son portátiles. Hay espectadores y convertidores
para casi todos los principales sistemas operativos, y otros programas de oficina a menudo puede
leer su formato nativo.
Microsoft Excel también tiene gran presentación y herramientas gráficas. Sé de varias tiendas que
utilizan Excel exclusivamente para los informes. Microsoft incluso ha anunciado que las nuevas
versiones de Excel es la herramienta de elección para trabajar con, la visualización y presentación
de informes sobre los conjuntos de datos, incluso de gran tamaño.
Añadir a esto la capacidad de la derecha del programa dentro de Microsoft Excel utilizando Visual
Basic para Aplicaciones (VBA), y usted tiene un argumento muy poderoso para el uso de esta
herramienta para acceder y procesar datos de SQL Server.
Ahora que estás convencido de que Excel ☺ es un ajuste natural para la integración de datos,
¿cómo se consigue que los datos dentro y fuera de sus bases de datos? Hay varios métodos, cada
uno con su propia aplicación para tener en cuenta. Antes de comenzar, sin embargo, hay un par de
cosas a tener en cuenta.
En primer lugar, porque Excel es un medio basado en archivo plano, puede que tenga que cambiar
su forma de pensar sobre los datos almacenados en las bases de datos relacionales. Usted podría
tener que crear un poco más de los objetos de base de datos, como las vistas y procedimientos
almacenados, para dar formato a los datos de una forma más de-normalizada, pre-unió camino
para que el usuario de hoja de cálculo.
Los mismos conceptos son válidas para las importaciones y exportaciones. Al crear el diseño de
las operaciones, esta diferencia entre el archivo plano y la estructura relacional se darán cuenta en
gran medida en el diseño. Puede que tenga que crear un proceso para romper el diseño plano en
una estructura relacional en la importación o exportación.
El otro tema para hacer frente a la integración del servidor de SQL en Excel es la conversión de
datos. El ejemplo más clásico es la forma en que SQL Server interpreta los valores hexadecimales
que viene de Microsoft Excel. Las fechas también puede ser problemático, lo mejor es comprobar
los resultados después de importar o exportar datos entre los dos programas. El problema surge
porque SQL Server se encarga de un montón de tipos de datos, dependiendo de la versión y la
edición de SQL Server que está utilizando. Éstos son sólo algunos de ellos:
char, nchar, varchar, nvarchar
bigint, smallint int y tinyint
Datetime y smalldatetime
dinero y smallmoney
decimal y numeric
float y real
texto y ntext
binary, varbinary y la imagen
poco, uniqueidentifier y marca de tiempo
Sin embargo, los grupos de datos de Excel en sólo unos pocos tipos básicos, una vez más,
dependiendo de la versión que usted tiene. Algunos de estos incluyen texto, valores, fechas y
fórmulas. Mientras tanto en Excel y SQL Server hacen un trabajo bastante bueno para adivinar el
tipo de datos que está entrando, no se puede confiar en la transferencia entre ellos a ciegas.
SQL Server y Excel Métodos de Integración
A pesar de algunas limitaciones, Excel sigue siendo una herramienta de integración
maravilloso.Existen varios métodos de integración; divide principalmente en categorías que llamaré
"Copiar datos" y "Linked Data". Hay un montón de maneras de realizar este proceso, me centraré
en los más populares aquí.
La principal diferencia entre estas categorías es la forma en que tratan los datos después de la
interacción. En una "Copia de datos", los datos se copian en esencia de un lugar a otro, y luego
comienza a separarse de su fuente inmediatamente después de la transferencia. En el "Data
Conectados" método, los datos se almacenan en una sola fuente, así que los cambios se reflejan
inmediatamente en ambas interfaces.
Por lo tanto, su primera decisión a tomar es la categoría de usar, y luego elegir el método de esa
categoría que mejor se adapte a las necesidades. Después de eso, poner a prueba el proceso, con
datos de la muestra, y luego trabajar en la medición del rendimiento y escribir controladores de
errores.
Copia de datos
Puede utilizar esta categoría de métodos cuando sólo tiene que tener una transferencia de los
datos almacenados en SQL Server o Excel. En otras palabras, el criterio de los siguientes métodos
es que haya terminado con los datos en un programa, y lo necesita en otro.
Por ejemplo, los usuarios pueden necesitar los datos que se almacenan en una base de datos de
SQL Server para presentar en un informe de gestión. En otro ejemplo, los datos almacenados en
hojas de cálculo distintas que se necesita en SQL Server para completar un conjunto de
datos.También he visto situaciones más complejas, donde se originan los datos en SQL Server, se
transfiere a las hojas de cálculo para el procesamiento, y se vuelve a importar de nuevo en el flujo
de datos SQL para su uso por otra aplicación.
En todos estos casos, hay una transferencia discreta de la propiedad de los datos. La limitación es
que estos métodos no permiten modificaciones en los datos de base mismos, al menos al mismo
tiempo.
Ahora que usted entiende que el uso de datos de copia, voy a profundizar en los detalles sobre
cómo hacer que eso suceda.
Simple copiar y pegar
El primero de estos métodos es buena de edad, copiar y pegar. Puede utilizar este método para
copiar un pequeño conjunto de registros rápidamente. Entender que este método es muy práctico,
es sin duda un "alto contacto" proceso! Aun así, es a menudo suficiente para ad-hoc o soluciones
de una sola vez.
Copia de datos de SQL Server a Excel mediante un proceso manual es muy simple:
1. Abrir el Analizador de consultas (QA) o SQL Server Management Studio (SSMS) ventana de
consulta. Ajuste la salida a la "red" con el icono en la barra de iconos en la parte superior de
la herramienta. Este es el valor predeterminado la mayor parte del tiempo de todos modos.
2. Ejecutar cualquier consulta usted está interesado pulg
3. Si está utilizando el Analizador de consultas, haga clic en la pequeña caja en la parte superior
izquierda del panel de resultados para seleccionar los resultados de la consulta y pulse
CTRL-C. Usted puede hacer lo mismo en SQL Server Management Studio, y de hecho no se
puede seleccionar sólo unas pocas células o filas, o incluso mantenga presionada la tecla
CTRL y haga clic para seleccionar las células que no son contiguas.
4. Abrir Excel y pegar los datos en el que lo desee.
La razón por la que esto funciona es que al copiar los datos de la vista de cuadrícula en SQL
Server, que se almacena en el portapapeles como una cadena de valores separados por
tabuladores. Al pegar en Excel (o casi cualquier red de Microsoft Office), coloca a los valores de allí
como valores separados por tabulaciones.
La dirección inversa (Excel a SQL Server) no es tan simple. Usted puede utilizar el Administrador
corporativo de "vista de datos" o SQL Server Management Studio "Editar datos" opción por una
mesa de botón derecho del ratón, pero puede que no funcione. Tendrá que asegurarse de que las
restricciones de tabla permitir que los datos que está tratando de pegar, y que usted está muy
seguro de esos tipos de datos. En la mayoría de todas las circunstancias, yo no la recomiendo el
método de copiar y pegar para importar datos a SQL Server.
Uso del Asistente de importación / exportación
Mientras que el método de copiar y pegar es muy bueno para las transferencias de una sola vez
simples, que no es muy sostenible. Si lo que busca un carácter más permanente, el método de
control de transferencia de datos, considere el uso de los servicios de SQL Server de
transformación de datos (DTS) en la versión 2000 e inferior, y SQL Server Integration Services
(SSIS) en SQL Server 2005 o superior. Estas características tienen varias funciones de gran
alcance, a partir de la transferencia de datos a los mapas de datos en desarrollo, el manejo de
errores y notificaciones, y se transforma. Además, el proceso se puede guardar y utilizar una y otra,
incluso provocada por otro evento de SQL Server.
Tengo los artículos sobre el uso de DTS y SSIS aquí en InformIT y hay varios escritos por otras
personas también. Por supuesto que puede empezar a explorar estas herramientas de gran
alcance de sus respectivas interfaces, pero no hay realmente una manera más sencilla de llevar
Excel (o muchos otros tipos de insumos para el caso) los datos de entrada y salida de SQL Server.
1. Abra el Administrador de la Empresa (2000 y menores) o SQL Server Management Studio
(2005 o superior)
2. Haga clic en cualquier nombre de base de datos y seleccione "Todas las tareas" en el menú
que aparece.
3. Seleccione "Importar y exportar datos" de ese submenú.
4. Verás un cuadro de diálogo de selección de fuente, en el que puede seleccionar SQL Server
o Microsoft Excel, dependiendo de la forma en que desea transferir los datos
5. Siga el resto del asistente, que le llevará a través del proceso de mapeo de campo, se
transforma, y guardar las selecciones (denominado paquete) para su uso posterior
6. Ejecute el paquete en el último cuadro de diálogo.
(Una referencia al final de este artículo entra en más profundidad en este proceso.)
Uso de Excel para la copia de datos
Para mover los datos utilizando Excel, usted tiene algunas opciones. Bajo el Excel "Datos" del
menú, usted encontrará "datos externos", así como un "Asistente para consultas", submenú, ya
que ambos utilizan los controladores ODBC para acceder a datos en SQL Server. En versiones
posteriores de Excel, usted tiene muchas más opciones - Hay incluso un "Obtener datos de SQL
Server" opción correcta en la opción de datos de la cinta de opciones.
Figura 1
Cada opción tiene un "refresh" la capacidad, por lo que puede copiar los cambios de datos en el
lado de SQL Server, pero tenga en cuenta este proceso es de un solo sentido. Los datos han
cambiado en Excel no automáticamente fluir de nuevo a SQL Server.
Este método se utiliza mejor para las pequeñas, las consultas ad hoc en la base de datos, y
normalmente debe ser utilizado a través de puntos de vista sobre las tablas de bases de datos, no
a través del acceso directo a las tablas base.
Mientras estamos en ese tema, es importante recordar que cualquier acceso a una base de datos
de SQL Server se ha de bloqueo y el bloqueo de las consecuencias. Permitir que no planificada, el
acceso ad hoc en un sistema de producción OLTP es casi siempre una mala idea. Es un proceso
mejor para crear especiales de sólo lectura que pueden acceder a las cuentas de algunos puntos
de vista sobre un sistema de información solamente. El uso de un sistema de información permite
el diseño de índice y un control de acceso para las consultas no planeadas.
Una vez que haya examinado las implicaciones de rendimiento, aquí está el resumen de uno de
esos procesos de importación de Excel - esto se debe trabajar con las versiones anteriores:
1. Abrir Excel
2. Seleccione "Datos" de la barra de menú, a continuación, "datos externos"
3. Seleccione "Importar datos ..."
4. Seleccione la opción "Fuente Nueva ..." botón
5. Seleccione "Microsoft SQL Server" como fuente, a continuación, "Siguiente"
6. Introduzca el nombre del servidor, a continuación, seleccione el método de autenticación -
llenar ese arreglo a lo establecido
7. Seleccione la base de datos que desea, luego de la tabla o vista y, a continuación, seleccione
"Siguiente"
8. Nombre de la conexión y luego seleccione "Finalizar"
9. Estás ahora habéis vuelto al "origen de datos" zona de
10. Haga clic en "Abrir", y luego seleccionar el área de la hoja de cálculo en los datos debe
comenzar
11. Haga clic en "Aceptar"
Como ya he mencionado hace un momento, un par de otros métodos de Excel pueden traer los
datos desde SQL Server. Mientras que en el paso 2, también puede seleccionar la opción
"Consulta de Microsoft", que usa un asistente similar a los pasos anteriores. Microsoft Query tiene
una interfaz más potente, donde se pueden hacer selecciones de todo el camino hasta el nivel de
columna. También puede editar la consulta en una interfaz similar a la que usted encontrará en el
área de acceso a las tablas en el Enterprise Manager o Microsoft Access. La consulta se pueden
guardar para su uso posterior.
Uso de VBA para llegar a los datos de SQL Server
Si lo que busca es un proceso menos manual, el último método es un poco más complejo, pero
incluso más potente. Microsoft Excel, al igual que todas las nuevas versiones de productos
Microsoft Office, tiene una interfaz de programación completa en la forma de Visual Basic para
Aplicaciones (VBA). Si tienes algo de experiencia en programación en absoluto, puede escribir
código contra una base de datos.
Una vez más, todas las advertencias anteriores sobre bloqueo de aplicar. Advertencias adicionales
están garantizados aquí, ya que con la programación puede afectar a los datos en la base de
datos, así como la lectura de la misma.
Hay algunos lugares que usted puede utilizar VBA en Excel, como las funciones y macros
personalizados, pero vamos a seguir con las macros de este ejemplo. El proceso básico es que se
crea una macro, editar, y luego ejecutarlo.
Puede editar la macro dentro de un editor, en el que se escribe el código para conectarse, el
acceso, y cerrar la conexión a una base de datos. Este es el proceso para crear su propia macro
para conectarse a SQL Server mediante programación:
1. Abrir Excel
2. Haga clic en "Herramientas", luego "Macro" y luego "Macros ..."
3. Nombre de la macro y, a continuación, haga clic en Crear
4. En la ventana del editor, escriba la información siguiente, sustituyendo los nombres propios
para el servidor y las tablas que desea en medio de la cadena "xxxx Sub" y "End Sub" tags:
'Declarar el objeto Dim qt QueryTable QueryTable Como' Juego de la
SqlString de sentencias SQL = "select au_fname, au_lname de autores"
'Configurar la cadena de conexión, haga referencia a una conexión ODBC
"Hay varias maneras de hacer esto" Deje el nombre y la contraseña en
blanco para autenticación de Windows NT connString = _ "ODBC; DSN = pubs;
UID =; PWD =; Database = pubs" 'Ahora implementar la conexión, ejecute la
consulta, y añade "los resultados de la hoja de cálculo a partir de la
fila A1 Con ActiveSheet.QueryTables . Añadir (Conexión: = connString,
Destino: = Range ("A1"), SQL: = SqlString) WithSave Actualizar final y
cierre la macro, y ejecutarlo desde el mismo menú que accedió en el paso
2..
NOTA
Es probable que existan por lo menos diez formas programáticas de hacer esto exactamente lo
mismo - y todos ellos son correctos! Si usted está realmente interesado en la programación con
VBA, usted encontrará varios buenos libros aquí en InformIT y también en Safari.
Linked Data
Ahora voy a alejarse de la "Copia de datos" a la categoría de "Datos Conectados" categoría de
métodos. Voy a describir los dos métodos principales para vincular los datos, tanto de los cuales
enlazan datos de Excel en una consulta de SQL Server.
Hay maneras de utilizar Excel para administrar los datos directamente en SQL Server, pero
implican la programación un poco más, y he encontrado que son un poco torpe - especialmente
para el uso diario. Excel sencillamente no se ocupa de las cerraduras y los problemas de
conectividad, así como lo necesario para las soluciones de producción a gran escala.
También puede codificar una solución que tenga acceso a los datos almacenados en una hoja de
cálculo Excel y actualizar una tabla de SQL Server como un resultado, pero esto es realmente
orientado a eventos y no una solución vinculada. (Una vez más, hay una gran cantidad de ayuda
para la programación en ese sentido aquí en la InformIT.)
Así que volviendo a los métodos que he encontrado fácil de implementar, hay dos opciones que
puede utilizar para consultar los datos en una hoja de cálculo Excel en SQL Server.
Acceso temporal a datos de Excel desde SQL Server
El primer método es bastante simple, y utiliza la función OPENROWSET. Ésta es la sintaxis para
una consulta rápida de una hoja de cálculo Excel con una pestaña por defecto en ella, se llama c: \
temp \ test.xls:
SELECT *
FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0; base de datos = c: \ temp \ test.xls; HDR = Yes',
"SELECT * FROM [Hoja1 $] ')
Acceso permanente a datos de Excel desde SQL Server
Si usted piensa que es posible que desee consultar la hoja de cálculo en varias líneas de código,
es posible que desee crear una conexión reutilizable a él - en otras palabras, lo tratan como un
servidor. Puede indicar a SQL Server que desea para el tratamiento de otro origen de datos (como
Excel, archivos de texto o bases de datos Oracle) como un servidor usando un "servidor
vinculado." Es bastante simple para crear uno. Así es como para hacer eso por una hoja de cálculo
llamada "test.xls" en el directorio c: \ temp:
1. El Analizador de consultas abierto o un servidor SQL Server Management Studio ventana de
consulta
2. Ejecute el siguiente código:
- Aquí hemos creado el servidor vinculado mediante el proveedor de JET
EXEC sp_addlinkedserver N'ExcelLink ',
@ Srvproduct = N'',
@ = Proveedor de N'Microsoft. Jet.OLEDB.4.0 ',
@ = Datasrc N'C: \ temp \ test.xls ',
@ Provstr N'Excel = 8,0; '
GO
De esta manera, se crea un servidor vinculado, y dar a ese respecto un nombre. Tenga en cuenta
también el nombre de la hoja de cálculo, que también puede estar en un recurso compartido de
red. Sólo tienes que hacer esto de una vez por cada hoja de cálculo, si no vamos a acceder a la
hoja de cálculo de nuevo, es una buena idea dejar caer el servidor vinculado después de haberlo
utilizado.
Ahora que tiene un servidor vinculado, puede acceder a los datos. El proceso para este método es
como sigue:
En una herramienta de consulta como el Analizador de consultas o en la ventana de consultas de
SSMS, escriba lo siguiente:
- Configuración de la conexión de un usuario de la hoja de cálculo
EXEC sp_addlinkedsrvlogin 'ExcelLink', 'false'
GO
- Obtener los datos de hoja de cálculo - "Sheet1" es el nombre de la ficha
SELECT * FROM OPENQUERY (ExcelLink, 'select * from [Hoja1 $]')
GO
En esta sección, hemos utilizado la función OPENQUERY, que pasa a la consulta con el
proveedor. Usted encontrará el lenguaje de consulta es bastante limitado, a veces, con Excel. Si
las consultas no son lo bastante selectivo, creado otra hoja de cálculo en el libro con los datos que
desea, y la consulta que uno.
Como puede ver, usted tiene varias opciones abiertas para integrar los datos entre Microsoft Excel
y SQL Server. Tengo bastantes recursos por debajo de que podrían ser útiles.
Artículos y capítulos de la muestra InformIT
William E. Pearson, III tiene un buen artículo sobre la integración de Microsoft SQL Server 2000 y
Microsoft Office OLAP, integración de Microsoft SQL Server 2000 y OLAP de Microsoft Office,
Parte 1: Creación de un informe de tabla dinámica de Excel con un cubo OLAP . Usted puede
explorar las capacidades de análisis del informe de tabla dinámica de Excel para presentar los
datos de un OLAP (Online Analytical Processing) cubo. En este tutorial, aprenderá a crear un
informe de tabla dinámica a partir de cero, y explorar los detalles de su cubo centrado en la
funcionalidad.
Libros y libros electrónicos
¿Necesitas algo más actual? Echa un vistazo a el libro de Roger Jenning, Edición Especial de Uso
de Microsoft Office Access 2007 , lo que incluye el Excel 2007 y SQL Server.
Recursos en la Red
Estos enlaces son de Microsoft. Hay otros varios por ahí, pero éstos se ocupan de algunos de los
temas que me hizo subir en el artículo.
Aquí está un artículo que detalla los males de tipo de datos con las importaciones DTS y Excel .
Este artículo de Microsoft se detalla el proceso de utilización de DTS con Excel .
Microsoft tiene una discusión más amplia de ODBC para Excel cuestiones de tipo de datos aquí .
Este artículo de Microsoft detalles de la programación con ADO en contra de archivos de Excel -
que muestra cómo abrir una como una base de datos.
Convertir Excel a SQL Server
Aplicaciones prácticas de ordenador (PCA) proporciona a los programadores expertos para
convertir sus aplicaciones de negocio desde Excel a SQL Server para crear aplicaciones repetibles
y confiables que conservar el propósito de negocio y operacional de la hoja de cálculo
personalizado.
Excel ofrece muchas herramientas poderosas para diseñar, gestionar y analizar datos
empresariales. Sin embargo, MS hojas de cálculo Excel que son útiles para el poder de un usuario
que lo construyó, se salen de control cuando se utiliza por más y más gente. La hoja de cálculo
crece en las características y la complejidad, y se copiado, modificado y enviado por correo
electrónico alrededor. Es difícil saber cuál es la versión correcta de una hoja de cálculo, si los
cambios se han hecho inadecuados, o cuando la seguridad de los datos se ha visto comprometida.
Cuando llegue el momento en que su multi-usuario de la aplicación MS Excel está fuera de control,
es el momento de migrar la aplicación de base de datos de Excel en una aplicación de base de
datos relacional basado en SQL Server. Esto eliminará todos los problemas multi-usuario,
proporciona funciones de negocios confiables y repetibles, y mejorar la calidad y la consistencia de
los datos subyacentes.
Bases de datos SQL para bases de datos permiten permiten para ser utilizados a través de
Internet.Estas bases de datos distribuidas tienen un papel a nivel de seguridad que limita lo que los
usuarios pueden hacer con los datos, a menos que los permisos adecuados se les concede.
Convertir hojas de cálculo Excel base de datos a una base de datos MS SQL Server proporciona
una mucho más estructurada, repetible enfoque más a los procesos críticos de negocio. Base de
datos SQL Server es compatible con los datos bien estructurados, los datos distribuidos, la
seguridad de Internet y datos repetibles y de alta calidad y procesos de flujo de trabajo, con un
número ilimitado de usuarios finales simultáneos.
PCA tiene una gran experiencia de conversión y migración de hojas de cálculo Excel a MS SQL
Server.Haremos que su aplicación Excel más estructurado, mientras que la preservación de la
integridad funcional de la original, original de hoja de cálculo MS Excel.
MS Excel SQL Server
Flujo de trabajo Ninguno (correo electrónico) Proceso de flujo definido
Actualizaciones
concurrentes No (usuario único)
Sí (varios usuarios
concurrentes)
Seguridad Una contraseña para todos Privilegios basados en
funciones
Problemas de versiones Alto (archivo de datos sello) Ninguno
Auditoría-capacidad de Bajo (fecha y comparaciones de
correo electrónico)
Alta (pista de auditoría
completa)
Integridad de los datos Baja (algo limitado) Muy limitada
Los datos de capacidad Limitado Ilimitado
Estructuras de datos Piso: 2-dimensiones Relacional: 3
dimensiones
Acceso a Internet con
seguridad No Sí
Una vez que una aplicación de Excel se ha convertido en una base de datos SQL Server, los datos
todavía se pueden ver, exportar, y se analizaron en MS Excel. Todos estos datos, ecuaciones, y la
manipulación de datos se convierte en parte de una aplicación estructurada, base de datos bien
definido.La nueva aplicación permite mejorar la coherencia de entrada de datos un mejor control de
versiones, y un fácil interfaz de usuario más sencilla. Lo mejor de todo, los datos pueden ser
controladas y puestas a disposición a través de seguridad basada en roles de seguridad a través
de Internet.
Convertir Excel a SQL Server
Aplicaciones prácticas de ordenador (PCA) proporciona a los programadores expertos para
convertir sus aplicaciones de negocio desde Excel a SQL Server para crear aplicaciones repetibles
y confiables que conservar el propósito de negocio y operacional de la hoja de cálculo
personalizado.
Excel ofrece muchas herramientas poderosas para diseñar, gestionar y analizar datos
empresariales. Sin embargo, MS hojas de cálculo Excel que son útiles para el poder de un usuario
que lo construyó, se salen de control cuando se utiliza por más y más gente. La hoja de cálculo
crece en las características y la complejidad, y se copiado, modificado y enviado por correo
electrónico alrededor. Es difícil saber cuál es la versión correcta de una hoja de cálculo, si los
cambios se han hecho inadecuados, o cuando la seguridad de los datos se ha visto comprometida.
Cuando llegue el momento en que su multi-usuario de la aplicación MS Excel está fuera de control,
es el momento de migrar la aplicación de base de datos de Excel en una aplicación de base de
datos relacional basado en SQL Server. Esto eliminará todos los problemas multi-usuario,
proporciona funciones de negocios confiables y repetibles, y mejorar la calidad y la consistencia de
los datos subyacentes.
Bases de datos SQL para bases de datos permiten permiten para ser utilizados a través de
Internet.Estas bases de datos distribuidas tienen un papel a nivel de seguridad que limita lo que los
usuarios pueden hacer con los datos, a menos que los permisos adecuados se les concede.
Convertir hojas de cálculo Excel base de datos a una base de datos MS SQL Server proporciona
una mucho más estructurada, repetible enfoque más a los procesos críticos de negocio. Base de
datos SQL Server es compatible con los datos bien estructurados, los datos distribuidos, la
seguridad de Internet y datos repetibles y de alta calidad y procesos de flujo de trabajo, con un
número ilimitado de usuarios finales simultáneos.
PCA tiene una gran experiencia de conversión y migración de hojas de cálculo Excel a MS SQL
Server.Haremos que su aplicación Excel más estructurado, mientras que la preservación de la
integridad funcional de la original, original de hoja de cálculo MS Excel.
MS Excel SQL Server
Flujo de trabajo Ninguno (correo electrónico) Proceso de flujo definido
Actualizaciones
concurrentes No (usuario único)
Sí (varios usuarios
concurrentes)
Seguridad Una contraseña para todos Privilegios basados en
funciones
Problemas de versiones Alto (archivo de datos sello) Ninguno
Auditoría-capacidad de Bajo (fecha y comparaciones de
correo electrónico)
Alta (pista de auditoría
completa)
Integridad de los datos Baja (algo limitado) Muy limitada
Los datos de capacidad Limitado Ilimitado
Estructuras de datos Piso: 2-dimensiones Relacional: 3
dimensiones
Acceso a Internet con
seguridad No Sí
Una vez que una aplicación de Excel se ha convertido en una base de datos SQL Server, los datos
todavía se pueden ver, exportar, y se analizaron en MS Excel. Todos estos datos, ecuaciones, y la
manipulación de datos se convierte en parte de una aplicación estructurada, base de datos bien
definido.La nueva aplicación permite mejorar la coherencia de entrada de datos un mejor control de
versiones, y un fácil interfaz de usuario más sencilla. Lo mejor de todo, los datos pueden ser
controladas y puestas a disposición a través de seguridad basada en roles de seguridad a través
de Internet.
La necesidad de producir informes de Excel desde SQL Server es muy
común. En este caso, Robyn Page y Phil Factor presentes las técnicas
prácticas para crear y manipular hojas de cálculo Excel a partir de SQL
Server, mediante servidores vinculados y T-SQL. La pièce de résistance
es un procedimiento almacenado que utiliza automatización OLE para
permitir un control total sobre el formato del informe de Excel, y la
posibilidad de incluir las sumas, cocinas, tablas dinámicas, etc. * /
/ *
El CRUD de Excel
=====================
Phil y yo se han unido en esta mesa de trabajo, que se muestra cómo crear, leer,
actualizar y borrar información en Excel usando T-SQL, a partir de SQL Server. Como
siempre, la mesa de trabajo está estructurado de manera que se puede pegar en
Analizador de consultas y SSMS, y los ejemplos individuales ejecutado - usted puede
descargar el sql de la "descarga de código de" enlace de arriba, cargarlo y empezar a
experimentar.!
Contenido
========
Crear hojas de cálculo Excel a través de ADODB
Manipulación de datos de Excel a través de un servidor vinculado
Sincronización de la hoja de cálculo con tablas de SQL Server
Manipulación de datos de Excel utilizando OPENDATASOURCE y funciones OPENROWSET
Crear hojas de cálculo Excel con sp_makewebtask
Automatización OLE
Comenzamos mostrando cómo crear una hoja de cálculo Excel a partir de SQL Server en
TSQL (Transact-SQL), crear una hoja de cálculo, se adhieren a él como un servidor
vinculado, escribir en él, leerlo, actualizarlo como si fuera una simple SQL tabla de
servidor de base de datos, y luego sincronizar los datos de la hoja de trabajo con SQL
Server. También ilustran el uso de OPENQUERY, OPENDATASOURCE y OPENROWSET.
Para crear la hoja de cálculo Excel, se muestra cómo conectar a una fuente de ADODB de
SQL Server y ejecutar SQL en esa fuente. A continuación, te mostraré una alternativa
manera de 'rápido truco' (usando sp_makewebtask) para crear y rellenar una hoja de
excel desde Transact SQL.
Si necesita más control sobre la hoja de cálculo Excel que se está creando, entonces
te mostrará cómo hacerlo a través de automatización OLE. Esto le permitirá hacer
cualquier cosa que usted puede hacer a través de las pulsaciones de teclado, y le
permiten generar completos informes en formato Excel con tablas dinámicas y gráficos.
Usando esta técnica, debe ser capaz de rellenar los datos, o colocar datos en las
llamadas particulares o rangos. Usted puede incluso hacer "sustituciones de macro '
Una palabra de advertencia antes de empezar. Si usted tiene la seguridad de su par en
par, no es como tú, que sería capaz de escribir datos en una hoja de cálculo. Un
intruso podría ser capaz de hacer con esa lista de contraseñas o números de tarjetas
de crédito. En un sistema de producción, este tipo de operación debe estar debidamente
delimitados. Tenemos la tendencia a crear una cola de trabajo y tener un usuario
especial, con los permisos adecuados, en el Programador de tareas, a hacer todo lo que
implica la automatización OLE o xp_cmdshell. Precauciones de seguridad pueden llegar a
ser muy compleja, pero están fuera del alcance del artículo.
Algunos de lo que ilustran puede hacerse utilizando DTS o SSIS. Desafortunadamente,
estos están fuera del alcance de este artículo. De hecho, la transferencia de datos
entre Excel y SQL Server se puede hacer en una sorprendente variedad de formas, y que
sería divertido un día para
tratar de enumerarlos todos
En primer lugar tenemos algunos datos de prueba simples
* /
CREATE TABLE # # CambridgePubs
(Pubname VARCHAR (40),
Dirección VARCHAR (80),
CP VARCHAR (8))
INSERT INTO # # CambridgePubs (Pubname, dirección, código postal)
SELECT 'Las abejas en la pared ", los '36 North Road,
',' Whittlesford, Cambridge CB2 4NZ '
INSERT INTO # # CambridgePubs (Pubname, dirección, código postal)
'Cabeza Blackamoors' SELECT, '205 Victoria Road,
Cambridge "," CB4 3LF '
INSERT INTO # # CambridgePubs (Pubname, dirección, código postal)
SELECT 'Blue Lion', '2 camino Horningsea,
Fen Ditton, Cambridge ',' CB5 8SZ '
INSERT INTO # # CambridgePubs (Pubname, dirección, código postal)
SELECT 'Cambridge Blue', '85-87 Gwydir la calle,
Cambridge ',' CB1 2LG '
INSERT INTO # # CambridgePubs (Pubname, dirección, código postal)
'Campeón del Támesis' SELECT, 68 King Street,
Cambridge ',' CB1 1LN '
INSERT INTO # # CambridgePubs (Pubname, dirección, código postal)
"Cross Keys 'SELECT, 77 calle del armiño,
',' Caxton, Cambridge CB3 8PQ '
INSERT INTO # # CambridgePubs (Pubname, dirección, código postal)
SELECT 'Crown Inn', '11 High Street,
',' Linton, Cambridge CB1 6HS '
INSERT INTO # # CambridgePubs (Pubname, dirección, código postal)
SELECT 'Devonshire Arms', '1 Devonshire Road,
Cambridge ',' CB1 2BH '
INSERT INTO # # CambridgePubs (Pubname, dirección, código postal)
SELECT 'duque de Argyle', '90 Argyle Street,
Cambridge ',' CB1 3LS '
INSERT INTO # # CambridgePubs (Pubname, dirección, código postal)
SELECT 'Duke Of Wellington', '49 limosna Hill,
',' Bourn, Cambridge CB3 7SH '
INSERT INTO # # CambridgePubs (Pubname, dirección, código postal)
SELECT 'Eagle Public House', 'Calle Benet,
Cambridge ',' CB2 3QN '
/ * Y así sucesivamente. (El archivo de importación completa se encuentra en el ZIP,
como es el Excel
archivo!)
Crear la tabla y luego ejecutar el contenido de CambridgePubs.SQL
Crear hojas de cálculo Excel a través de ADODB
-----------------------------------------
En primer lugar, tenemos que crear la hoja de cálculo con los títulos correctos
(pubname, dirección, código postal)
Hay dos formas posibles que se podría hacer esto. La manera más obvia es mediante la
sentencia CREATE para crear la hoja de trabajo y definir las columnas, pero parece que
no hay manera de hacerlo vinculando el archivo de Excel, a menos que el archivo de
Excel que ya existe. Necesitamos un procedimiento almacenado de utilidad para llegar a
ADODB con el fin de crear bases de datos y ejecutar SQL DDL y en contra de ella. * /
CREAR PROCEDIMIENTO spExecute_ADODB_SQL
@ DDL VARCHAR (2000),
@ DataSource VARCHAR (100),
@ Hoja VARCHAR (100) = NULL,
@ ConnectionString VARCHAR (255)
= "Provider = Microsoft.Jet.OLEDB.4.0;
Data Source =% DataSource;
Extended Properties = Excel 8.0 '
AS
DECLARE
@ ObjExcel INT,
@ Hr INT,
@ Comandos VARCHAR (255),
@ StrErrorMessage VARCHAR (255),
@ ObjErrorObject INT,
@ ObjConnection INT,
@ Balde INT
SELECT @ ConnectionString
= REEMPLAZAR (@ ConnectionString, '% DataSource', @ DataSource)
SI Hoja @ IS NOT NULL
SELECT @ DDL = REPLACE (@ DDL, 'hoja'%, Hoja @)
SELECT @ strErrorMessage = 'Hacer la conexión ADODB',
@ ObjErrorObject = NULL
EXEC @ hr = sp_OACreate 'ADODB.Connection', @ objConnection OUT
SI @ hr = 0
SELECT @ strErrorMessage = 'Asignación de ConnectionString propiedad "'
+ @ + ConnectionString '"',
@ = @ ObjErrorObject objConnection
SI @ hr = 0 EXEC @ hr = sp_OASetProperty @ ObjConnection,
'ConnectionString', @ ConnectionString
SI @ hr = 0 SELECT @ strErrorMessage
= "Conexión de apertura a XLS, para el archivo Crear o Append '
SI @ hr = 0 EXEC @ hr = sp_OAMethod ObjConnection @, 'Open'
SI @ hr = 0 SELECT @ strErrorMessage
'Ejecutar DDL = "' + @ DDL + '"'
SI @ hr = 0 EXEC @ hr = sp_OAMethod @ ObjConnection, "Ejecutar",
@ Cubo a cabo, @ DDL
IF @ hr <> 0
COMENZAR
DECLARE
En origen VARCHAR (255),
@ Descripción VARCHAR (255),
@ Helpfile VARCHAR (255),
@ HelpID INT
EXECUTE sp_OAGetErrorInfo @ ObjErrorObject, la producción en origen,
@ Descripción del producto, la producción Helpfile @, @ salida de HelpID
SELECT @ strErrorMessage = 'Error al mismo tiempo'
+ COALESCE (@ strErrorMessage, 'hacer algo') + ','
+ COALESCE (@ Descripción,'')
RAISERROR (@ strErrorMessage, 16, 1)
FIN
EXEC @ hr = sp_OADestroy @ ObjConnection
GO
--------------------------------------
/ * Ahora que lo tenemos, es fácil * /
spExecute_ADODB_SQL @ DDL = 'Crear CambridgePubs mesa
(Texto pubname, texto Dirección, Código postal Texto) ',
@ DataSource = "C: \ CambridgePubs.xls '
- El archivo de Excel se han creado en el servidor de base de datos de la
- Base de datos que actualmente tiene una conexión a
- Ahora puede insertar datos en la hoja de cálculo, si quisiéramos
spExecute_ADODB_SQL @ DDL = 'INSERT INTO CambridgePubs
(Pubname, dirección, código postal)
los valores ('' El pájaro en la mano'',
'' 23, Marshall Road, Cambridge CB4 2DQ'',
'' CB4 2DQ'') ",
@ DataSource = "C: \ CambridgePubs.xls '
- Se puede caer otra vez!
spExecute_ADODB_SQL @ DDL = 'drop table CambridgePubs',
@ DataSource = 'C: \ CambridgePubs.xls'
O manipulación de archivos de Excel los datos a través de un servidor
vinculado
----------------------------------------------
Ahora podemos vincular a la creación de archivos de Excel de la siguiente manera * /
EXEC sp_addlinkedserver 'CambridgePubDatabase',
@ Srvproduct ='',
@ = Proveedor de 'Microsoft.Jet.OLEDB.4.0',
@ Datasrc = 'C: \ CambridgePubs.xls',
@ Provstr = 'Excel 8.0; "
GO
EXEC sp_addlinkedsrvlogin 'CambridgePubDatabase', 'false'
GO
- A abandonar el enlace, lo hacemos!
- EXEC sp_dropserver 'CambridgePubDatabase', 'droplogins'
- Obtener los datos de hoja de cálculo a través de AbrirConsulta
SELECT * FROM OPENQUERY
(CambridgePubDatabase, 'select * from [CambridgePubs]')
GO
- O, más sencillamente, hacer esto
SELECT * FROM CambridgePubDatabase ... CambridgePubs
- Por lo que ahora podemos insertar nuestros datos en la hoja de cálculo Excel
INSERT INTO ... CambridgePubDatabase CambridgePubs
(Pubname, dirección, código postal)
SELECT pubname, dirección, código postal desde # # CambridgePubs
/ * Sincronización de la hoja de cálculo con tablas de SQL Server
-------------------------------------------------- ----
Como estamos manipular directamente los datos de Excel en la hoja de trabajo como si
fuera una tabla que podemos hacer combinaciones.
¿Qué pasa con la sincronización de la tabla después de editar la hoja de cálculo de
Excel?
Para probar esto, usted tendrá que suprimir, alterar e insertar unas cuantas filas de
la hoja de cálculo Excel, recordando a cerrar después de que lo haya hecho
* /
- En primer lugar, vamos a eliminar todas las filas de # # CambridgePubs
- Que no existen en la hoja de cálculo Excel
DELETE FROM # # CambridgePubs
Desde # # CambridgePubs c
LEFT OUTER JOIN CambridgePubDatabase ... CambridgePubs ex
En la letra c. La dirección como el ex. La dirección
Y c. Pubname como el ex. Pubname
Y c. Código postal como el ex. Código postal
DONDE ex. Pubname IS NULL
- A continuación insertamos a # CambridgePubs las filas de la hoja de cálculo
- Que no existen en # CambridgePubs
INSERT INTO # # CambridgePubs (Pubname, dirección, código postal)
SELECT ex. Pubname, ej. Dirección, ej. CP
DE CambridgePubDatabase ... CambridgePubs ex
LEFT OUTER JOIN # # CambridgePubs c
En la letra c. La dirección como el ex. La dirección
Y c. Pubname como el ex. Pubname
Y c. Código postal como el ex. Código postal
Donde c. Pubname IS NULL
- Todo hecho (syncronisation contrario sería similar)
/ * Manipulación de datos de Excel utilizando OPENDATASOURCE y OPENROWSET
-------------------------------------------------- -----------
Si usted no quiere hacer la conexión, también puede leer los datos como
este * /
SELECT *
DE OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0',
"Data Source =" C: \ CambridgePubs.xls ";
Extended Properties = Excel 8.0 ') ... CambridgePubs
- Y escribir en él
ACTUALIZACIÓN OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0',
"Data Source =" C: \ CambridgePubs.xls ";
Extended Properties = Excel 8.0 ') ... CambridgePubs
Dirección SET = 'San Kilda Road, Cambridge '
DONDE pubname = "Jenny Wren"
INSERT INTO OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0',
"Data Source =" C: \ CambridgePubs.xls ";
Extended Properties = Excel 8.0 ') ... CambridgePubs
(Pubname, dirección, código postal)
SELECT 'El St George', '65 Cavendish Road ',' CB2 4RT '
- Usted puede leer y escribir Hoja ToExcel utilizando OPENROWSET,
- Si el estado de ánimo le lleva
SELECT * FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0; DATABASE = C: \ CambridgePubs.xls', 'Select * from CambridgePubs')
ACTUALIZACIÓN OPENROWSET ('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0; DATABASE = c: \ CambridgePubs.xls',
"Select * from CambridgePubs ')
SET Dirección = '34 Glemsford Road 'donde dirección = '65 Cavendish Road'
INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0; DATABASE = c: \ CambridgePubs.xls',
"Select * from CambridgePubs ')
(Pubname, dirección, código postal)
SELECT 'El Toro', 'camino de Antioquía', 'CB2 5TY'
/ * Crear hojas de cálculo Excel con sp_makewebtask
--------------------------------------------------
En lugar de crear la hoja de cálculo Excel con OLEDB Uno puede usar la
sp_makewebtask
Los usuarios deben tener permisos SELECT para ejecutar una consulta específica y
permisos CREATE PROCEDURE en la base de datos en la que la consulta se ejecutará. La
cuenta de SQL Server debe tener permisos para escribir el documento HTML generado en
la ubicación especificada. Sólo los miembros de la función de servidor sysadmin pueden
suplantar a otros usuarios.
* /
sp_makewebtask @ outputfile = 'c: \ CambridgePubsHTML2.xls',
@ Query = "Select * from # # CambridgePubs ',
@ Colheaders = 1,
@ FixedFont = 0, @ lastUpdated = 0, @ resultstitle = 'Cambridge Bares',
@ Dbname = 'MyDatabaseName'
/ * Esto está bien para la distribución de información de bases de datos, pero bueno
no
si posteriormente desea abrir a través de ODBC. * /
/ * La automatización OLE
----------------
Hasta ahora, todo bien. Sin embargo, de verdad queremos algo más que esto. Cuando
creamos un archivo de Excel para un informe de negocio, queremos que los datos y
también queremos que el formato agradable, los rangos definidos, sumas, campos
calculados y gráficos bonitos. Si lo hacemos los informes financieros, queremos una
tabla dinámica, y así sucesivamente con el fin de permitir un grado de minería de
datos por parte del beneficiario. Un enfoque diferente se requiere.
Podemos, por supuesto, el uso de Excel para extraer los datos de la base de datos. Sin
embargo, en este ejemplo, vamos a crear una hoja de cálculo, escribir los datos en él,
encaja muy bien las columnas y definir un rango en torno a los datos
* / ALTER PROCEDURE [dbo]. [SpDMOExportToExcel] (
@ SourceServer VARCHAR (30),
@ SourceUID VARCHAR (30) = NULL,
@ SourcePWD VARCHAR (30) = NULL,
@ QueryText VARCHAR (200),
@ Nombre VARCHAR (100),
@ WorksheetName VARCHAR (100) = "Hoja de Trabajo",
@ RangeName VARCHAR (80) = 'MyRangeName'
)
AS
INT DECLARE @ objServer,
@ ObjQueryResults INT
@ ObjCurrentResultSet INT,
@ ObjExcel INT,
@ ObjWorkBooks INT
@ ObjWorkBook INT,
@ ObjWorkSheet INT,
@ ObjRange INT,
@ Hr INT,
@ Columns INT,
@ Int filas, los
@ Salida INT,
@ CurrentColumn INT,
@ CurrentRow INT,
@ ResultSetRow INT,
@ Off_Column INT,
@ Off_Row INT,
@ Comandos VARCHAR (255),
@ ColumnName VARCHAR (255),
@ Valor VARCHAR (255),
@ StrErrorMessage VARCHAR (255),
@ ObjErrorObject INT,
@ Alfabeto VARCHAR (27)
SELECT @ alfabeto = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
SI @ QueryText IS NULL
COMENZAR
RAISERROR ('Una cadena de consulta se requiere para spDMOExportToExcel', 16, 1)
RETURN 1
FIN
- Establece el servidor en el servidor local
SI SourceServer @ IS NULL SELECT @ SourceServer = @ @ servername
SET NOCOUNT ON
SELECT @ strErrorMessage = 'una instancia de la DMO ",
@ = @ ObjErrorObject objServer
EXEC @ hr = sp_OACreate 'SQLDMO.SQLServer', @ objServer OUT
SI @ SourcePWD IS NULL OR @ SourceUID IS NULL
COMENZAR
- Utilizar una conexión de confianza
SI @ hr = 0 SELECT @ strErrorMessage =
'Configuración de inicio de sesión para la autenticación de Windows en el'
+ @ SourceServer, objErrorObject @ = @ objServer
SI @ hr = 0 EXEC @ hr = sp_OASetProperty @ ObjServer, 'LoginSecure', 1
SI @ hr = 0 SELECT @ strErrorMessage =
'Iniciar sesión en el servidor solicitado mediante la autenticación de Windows en el'
+ @ SourceServer
SI @ SourceUID ES NULA Y @ hr = 0 EXEC @ hr = sp_OAMethod @ ObjServer,
Los Conéctate ', null, @ SourceServer
SI @ SourceUID no es NULL y @ hr = 0
EXEC @ hr = sp_OAMethod
@ ObjServer, 'Connect', NULL, @ SourceServer, @ SourceUID
FIN
ELSE
COMENZAR
SI @ hr = 0
SELECT @ strErrorMessage = 'Conexión''' + @ + SourceServer
'' 'Con el ID de usuario''' + @ + SourceUID'''',
@ = @ ObjErrorObject objServer
SI @ hr = 0
EXEC @ hr = sp_OAMethod @ ObjServer, 'Connect', NULL,
@ SourceServer, SourceUID @, @ SourcePWD
FIN
- Ahora que ejecutar la consulta
SI @ hr = 0 SELECT @ strErrorMessage = 'ejecutar la consulta "'
+ @ QueryText + '", en' + @ SourceServer,
@ = @ ObjErrorObject objServer,
@ = Comando de ExecuteWithResults ("'+ @ QueryText +'") '
SI @ hr = 0
EXEC @ hr = sp_OAMethod @ ObjServer, comando @, @ objQueryResults OUT
SI @ hr = 0
SELECT @ strErrorMessage = 'conseguir el primer conjunto de resultados para "'
+ @ QueryText + '", en' + @ SourceServer,
@ = @ ObjErrorObject objQueryResults
SI @ hr = 0 EXEC @ hr = sp_OAMethod
@ ObjQueryResults, 'CurrentResultSet', @ objCurrentResultSet OUT
SI @ hr = 0
SELECT @ strErrorMessage = 'a encontrar las filas y columnas "'
+ @ QueryText + '", en' + @ SourceServer
SI @ hr = 0
EXEC @ hr = sp_OAMethod @ ObjQueryResults, 'Columnas', @ columns OUT
SI @ hr = 0
EXEC @ hr = sp_OAMethod @ ObjQueryResults, las filas, filas de @ OUT
- Por lo que ahora tenemos los queryresults. Ponemos en marcha Excel
SI @ hr = 0
SELECT @ strErrorMessage = 'Crear la aplicación de Excel, en'
+ @ SourceServer, objErrorObject @ = @ objExcel
SI @ hr = 0
EXEC @ hr = sp_OACreate "Excel.Application ', @ objExcel OUT
SI @ hr = 0 SELECT @ strErrorMessage = 'Obtener el objeto Workbooks'
SI @ hr = 0
EXEC @ hr = sp_OAGetProperty @ ObjExcel, de los libros ",
@ ObjWorkBooks OUT
- Crear un libro
SI @ hr = 0
SELECT @ strErrorMessage = 'Agregar un libro ",
@ = @ ObjErrorObject objWorkBooks
SI @ hr = 0
EXEC @ hr = sp_OAGetProperty @ ObjWorkBooks, 'Añadir', @ objWorkBook OUT
- Y una hoja de cálculo
SI @ hr = 0
SELECT @ strErrorMessage = 'Agregar una hoja de trabajo ",
@ = @ ObjErrorObject objWorkBook
SI @ hr = 0
EXEC @ hr = sp_OAGetProperty ObjWorkBook @, 'Worksheets.Add',
@ ObjWorkSheet OUT
SI @ hr = 0
SELECT @ strErrorMessage = "Poner nombre a una hoja de cálculo como" '
+ @ + WorksheetName '"', @ = @ objErrorObject objWorkBook
SI @ hr = 0
EXEC @ hr = sp_OASetProperty @ ObjWorkSheet, 'nombre', @ WorksheetName
SELECT @ CurrentRow = 1
- Así que vamos a escribir los títulos de las columnas
SELECT @ CurrentColumn = 1
WHILE (@ CurrentColumn INFERIOR O IGUAL @ columns Y @ hr = 0)
COMENZAR
SI @ hr = 0
SELECT @ strErrorMessage = 'conseguir título de la columna'
+ LTRIM (STR (@ CurrentColumn)),
@ = @ ObjErrorObject objQueryResults,
@ Command = 'ColumnName ('
+ CONVERT (VARCHAR (3), @ CurrentColumn) + ')'
SI @ hr = 0 EXEC @ hr = sp_OAGetProperty @ ObjQueryResults,
Comando @, @ ColumnName OUT
SI @ hr = 0
SELECT @ strErrorMessage = 'asignar el título de la columna' +
+ LTRIM (STR (@ CurrentColumn))
+ 'De la cadena de consulta ",
@ = @ ObjErrorObject objExcel,
@ = Comando de las células ('+ LTRIM (STR (@ CurrentRow)) +', '
+ LTRIM (STR (@ CurrentColumn)) + '). Valor'
SI @ hr = 0
EXEC @ hr = sp_OASetProperty @ ObjExcel, comando @, @ ColumnName
SELECT @ @ CurrentColumn CurrentColumn = + 1
FIN
- Formato a los títulos en negrita muy bien
SI @ hr = 0
SELECT @ strErrorMessage = 'dar formato a los encabezados de columna en negrita',
@ = @ ObjErrorObject objWorkSheet,
@ Command = 'Range ("A1:'
+ SUBSTRING (@ alfabeto, @ CurrentColumn / 26, 1)
+ SUBSTRING (@ alfabeto, @ CurrentColumn 26%, 1)
+ '1 '+' "). Font.Bold '
SI @ hr = 0 EXEC @ hr = sp_OASetProperty @ ObjWorkSheet, comando @, 1
- Ahora escribimos los datos
SELECT @ CurrentRow = 2
WHILE (@ = @ CurrentRow menores Filas + 1 Y @ hr = 0)
COMENZAR
SELECT @ CurrentColumn = 1
WHILE (@ CurrentColumn INFERIOR O IGUAL @ columns Y @ hr = 0)
COMENZAR
SI @ hr = 0
SELECT
@ StrErrorMessage =
'Obtener el valor de la cadena de consulta "
+ LTRIM (STR (@ CurrentRow)) + ','
+ LTRIM (STR (@ CurrentRow)) + ')',
@ = @ ObjErrorObject objQueryResults,
@ @ CurrentRow ResultSetRow = - 1
SI @ hr = 0
EXEC @ hr = sp_OAMethod @ ObjQueryResults, 'GetColumnString',
@ Valor de salida, ResultSetRow @, @ CurrentColumn
SI @ hr = 0
SELECT @ strErrorMessage =
'Asignar el valor de la cadena de consulta "
+ LTRIM (STR (@ CurrentRow - 1)) + ','
+ LTRIM (STR (@ CurrentColumn)) + ')',
@ = @ ObjErrorObject objExcel,
@ = Comando de las células ('+ STR (@ CurrentRow) +', '
+ STR (@ CurrentColumn) + '). Valor'
SI @ hr = 0
EXEC @ hr = sp_OASetProperty @ ObjExcel, comando @, @ valor
SELECT @ @ CurrentColumn CurrentColumn = + 1
FIN
SELECT @ @ CurrentRow CurrentRow = + 1
FIN
- Definir el nombre de rango
-.. Cambiar el tamaño de las células (1, 1) (10, 5) Nombre = "theData"
SI @ hr = 0 SELECT @ strErrorMessage = 'asignar un nombre a un rango'
+ LTRIM (STR (@ CurrentRow - 1)) + ','
+ LTRIM (STR (@ CurrentColumn - 1)) + ')',
@ = @ ObjErrorObject objExcel,
@ = Comando de las células (1, 1) Cambiar el tamaño de (. '+ STR (@ CurrentRow -
1) +', '
+ STR (@ CurrentColumn - 1) + '.) Nombre'
SI @ hr = 0 EXEC @ hr = sp_OASetProperty @ ObjExcel, comando @, @ RangeName
- Ahora AutoFilt las columnas que hemos escrito a la
SI @ hr = 0 SELECT @ = strErrorMessage de ajuste automático de las columnas ",
@ = @ ObjErrorObject objWorkSheet,
@ Command = 'Columnas ("A:"
+ SUBSTRING (@ alfabeto, (@ columns / 26), 1)
+ SUBSTRING (@ alfabeto, (@% Columnas 26), 1) +
'"). Ajuste automático'
SI @ hr = 0 - inserto dentro de la cubeta @ (cubo)
EXEC @ hr = sp_OAMethod @ ObjWorkSheet, comando @, @ a cabo la producción
SI @ hr = 0 SELECT @ command = "del" '+ @ nombre +' "'
SI @ hr = 0 EXECUTE master .. xp_cmdshell @ Command, no_output
SI @ hr = 0
SELECT @ strErrorMessage = 'Guardar el libro como "' + @ nombre + '"',
@ = @ ObjErrorObject objRange,
@ Command = 'SaveAs ("' + @ nombre + '")'
SI @ hr = 0 EXEC @ hr = sp_OAMethod @ ObjWorkBook, @ Command
SI @ hr = 0 SELECT @ strErrorMessage = 'cierre de Excel,
@ = @ ObjErrorObject objExcel
EXEC @ hr = sp_OAMethod ObjWorkBook @, 'Close'
EXEC sp_OAMethod @ ObjExcel, 'Close'
IF @ hr <> 0
COMENZAR
DECLARE
En origen VARCHAR (255),
@ Descripción VARCHAR (255),
@ Helpfile VARCHAR (255),
@ HelpID INT
EXECUTE sp_OAGetErrorInfo @ ObjErrorObject,
@ Salida de la fuente, la salida @ Descripción,
@ Salida de Archivo de ayuda, la salida @ HelpID
SELECT @ h, en origen, @ Descripcion, @ Helpfile, @ HelpID de salida
SELECT @ strErrorMessage = 'Error al mismo tiempo'
+ COALESCE (@ strErrorMessage, «hacer algo»)
+ ',' + COALESCE (@ Descripción,'')
RAISERROR (@ strErrorMessage, 16, 1)
FIN
EXEC sp_OADestroy @ ObjServer
EXEC sp_OADestroy @ ObjQueryResults
EXEC sp_OADestroy @ ObjCurrentResultSet
EXEC sp_OADestroy @ ObjExcel
EXEC sp_OADestroy @ ObjWorkBookks
EXEC sp_OADestroy @ ObjWorkBook
EXEC sp_OADestroy @ ObjRange
REGRESO @ h
GO
- Ahora podemos crear nuestra hoja de cálculo de los pubs, y puede hacerlo desde
cualquiera de
- Nuestros servidores
-
spDMOExportToExcel SourceServer @ = "MyServer",
@ SourceUID = 'MYUSERID',
@ SourcePWD = "micontraseña",
@ QueryText = 'uso MyDatabase
seleccione pubname, dirección, código postal desde # # CambridgePubs ',
@ Filename = "C: \ MyPubDatabase.xls ',
@ WorksheetName = 'MyFavouritePubs',
@ RangeName = 'MyRangeName'
- O si usted está utilizando la seguridad integrada!
spDMOExportToExcel SourceServer @ = "MyServer",
@ QueryText = 'uso MyDatabase
seleccione pubname, dirección, código postal desde # # CambridgePubs ',
@ Filename = "C: \ MyPubDatabase.xls ',
@ WorksheetName = 'MyFavouritePubs',
@ RangeName = 'MyRangeName'
/ * Si bien este es un procedimiento muy útil almacenado, es probable que tenga que
modificar y agregar a él para fines particulares.
Utilizamos el método de DMO porque nos gusta para volcar los datos en Excel crear
hojas de cálculo de los usuarios, por ejemplo, nombres de usuarios, Historias de
empleo. Sin embargo, una versión ADODB es muy simple de hacer y se puede hacer mucho
más rápido para lecturas y escrituras.
Acabamos de insertar los valores, pero usted puede insertar fórmulas y NumberFormat
formato) y crear o cambiar las fronteras. Usted puede, de hecho, manipular la hoja de
cálculo en cualquier forma que desee. Cuando hacemos esto, podemos grabar macros en
Excel y luego convertir estas macros para TSQL! Utilizando el ejemplo anterior, que
debe ser simple * /
Introduction
In many time you will find yourself with some Excel file need to be saved or Export to SQL Server,I
know it possible by using SQL Server Import and Export Wizard.But this depend on business
Scenario itself ,so one of the scenario is creating web application or windows application that read
Excel file and Move it to SQL SERVER Database,so this sample assume that you have one of those
scenarios.
Sample Demo
As you can see from the above image the sample contain the page which byself contain three
controls
1. FileUpload Control to allow you browse and upload excel file
2. Button to Import data from excel and export it to SQL Server
3. Label to show the message about uploading status
Building the Sample
Before run and test the sample you need to follow the next steps.
Note: before start reading the steps I want let you to know that I haven’t test this sample either on
ASP.NET 3.5 or 32-bit MS Office,so may be you do not need the step 1 and step 2.
1. Create an IIS web site
2. Change .Net Framework to 4.0 for Application Pool of this site
3. Create Excel file and make sure that file contain the column name as following image
4.Create Table in SQL Server and make sure has the same Columns name with appropriate
columns data type.
Description
This sample work as I mentioned above by ask user to upload Excel file (*.xsl,*.xslx) and then check the
content type of that file then open excel file and save it's data to SQL Server Database.
Visual BasicC#
'if you have Excel 2007 uncomment this line of code
' string excelConnectionString =string.Format("Provider=Microsof
t.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0",path);
'Define the content type
Dim ExcelContentType As String = "application/vnd.ms-excel"
Dim Excel2010ContentType As String = "application/vnd.openxmlform
ats-officedocument.spreadsheetml.sheet"
If FileUpload1.HasFile Then
If FileUpload1.PostedFile.ContentType = ExcelContentType Or F
ileUpload1.PostedFile.ContentType = Excel2010ContentType Then
Try
'Save file path
Dim path As String = String.Concat(Server.MapPath("~/
TempFiles/"), FileUpload1.FileName)
'Save File as Temp then you can delete it if you want
FileUpload1.SaveAs(path)
'For Office Excel 2010 please take a look to the fol
lowng link http://social.msdn.microsoft.com/Forums/en-
US/exceldev/thread/0f03c2de-3ee2-475f-b6a2-f4efb97de302/#ae1e6748-297d-
4c6e-8f1e-8108f438e62e
Dim excelConnectionString As String = String.Format("
Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Exc
el 8.0", path)
' Create Connection to Excel Workbook
Using connection As New OleDbConnection(excelConnecti
onString)
Dim Command As OleDbCommand = New OleDbCommand("S
elect * FROM [Sheet1$]", connection)
connection.Open()
'Create DbDataReader to Data Worksheet
Using reader As DbDataReader = Command.ExecuteRea
der()
' SQL Server Connection String
Dim sqlConnectionString As String = "Data Sou
rce=.\sqlexpress;Initial Catalog=ExcelDB;Integrated Security=True"
' Bulk Copy to SQL Server
Using bulkCopy As New SqlBulkCopy(sqlConnecti
onString)
bulkCopy.DestinationTableName = "Employee
"
bulkCopy.WriteToServer(reader)
Label1.Text = "The data has been exported
succefuly from Excel to SQL"
End Using
End Using
End Using
Catch ex As Exception
Label1.Text = ex.Message
End Try
End If
End If