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.

Bases de datos de Access

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.

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.

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.

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.