Acceso a datos externos

Introdución

Explicarase como traballar desde a folla de cálculo Excel con datos externos a un libro de Excel con datos creados con outras aplicaciñons distintas (bases de datos, etc.). Tamén se estudia o modo de acceder a datos que proveñan de ficheiros de texto, de bases de datos de Access ou de páxinas de Internet.

Excel permite acceder a un gran número de formatos de datos distintos. Cando accedemos e traballamos cos datos desde Excel podemos facer uso de todas as súas características para obter máis información destes datos: facer cálculos, amosar información nun gráfico, filtrar os datos, etc.

Acceso a ficheiros de texto

Os ficheiros de texto son un medio de compartir información moi empregado entre aplicacións informáticas e entre usuarios, xa que son ficheiros que se poden ler desde varios programas.

Excel permite acceder os datos almacenados en ficheiros de texto dun xeito moi sinxelo; pero a información que hai nestes ficheiros deberá estar delimitada dalgunha forma para que teña sentido na folla de cálculo.

Obter e transformar datos

Grupo de comandos Obtener y transformar datos da lapela Datos da cinta de opcións

No grupo de comandos Obtener y transformar datos dispoñemos de varias opcións para traballar con datos externos, importándoos desde distintas fontes.

Desde o desplegable de Obtener datos podes ver todas as opcións disponibles para a obtención de datos externos:

• desde outro libro de Excel
• desde un ficheiro de texto
• desde un ficheiro XML
• desde unha base de datos de Access ou de SQL Server
• desde a web
• desde unha fonte ODBC ou OLEDB
• ...

Neste apartado estamos tratando as opcións para acceso a ficheiros de texto:

A continuación indicaremos a orixe dos datos (o ficheiro de texto o que imos acceder para obter os datos).

Por exemplo, poderiamos importar un ficheiro clientes.txt:

Cargar ou transformar datos

Desde as opcións desta fiestra indicaremos como debe importarse a información almacenada no ficheiro de texto. O máis importante neste caso é que a información do ficheiro de texto esté delimitada dalgun xeito (o máis habitual é o uso de coma, ou de punto e coma), para que Excel poida situala en distintas columnas da folla de cálculo.

No exemplo amosado están delimitados por ; coma se pode ver no ficheiro de texto:

Outra opción é que estén delimitados por unha anchura fixa:

Desde a lista Delimitador e onde se pode indicar cal é. Excel intentará seleccionar o delimitador máis axeitado pero pode ser cambiado.

PREGUNTA. Podes utilizar o caracter de : para separar os distintos campos de información no ficheiro de texto?
RESPOSTA. Si, indicando o caracter separador ao importar o texto.

Desde a lista Detección do tipo de datos pódese indicar se Excel establecerá o formato dos datos para cada campo. Isto é, se indicará que formato de numero, data, texto ou outro se aplicará. Pódese seleccionar se o tipo de datos será establecido baseandose nas primeiras 200 filas, en todas as filas do ficheiro ou non se detectará de xeito automático.

Cargar ou transformar datos

Finalmente:

• o botón Cargar inserta os datos nunha folla de cálculo nova
• o botón Transformar datos abrirá o Editor de Power Query onde poderemos crear unha consulta para recuperar exactamente os datos que desexamos ou aplicar algún tipo de transformación os datos.

Despregando a lista do botón Cargar poderemos seleccionar Cargar en que amosa un cadro para indicar a celda que actuará como esquina superior esquerda para o rango de datos que se vai crear. Tamén poderemos establer:

• que se poñan os datos na celda que indiques da folla actual, noutra folla de cálculo ou nunha nova folla de cálculo
• que os datos se insiran coma unha táboa, coma unha táboa dinámica ou cuma unha gráfica dinámica

Coma os datos estaban separados por ; no ficheiro de texto, a importación desde este ficheiro fíxose correctamente, situandoos axeitadamente nas columnas da folla de cálculo:

Excel coloca os datos como unha táboa (habilitando a lapela Diseño de tabla polo que está co formato axeitado e cun filtro na súa fila de encabezado

Táboa.
Unha serie de filas que teñen datos relacionados. Nunha táboa ou lista, as distintas filas da folla de cálculo son os distintos rexistros ou fichas, mentres que as columnas son os distintos campos.

Consultas e conexións

Na dereita amósase o panel Consultas y conexiones que permite administrar e traballar cos elementos. Este panel amosa as consultas que se utilizaron no libro actual para amosar datos nel.

Se pulsas nunha das consultas que se amosan neste panel situaraste na folla e celdas onde están os datos. Desde o menú contextual das consultas podes actualizar os datos ou cambiar o xeito como están cargados estes datos:

• como unha táboa
• como unha táboa dinámica
• como unha gráfica dinámica

Se situas o rato sobre unha das consultas amosarase unha fiestra con información sobre a mesma, coma poden ser os datos importados, o ficheiro de orixe ou a data e hora da última actualización.

PISTA
Se pechas este panel podes volver a amosalo desde o botón Consultas y conexiones da lapela Datos da cinta de opcións.

Se eliminas unha consulta deste panel, os datos que se importaron da consulta non se poderán actualizar (de xeito similar a como veremos máis adiante coa opción Desvincular, pero eliminando ademáis a consulta.

PREGUNTA Excel indica dalgún xeito se os datos importados cambiaron?
RESPOSTA. Non. Podes actualizar os datos para ver os cambios, pero Excel non nos indica se foron modificados os datos orixen.

Acceso a ficheiros dun cartafol

Podemos elixir un cartafol con varios ficheiros a importar. Esta opción e interesante. Por exemplo, poderíamos ter varios ficheiros de texto ou CSV con información por meses, anos, etc. Para importar todos nunha sola operación, poderíamos gardalos nun mesmo cartafol e así importalos.

Acceso a Bases de datos

Si temos moita información e ademáis está relacionada entre sí, non adoitamos ter os datos nun ficheiro de texto; empregaremos un sistema xestor de base de datos, que é un programa para crear e xestionar esa información.

Access

Entre outros, podemos traballar coa información a través de programas de bases de datos coma Microsft Access (pertencente a Microsoft Office).

Para acceder a información que temos nunha base de datos de Microsoft Access:

Desde Access 2007, os ficheiros de bases de datos teñen a extensión accdb (Access Database). Tamén é posible importar anteriores en formato mdb. Finalmente, tamén e posible importar datos de ficheiros accde, que son ficheiros de bases de datos de Access en modo de só execución (en anteriores versións denominados mde). Estes ficheiros non permiten crear ou modificar o deseño de formularios e informes nin ver ou modificar o código de Visual Basic para Aplicaciones (VBA), ainda que sí ver os formularios e informes e executar o código.

O proceso de importación será semellante o visto para ficheiros de texto, escollendo o ficheiro correspondente á base de datos.

Na ventana amosanse as táboas e consultas que ten a base de datos. A continuación seleccionamos onde se atopan os datos que desexamos importar a Exccel.

.

Despois de premer Cargar, a importación lévase a cabo:

Coma os datos xa estaban nunha táboa de Access, organizados de xeito estruturado, Excel sabe como distrubuilos en filas e columnas. Igual que antes, Excel crea unha táboa para que sexa máis sinxelo operar con eses datos. En todo caso, se non precisas unha táboa, podes convertir o conxunto de celdas que forman a táboa nun rango de datos premendo en Convertir en rango.

Alternativa con copiar e pegar

Outra forma de empregar os datos de Access en Excel é utilizando as opcións de Copiar e Pegar:

1. amosamos en Access os datos en forma de folla de datos (cos datos dunha táboa, dunha consulta ou dun formulario)
2. seleccionamos os rexistros desexados e os copiamos
3. accedemos a Excel e os pegamos

Unha última opción e desde Access exportar obxectos a Excel. Deste xeito, Access gardará os datos da táboa, consulta, formulario ou informe seleccionado coma un documento de Excel (é dicir, con extensión xlsx), puidendo abrir desde Excel este documento e ver os datos

Ademáis, desde Access tamén podes vincular os datos que tes nun libro de Excel, de forma que os verás en Access coma se se tratara dunha táboa. Desta forma, se modificas os datos desde Excel, tamén se verán reflexados estes cambios desde o vínculo existente en Access.

Ten en conta, que cando importamos bases de datos ou ficheiros de texto, os datos cambiados en Excel non se reflexarán nos datos orixinais.

PREGUNTA. Precisas expotar unha táboa de datos desde Access para poder ver a información que contén desde Excel?
RESPOSTA. Non. Podes importar unha base de datos de Access directamnte desde Excel.

SQL Server

Excede do que imos cubrir, pero deixamos para autoaprendizaxe dúas referencias:

• Cómo Exportar de SQL Server a Excel de dous xeitos
• Con exemplos prácticos

Analysis services

Novamente deixanse duas referencias:

• Connect with Excel
• Connect Excel to an Azure Analysis Services server

Acceso desde Azure ou servizos en liña

Azure é unha plataforma de aplicacións e servizos empresariais na nube de Microsoft. Se a nosa empresa ten contratado este tipo de servizos, importar datos desde ahi pode ser necesario.

Coa opción desde os servizos en liña, podes obter datos abertos desde Facebook, por exemplo.

Acceso desde outras fontes

Hai outras fontes ou orixes de datos. Por exemplo, existe a posibilidade de obter datos desde unha web ou bases de datos máis antigas que sigan o protocolo ODBC.

Acceso a datos da web

Podemos acceder a datos provintes de páxinas web:

• de Internet
• da nosa Intranet

No grupo Obtener y trasformar datos, atópase a opción Desde la web:

Ahora debemos introducir a páxina web que ten os datos a importar. Esta debe amosar os datos en forma de táboas para que Excel os recoñeza e os importe correctamente na folla de cálculo.

A opción Uso avanzado permite algúns parametros coma un ou varios encabezados da solicitude HTTP ou o tempo de espera do comando.

Probaremos a recoller datos da web do INE (https://www.ine.es/censos2011_datos/cen11_datos_inicio.htm):

Excel analiza a web para identificar as táboas e as carga, para que escollamos a que nos interesa:

Escollendo a táboa desexada

A importación levarase a cabo premendo en Cargar:

Autoexercicio. 
Proba a importar os gañadores do Oscar a mellor película:
https://es.wikipedia.org/wiki/Anexo:%C3%93scar_a_la_mejor_pel%C3%ADcula
Para a mellor visualización, pode ser precisión facer transformacións con Power Query(explicado en seccións vindeiras).

Actualizar os datos

Se o ficheiro de texto ou a base de datos orixinal se modifican e posible que desexes ver estas modificacións actualizadas na folla de Excel.

A través do grupo de comandos Datos externos de tabla na lapela contextual Diseño de tabla temos sempre a posibilidade de actualizar os datos importados tiveran ou non algunha modificación.

PISTA.
Os controis para actualizar e ver as propiedades do rango de datos que están neste grupo de comandos Datos externos están tamén disponibles desde a lapela Datos no grupo de comandos Consultas y conexiones; e tamén desde a lapela contextual Consulta.
Isto é así porque Excel garda no libro de traballo a información sobre o ficheiro (e a consulta no caso de utilizar algunha) que foi proporcionada de xeito que poidas acceder de novo a eles cando desexes actualizalos.

Cando pulsamos no botón Propiedades de rango de datos poderemos establecer no cuadro de diálogo algunhas das características que afectan ó modo en que Excel traballa cos datos externos.

Por exemplo, desde o apartado Diseño y formato de datos podes activar distintas opcións que inflúen no aspecto dos datos importados, coma:

• se se deben incluir número de fila nos distintos rexistros
• se se debe axustar o ancho das columnas ó dato máis ancho
• se se debe manter o formato das celdas

As opcións da parte inferior do cadro indican o comportamente ó actualizar os datos desde a base de datos no caso de que cambie o número de rexistros.

Pulsando no botón Propiedades de conexión na parte superior dereita:

Neste cadro de diálogo tes máis opcións sobre a conexión cos datos externos e o seu comportamento respecto á actualización de datos.

PISTA.
O cadro Propiedades de los datos externos tamén é accesible desde o botón Propiedades da lapela Datos.

PISTA.
Tamén aparece o cadro Propiedades de la consulta
1. Desde o botón Propiedades da lapela Consulta
2. Desde a opción Propiedades de conexión do menú do botón Actualizar na lapela Datos ou Diseño de tabla

Posibilidades a considerar:

• Se desexamos que os datos importados se actualicen cada certo período de tempo activaremos a casilla Actualizar cada indicando os minutos.
• Con Actualizar al abrir el archivo os datos actualizaranse desde o ficheiro externo ó abrir o libro de Excel
• Se activamos esta última opción, a casilla Quitar datos del rango de datos externo antes de guardar el libro fai que se garde no libro só a definición da consulta pero non os datos externos

Na lapela Definición podes ver información sobre a conexión e a súa orixe:

Na lapela Usada en indica en qué lugar do libro de Excel estanse visualizando os datos externos:

Finalmente se pulsamos no botón Desvincular eliminarase a conexión co rango de datos externos, permanecendo os datos na folla de Excel; así teremos os datos na folla de cálculo dun xeito independente. Excel non volverá a actulizar os datos externos xa que estarán almacenados coma un rango estático de datos.

Combinar consultas

Permitenos combinar varias consultas para obter un conxunto de datos máis complexo. Poderemos ademáis anexar datos a outros xa importados.

Para poder realizar unha consulta de combinación e preciso que as consultas implicadas teñan unha columna en común.

Iniciar editor de Power Query

Permitiranos crear consultas: é a ferramenta que nos da esa potencia adicional á simple importación de datos permitindo a transformación.

Outra forma de acceder ó editor é pulsando no botón Transformar datos cando esteamos a facer unha importación.

Por exemplo, importando unha base de datos Access de clientes e premendo nese botón:

Esta ferramenta dispón dunha cinta de opcións con lapelas ó estilo do propio Excel. Permite modificar ou transformar de distintos xeitos este conxunto de datos que temos seleccionado o elixir a táboa Clientes da base de datos.

Cando consigamos o conxunto de datos desexado premeremos no botón Cargar y cerrar.

Entre as opcións posibles para escoller o conxunto de datos (sen que afecte a orixe dos datos):

• usar os filtros na primeira fila que serve de encabezado
• eliminar columnas que non precisemos (botón Quitar columnas)
• cambiar o nome das columnas (botón dereito no encabezado e escoller Cambiar nombre
• mover unha columna de lugar (arrastrando co rato)
• limpar os datos. Por exemplo, onde aparece NULL (lapela Transformar, botón Reemplazar los valores del grupo de comandos Cualquier columna
• agregar columnas con datos calculdas. (lapela Agregar columnas)

PISTA
Cada vez que se fai unha operación, queda reflexada no panel da dereita PASOS APLICADOS, permitindo fácilmente revertila de ser preciso.

PISTA
En ocasións os datos que se están importando inclúen como primeira fila xustamente os encabezados de columna (moi típico en ficheiros de texto plano ou CSV)
Para indicar que son encabezados e non datos, utilizamos o botón Usar la primera fila como encabezado da sección Transformar.

PREGUNTA. Os datos dunha consulta actualízanse automáticamente se cambian na orixe dos datos?
RESPOSTA. Non se actualizan. É necesario pedir a actualización desde Excel explícitamente.

Resumo

Pódense utilizar as distintas opcións do grupo Obtener y transformar datos para amosar en Excel datos creados noutras aplicacións.

Para importar en Excel esta información que tes en ficheiros de texto, os datos teñen que estar estruturados dalgún xeito para que Excel poida saber como colocalos na folla de cálculo. Para iso utiliza un caracter denominado delimitador para separar os distintos campos, ou alinea os campos en columnas facendo que cada campo teña un ancho fixo.

Para importar táboas dunha base de datos Access, os datos xa están estructurados nos distintos campos das táboas. Porén, o unico que precisaremos e indicar que táboa contén os datos.

Podes realizar varias accións sobre os grupos importados a Excel desde o grupo de comandos:

• Consultas y conexiones da lapela Datos
• Datos externos de la tabla da lapela contextual Diseño de la tabla, se os datos foron importados a unha táboa de Excel

Entre outras:

• Actualizar os datos importados
• Ver ou cambiar algunahas propiedades do rango de datos importados
• Desvincular. Este botón da lapela Diseño de tabla elimina a conexión co rango de datos externo, permanecendo os datos na folla de Excel (facendo que os datos queden de xeito indenpendente)

Para acceder a datos que proveñan dunha páxina web utilizamos o botón Desde la web do grupo de comandos Obtener y transformar datos. Así poderemos seleccionar as táboas da web que teñen a información que importaremos a Excel.