Hay diferentes maneras de importar datos desde un archivo de Excel a una base de datos de SQL Server usando:
- SQL Server Integration Services (SSIS)
- Las funciones OPENROWSET y OPENDATASOURCE
- Servidores vinculados de SQL Server
- El Asistente de importación y exportación de SQL Server
En este artículo, los pasos para importar datos de un archivo de Excel a una base de datos de SQL Server se podrán explicar mediante el Asistente de importación y exportación de SQL Server, incluidos algunos de los problemas que podrán ocurrir durante los procesos.
Para iniciar el proceso de importación de datos desde un archivo de Excel a una base de datos de SQL Server utilizando el Asistente de importación y exportación de SQL Server, es importante que se deba iniciar el Asistente de importación y exportación de SQL Server. Hay varias formas de hacerlo, y esto se podrá ver en la sección Cómo importar / exportar datos a SQL Server usando la página Asistente de importación y exportación de SQL Server.
La primera página que se muestra cuando se lanzó el Asistente para importación y exportación de SQL Server es la página de bienvenida:
En esta página, solo se muestra una introducción rápida del Asistente de importación y exportación de SQL Server.
Luego haga clic en el siguiente botón para continuar. La siguiente página del Asistente para importación y exportación de SQL Server mostrara Elegir una página de origen de datos:
Estando en la página Elegir una fuente de datos, para poder continuar con la importación de datos desde Excel a SQL Server, se deberá proporcionar el proveedor de la fuente de datos y la forma de conectarse con la fuente de datos. En nuestro caso, el proveedor para conectarse al archivo de Excel será el proveedor de Microsoft Excel.
En el cuadro desplegable Origen de datos, seleccione el proveedor de Microsoft Excel:
Como se puede ver, no hay un proveedor de Microsoft Excel en la lista del cuadro desplegable Origen de datos. Para ello hay varias razones para esto. La primera razón podría ser que Microsoft Office no esté instalado. Pero no es necesario instalar Microsoft Office (Microsoft Excel) para ver el proveedor de Microsoft Excel en la lista desplegable de Fuente de datos.
Para ver el proveedor de Microsoft Excel en la lista, instale Microsoft Access Database Engine 2016 Redistributable. La última versión de Microsoft Access Database Engine podrá abrir la versión anterior de Excel, así que asegúrese de tener la última.
El motor de base de datos de Microsoft Access 2016 Redistributable viene con dos versiones:
- AccessDatabaseEngine.exe es la versión de 32 bits
- AccessDatabaseEngine_X64.exe es una versión de 64 bits
Ahora, cuando Microsoft Access Database Engine 2016 Redistributable está ya instalado, deberíamos ver el proveedor de Microsoft Excel en la lista, pero desafortunadamente el proveedor de Microsoft Excel no aparecerá en la lista del cuadro desplegable Fuente de datos.
Esto se debe a que puede se pudo ejecutar una versión incorrecta del Asistente para importación y exportación de SQL Server. Por ejemplo, AccessDatabaseEngine.exe está instalado y se lanza la versión de 64 bits del Asistente de importación y exportación de SQL Server. En este caso, es muy importante que para ver el proveedor de Microsoft Excel en la lista del cuadro desplegable Origen de datos, deberá iniciar la versión de 32 bits del Asistente de importación y exportación de SQL Server y el proveedor de Microsoft Excel aparecerá en la lista:
Ahora, cuando todo está instalado, en la lista, deberá seleccionar el proveedor de Microsoft Excel. En la página Elegir un origen de datos, aparecen opciones adicionales:
En el cuadro Ruta del archivo de Excel, escriba la ubicación al archivo de Excel o use el botón Examinar para navegar a la ubicación:
Desde el cuadro desplegable de la versión de Excel, deberá elegir la versión de Excel que usará el libro de trabajo de origen. En nuestro caso, esa es la versión de Microsoft Excel 2016:
|La última opción en esta página es la casilla de verificación de Primera fila con nombres de columna:
Por defecto, esta opción estará marcada. Se marca o pisa las primeras filas de la fuente de datos como los nombres de columna:
Pero si esta opción está habilitada y el origen de datos no contiene nombres de columna, el Asistente de importación y exportación de SQL Server solo agregará los nombres de columna, comenzando con el nombre F1 para el nombre de la primera columna:
Si esta opción está deshabilitada y el origen de datos contiene los nombres de las columnas, el Asistente de importación y exportación de SQL Server tratara estas columnas como la primera fila de datos:
Ahora, cuando todo este configurado en la página Elegir un origen de datos del Asistente de importación y exportación de SQL Server, deberá hacer clic en el botón Siguiente para continuar.
El siguiente mensaje de advertencia podrá aparecer después de hacer clic en el botón Siguiente:
La operación no pudo ser completada.
Información Adicional:
El proveedor ‘Microsoft.ACE.OLEDB.16.0’ no está registrado en la máquina local. (Datos de sistema)
Normalmente, este mensaje de advertencia siempre aparece cuando el Asistente de importación y exportación de SQL Server se inicia a través de SQL Server Management Studio (SSMS), que es una aplicación de 32 bits, y la versión de 32 bits del Asistente de importación y exportación de SQL Server se inicia, pero como han instalado la versión de 64 bits redistribuible de Microsoft Access Database Engine 2016.
Hay dos soluciones para resolver este problema:
- La primera es lanzar la versión de 64 bits del Asistente de importación y exportación de SQL Server a través del menú Inicio
- La segunda resolución es instalar la versión redistribuible de 32 bits de Microsoft Access Database Engine 2016 y continuar usando el Asistente de importación y exportación de SQL Server a través de SSMS, pero primero, se deberá desinstalar la versión de 64 bits de Microsoft Access Database Engine 2016 Redistributable, de lo contrario, aparecerá el siguiente mensaje de advertencia al iniciar el paquete de instalación:
Nota: El motor de base de datos de Microsoft Access 2016 Redistributable se podrá instalar en modo silencioso.
Deberá abrir la ventana del símbolo del sistema y ejecutar lo siguiente:
Para versión de 32 bits.
C:\Users\<Nombre_usuario>\Downloads\AccessDatabaseEngine.exe /quiet
Para versión de 64 bits.
C:\Users\<Nombre_usuario>\Downloads\AccessDatabaseEngine_X64.exe /quiet
La siguiente página del Asistente para importación y exportación de SQL Server será la página Elegir un destino:
En esta página, deberá determinar el destino donde se colocarán los datos del origen de datos (archivo Excel). En nuestro caso, el destino será una base de datos SQL Server.
En el cuadro desplegable Destino, debe elegir un proveedor que pueda conectarse a una base de datos de SQL Server.
Uno de los proveedores que pueden conectarse a SQL Server es:
- NET Framework Data Provider para SqlServer
- Proveedor Microsoft OLE DB para SQL Server
- SQL Server Native Client 11.0
En este caso, el SQL Server Native Client 11.0 se seleccionará de la lista Destino:
En el cuadro combinado Nombre del servidor, debe seleccionar la instancia de SQL Server:
En la sección Autenticación, deberá determinar cómo se conectará a SQL Server mediante el modo de autenticación de Windows o SQL Server.
En el cuadro desplegable Base de datos, seleccione una base de datos en la que se ubicarán los datos del origen de datos (archivo Excel):
O cree una nueva base de datos como destino para los datos del origen de datos.
Para hacerlo, deberá hacer clic en el botón Nuevo y en el cuadro de diálogo Creara la base de datos, para ello establezca los parámetros para la nueva base de datos de destino de SQL Server:
Cuando todo esté configurado en la página Elegir un destino, haga clic en el botón Siguiente para continuar.
En la página Especificar copia de tabla o consulta, deberá determinar cómo se copiarán los datos del origen de datos al destino:
Si se seleccionara el botón de opción Copiar datos de una o más tablas o vistas, se deberán copiar todos los datos de las hojas de trabajo elegidas.
Si se eligiera Escribir una consulta para especificar los datos a transferir, solo los datos que se especifican en una consulta SQL se deberán copiar de una hoja de cálculo de Excel a una base de datos de SQL Server de destino.
Si se seleccionara Escribir una consulta para especificar los datos a transferir en la página Elegir un destino, se deberá mostrar la página Proporcionar una consulta de origen cuando se presione el botón Siguiente:
En el cuadro de texto de la declaración SQL, escriba la consulta que seleccionará los datos para copiar del archivo de Excel a la base de datos de SQL Server o cargue una consulta utilizando el botón Examinar.
Para consultar con éxito una hoja de cálculo, se deben agregar $ al final del nombre de la hoja y los corchetes alrededor del nombre de la hoja ([BusinessEntity $]), de lo contrario, podrán aparecer los siguientes mensajes de advertencia:
La declaración no pudo ser analizada.
Información Adicional:
Si el motor de base de datos de Microsoft Access no pudo encontrar el objeto “BusinessEntity”. Asegúrese de que el objeto exista y de deletrear su nombre y el nombre de la ruta correctamente. Si ‘BusinessEntity’ no es un objeto local, debe verificar su conexión de red o ponerse en contacto con el administrador del servidor. (Microsoft Access Database Engine)
O esto:
La declaración no pudo ser analizada.
Información Adicional:
Error de sintaxis en la cláusula FROM. (Microsoft Access Database Engine)
Si se eligiera el botón de opción Copiar datos de una o más tablas o vistas, cuando se presiona el botón Siguiente, se deberá mostrar la página Seleccionar tablas y vistas de origen:
En esta página, todas las hojas de trabajo para el archivo de Excel (ImportData.xlsx) se enumerarán en la columna Fuente. En la lista Fuente, debe elegir de qué hojas de trabajo desea importar datos a la base de datos de SQL Server haciendo clic en la casilla de verificación junto al nombre de las hojas de trabajo. Para esto las hojas de trabajo seleccionadas aparecerán en la columna Destino:
En el nombre de las tablas en la base de datos de SQL Server por defecto aparecerán los nombres de las hojas de trabajo seleccionadas de la columna Fuente, pero estos nombres se pueden cambiar haciendo clic en el nombre en la columna Destino:
Como se puede observar, el icono en la columna Destino para el campo BusinessEntity $ es diferente de los campos Tabla1 y Tabla2. Esto se debe a que la tabla en la base de datos de SQL Server que se elige como destino ya existe y para los campos Tabla1 y Tabla2, se crearán nuevas tablas.
Cuando se utilice una tabla existente, asegúrese de que la tabla de destino tenga el mismo número de columnas que tienen origen de datos; caso contrario, las columnas del origen de datos que no tengan una columna de destino adecuada serán ignoradas por defecto (<ignorar>) y la de los datos de esas columnas no se importarán a una tabla de destino:
Además, deberá asegurarse de que las columnas en la tabla de destino tengan tipos de datos que sean compatibles con los tipos de datos en las columnas de los datos de origen, de lo contrario podrá aparecer el siguiente error:
Si se encontraron 1 conversión(es) de tipo de columna desconocida Sólo se le permitirá guardar el paquete
Cuando se configuren las columnas y los tipos de datos adecuados, haga clic en el botón Siguiente, luego aparecerá la página Guardar y ejecutar el paquete del Asistente de importación y exportación de SQL Server:
Haga clic en el botón Siguiente si desea importar datos de un archivo de Excel a una base de datos de SQL Server, pero si por el contrario desea guardar el paquete SSIS para su uso posterior, marque el botón Guardar paquete SSIS.
En la página Completar el asistente, se mostraran las elecciones realizadas en las páginas del asistente anterior:
Haga clic en el botón Siguiente para importar datos de un archivo de Excel a una base de datos de SQL Server. La página Performing Operation mostrara el estado del proceso de importación ya sea si se completa con éxito o con los errores:
Si el error se produce durante el proceso de importación de datos desde un archivo de Excel a la base de datos de SQL Server, la palabra Mensajes… deberá aparecer en la columna Mensaje para mostrar la acción que falló:
Cuando haga clic en los mensajes…, aparecerá el cuadro de diálogo Ver informe con información detallada sobre el error:
Este error:
– Validando (Error)
Mensajes
- Error 0xc0202049: Tarea 1 del flujo de datos: falla al insertar en la columna de “solo lectura”.
(Asistente de importación y exportación de SQL Server)
- Error 0xc0202045: tarea de flujo de datos 1: error en la validación de metadatos de columna.
(Asistente de importación y exportación de SQL Server)
- Error 0xc004706b: Tarea de flujo de datos 1: “Destino – prueba1” falló la validación y devolvió el estado de validación “VS_ISBROKEN”.
(Asistente de importación y exportación de SQL Server)
- Error 0xc004700c: Tarea 1 de flujo de datos: uno o más componentes fallaron la validación.
(Asistente de importación y exportación de SQL Server)
- Error 0xc0024107: Tarea 1 del flujo de datos: hubo errores durante la validación de la tarea.
(Asistente de importación y exportación de SQL Server)
Por lo general, aparece cuando la tabla de destino tiene una columna IDENTIDAD. Para resolver esto, se debe volver a la página Seleccionar tablas y vistas de origen, luego seleccione las tablas que tienen propiedades de identidad y presione el botón Editar asignaciones. En el cuadro de diálogo Configuración de transferencia, debe seleccionar la casilla de verificación Habilitar inserción de identidad:
Además, otro problema común que podrá aparecer al importar datos desde el origen de datos a las tablas de SQL Server de destino es el problema de restricciones FOREIGN KEY. En el siguiente error, se muestran dos tablas de destino test1 y test2. La tabla test2 que hace referencia a la tabla test1:
– Copiando a [dbo]. [Prueba1] (Error)
Mensajes
(Asistente de importación y exportación de SQL Server)
(Asistente de importación y exportación de SQL Server)
(Asistente de importación y exportación de SQL Server)
– Copiando a [dbo].[test2] (Error)
Mensajes
(Asistente de importación y exportación de SQL Server)
(Asistente de importación y exportación de SQL Server)
Es importante tomar en cuenta que, para resolver este problema, se deberá deshabilitar la restricción para la tabla de referencia (prueba2) ejecutando el siguiente código en SSMS:
|
1 |
ALTER TABLE test2 NOCHECK CONSTRAINT ALL |
Después de importar datos desde el origen de datos a la base de datos de SQL Server de destino, deberá ejecutar el siguiente código en SSMS para poder habilitar la restricción para la tabla test2:
|
1 |
ALTER TABLE test2 WITH CHECK CHECK CONSTRAINT ALL |
En algunos casos, podrá aparecer el mensaje de advertencia de la imagen a continuación:
– Validando (Advertencia)
Mensajes
(Asistente de importación y exportación de SQL Server)
(Asistente de importación y exportación de SQL Server)
(Asistente de importación y exportación de SQL Server)
(Asistente de importación y exportación de SQL Server)
(Asistente de importación y exportación de SQL Server)
(Asistente de importación y exportación de SQL Server)
(Asistente de importación y exportación de SQL Server)
Para resolver esto, deberá ir a la tabla de destino de SQL Server y aumentar el tamaño de columna para las columnas que se enumeran en el mensaje de advertencia.
Para verificar que los datos del archivo de Excel se importen a la base de datos de SQL Server, vaya a SSMS, deberá buscar la base de datos en la que se importan los datos y enumerar todos los datos de las tablas:
Referencias:
Actualmente trabajando para ApexSQL LLC como un Ingeniero de Ventas de Software, él está ayudando a los clientes con problemas técnicos y hace aseguramiento de calidad para los complementos ApexSQL Complete, ApexSQL Refactor y ApexSQL Search.
Ver todas las entradas de Marko Zivkovic