Extracción de datos de un origen XML online en Microsoft Excel

 Hasta ahora hemos visto cómo usar la función SERVICIOWEB y XMLFILTRO de Microsoft Excel por separado.

Hoy veremos la potencia de unir ambas funciones.

Usaremos servicio web para recuperar un documento XML de una dirección web y posteriormente usaremos XML filtro para borrar un dato de el fichero XML recuperado.

Insertamos la función servicio web desde el asistente de funciones incluimos la dirección de Open Data que podéis ver a continuación.

=SERVICIOWEB("https://opendata.euskadi.eus/contenidos/ds_informes_estudios/dwc_dataset_103/opendata/eml.xml")

Una vez que pulsamos aceptar se mostraran los datos en la celda de la hoja de cálculo.

Para poder ver un poco mejor los datos será necesario modificar ancho y alto de la celda así como alineación tanto horizontal como vertical.

Aquí se muestra el resultado y la función en la barra de fórmulas.

A continuación en la celda B1 insertamos también con el asistente de funciones la función XMLFILTRO.

Esta función tiene dos parámetros el primero de ellos es el fichero XML y el segundo la ruta.

En este caso el fichero XML será el resultado de recuperar el documento XML de la dirección web con la función SERVICIOWEB.

Aquí podéis ver el fichero XML copiado en un editor de texto.

La moción que usaremos es la mostrada a continuación.

=XMLFILTRO(A1;"//dataset/title")

En ella buscamos dentro del dataset la primera etiqueta title.

Vemos la función en la barra de fórmulas.

En este esquema podéis ver cómo los datos llegan hasta la celda B1.

La ventaja de recuperar documento XML con la función servicio web es que solo con actualizar la hoja de cálculo tendremos los datos refrescados en tiempo real desde la web.

Una posible aplicación de este sistema es usar servicio web para realizar una llamada a una API que nos conecte a una web o base de datos y recuperar la información directamente en la hoja de cálculo.

Utilizar Xpath en Microsoft Excel con XMLFILTRO

Hoy traigo una demostración del uso de la función XMLFILTRO en Microsoft Excel para extraer datos de una celda en la que se ha pegado un fichero XML.

Partimos del ejemplo del fichero XML en el que guardo la información de algunos de los vehículos de mi garaje.

Lo copiamos y lo pegamos en la celda A1 de nuestra hoja de cálculo de Microsoft Excel.

El dato que deseamos obtener, es la marca del primer vehículo.

Como se puede ver en la barra de fórmulas, los datos están pegados en la celda.

En otro post veremos cómo obtenerlos mediante una función de Excel.

La función que usaremos para obtener la marca del vehículo es XMLFILTRO.

Esta función tiene dos parámetros.

Primero el documento XML, que en este caso será una referencia a la celda A1.

Y segundo, la ruta en formato XPATH del dato a extraer.

La ruta es la sucesión de etiquetas que debemos recorrer para obtener el dato buscado.

En este caso la ruta es //garaje/vehiculo/marca

Al aceptar la función veremos que en la celda B2, ahora aparece la marca del primer vehículo "Ferrari".

En esta imagen vemos la función completa en la barra de fórmulas.

La función es 

=XMLFILTRO(A1;"//garaje/vehiculo/marca")

Pronto veremos cómo usar esta función junto con la función SERVICIOWEB lo que nos va a permitir acceder a datos de ficheros disponibles online.

Tipos de ficheros XML

Tras haber introducido ayer el formato XML, hoy os traigo unos ejemplos de implementaciones reales de este tipo de ficheros, para entender su utilidad.

Partimos de la idea que el formato XML permite definir cualquier estructura de etiquetas necesaria para almacenar información.

C9mo ejemplo, un XML que podría almacenar la información de los vehículos que guardo en mi garaje. ;)

Sin embargo también es posible definir un estándar a cumplir para un determinado protocolo.

Por ejemplo, uno de los lenguajes basado en XML es el omnipotente HTML, que es la base de todas las páginas web.

Un campo en el que este tipo de ficheros es muy usado es en el de intercambio de información.

En el siguiente ejemplo, un fichero en formato ONIX el cuanse usa para intercambiar información sobre libros.

La información de este formato se puede consultar en la web de Editeur.

Otro ejemplo más es el formato bdd facturas electrónicas FacturaE.

Y finalmente, quizá la mayor implementación de un fichero de tipo XML sean todos los formatos del estándar EDI que permite el Inter de pedidos, albaranes, facturas, etc.

Entre ellos podremos encontrar los ficheros DESADV, ORDERS, etc.

Introducción al formato XML

XML (Extensible Markup Language) es un lenguaje de etiquetas de propósito general utilizado para almacenar y transmitir datos en formato legible por humanos, ampliamente usado.

Este lenguaje permite definir las categorías de los datos mediante etiquetas.

Existen muchos lenguajes basados en XML, pero el más utilizado es sin duda el lenguaje HTML.

XML fue definido por el consorcio W3C.

Se puede encontrar información y ejemplos en la web.

https://www.w3schools.com/xml/

Como ejemplo muestro un fichero XML.

Para que resulte más claro muestro el fichero con márgenes para facilitar la identificación de información.

En Microsoft Excel existen dos funciones especialmente relacionadas con este formato SERVICIOWEB y XMLFILTRO.

Existe una opción de importación de ficheros XML en la pestaña "Desarrollador".

Y finalmente, es posible importar ficheros XML mediante Power Query.

Powertoys v0.49

Esta semana se actualizaron las PowerToys de Microsoft.

Entre las novedades, rediseños de pantallas y una nueva opción para encontrar el puntero del ratón.

Esta versión tiene el número 0.49.1

La principal novedad es una opción que al pulsar la tecla CTRL dos veces oscure e toda la pantalla excepto un círculo para ayudar a localizar el puntero del mouse.

Hay que decir que ya existía una opción similar en Windows, por lo que esta opción no representa ninguna gran novedad.

La diferencia con la opción existente es que permite indicar que no funcione cuando se está ejecutando un juego.

En la imagen muestro el aspecto de la pantalla al pulsar dos veces la tecla CTRL.

También se ha modificado power rename.

En este caso se trata de un rediseño.

También hay cambios en la opción de silenciar videoconferencia.

Y en la herramienta de cambio de tamaño se imágenes.

Siendo agora la pantalla más intuitiva.

Además se han aplicado diversas correcciones de errores, por ejemplo en Fancy Zones.

Función servicioweb en Microsoft Excel

 

Hoy os presento una función que permite recuperar datos desde un servicio web, como puede ser una API.

Esto puede ser muy útil si se dispone de una API que de acceso a los datos de un ERP, o una web.

Se trata de la función SERVICIO WEB, que se puede encontrar en el apartado WEB del asistente de funciones.

Tiene un único parámetro que es la URL del servicio web.

En el caso del ejemplo se usa la URL https://datos.gob.es/es/catalogo/a09002970-incendios-forestales-por-comarca-en-catalunya-anos-2011-2020.xml

También se puede usar directamente desde la barra de fórmulas.

Una vez que introducimos la url en el asistente de funciones, podemos ver parte del resultado.

Al aceptar se mestra el resultado en la celda.

Para poder leer mejor los datos aplicaremos la opción ajustar todo.

Y ya tendremos los datos en la celda.

Esta función se complementa a la perfección con la función XMLFILTRO.

Nombres de ficheros prohibidos en Microsoft Windows

En Windows existen ciertas palabras reservadas que representan dispositivos hardware y que no se pueden usar como nombres de fichero.

Las siguientes palabras, entre otras, no se pueden usar para nombrar ficheros, cualquiera que sea la extensión que tengan.

CON, PRN, AUX, NUL, COM1, LPT1, etc

Podéis encontrar información adicional en este enlace.

https://www.adslzone.net/2018/11/07/nombres-archivo-prohibidos-w10-historia/

Vamos a crear un fichero de texto e intentar asignarle algunos de los nombres reservados, para comprobar si funciona.

Con el botón derecho del ratón, creamos un nuevo fichero de texto.

Una vez creado, intentamos darle un nombre.

En este caso intentaremos llamarle "LPT1.txt".

En el momento que pulsamos la tecla Enter se nos muestra una ventana emergente.

Con el mensaje "El nombre del dispositivo no es válido".

Al darle a aceptar, o pulsar Enter, vuelve a mostrarse el nombre que tenía el fichero con anterioridad.

Probamos con otro nombre, en este caso "CON.txt".

El resultado es el mismo.

Debemos conocer esta limitación y entender porqué no se crea el fichero, ya que podría darnos verdaderos dolores de cabeza intentar saber porqué no se está creando el fichero.

Imaginemos que tenemos un programs que nos pide el nombre del fichero que generará con la copia de informe que debemos presentar a nuestro jefe.

Le decimos que se llame CON.txt, y le pedimos que lo guarde en nuestro pendrive.

¡Cuál puede ser nuestra sorpresa al descubrir que no hay ningún fichero!