Cómo importar datos de Excel a SQL Server

Resumen

En este artículo paso a paso se muestra cómo importar datos de hojas de cálculo de Microsoft Excel a bases de datos de Microsoft SQL Server mediante una variedad de métodos.

Descripción técnica

En los ejemplos de este artículo se importan datos de Excel con:

  • Servicios de transformación de datos (DTS) de SQL Server
  • Microsoft SQL Server 2005 Integration Services (SSIS)
  • Servidores vinculados de SQL Server
  • Consultas distribuidas de SQL Server
  • Objetos de datos ActiveX (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 service packs que se necesitan:

  • Instancia disponible de Microsoft SQL Server 7.0, Microsoft SQL Server 2000 o Microsoft SQL Server 2005
  • Microsoft Visual Basic 6.0 para las muestras de ADO que utilicen Visual Basic
En el contenido de este artículo se supone que está familiarizado con los temas siguientes:

  • Servicios de transformación de datos
  • Los servidores vinculados y consultas distribuidas
  • Desarrollo de ADO en Visual Basic

Ejemplos

Importar vs. Anexar

Las instrucciones SQL de ejemplo que se utilizan en este artículo muestran consultas Create Table que importan datos de Excel en una nueva tabla de SQL Server utilizando las sintaxis SELECT... INTO... FROM. Puede convertir estas instrucciones en consultas de datos anexados mediante el uso de las sintaxis INSERT INTO... SELECT... FROM mientras continúe haciendo referencia a los objetos de origen y de destino como se muestra en estos ejemplos de código.

Uso de DTS o SSIS

Puede utilizar el Asistente para importación de servicios de transformación de datos (DTS) de SQL Server o el Asistente para importación de SQL Server y Asistente para exportación para importar datos de Excel a las tablas de SQL Server. Cuando se recorre el asistente y se seleccionan 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, Hoja1$) y que los nombres de objeto normal sin el signo de dólar representan Excel rangos con nombre.

Uso de 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: utilizar Excel con SQL Server servidores vinculados y las 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" a una nueva tabla de SQL Server denominada XLImport1:

SELECT * INTO XLImport1 FROM EXCELLINK...[Customers$]
También puede ejecutar la consulta en el origen de un modo passthrough mediante OPENQUERY como sigue:

SELECT * INTO XLImport2 FROM OPENQUERY(EXCELLINK,    'SELECT * FROM [Customers$]')

Utilizar consultas distribuidas

Si no desea configurar una conexión persistente con el libro de Excel como un servidor vinculado, puede importar datos para un propósito específico mediante las funciones OPENDATASOURCE o OPENROWSET. Los ejemplos de código siguientes también importan los datos de la hoja de cálculo Clientes Excel a 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 se conecta a SQL Server en una aplicación de ADO con Microsoft OLE DB para SQL Server (SQLOLEDB), puede utilizar la misma sintaxis "consulta distribuida" de la sección Utilizar consultas distribuidas para importar datos de Excel a SQL Server.

El siguiente ejemplo de código de Visual Basic 6.0 requiere que agregue una referencia de proyecto a ActiveX Data Objects (ADO). Este ejemplo de código también muestra cómo utilizar OPENDATASOURCE y OPENROWSET a través de 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

Uso de ADO y el proveedor Jet

En el ejemplo de la sección anterior se ha utilizado ADO con el proveedor de SQLOLEDB para conectar con el 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 referenciar bases de datos externas en sentencias de SQL utilizando una sintaxis especial que tiene tres formatos diferentes:

  • [Ruta de acceso completa a la base de datos de Microsoft Access]. [Nombre de tabla]
  • [Nombre ISAM; Cadena de conexión de ISAM]. [Nombre de tabla]
  • [ODBC; Cadena de conexión ODBC]. [Nombre de tabla]
Esta sección utiliza el tercer formato para realizar una conexión ODBC con la base de datos de SQL Server de destino. 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 mediante el 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 a otras bases de datos de Microsoft Access, bases de datos de método de acceso secuencial indizado (ISAM) ("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 rangos de hojas de cálculo (por ejemplo, Sheet1$) y que los nombres de objeto simple representan rangos de Excel con nombre.
  • En algunos casos, especialmente al designar los datos de origen de Excel utilizando el nombre de tabla en lugar de una consulta SELECT, las columnas de la tabla de SQL Server de destino se reorganizan en orden alfabético. Para obtener información adicional acerca de este problema con el proveedor de Jet, haga clic en el número de artículo siguiente para verlo en Microsoft Knowledge Base:

    299484 PRB: columnas se ordenan alfabéticamente cuando utiliza ADOX para recuperar las columnas de la 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 de "mayoría" y devuelve valores no coincidentes como valores NULL. Para obtener información adicional acerca de cómo solucionar temporalmente este problema, haga clic en el número de artículo siguiente para verlo en Microsoft Knowledge Base:

    194124 PRB: Excel los valores que se devuelven como NULL utiliza OpenRecordset de DAO

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 VBA
Para obtener información adicional acerca de cómo transferir datos a Excel, haga clic en los números de artículo siguientes para verlos en Microsoft Knowledge Base:

295646 HOWTO: transferir datos desde un origen de datos 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: transferir datos a Excel mediante servicios de transformación de datos de SQL Server
306125 Cómo: importar datos de SQL Server en Microsoft Excel
Propiedades

Id. de artículo: 321686 - Última revisión: 17 ene. 2017 - Revisión: 1

Comentarios