Mover datos de Excel a Access

En este artículo se muestra cómo mover los datos de Excel a Access y convertir los datos en tablas relacionales para que pueda usar Microsoft Excel y Access juntos. En Resumen, Access es el mejor para capturar, almacenar, consultar y compartir datos, y Excel es la mejor opción para calcular, analizar y visualizar datos.

Dos artículos, utilizando Access o Excel para administrar los datos y las10 razones para usar Access con Excel, explique qué programa se adapta mejor a una tarea en particular y cómo usar Excel y Access conjuntamente para crear una solución práctica.

Al mover datos desde Excel a Access, el proceso tiene tres pasos básicos.

tres pasos básicos

Nota: Para obtener información sobre el modelado de datos y las relaciones en Access, consulte conceptos básicos del diseñode una base de datos.

Paso 1: importar datos de Excel a Access

Importar datos es una operación que puede resultar mucho más sencilla si toma tiempo para preparar y limpiar los datos. Importar datos es como ir a una nueva casa. Si limpia y organiza a sus posesiones antes de que se mueva, la liquidación en su casa nueva es mucho más fácil.

Limpiar los datos antes de importar

Antes de importar datos a Access, en Excel es buena idea:

  • Convertir celdas que contienen datos no atómicos (es decir, varios valores en una celda) en varias columnas. Por ejemplo, una celda de una columna "Skills" que contiene varios valores de aptitudes, como "programación de C#", "programación de VBA" y "diseño web" se debe desglosar para separar las columnas que contienen solo un valor de habilidad.

  • Use el comando Recortar para quitar los espacios iniciales, finales y varios espacios incrustados.

  • Quite los caracteres no imprimibles.

  • Buscar y corregir errores de ortografía y puntuación.

  • Quite las filas duplicadas o los campos duplicados.

  • Asegúrese de que las columnas de datos no contienen formatos combinados, especialmente números con formato de texto o fechas con formato de número.

Para obtener más información, consulte los siguientes temas de ayuda de Excel:

Nota: Si las necesidades de limpieza de datos son complejas, o si no tiene tiempo ni recursos para automatizar el proceso por su cuenta, puede usar un proveedor de terceros. Para obtener más información, busque "software de limpieza de datos" o "calidad de datos" por su motor de búsqueda favorito en el explorador Web.

Elegir el mejor tipo de datos al importar

Durante la operación de importación en Access, desea tomar buenas elecciones para recibir errores de conversión (si los hay) que requerirán intervención manual. La siguiente tabla resume cómo se convierten los formatos de número de Excel y los tipos de datos de Access al importar datos de Excel a Access y ofrece algunas sugerencias sobre los mejores tipos de datos que puede elegir en el Asistente para importar hojas de cálculo.

Formato de número de Excel

Tipo de datos de Access

Comentarios

Procedimiento recomendado

Texto

Texto, Memo

El tipo de datos de texto de Access almacena datos alfanuméricos de hasta 255 caracteres. El tipo de datos Memo de Access almacena datos alfanuméricos de hasta 65.535 caracteres.

Elija memorando para evitar truncar datos.

Número, porcentaje, fracción, científico

Número

Access tiene un tipo de datos numérico que varía en función de una propiedad de tamaño de campo (byte, entero, entero largo, simple, doble, decimal).

Elija doble para evitar errores de conversión de datos.

Fecha

Fecha

Tanto Access como Excel usan el mismo número de fecha de serie para almacenar fechas. En Access, el intervalo de fechas es mayor: de-657.434 (1 de enero de 100 D.C.) a 2.958.465 (31 de diciembre de 9999 D.C.).

Como Access no reconoce el sistema de fechas de 1904 (usado en Excel para Macintosh), debe convertir las fechas en Excel o en Access para evitar confusiones.

Para obtener más información, vea cambiar el sistema de fechas, el formato o la interpretación de años de dos dígitos e importar o vincular a los datos de un libro de Excel.

Elija fecha.

Hora

Hora

Access y Excel almacenan valores de tiempo con el mismo tipo de datos.

Elija hora, que suele ser el valor predeterminado.

Moneda, contabilidad

Moneda

En Access, el tipo de datos moneda almacena los datos como números de 8 bytes con precisión de cuatro posiciones decimales y se usa para almacenar datos financieros y evitar el redondeo de valores.

Elija moneda, que suele ser el valor predeterminado.

Boolean

Sí/No

Access usa-1 para todos los valores sí y 0 para todos los valores no, mientras que Excel usa 1 para todos los valores verdaderos y 0 para todos los valores falsos.

Elija sí/no, que convierte automáticamente los valores subyacentes.

Hipervínculo

Hipervínculo

Un hipervínculo en Excel y Access contiene una dirección URL o una dirección web en la que se puede hacer clic y seguir.

Elija hipervínculo; de lo contrario, Access puede usar el tipo de datos de texto de forma predeterminada.

Una vez que los datos están en Access, puede eliminar los datos de Excel. No olvide hacer una copia de seguridad del libro de Excel original primero antes de eliminarlo.

Para obtener más información, vea el tema de ayuda de Access importar o vincular a los datos de un libro de Excel.

Anexar datos automáticamente de forma sencilla

Un problema común que tienen los usuarios de Excel es anexar datos con las mismas columnas en una hoja de cálculo grande. Por ejemplo, puede tener una solución de seguimiento de activos que comenzó en Excel, pero que ahora ha crecido para incluir archivos de muchos grupos de trabajo y departamentos. Estos datos pueden estar en diferentes hojas de cálculo y libros, o en archivos de texto que sean fuentes de datos de otros sistemas. No hay ningún comando de interfaz de usuario ni una forma fácil de anexar datos similares en Excel.

La mejor solución es usar Access, donde puede importar y anexar datos fácilmente en una tabla mediante el Asistente para importar hojas de cálculo. Además, puede anexar una gran cantidad de datos en una tabla. Puede guardar las operaciones de importación, agregarlas como tareas de Microsoft Outlook programadas e incluso usar macros para automatizar el proceso.

Paso 2: normalizar datos con el Asistente para analizar tablas

A primera vista, seguir el proceso de normalización de los datos puede parecer una tarea desalentadora. Afortunadamente, la normalización de las tablas en Access es un proceso mucho más fácil, gracias al Asistente para analizar tablas.

el asistente del analizador de tablas

1. arrastre las columnas seleccionadas a una nueva tabla y cree relaciones automáticamente.

2. Use los comandos de los botones para cambiar el nombre de una tabla, agregar una clave principal, convertir una columna existente en una clave principal y deshacer la última acción.

Puede usar este asistente para hacer lo siguiente:

  • Convertir una tabla en un conjunto de tablas pequeñas y crear automáticamente una relación de clave principal y externa entre las tablas.

  • Agregue una clave principal a un campo existente que contenga valores únicos, o cree un nuevo campo de identificador que use el tipo de datos autonumeración.

  • Crear automáticamente relaciones para exigir la integridad referencial con actualizaciones en cascada. Las eliminaciones en cascada no se agregan automáticamente para evitar la eliminación accidental de datos, pero puede agregar fácilmente eliminaciones en cascada más adelante.

  • Busque en las nuevas tablas datos redundantes o duplicados (por ejemplo, el mismo cliente con dos números de teléfono diferentes) y actualice según sea necesario.

  • Haga una copia de seguridad de la tabla original y cambie el nombre anexando "_OLD" a su nombre. Después, cree una consulta que reconstruya la tabla original con el nombre de tabla original para que todos los formularios o informes existentes basados en la tabla original funcionen con la nueva estructura de tabla.

Para obtener más información, vea normalizar los datos con el analizador de tablas.

Paso 3: conectarse a datos de Access desde Excel

Una vez que se han normalizado los datos en Access y se ha creado una consulta o una tabla que reconstruye los datos originales, es muy sencillo conectarse a los datos de Access desde Excel. Los datos ahora se encuentran en Access como origen de datos externo y, por tanto, se pueden conectar al libro a través de una conexión de datos, que es un contenedor de información que se usa para buscar, iniciar sesión y obtener acceso al origen de datos externo. La información de conexión se almacena en el libro y también se puede almacenar en un archivo de conexión, como un archivo de conexión de datos de Office (ODC) (extensión de nombre de archivo. odc) o un archivo de nombre de origen de datos (extensión. DSN). Después de conectarse a los datos externos, también puede actualizar automáticamente (o actualizar) el libro de Excel desde Access cada vez que se actualicen los datos en Access.

Para obtener más información, vea importar datos de orígenes de datos externos (Power Query).

Obtener acceso a los datos

Esta sección le guiará a través de las siguientes fases de normalización de datos: dividir los valores de las columnas vendedor y dirección en sus partes más atómicas, dividir temas relacionados en sus propias tablas, copiar y pegar esas tablas de Excel a Access, crear relaciones clave entre las tablas de Access recién creadas y crear y ejecutar una consulta simple en Access para devolver información.

Datos de ejemplo en un formulario no normalizado

La siguiente hoja de cálculo contiene valores no atómicos en la columna vendedor y en la columna dirección. Ambas columnas deben dividirse en dos o más columnas independientes. Esta hoja de cálculo también contiene información sobre los vendedores, los productos, los clientes y los pedidos. Esta información también debe dividirse, por tema, en tablas distintas.

Vendedor

Id. de pedido

Fecha del pedido

Id. de producto

Ajuste

Precio

Nombre del cliente

Dirección

Teléfono

Li, Yale

2349

3/4/09

C-789

3

$7,00

Fourth Coffee

7007 Cornell St Redmond, WA 98199

425-555-0201

Li, Yale

2349

3/4/09

C-795

6

$9,75

Fourth Coffee

7007 Cornell St Redmond, WA 98199

425-555-0201

Adams, Yolanda

2350

3/4/09

A-2275

2

$16,75

Adventure Works

1025 Columbia Circle Kirkland, WA 98234

425-555-0185

Adams, Yolanda

2350

3/4/09

F-198

6

$5,25

Adventure Works

1025 Columbia Circle Kirkland, WA 98234

425-555-0185

Adams, Yolanda

2350

3/4/09

B-205

1

$4,50

Adventure Works

1025 Columbia Circle Kirkland, WA 98234

425-555-0185

Hance, Jim

2351

3/4/09

C-795

6

$9,75

Contoso, Ltd.

2302 Harvard Ave Bellevue, WA 98227

425-555-0222

Hance, Jim

2352

3/5/09

A-2275

2

$16,75

Adventure Works

1025 Columbia Circle Kirkland, WA 98234

425-555-0185

Hance, Jim

2352

3/5/09

D-4420

3

$7,25

Adventure Works

1025 Columbia Circle Kirkland, WA 98234

425-555-0185

Koch, Reed

2353

3/7/09

A-2275

6

$16,75

Fourth Coffee

7007 Cornell St Redmond, WA 98199

425-555-0201

Koch, Reed

2353

3/7/09

C-789

5

$7,00

Fourth Coffee

7007 Cornell St Redmond, WA 98199

425-555-0201

Información en sus partes más pequeñas: datos atómicos

Trabajar con los datos de este ejemplo, puede usar el comando texto a columna de Excel para separar las partes "atómicas" de una celda (como la calle, la ciudad, el estado y el código postal) en columnas discretas.

En la tabla siguiente se muestran las nuevas columnas de la misma hoja de cálculo después de que se hayan dividido para que todos los valores sean atómicos. Tenga en cuenta que la información de la columna vendedor se ha dividido en apellidos y las columnas de nombre, y que la información de la columna dirección se ha dividido en columnas de dirección, ciudad, estado y código postal. Estos datos están en "el primer formulario normal".

Apellido

Nombre

 

Calle

Ciudad

Estado:

Código postal

&

Yale

2302 Harvard Ave

Santoña

WA

98227

García

Yolanda

1025 Colombia círculo

Kirkland

WA

98234

Hance

Javier

2302 Harvard Ave

Santoña

WA

98227

Koch

Lengüeta

7007 Cornell St Redmond

Redmond

WA

98199

Dividir los datos en temas organizados en Excel

Las diversas tablas de datos de ejemplo siguientes muestran la misma información de la hoja de cálculo de Excel después de que se ha dividido en tablas para vendedores, productos, clientes y pedidos. El diseño de la tabla no es definitivo, pero está en la pista adecuada.

La tabla de vendedores solo contiene información sobre el personal de ventas. Tenga en cuenta que cada registro tiene un identificador único (ID comercial). El valor de ID comercial se utilizará en la tabla pedidos para conectar pedidos con vendedores.

Vendedores

ID comercial

Apellido

Nombre

101

&

Yale

103

García

Yolanda

105

Hance

Javier

107

Koch

Lengüeta

La tabla productos solo contiene información sobre los productos. Tenga en cuenta que cada registro tiene un identificador único (ID. de producto). El valor de ID de producto se usará para conectar información del producto con la tabla detalles del pedido.

Los

Id. de producto

Precio

A-2275

16,75

B-205

4,50

C-789

7,00

C-795

9,75

D-4420

7,25

F-198

5,25

La tabla compradores solo contiene información sobre los clientes. Tenga en cuenta que cada registro tiene un identificador único (ID. de cliente). El valor de ID de cliente se utilizará para conectar la información del cliente a la tabla pedidos.

Clientes

Id. de cliente

Nombre

Calle

Ciudad

Estado:

Código postal

Teléfono

1001

Contoso, Ltd.

2302 Harvard Ave

Santoña

WA

98227

425-555-0222

1003

Adventure Works

1025 Colombia círculo

Kirkland

WA

98234

425-555-0185

1005

Fourth Coffee

7007 Cornell St

Redmond

WA

98199

425-555-0201

La tabla pedidos contiene información sobre pedidos, vendedores, clientes y productos. Tenga en cuenta que cada registro tiene un identificador único (ID de pedido). Parte de la información de esta tabla debe dividirse en una tabla adicional que contenga detalles de pedidos para que la tabla pedidos contenga solo cuatro columnas: el identificador de pedido único, la fecha del pedido, el identificador del vendedor y el identificador del cliente. La tabla que se muestra aquí aún no se ha dividido en la tabla detalles del pedido.

Pedidos

Id. de pedido

Fecha del pedido

ID comercial

Id. de cliente

Id. de producto

Ajuste

2349

3/4/09

101

1005

C-789

3

2349

3/4/09

101

1005

C-795

6

2350

3/4/09

103

1003

A-2275

2

2350

3/4/09

103

1003

F-198

6

2350

3/4/09

103

1003

B-205

1

2351

3/4/09

105

1001

C-795

6

2352

3/5/09

105

1003

A-2275

2

2352

3/5/09

105

1003

D-4420

3

2353

3/7/09

107

1005

A-2275

6

2353

3/7/09

107

1005

C-789

5

Los detalles del pedido, como el identificador de producto y la cantidad, se mueven fuera de la tabla pedidos y se almacenan en una tabla denominada detalles de pedidos. Tenga en cuenta que hay 9 pedidos, por lo que tiene sentido que hay 9 registros en esta tabla. Tenga en cuenta que la tabla pedidos tiene un identificador único (ID. de pedido), al que se hace referencia en la tabla detalles de pedidos.

El diseño final de la tabla pedidos debería ser similar al siguiente:

Pedidos

Id. de pedido

Fecha del pedido

ID comercial

Id. de cliente

2349

3/4/09

101

1005

2350

3/4/09

103

1003

2351

3/4/09

105

1001

2352

3/5/09

105

1003

2353

3/7/09

107

1005

La tabla detalles de pedidos no contiene columnas que requieren valores únicos (es decir, no hay ninguna clave principal), por lo que es correcto que cualquiera o todas las columnas contengan datos "redundantes". Sin embargo, dos registros de esta tabla no deberían ser completamente idénticos (esta regla se aplica a cualquier tabla de una base de datos). En esta tabla, debe haber 17 registros (cada uno corresponde a un producto en un pedido individual). Por ejemplo, en el orden 2349, tres productos C-789 comprende una de las dos partes de todo el pedido.

Por lo tanto, la tabla detalles de pedidos debería ser similar a la siguiente:

Detalles del pedido

Id. de pedido

Id. de producto

Ajuste

2349

C-789

3

2349

C-795

6

2350

A-2275

2

2350

F-198

6

2350

B-205

1

2351

C-795

6

2352

A-2275

2

2352

D-4420

3

2353

A-2275

6

2353

C-789

5

Copiar y pegar datos de Excel a Access

Ahora que la información sobre los vendedores, los clientes, los productos, los pedidos y los detalles del pedido se han desglosado en diferentes asuntos de Excel, puede copiar esos datos directamente en Access, donde se convertirán en tablas.

Crear relaciones entre las tablas de Access y ejecutar una consulta

Después de mover los datos a Access, puede crear relaciones entre las tablas y, a continuación, crear consultas para devolver información acerca de varios temas. Por ejemplo, puede crear una consulta que devuelva el identificador de pedido y los nombres de los vendedores para los pedidos introducidos entre 3/05/09 y 3/08/09.

Además, puede crear formularios e informes para facilitar la entrada de datos y el análisis de ventas.

¿Necesitas más ayuda?

Siempre puede preguntar a un experto en Excel Tech Community, obtener soporte técnico en la Comunidad de respuestas o sugerir una característica nueva o mejora en el UserVoice de Excel.

¿Necesita más ayuda?

Ampliar sus conocimientos de Office
Explorar los cursos
Obtener nuevas características primero
Únase a los participantes de Office Insider

¿Le ha sido útil esta información?

¡Gracias por sus comentarios!

Gracias por sus comentarios. Quizá le interese ponerse en contacto con uno de nuestros agentes de soporte de Office.

×