JASoft.org

El blog de José Manuel Alarcón Aguín. Programación web y mucho más...

MENÚ - JASoft: JM Alarcón

Cómo procesar y tratar datos JSON con Excel

Hace 15 años parecía que el futuro del intercambio de datos pasaba por XML. Este lenguaje de marcas permitía definir estructuras de datos todo lo complejas que necesitáramos, con varios niveles de estructura anidados, y de manera sencilla pues está basado en texto. De hecho recuerdo que cuando apareció la plataforma .NET lo que más destacaban en la documentación era el soporte para XML (hasta el propio nombre se derivaba en cierto modo de que estaba preparado para la Red gracias a este soporte).

Desde entonces ha llovido mucho, y las cosas han cambiado mucho también. En la actualidad existe un consenso bastante amplio en que el formato de intercambio de datos por antonomasia es JSON: JavaScript Object Notation. Este formato, basado en sintaxis JavaScript, tiene todas las ventajas del XML como el poder describir estructuras todo lo complejas que queramos, pero añade varias ventajas, como que es más fácil de leer y escribir por los humanos y, sobre todo, que es mucho más compacto para ser transmitido por la Red. Además, si consideramos su uso en la Web, el consumo de datos JSON desde una aplicación escrita en JavaScript es directo. No podría ser más cómodo.

Si necesitamos tratar información que está en formato JSON desde una herramienta más convencional como una hoja de cálculo o una base de datos, es cuando podemos tener más dificultades. Este tipo de herramientas están pensadas para manejar datos de forma tabular, por lo que acceder desde ellas a estructuras complejas, con datos anidados dentro de otros datos, etc... puede ser complicado.

Microsoft Excel es una herramienta que la mayor parte de la gente desaprovecha. Una gran mayoría de usuarios la usa de una manera muy básica, pero realmente para mi (y para mucha otra gente) es la verdadera estrella de la suite de Office, y la que de verdad marca una diferencia brutal entre Office y otros conjuntos de herramientas ofimáticas. Sus características avanzadas son bastante desconocidas, pero lo cierto es que se pueden hacer verdaderas "virguerías" con ella.

En el siguiente vídeo te cuento cómo puedes usar Excel para beber datos en formato JSON directamente desde la fuente (o sea, desde Internet por ejemplo), cómo puedes decidir exactamente convertirlos en una tabla, cómo los puedes expandir y transformar y, finalmente, cómo utilizarlos desde una hoja de cálculo para filtrarlos, analizarlos, graficarlos o introducirlos en otros sistemas como una base de datos. Se puede conseguir gracias a Power Query y un poco de habilidad y es algo que te puede resultar muy útil en muchas ocasiones.

Ahí va el vídeo:

IMPORTANTE / 2020: en este vídeo utilizo la API de Open Clipart para obtener unos valores JSON para hacer la prueba e importar los datos. Esta API ha sido eliminada de la página por lo que ya no te funcionará. No obstante todo lo demás sigue siendo válido. Simplemente prueba con otra API o con cualquier documento JSON que tengas a mano.

¡Espero que te resulte útil!

¿Este post te ha ayudado?, ¿has aprendido algo nuevo? Pues ¡ayúdame a ayudar a otros! No te costará nada y puedes conseguir premio. Es muy importante. Pulsa para saber más...
José Manuel Alarcón José Manuel Alarcón
Fundador de campusMVP.es, el proyecto de referencia en formación on-line para programadores en lengua española. Autor de varios libros y cientos de artículos. Galardonado como MVP de Microsoft desde 2004. Gallego de Vigo, amante de la ciencia y la tecnología, la música y la lectura. Ayudando a la gente en Internet desde 1996.
Descarga GRATIS mi último libro (no técnico): "Tres Monos, Diez Minutos".
Banner

Comentarios (8) -

Hola José Manuel,

Estupendo artículo y video. Muy claro y explicativo. Muchas gracias por la información.

También se puede abrir un fichero json desde excel directamente. En la pestaña 'Datos' y en el botón 'Consultas', le das a abrir un fichero de texto. Allí le pones que la extensión a buscar sea cualquiera para que te mestre las .json. Importas el fichero y enseguida se te abre el mismo diálogo que muestras en tu vídeo.

Un saludo,
Rafael

Responder

Hugo Alvarez

Jose Manuel,

gran trabajo y muy claro. Te quiero pedir una ayuda adicional. Tengo la siguiente URL de ejemplo:

api.tester.cl/.../procurements.json

Donde xxxxxx es un parametro que conozco y tengo en una celda especifica de Excel (Por ejemplo Hoja1!A1)

Como podria usar este valor variable de la celda Hoja1!A1 para hacer consultas parametrizadas?

Responder

by Jose M. Alarcon

Hola Hugo:

Para variar dinámicamente el origen de los datos, que es lo que me estás indicando, es decir, para poder indicar qué URL quieres utilizar como origen para que no sea una constante, como la que yo he indicado, sino una que cambie en función de lo que necesites, no te queda más remedio que yo sepa que usar VBA, o sea, programar.

Si no me equivoco en el código de un botón o lo que fuera que quieres usar para lanzar la carga de los datos desde la web tienes que poner algo como esto:

With ActiveSheet.QueryTables(1)
    .Connection = "URL;" & laURLQueNecesitas
    .Refresh
End With

Pero yo no soy un experto en Excel, solo voy aprendiendo (y compartiendo) lo que me interesa específicamente para algo cuando lo necesito.

Espero que te sirva.

Saludos.

Responder

Ronald Acevedo

Hola, estoy en busca de una respuesta, soy nuevo en json, la consulta es que estoy ingresando datos a una lista de SharePoint ejemplo 120500 y deseo que el formato de esa columna quede de esta forma K120+500. como se observa estoy incluyendo la letra K y el signo +. es como el formato de una celda que se manejaría de esta forma "K"###"+"###.

Gracias

Responder

Patricio Brown

Hola José Manuel,
antes que todo felicidades por el artículo, me sirvió mucho.
Sin embargo hay algo con lo que no puedo dar:
necesito incluir en la tabla un elemento anterior al payload, digamos el numero de resultados (161) en cada fila de la tabla generada.  Es posible?

muchas gracias de antemano

saludos,

Patricio

Responder

Soy nuevo en el tema de JSON. He segudo los pasos del video perome sale algo distinti a lo que indicas. En la consulta me salen lo que seria los nombres de las columnas como LIST y los valores de cada columna en vez de record como en tu ejemplo, salen tambien como LIST. El ditor de la consulta me sale asi:
success         Success
next_page    TRUE
Limit              1000
records         1000
header           List
csv                  List

header son los nombres de los campos y list los datos de cada campo. Si pulso en list me salen las filas de los datos perono tipo record, sigue poniendo lis y veo los datos reales pero solo en una columna.

Responder

by Jose M. Alarcon

Hola Gerardo:

Seguramente tu estructura JSON es diferente. Tienes que ver cómo es y aplicar los pasos que correspondan según el caso. El vídeo muestra un ejemplo concreto. No te puedo ayudar mucho más, lo siento. Lo que te puedo decir es que funcionar, funciona 😊

Saludos.

Responder

Norma Cruz

Hola tengo una macro que ejecuto con Outlook para crear tickets para ciertos correos, hago  Rest API Post y leo el resultado con GET, la primera vez funciona bien, pero la segunda vez me envía el error de “403 – Invalid CSRF token “null” was found on the request parameter “_csrf” or header X-CSRF-TOKEN”, me podría ayudar por favor a saber cuál es y cómo resolver el problema? este es el codigo:
            Set objRequest = CreateObject("MSXML2.XMLHTTP")
            strUrl = "http://160.118.117.80:8080/oo/rest/executions"        
            ' Realiza la conexión del API para realizar el requerimiento de creacion de ticket
            With objRequest
                .Open "POST", strUrl, blnAsync
                .SetRequestHeader "Authorization", "Basic SU5DSURFTlQtSFBPTy1NWDrCoUNyM2F0ZVRpY2szdCEhLg=="
                .SetRequestHeader "Content-Type", "application/json"
                .Send Body
                'Proceso para esperar la respuesta
                While objRequest.readyState <> 4
                    DoEvents
                Wend
                strResponse = .ResponseText
            End With
            PosIni = InStr(strResponse, "executionId") + 14
            Rcode = Mid(strResponse, PosIni, 9)
            Debug.Print strResponse
            
            rstatusr = "RUNNING"
            While rstatusr = "RUNNING"
                For i = 1 To 2000
                    ncont = i
                Next i
                Call GetSts
            Wend

Sub GetSts()
            'Realiza la conexión del API para verificar el status de creacion de ticket (get)
            strUrl = "http://160.118.117.80:8080/oo/rest/executions/" & Rcode & "/execution-log"
            blnAsync = True

            With objRequest
                .Open "GET -H", strUrl, blnAsync
                .SetRequestHeader "Authorization", "Basic SU5DSURFTlQtSFBPTy1NWDrCoUNyM2F0ZVRpY2szdCEhLg=="
                .Send
                'Proceso para esperar la respuesta
                While objRequest.readyState <> 4
                    DoEvents
                Wend
                strResponse = .ResponseText
                rstatus = Mid(strResponse, PosIni + 9, 9)
            End With
            Set objRequest = Nothing
End Sub

Responder

Agregar comentario