Cómo se viaja a través de Los datos a través de Excel

Estas son las principales cosas que puede hacer mientras los datos están en la estación de tren de Excel:

• Importar    Puede importar datos de muchos orígenes de datos externos diferentes. Estos orígenes de datos pueden estar en su equipo, en la nube o a medio camino por todo el mundo. Para obtener más información, vea Importar datos de orígenes de datos externos.

• Power Query    Puede usar Power Query (anteriormente denominado Obtener & Transformar) para crear consultas para dar forma, transformar y combinar datos de varias maneras. Puede exportar su trabajo como plantilla de Power Query para definir una operación de flujo de datos en Power Apps. Incluso puede crear un tipo de datos para complementar los tipos de datos vinculados. Para obtener más información, vea Ayuda de Power Query para Excel.

• Seguridad    La privacidad de los datos, las credenciales y la autenticación siempre son una preocupación constante. Para obtener más información, vea Administrar la configuración y los permisos del origen de datos y Establecer niveles de privacidad.

• Actualizar    Los datos importados normalmente requieren una operación de actualización para traer cambios, como adiciones, actualizaciones y eliminaciones, a Excel. Para obtener más información, vea Actualizar una conexión de datos externos en Excel.

• Connections/Properties    Cada origen de datos externo tiene información de propiedad y conexión variada asociada a él que a veces requiere cambios según sus circunstancias. Para obtener más información, vea Administrar rangos de datos externos y sus propiedades, Crear, editary administrar conexiones a datos externosy Propiedades de conexión.

• Heredado    Los métodos tradicionales, como los Asistentes para importación heredados y MSQuery, siguen estando disponibles para su uso. Para obtener más información, vea Opciones de importación y análisis de datos y Usar Microsoft Query para recuperar datos externos.

Las secciones siguientes proporcionan más detalles de lo que está sucediendo en segundo plano en esta concurrida estación de tren de Excel.

Hay propiedades de conexión, consulta y rango de datos externos. Las propiedades de conexión y consulta contienen información de conexión tradicional. En un título de cuadro de diálogo, Propiedades de conexión significa que no hay ninguna consulta asociada, pero Propiedades de consulta significa que existe. Las propiedades del rango de datos externo controlan el diseño y el formato de los datos. Todos los orígenes de datos tienen un cuadro de diálogo Propiedades de datos externos, pero los orígenes de datos que tienen credenciales asociadas y información de actualización usan el cuadro de diálogo Propiedades de datos de rango externo más grande.

La siguiente información resume los cuadros de diálogo, paneles, rutas de comandos y los temas de ayuda más importantes.

Cuadro de diálogo o panel Rutas de comandos	Pestañas y túneles	Tema principal de la Ayuda
Orígenes recientes Datos > orígenes recientes	(Sin pestañas) Cuadro de diálogo Túneles> Navegador	Administrar la configuración y los permisos del origen de datos
Propiedades de conexión O Asistente para conexión de datos Data > Consultas & Connections > pestaña Connections > (hacer clic con el botón derecho en una conexión) > Propiedades	Ficha Definición de la pestaña Uso de la pestaña Usados en	Propiedades de conexión
Propiedades de consulta Data> Existing Connections > (hacer clic con el botón derecho en una conexión) > Editar propiedades de conexión O Consultas> datos & de conexión| Pestaña Consultas > (haga clic con el botón derecho en una conexión) > Propiedades O Propiedades de>consulta O Datos> actualizar todas las> (cuando se coloca en una hoja de cálculo de consulta cargada)	Ficha Definición de la pestaña Uso de la pestaña Usados en	Propiedades de conexión
Consultas & Connections Consultas > datos & Connections	Pestaña Consultas Pestaña Conexiones	Propiedades de conexión
Conexiones existentes Datos > conexiones existentes	Pestaña Tablas de la pestaña Conexiones	Conectarse a datos externos
Propiedades de datos externos O Propiedades de rango de datos externos O Propiedades de> datos (deshabilitadas si no están en una hoja de cálculo de consulta)	Se usa en la pestaña (desde el cuadro de diálogo Propiedades de conexión)   Botón Actualizar en los túneles de la derecha a Propiedades de consulta	Administrar rangos de datos externos y sus propiedades
Propiedades de > pestaña Definición > Exportar archivo de conexión O Consulta>exportar archivo de conexión	(Sin pestañas) Cuadro de diálogo Túneles a archivo Carpeta Orígenes de datos	Crear, editar y administrar conexiones a datos externos

Los datos de un libro de Excel pueden venir de dos ubicaciones diferentes. Los datos pueden almacenarse directamente en el libro o pueden almacenarse en un origen de datos externo, como un archivo de texto, una base de datos o un cubo de procesamiento analítico en línea (OLAP). Este origen de datos externo está conectado al libro a través de una conexión de datos, que es un conjunto de información que describe cómo localizar, iniciar sesión y obtener acceso al origen de datos externo.

La principal ventaja de conectarse a datos externos es que puede analizar periódicamente estos datos sin copiar repetidamente los datos en el libro, que es una operación que puede llevar mucho tiempo y ser propensa a errores. Después de conectarse a datos externos, también puede actualizar (o actualizar) automáticamente los libros de Excel desde el origen de datos original siempre que el origen de datos se actualice con información nueva.

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) (.odc) o un archivo de nombre de origen de datos (.dsn).

Para llevar datos externos a Excel, necesita tener acceso a los datos. Si el origen de datos externo al que desea obtener acceso no está en su equipo local, es posible que tenga que ponerse en contacto con el administrador de la base de datos para obtener una contraseña, permisos de usuario u otra información de conexión. Si el origen de datos es una base de datos, asegúrese de que la base de datos no se abre en modo exclusivo. Si el origen de datos es un archivo de texto o una hoja de cálculo, asegúrese de que otro usuario no lo tiene abierto para acceso exclusivo.

Muchos orígenes de datos también requieren un controlador ODBC o un proveedor OLE DB para coordinar el flujo de datos entre Excel, el archivo de conexión y el origen de datos.

En el siguiente diagrama se resumen los puntos clave sobre las conexiones de datos.

1. Hay una variedad de orígenes de datos a los que puede conectarse: Analysis Services, SQL Server, Microsoft Access, otras bases de datos OLAP y relacionales, hojas de cálculo y archivos de texto.

2. Muchos orígenes de datos tienen un controlador ODBC asociado o un proveedor OLE DB.

3. Un archivo de conexión define toda la información necesaria para obtener acceso y recuperar datos de un origen de datos.

4. La información de conexión se copia de un archivo de conexión en un libro y la información de conexión se puede editar fácilmente.

5. Los datos se copian en un libro para que pueda usarlos igual que usa los datos almacenados directamente en el libro.

Para buscar archivos de conexión, use el cuadro de diálogo Conexiones existentes. (Seleccione Datos >conexiones existentes.) Con este cuadro de diálogo, puede ver los siguientes tipos de conexiones:

• Conexiones en el libro    

  Esta lista muestra todas las conexiones actuales en el libro. La lista se crea a partir de conexiones que ya definió, que creó mediante el cuadro de diálogo Seleccionar origen de datos del Asistente para la conexión de datos o a partir de conexiones que seleccionó previamente como conexión desde este cuadro de diálogo.

• Archivos de conexión en el equipo    

  Esta lista se crea a partir de la carpeta Mis orígenes de datos que normalmente se almacena en la carpeta Documentos.

• Archivos de conexión en la red    

  Esta lista se puede crear a partir de un conjunto de carpetas de la red local, cuya ubicación se puede implementar en toda la red como parte de la implementación de directivas de grupo de Microsoft Office o una biblioteca de SharePoint. 

También puede usar Excel como editor de archivos de conexión para crear y editar conexiones a orígenes de datos externos almacenados en un libro o en un archivo de conexión. Si no encuentra la conexión que desea, puede crear una conexión haciendo clic en Buscar más para mostrar el cuadro de diálogo Seleccionar origen de datos y, a continuación, haciendo clic en Nuevo origen para iniciar el Asistente para la conexión de datos.

Después de crear la conexión, puede usar el cuadro de diálogo Propiedades de conexión (Seleccionar consultas de > datos & Ficha Conexiones > > (haga clic con el botón derecho en una conexión) > Propiedades)para controlar varias opciones de configuración para las conexiones a orígenes de datos externos y para usar, reutilizar o cambiar archivos de conexión.

Nota    A veces, el cuadro de diálogo Propiedades de conexión se denomina el cuadro de diálogo Propiedades de consulta cuando hay una consulta creada en Power Query (anteriormente denominada Obtener & Transformar) asociada a ella.

Si usa un archivo de conexión para conectarse a un origen de datos, Excel copia la información de conexión del archivo de conexión en el libro de Excel. Al realizar cambios mediante el cuadro de diálogo Propiedades de conexión, está editando la información de conexión de datos almacenada en el libro de Excel actual y no el archivo de conexión de datos original que se puede haber usado para crear la conexión (indicado por el nombre de archivo que se muestra en la propiedad Archivo de conexión de la pestaña Definición). Después de editar la información de conexión (con la excepción de las propiedades Nombre de conexión y Descripción de la conexión), se quita el vínculo al archivo de conexión y se borra la propiedad Archivo de conexión.

Para asegurarse de que el archivo de conexión siempre se usa cuando se actualiza un origen de datos, haga clic en Intentar usar siempre este archivo para actualizar estos datos en la pestaña Definición. Si activa esta casilla, todos los libros que usen ese archivo de conexión siempre usarán las actualizaciones del archivo de conexión, que también deben tener este conjunto de propiedades.

Mediante el cuadro de diálogo Conexiones, puede administrar fácilmente estas conexiones, como crearlas, editarlas y eliminarlas (Seleccione Consultas de > datos & Pestaña Conexiones > Connections > (haga clic con el botón derecho en una conexión) > Propiedades).) Puede usar este cuadro de diálogo para hacer lo siguiente: 

• Cree, edite, actualice y elimine las conexiones que están en uso en el libro.

• Compruebe el origen de datos externos. Es posible que desee hacerlo en caso de que otro usuario haya definido la conexión.

• Mostrar dónde se usa cada conexión en el libro actual.

• Diagnosticar un mensaje de error sobre las conexiones a datos externos.

• Redirigir una conexión a otro servidor o origen de datos, o reemplazar el archivo de conexión de una conexión existente.

• Facilita la creación y el compartir archivos de conexión con los usuarios.

Los archivos de conexión son especialmente útiles para compartir conexiones de forma coherente, hacer que las conexiones sean más reconocibles, ayudar a mejorar la seguridad de las conexiones y facilitar la administración del origen de datos. La mejor manera de compartir archivos de conexión es colocarlos en una ubicación segura y de confianza, como una carpeta de red o una biblioteca de SharePoint, donde los usuarios pueden leer el archivo, pero solo los usuarios designados pueden modificar el archivo. Para obtener más información, vea Compartir datos con ODC.

Usar archivos ODC

Puede crear archivos de conexión de datos de Office (ODC) (.odc) conectándose a datos externos a través del cuadro de diálogo Seleccionar origen de datos o usando el Asistente para la conexión de datos para conectarse a nuevos orígenes de datos. Un archivo ODC usa etiquetas HTML y XML personalizadas para almacenar la información de conexión. Puede ver o editar fácilmente el contenido del archivo en Excel.

Puede compartir archivos de conexión con otras personas para darles el mismo acceso que tiene a un origen de datos externo. Otros usuarios no necesitan configurar un origen de datos para abrir el archivo de conexión, pero es posible que deban instalar el controlador ODBC o el proveedor OLE DB necesario para obtener acceso a los datos externos en su equipo.

Los archivos ODC son el método recomendado para conectarse a datos y compartir datos. Puede convertir fácilmente otros archivos de conexión tradicionales (DSN, UDL y archivos de consulta) en un archivo ODC abriendo el archivo de conexión y haciendo clic en el botón Exportar archivo de conexión de la pestaña Definición del cuadro de diálogo Propiedades de conexión.

Usar archivos de consulta

Los archivos de consulta son archivos de texto que contienen información del origen de datos, incluido el nombre del servidor donde se encuentran los datos y la información de conexión que proporciona al crear un origen de datos. Los archivos de consulta son una forma tradicional de compartir consultas con otros usuarios de Excel.

Usar archivos de consulta .dqy    Puede usar Microsoft Query para guardar archivos .dqy que contienen consultas de datos de bases de datos relacionales o archivos de texto. Al abrir estos archivos en Microsoft Query, puede ver los datos devueltos por la consulta y modificar la consulta para recuperar resultados diferentes. Puede guardar un archivo .dqy para cualquier consulta que cree, ya sea con el Asistente para consultas o directamente en Microsoft Query.

Usar archivos de consulta .oqy    Puede guardar archivos .oqy para conectarse a datos en una base de datos OLAP, ya sea en un servidor o en un archivo de cubo sin conexión (.cub). Cuando se usa el Asistente para la conexión multidimensional en Microsoft Query para crear un origen de datos para una base de datos OLAP o un cubo, se crea automáticamente un archivo .oqy. Como las bases de datos OLAP no están organizadas en registros o tablas, no puede crear consultas ni archivos .dqy para obtener acceso a estas bases de datos.

Usar archivos de consulta .rqy    Excel puede abrir archivos de consulta en formato .rqy para admitir controladores de origen de datos OLE DB que usan este formato. Para obtener más información, consulte la documentación del controlador.

Usar archivos de consulta .qry    Microsoft Query puede abrir y guardar archivos de consulta en formato .qry para usarlos con versiones anteriores de Microsoft Query que no pueden abrir archivos .dqy. Si tiene un archivo de consulta en formato .qry que desea usar en Excel, abra el archivo en Microsoft Query y guárdelo como un archivo .dqy. Para obtener información sobre cómo guardar archivos .dqy, vea Ayuda de Microsoft Query.

Usar archivos de consulta web .iqy    Excel puede abrir archivos de consulta web .iqy para recuperar datos de la Web. Para obtener más información, vea Exportar a Excel desde SharePoint.

Un rango de datos externos (también denominado tabla de consulta) es un nombre definido o un nombre de tabla que define la ubicación de los datos que se incluyen en una hoja de cálculo. Cuando se conecta a datos externos, Excel crea automáticamente un rango de datos externos. La única excepción es un informe de tabla dinámica conectado a un origen de datos, que no crea un rango de datos externo. En Excel, puede aplicar formato y establecer un rango de datos externos o usarlo en cálculos, como con cualquier otro dato.

Excel asigna automáticamente un nombre a un rango de datos externos de la siguiente manera:

• Los rangos de datos externos de los archivos de conexión de datos de Office (ODC) tienen el mismo nombre que el nombre de archivo.

• Los rangos de datos externos de las bases de datos se denominan con el nombre de la consulta. De forma Query_from_origen de datos es el nombre del origen de datos que usó para crear la consulta.

• Los rangos de datos externos de los archivos de texto se denominan con el nombre del archivo de texto.

• Los rangos de datos externos de las consultas web se denominan con el nombre de la página web desde la que se recuperaron los datos.

Si la hoja de cálculo tiene más de un rango de datos externos del mismo origen, los rangos se numeran. Por ejemplo, MyText, MyText_1, MyText_2, y así sucesivamente.

Un rango de datos externo tiene propiedades adicionales (que no debe confundirse con las propiedades de conexión) que puede usar para controlar los datos, como la conservación del formato de celda y el ancho de columna. Puede cambiar estas propiedades del rango de datos externo haciendo clic en Propiedades en el grupo Conexiones de la pestaña Datos y, después, realizando los cambios en los cuadros de diálogo Propiedades del rango de datos externos o Propiedades de datos externos.

Hay varios objetos de datos (como un rango de datos externo y un informe de tabla dinámica) que puede usar para conectarse a diferentes orígenes de datos. Sin embargo, el tipo de origen de datos al que se puede conectar es diferente entre cada objeto de datos.

Puede usar y actualizar datos conectados en Servicios de Excel. Al igual que con cualquier origen de datos externo, es posible que tenga que autenticar el acceso. Para obtener más información, vea Actualizar una conexión de datos externos en Excel. Fo más información sobre las credenciales, vea Configuración de autenticación de Servicios de Excel.

En la tabla siguiente se resumen los orígenes de datos admitidos para cada objeto de datos en Excel.

Excel datos objeto	Crea Externo datos rango?		OLE DB	ODBC	Texto archivo	HTML archivo	XML archivo	SharePoint lista
Asistente para importar texto	Sí		No	No	Sí	No	No	No
Informe de tabla dinámica (no OLAP)	No		Sí	Sí	Sí	No	No	Sí
Informe de tabla dinámica (OLAP)	No		Sí	No	No	No	No	No
Tabla de Excel	Sí		Sí	Sí	No	No	Sí	Sí
Mapa XML	Sí		No	No	No	No	Sí	No
Consulta web	Sí		No	No	No	Sí	Sí	No
Asistente para conexión de datos	Sí		Sí	Sí	Sí	Sí	Sí	Sí
Microsoft Query	Sí		No	Sí	Sí	No	No	No

Solución alternativa de Servicios de Excel para tablas de Excel y rangos con nombre

Si desea mostrar un libro de Excel en Servicios de Excel, puede conectarse a datos y actualizarlo, pero debe usar un informe de tabla dinámica. Excel Services no admite rangos de datos externos, lo que significa que Servicios de Excel no admite una tabla de Excel conectada a un origen de datos, una consulta web, una asignación XML o Microsoft Query.

Sin embargo, puede evitar esta limitación mediante una tabla dinámica para conectarse al origen de datos y, después, diseñar y diseñar la tabla dinámica como una tabla bidimensional sin niveles, grupos o subtotales para que se muestren todos los valores de fila y columna deseados. 

Vamos a hacer un viaje por el carril de memoria de la base de datos.

Acerca de MDAC, OLE DB y OBC

En primer lugar, disculpas por todas las siglas. Microsoft Data Access Components (MDAC) 2.8 se incluye con Microsoft Windows. Con MDAC, puede conectarse y usar datos de una amplia variedad de orígenes de datos relacionales y no relacionales. Puede conectarse a muchos orígenes de datos diferentes mediante controladores ODBC (Open Database Connectivity) o proveedores de OLE DB, que Microsoft ha creado y enviado o desarrollado por varios terceros. Al instalar Microsoft Office, se agregan controladores ODBC adicionales y proveedores OLE DB al equipo.

Para ver una lista completa de proveedores OLE DB instalados en el equipo, muestre el cuadro de diálogo Propiedades del vínculo de datos de un archivo de vínculo de datos y, a continuación, haga clic en la pestaña Proveedor.

Para ver una lista completa de proveedores ODBC instalados en el equipo, muestre el cuadro de diálogo Administrador de bases de datos ODBC y, a continuación, haga clic en la pestaña Controladores.

También puede usar controladores ODBC y proveedores OLE DB de otros fabricantes para obtener información de orígenes distintos de los orígenes de datos de Microsoft, incluidos otros tipos de bases de datos ODBC y OLE DB. Para obtener información sobre la instalación de estos controladores ODBC o proveedores OLE DB, consulte la documentación de la base de datos o póngase en contacto con el proveedor de la base de datos.

Usar ODBC para conectarse a orígenes de datos

En la arquitectura ODBC, una aplicación (como Excel) se conecta al Administrador de controladores ODBC, que a su vez usa un controlador ODBC específico (como el controlador ODBC de Microsoft SQL) para conectarse a un origen de datos (como una base de datos Microsoft SQL Server).

Para conectarse a orígenes de datos ODBC, haga lo siguiente:

1. Asegúrese de que el controlador ODBC adecuado está instalado en el equipo que contiene el origen de datos.

2. Defina un nombre de origen de datos (DSN) mediante el administrador de origen de datos ODBC para almacenar la información de conexión en el Registro o un archivo DSN, o una cadena de conexión en código de Microsoft Visual Basic para pasar la información de conexión directamente al Administrador de controladores ODBC.

   Para definir un origen de datos, en Windows, haga clic en el botón Inicio y, a continuación, haga clic en Panel de control. Haga clic en Sistema y mantenimientoy, a continuación, en Herramientas administrativas. Haga clic en Rendimiento y mantenimiento,haga clic en Herramientas administrativas. y, a continuación, haga clic en Orígenes de datos (ODBC). Para obtener más información sobre las diferentes opciones, haga clic en el botón Ayuda de cada cuadro de diálogo.

Orígenes de datos de máquina

Los orígenes de datos de máquina almacenan información de conexión en el Registro, en un equipo específico, con un nombre definido por el usuario. Solo puede usar orígenes de datos de máquina en el equipo en el que están definidos. Hay dos tipos de orígenes de datos de máquina: usuario y sistema. Los orígenes de datos de usuario solo los puede usar el usuario actual y solo son visibles para ese usuario. Todos los usuarios de un equipo pueden usar orígenes de datos del sistema y pueden ser visibles para todos los usuarios del equipo.

Un origen de datos de máquina es especialmente útil cuando se desea proporcionar seguridad adicional, ya que ayuda a garantizar que solo los usuarios que han iniciado sesión pueden ver un origen de datos de máquina y un origen de datos de máquina no se puede copiar por un usuario remoto a otro equipo.

Orígenes de datos de archivos

Los orígenes de datos de archivos (también denominados archivos DSN) almacenan información de conexión en un archivo de texto, no en el Registro, y generalmente son más flexibles de usar que los orígenes de datos de máquina. Por ejemplo, puede copiar un origen de datos de archivo en cualquier equipo con el controlador ODBC correcto, de modo que la aplicación pueda basarse en información de conexión coherente y precisa en todos los equipos que usa. También puede colocar el origen de datos de archivo en un único servidor, compartirlo entre muchos equipos de la red y mantener fácilmente la información de conexión en una ubicación.

Un origen de datos de archivo también puede no compartirse. Un origen de datos de archivo no compartido reside en un único equipo y apunta a un origen de datos de máquina. Puede usar orígenes de datos de archivos no compartidos para obtener acceso a orígenes de datos de máquina existentes desde orígenes de datos de archivos.

Usar OLE DB para conectarse a orígenes de datos

En la arquitectura OLE DB, la aplicación que tiene acceso a los datos se denomina consumidor de datos (como Excel) y el programa que permite el acceso nativo a los datos se denomina proveedor de bases de datos (como el proveedor ole db de Microsoft para SQL Server).

Un archivo de vínculo de datos universal (.udl) contiene la información de conexión que un consumidor de datos usa para obtener acceso a un origen de datos a través del proveedor OLE DB de ese origen de datos. Puede crear la información de conexión siguiendo uno de estos procedimientos:

• En el Asistente para la conexión de datos, use el cuadro de diálogo Propiedades del vínculo de datos para definir un vínculo de datos para un proveedor OLE DB. 

• Cree un archivo de texto en blanco con una extensión de nombre de archivo .udl y, después, edite el archivo, que muestra el cuadro de diálogo Propiedades del vínculo de datos.