Si los datos siempre están en un viaje, Excel es como Grand Central Station. Imagine que los datos son un tren lleno de pasajeros que entra regularmente en Excel, realiza cambios y después sale. Hay docenas de formas de ingresar a Excel, que importa datos de todo tipo y la lista sigue creciendo. Una vez que los datos están en Excel, están listos para cambiar de forma de la forma que quiera con Power Query. Los datos, como todos nosotros, también requieren "cuidado y alimentación" para que las cosas funcionen sin problemas. Ahí es donde entran en juego las propiedades de conexión, consulta y datos. Por último, los datos salen de la estación de tren de Excel de muchas maneras: importados por otros orígenes de datos, compartidos como informes, gráficos y tablas dinámicas, y exportados a Power BI y Power Apps.
Cosas principales que puede hacer con los datos en la estación de tren de Excel
Estas son las principales tareas 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 en medio 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) para crear consultas para dar forma, transformar y combinar datos de varias maneras. 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 de los datos, las credenciales y la autenticación son siempre una preocupación constante. Para obtener más información, consulte Administrar permisos y configuración del origen de datos y Establecer niveles de privacidad.
- Actualizar Normalmente, los datos importados requieren una operación de actualización para introducir cambios, como adiciones, actualizaciones y eliminaciones, en Excel. Para obtener más información, vea Actualizar una conexión de datos externa en Excel.
- Conexiones/Propiedades Cada origen de datos externo tiene asociada una variedad de información de conexión y propiedad 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, editar y administrar conexiones a datos externos y Propiedades de conexión.
- Heredado Los métodos tradicionales, como los asistentes de importación heredados y MSQuery, siguen estando disponibles para su uso. Para obtener más información, consulte 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 sucede entre bastidores en esta concurrida estación de tren de Excel.
Resumen de conexiones y propiedades
Hay propiedades de rango de datos externos, de conexión y de consulta. 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 la hay. 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 asociada la información de credenciales y 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 temas de ayuda correspondientes más importantes.
| Cuadro de diálogo o panel Rutas de comandos |
Pestañas y túneles | Tema de ayuda principal |
|---|---|---|
|
Orígenes recientes Datos>Orígenes recientes |
(Sin pestañas) Cuadro de diálogo delnavegador Túneles para conectarse> |
Administrar permisos y configuración del origen de datos |
|
Propiedades de la conexión O bien Asistente para conexión de datos Datos>Consultas & conexiones>Pestaña> Conexiones (clic con el botón derecho en una conexión) >Propiedades |
Pestaña Uso Pestaña Definición Pestaña Usado en |
Propiedades de conexión |
|
Consulta Propiedades Datos>Conexiones> existentes (clic derecho en una conexión) >Editar propiedades de conexión O bien Datos>Consultas & conexións | Pestaña Consultas> (clic con el botón derecho en una conexión) >Propiedades O bien Consulta>Propiedades O bien Datos>Actualizar todo>Conexiones (cuando se colocan en una hoja de cálculo de consultas cargada) |
Pestaña Uso Pestaña Definición Pestaña Usado en |
Propiedades de conexión |
|
Consultas & conexiones Datos>Consultas & conexiones |
Pestaña Consultas Pestaña Conexiones |
Propiedades de conexión |
|
Conexiones existentes Datos>Conexiones existentes |
Pestaña Conexiones Pestaña Tablas |
Conectar a datos externos |
|
Propiedades de datos externos O bien Propiedades de rango de datos externos O bien Datos>Propiedades (deshabilitado si no se coloca 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 derechos a Propiedades de consulta |
Administrar rangos de datos externos y sus propiedades |
|
Propiedades de> conexiónPestaña Definición>Exportar archivo de conexión O bien Consulta>Exportar archivo de conexión |
(Sin pestañas) Túneles a Cuadro de diálogo Archivo Carpeta Orígenes de datos |
Crear, editar y administrar conexiones a datos externos |
Conceptos básicos de las conexiones de datos
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 copiarlos repetidamente en el libro, lo que puede llevar mucho tiempo y es propenso a errores. Después de conectarse a datos externos, también puede actualizar automáticamente (o actualizar) los libros de Excel desde el origen de datos original cada vez que el origen de datos se actualice con nueva información.
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 incorporar datos externos en Excel, necesita acceso a los datos. Si el origen de datos externo al que desea tener 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 tenga abierto para tener 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 diagrama siguiente 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 acceder a los datos y recuperarlos de un origen de datos.
4. La información de conexión se copia de un archivo de conexión a 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.
Buscar conexiones
Para buscar archivos de conexión, use el cuadro de diálogo Conexiones existentes . (Seleccionar datos>Conexiones existentes). En este cuadro de diálogo, puede ver los siguientes tipos de conexiones:
-
Conexiones en el libro
Esta lista muestra todas las conexiones actuales del libro. La lista se crea a partir de conexiones ya definidas, creadas mediante el cuadro de diálogo Seleccionar origen de datos del Asistente para conexión de datos o a partir de conexiones que seleccionó previamente como una conexión en 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 la red como parte de la implementación de directivas de grupo de Microsoft Office o una biblioteca de SharePoint.
Editar propiedades de conexión
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 haciendo clic en Examinar para más para mostrar el cuadro de diálogo Seleccionar origen de datos y, después, haciendo clic en Nuevo origen para iniciar el Asistente para 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>&pestaña >Conexiones> (haga clic con el botón derecho en una conexión) >Propiedades) para controlar varias configuraciones para 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 cuadro de diálogo Propiedades de consulta cuando hay una consulta creada en Power Query (anteriormente denominada Obtener & transformación) 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 puede haberse utilizado 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 se utiliza siempre cuando se actualiza un origen de datos, haga clic en Intentar utilizar siempre este archivo para actualizar estos datos en la pestaña Definición . Al activar esta casilla, se asegurará de que todas las actualizaciones del archivo de conexión sean utilizadas siempre por todos los libros que usan ese archivo de conexión, que también debe tener esta propiedad establecida.
Administrar conexiones
Mediante el cuadro de diálogo Conexiones, puede administrar fácilmente estas conexiones, incluida la creación, edición y eliminación de ellas (seleccioneConsultasde datos> & pestaña >Conexiones> (haga clic con el botón derecho en una conexión) >Propiedades). Puede usar este cuadro de diálogo para realizar una de las siguientes acciones:
- Cree, edite, actualice y elimine las conexiones que están en uso en el libro.
- Compruebe el origen de los datos externos. Es posible que desee hacer esto en caso de que la conexión la haya definido otro usuario.
- Mostrar dónde se usa cada conexión en el libro actual.
- Diagnosticar un mensaje de error sobre conexiones a datos externos.
- Redirija una conexión a un servidor o un origen de datos diferente, o reemplace el archivo de conexión de una conexión existente.
- Facilite la creación y el uso compartido de archivos de conexión con los usuarios.
Compartir ODC y conexiones de consulta en archivos
Los archivos de conexión son especialmente útiles para compartir conexiones de forma coherente, haciendo que las conexiones sean más fáciles de detectar, ayudando a mejorar la seguridad de las conexiones y facilitando la administración de fuentes 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 puedan leer el archivo, pero solo los usuarios designados puedan modificarlo. Para obtener más información, consulte Compartir datos con ODC.
Uso de archivos ODC
Puede crear archivos de conexión de datos de Office (ODC) (.odc) conectándose a datos externos mediante el cuadro de diálogo Seleccionar origen de datos o mediante el Asistente para conexión de datos para conectarse a nuevos orígenes de datos. Un archivo ODC utiliza 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 concederles el mismo acceso que usted 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 necesario para tener acceso a los datos externos de su equipo.
Los archivos ODC son el método recomendado para conectarse a datos y compartirlos. 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 .
Uso de 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 se proporciona al crear un origen de datos. Los archivos de consulta son una forma tradicional de compartir consultas con otros usuarios de Excel.
Uso de archivos de consulta .dqy Puede usar Microsoft Query para guardar archivos .dqy que contengan 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.
Uso de archivos de consulta .oqy Puede guardar archivos .oqy para conectarse a los datos de una base de datos OLAP, en un servidor o en un archivo de cubo sin conexión (.cub). Cuando utiliza el Asistente para conexión multidimensional en Microsoft Query para crear un origen de datos para una base de datos o cubo OLAP, se crea automáticamente un archivo .oqy. Dado que las bases de datos OLAP no están organizadas en registros o tablas, no puede crear consultas ni archivos .dqy para tener acceso a estas bases de datos.
Uso de archivos de consulta .rqy Excel puede abrir archivos de consulta en formato .rqy para admitir controladores de origen de datos OLE DB que usen este formato. Para obtener más información, consulte la documentación del controlador.
Uso de archivos de consulta .qry Microsoft Query puede abrir y guardar archivos de consulta en formato .qry para utilizarlos con versiones anteriores de Microsoft Query que no puedan 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 la Ayuda de Microsoft Query.
Uso de 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.
Usar propiedades de datos externos
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 introducen en una hoja de cálculo. Al conectarse 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 dar formato y diseño a 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) reciben el mismo nombre que el nombre de archivo.
- Los rangos de datos externos de las bases de datos se nombran 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 nombran 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, MiTexto, MyText_1, MyText_2, etc.
Un rango de datos externos tiene propiedades adicionales (que no deben 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. Para cambiar estas propiedades del rango de datos externos, haga clic en Propiedades en el grupo Conexiones de la pestaña Datos y, a continuación, realice los cambios en los cuadros de diálogo Propiedades del rango de datos externos o Propiedades de datos externos .
|
|
|---|
Compatibilidad con orígenes de datos en Excel Services
Hay varios objetos de datos (por ejemplo, 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 los datos conectados en Excel Services. Al igual que con cualquier origen de datos externo, es posible que deba autenticar su acceso. Para obtener más información, vea Actualizar una conexión de datos externa en Excel. Paraobtener más información sobre las credenciales, consulte Configuración de autenticación de Excel Services.
En la tabla siguiente se resumen qué orígenes de datos son compatibles con cada objeto de datos en Excel.
|
Excel confidenciales objeto |
Creación de un Externa confidenciales alcance? |
OLE NOSQL |
ODBC |
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 (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í | |
| 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 |
Nota
Estos archivos, un archivo de texto importado mediante el Asistente para importación de texto, un archivo XML importado mediante una asignación XML y un archivo HTML o XML importado mediante una consulta web, no usan un controlador ODBC ni un proveedor OLE DB para establecer la conexión con el origen de datos.
Solución alternativa de Excel Services 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, un mapa XML o Microsoft Query.
Sin embargo, puede solucionar esta limitación usando una tabla dinámica para conectarse al origen de datos y, después, diseñar y distribuir la tabla dinámica como una tabla bidimensional sin niveles, grupos o subtotales, de modo que se muestren todos los valores de fila y columna deseados.
Componentes de acceso a datos ODBC y OLE DB
Hagamos un viaje por el carril 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 utilizar 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 a bases de datos (ODBC) o proveedores OLE DB, que son compilados y enviados por Microsoft o desarrollados por varios terceros. Al instalar Microsoft Office, se agregan controladores ODBC y controladores OLE DB adicionales al equipo.
Para ver una lista completa de los proveedores OLE DB instalados en el equipo, abra el cuadro de diálogo Propiedades de vínculo de datos desde un archivo de vínculo de datos y, después, haga clic en la pestaña Proveedor .
Para ver una lista completa de los proveedores ODBC instalados en su equipo, abra el cuadro de diálogo Administrador de base de datos ODBC y haga clic en la pestaña Controladores .
También puede usar controladores ODBC y controladores 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 acerca de cómo instalar estos controladores ODBC o controladores OLE DB, consulte la documentación de la base de datos o póngase en contacto con su proveedor de la misma.
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, el cual, 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:
- Asegúrese de que esté instalado el controlador ODBC adecuado en el equipo que contiene el origen de datos.
- Defina un nombre del origen de datos (DSN) con el Administrador del origen 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, haga clic en el botón Inicio y luego en el Panel de control. Haga clic en Sistema y mantenimiento y luego 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 distintas 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 la información de conexión en el registro, en un equipo específico, con un nombre definido por el usuario. Sólo puede utilizar orígenes de datos de máquina en el equipo en el que están definidos. Hay dos tipos de fuentes de datos de máquina: usuario y sistema. Solo el usuario actual puede usar los orígenes de datos de usuario y solo ese usuario puede verlos. Todos los usuarios de un equipo pueden usar los orígenes de datos del sistema y son visibles para todos los usuarios del equipo.
Un origen de datos de máquina es especialmente útil cuando se desea proporcionar mayor seguridad, ya que ayuda a garantizar que solo los usuarios que han iniciado sesión puedan ver un origen de datos de equipo, y un origen de datos de máquina no puede ser copiado por un usuario remoto en 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 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 a cualquier equipo con el controlador ODBC correcto, de modo que la aplicación pueda confiar en información de conexión coherente y precisa a todos los equipos que usa. O bien, puede colocar el origen de datos del archivo en un único servidor, compartirlo entre muchos equipos de la red y mantener fácilmente la información de conexión en un solo lugar.
Un origen de datos de archivo también puede ser no compartible. Un origen de datos de archivo que no se puede compartir 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 (como Excel) y el programa que permite el acceso nativo a los datos se denomina proveedor de base de datos (como Microsoft OLE DB Provider for 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 tener acceso a un origen de datos mediante el proveedor OLE DB de ese origen de datos. Puede crear la información de conexión mediante uno de los métodos siguientes:
- En el Asistente para 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, edítelo, que muestra el cuadro de diálogo Propiedades de vínculo de datos .