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:

• Importación    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 mitad de 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 transformación de &) para crear consultas para dar forma, transformar y combinar datos de varias formas. Puede exportar su trabajo como una 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 Power Query para la Ayuda de Excel.

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

• Actualizar    Los datos importados normalmente requieren una operación de actualización para incorporar 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/Propiedades    Cada origen de datos externo tiene una conexión variada e información de propiedad asociada que a veces requiere cambios en función de sus circunstancias. Para obtener más información, vea Administrar rangos de datos externos y sus propiedades, Crear, editar y administrar conexiones a datos externos y Propiedades de conexión.

• Legado    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 siguientes secciones proporcionan más detalles de lo que está sucediendo en segundo plano en esta ocupada 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 el título de un cuadro de diálogo, Propiedades de la conexión significa que no hay ninguna consulta asociada, pero Propiedades de la consulta significa que sí. Las propiedades del rango de datos externos 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 correspondientes más importantes.

Cuadro de diálogo o panel Rutas de comandos	Pestañas y túneles	Tema principal de ayuda
Orígenes recientes Orígenes de datos > recientes	(Sin pestañas) Cuadro de diálogo Túneles para conectar > navegador	Administrar la configuración y los permisos del origen de datos
Propiedades de conexión O Asistente para la conexión de datos La pestaña Consultas de> de datos & Connections > pestaña Connections > (hacer clic con el botón derecho en una conexión) > Propiedades	Pestaña Definición de la pestaña Usousada en	Propiedades de conexión
Consulta Propiedades Datos > > de Connections existente (haga clic con el botón derecho en una conexión) > Editar propiedades de conexión O Consultas de> dedatos & conexión| La pestaña Consultas > (haga clic con el botón derecho en una conexión) > Propiedades Propiedades de> de consulta OR O datos > Actualizar todos los Connections de > (cuando se coloca en una hoja de cálculo de consulta cargada)	Pestaña Definición de la pestaña Usousada en	Propiedades de conexión
& Connections de consultas & Connections Consultas de> de datos	Pestaña Consultas Connections  tab	Propiedades de conexión
Connections existente > de datos Connections existentes	pestaña Tablas de Connections	Conectar a datos externos
Propiedades de datos externos O Propiedades del rango de datos externo O Propiedades de la> de datos (deshabilitada si no está 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 derecho a Las propiedades de consulta	Administrar rangos de datos externos y sus propiedades
Propiedades de conexión > pestaña Definición > Exportar archivo de conexión O Consulta > exportar archivo de conexión	(Sin pestañas) Carpeta Orígenes dedatos del cuadro de diálogo Túneles a archivo	Crear, editar y administrar conexiones a datos externos

Los datos de un libro de Excel pueden proceder de dos ubicaciones diferentes. Los datos pueden almacenarse directamente en el libro o 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 buscar, 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 es 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) o un archivo de nombre de origen de datos (.dsn).

Para incluir datos externos en Excel, necesita acceso a los datos. Si el origen de datos externo al que desea obtener acceso no está en el equipo local, es posible que deba 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.

El siguiente diagrama resume los puntos clave sobre las conexiones de datos.

1. Hay diversos 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 acceder a los datos de un origen de datos y recuperarlos.

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 del mismo modo que usa los datos almacenados directamente en el libro.

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

• Connections en el libro    

  Esta lista muestra todas las conexiones actuales del libro. La lista se crea a partir de conexiones que ya ha definido, que ha creado 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ó anteriormente 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 su red local, cuyo lugar se puede implementar a través de 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 > de datos& Connections > pestaña Connections > (haga clic con el botón derecho en una conexión) > Propiedades) para controlar varias configuraciones de conexiones a orígenes de datos externos y para usar, volver a usar o cambiar archivos de conexión.

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

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 (a 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, se asegurará de que todos los libros que usen ese archivo de conexión siempre usen las actualizaciones del archivo de conexión, que también deben tener establecida esta propiedad.

Mediante el cuadro de diálogo Connections, puede administrar fácilmente estas conexiones, como crearlas, editarlas y eliminarlas (seleccione Consultas de> de datos & Connections > pestaña 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 se usan en el libro.

• Compruebe el origen de datos externos. Es posible que desee hacer esto en caso de que la conexión haya sido definida por otro usuario.

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

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

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

• Facilite la creación y el uso compartido de 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 forma de compartir archivos de conexión es colocarlos en una ubicación segura y de confianza, como una carpeta de red o 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) conectándose a datos externos a través del cuadro de diálogo Seleccionar origen de datos o mediante 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 proporcionarles 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 necesiten instalar el controlador ODBC o el proveedor OLE DB necesarios para tener 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) a un archivo ODC abriendo el archivo de conexión y, a continuación, 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 mediante el Asistente para consultas o directamente en Microsoft Query.

Usar archivos de consulta .oqy    Puede guardar archivos .oqy para conectarse a los datos de una base de datos OLAP, ya sea en un servidor o en un archivo de cubo sin conexión (.cub). Al usar el Asistente para la conexión multidimensional en Microsoft Query para crear un origen de datos para una base de datos OLAP o 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 o archivos .dqy para tener 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, consulta 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, ábralo en Microsoft Query y guárdelo como un archivo .dqy. Para obtener información sobre cómo guardar archivos .dqy, vea la 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 incluidos 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 a esto es un informe de tabla dinámica conectado a un origen de datos, que no crea un rango de datos externo. En Excel, puede dar formato y diseñar un rango de datos externos o usarlo en cálculos, como con cualquier otro dato.

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

• 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 predeterminada, Query_from_source 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 de archivo de texto.

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

Si la hoja de cálculo tiene más de un rango de datos externos del mismo origen, se numeran los rangos. Por ejemplo, MiTexto, MyText_1, MyText_2, etc.

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 externos haciendo clic en Propiedades en el grupo Connections de la pestaña Datos y 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 orígenes de datos diferentes. Sin embargo, el tipo de origen de datos al que se puede conectar es diferente entre cada objeto de datos.

Puede usar y actualizar los datos conectados en Excel Services. 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, consulte Configuración de autenticación de Excel Services.

La tabla siguiente resume qué orígenes de datos son compatibles con cada objeto de datos en Excel.

Sobresalir datos objeto	Crea Externo datos ¿gama?		OLE DB	ODBC	Mensaje de texto archivo	HTML archivo	XML archivo	SharePoint lista
Asistente para importación de texto	Sí		No	No	Sí	No	No	No
Informe de tabla dinámica (sin 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í
Asignación 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

Excel Services solución alternativa para tablas de Excel y rangos con nombre

Si desea mostrar un libro de Excel en Excel Services, puede conectarse a los datos y actualizarlos, pero debe usar un informe de tabla dinámica. Excel Services no admite rangos de datos externos, lo que significa que Excel Services 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 si usa una tabla dinámica para conectarse al origen de datos y, a continuación, diseña y diseña la tabla dinámica como una tabla bidimensional sin niveles, grupos ni subtotales para que se muestren todos los valores de fila y columna deseados. 

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

Acerca de MDAC, OLE DB y OBC

En primer lugar, disculpas por todos los acrónimos. 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 de conectividad abierta de bases de datos (ODBC) o proveedores de OLE DB, que son construidos y enviados por Microsoft o desarrollados por varios terceros. Al instalar Microsoft Office, se agregan controladores ODBC y proveedores OLE DB adicionales 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 desde un archivo de vínculo de datos y, a continuación, haga clic en la pestaña Proveedor .

Para ver una lista completa de los 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 cómo instalar 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 de 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 orígenes de datos ODBC para almacenar la información de conexión en el Registro o en un archivo DSN, o bien 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 mantenimiento y, a continuación, haga clic en Herramientas administrativas. Haga clic en Rendimiento y mantenimiento y 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 en 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. Puede usar orígenes de datos de máquina solo 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 son visibles para todos los usuarios del equipo.

Un origen de datos de equipo es especialmente útil cuando se desea proporcionar mayor seguridad, ya que ayuda a garantizar que solo los usuarios que inicien sesión puedan ver un origen de datos del equipo y que un usuario remoto no pueda copiarlo a otro equipo.

Orígenes de datos de archivo

Los orígenes de datos de archivo (también denominados archivos DSN) almacenan información de conexión en un archivo de texto, no en el Registro, y por lo general 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, para que la aplicación pueda confiar en información de conexión coherente y precisa a todos los equipos que usa. También puede colocar el origen de datos del archivo en un único servidor, compartirlo entre varios equipos de la red y mantener fácilmente la información de conexión en una única ubicación.

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

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 (por ejemplo, Excel) y el programa que permite el acceso nativo a los datos se denomina proveedor de base de datos (por ejemplo, Proveedor DE OLE DB de Microsoft para SQL Server).

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

• En el Asistente para la conexión de datos, use el cuadro de diálogo Propiedades de 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, a continuación, edite el archivo, que muestra el cuadro de diálogo Propiedades del vínculo de datos.