Estrarre JSON in Tabella L’argomento è risolto

Non è propriamente un programma di BI, ma nelle sue versioni più recenti anche Excel può fare grandi cose!

Moderatore: Utilizzo_prof_Excel

Rispondi

Mr. Table
Messaggi: 4 | Topic creati
Iscritto il: dom 6 nov 2022, 8:51

Estrarre JSON in Tabella

Messaggio da Mr. Table »

Ciao a tutti,
ho quotidianamente dei file JSON nuovi che metto in una cartella.

Mia intenzione è quella di automatizzare l' estrazione di alcuni dati in una tabella excel con power query.


Mi servirebbe però un aiuto perchè il file JSON ha dati molto annidati e non riesco ad estrarli.
Vi lascio un zip con dentro due file JSON come esempio ed un file excel con un esempio di tabella che vorrei estrarre.

Grazie a chi abbia voglia e capacità per aiutarmi e spiegarmi come fare.

Grazie!

JSON.zip
Esempi + Tabella
(10.17 KiB) Scaricato 18 volte


Avatar utente

Enrico Galli
Messaggi: 890 | Topic creati
Iscritto il: dom 28 giu 2020, 19:03
Luogo: San Giovanni in Persiceto (BO)
Ringraziato: 325 volte
Contatta:

Estrarre JSON in Tabella

Messaggio da Enrico Galli »

Ciao. In Power Query non ci provo neanche a farlo, ma mi sono voluto esercitare con Python, e se mai potrà essere utile, posto qui il risultato

Codice: Seleziona tutto

import pandas as pd
import numpy as np
import json
import glob

# Creo una lista con tutti i json nella cartella
path = 'C:/Users/Enric/OneDrive/MondoBI/quesiti/json/' # Percorso base cartella
json_files = glob.glob(path + '*.json')


# Funzione per trasformare i json in dataframe
def fxJson(filepath):
    f = open(filepath)
    data = json.loads(f.read())

    cols_to_delete = ['measureType', 'main peak amplitude unit', 'main peak amplitude unit mode',
                     'main peak frequency unit', 'overall unit', 'overall unit mode', 'setup max frequency value',
                     'setup max frequency unit', 'setup high-pass frequency value', 'setup high-pass frequency unit',
                     'setup No. of lines', 'setup No. of averages']

    x = data.get('projects')[0]
    cod = list(x)[0]

    dfx = pd.json_normalize(pd.DataFrame(x.get(cod)[0])['POINT 2X'], sep = ' ', )
    dfx.drop(columns = cols_to_delete, inplace=True)
    dfx.columns = 'POINT 2X ' + dfx.columns
    dfx.index = [cod]

    dfy = pd.json_normalize(pd.DataFrame(x.get(cod)[1])['POINT 2Y'], sep = ' ', )
    dfy.drop(columns = cols_to_delete, inplace=True)
    dfy.columns = 'POINT 2Y ' + dfy.columns
    dfy.index = [cod]

    dfz = pd.json_normalize(pd.DataFrame(x.get(cod)[2])['POINT 2Z'], sep = ' ', )
    dfz.drop(columns = cols_to_delete, inplace=True)
    dfz.columns = 'POINT 2Z ' + dfz.columns
    dfz.index = [cod]

    df = pd.concat([dfx, dfy, dfz], axis = 1)
    return df

# Accodamento di tutti i dataframe
df_list = []
js = pd.DataFrame()

for file in json_files:
    js = fxJson(file)
    df_list.append(js)

df_finale = pd.concat(df_list)

# Salvataggio in Excel
df_finale.to_excel(
    path + 'output_json.xlsx',
    index=True
)
Allegati
output_json.xlsx
(9.29 KiB) Scaricato 20 volte
Enrico Galli
Link utili: I nostri tutorial | Come inserire: Immagini - Codice - Risolto
Se il forum ti è stato utile, considera di supportarlo con una libera donazione
Avatar utente

Enrico Galli
Messaggi: 890 | Topic creati
Iscritto il: dom 28 giu 2020, 19:03
Luogo: San Giovanni in Persiceto (BO)
Ringraziato: 325 volte
Contatta:

Estrarre JSON in Tabella

Messaggio da Enrico Galli »

Anzi ho detto una bugia :lol: ci ho provato ed ecco il risultato:

Funzione per trasformare i json:

Codice: Seleziona tutto

// fxJson
(jsonfile) =>
let
    Origine = Json.Document(jsonfile),
    projects = Origine[projects],
    projects1 = projects{0},
    #"1" = projects1,
    #"Conversione in tabella" = Record.ToTable(#"1"),
    #"Tabella Value espansa" = Table.ExpandListColumn(#"Conversione in tabella", "Value"),
    #"Tabella Value espansa1" = Table.ExpandRecordColumn(#"Tabella Value espansa", "Value", {"POINT 2X", "POINT 2Y", "POINT 2Z"}, {"POINT 2X", "POINT 2Y", "POINT 2Z"}),
    #"Tabella POINT 2X espansa" = Table.ExpandListColumn(#"Tabella Value espansa1", "POINT 2X"),
    #"Tabella POINT 2Y espansa" = Table.ExpandListColumn(#"Tabella POINT 2X espansa", "POINT 2Y"),
    #"Tabella POINT 2Z espansa" = Table.ExpandListColumn(#"Tabella POINT 2Y espansa", "POINT 2Z"),
    #"Tabella POINT 2X espansa1" = Table.ExpandRecordColumn(#"Tabella POINT 2Z espansa", "POINT 2X", {"main peak", "overall", "dateTime"}, {"POINT 2X.main peak", "POINT 2X overall", "POINT 2X dateTime"}),
    #"Tabella POINT 2Y espansa1" = Table.ExpandRecordColumn(#"Tabella POINT 2X espansa1", "POINT 2Y", {"main peak", "overall", "dateTime"}, {"POINT 2Y.main peak", "POINT 2Y overall", "POINT 2Y dateTime"}),
    #"Tabella POINT 2Z espansa1" = Table.ExpandRecordColumn(#"Tabella POINT 2Y espansa1", "POINT 2Z", {"main peak", "overall", "dateTime"}, {"POINT 2Z.main peak", "POINT 2Z overall", "POINT 2Z dateTime"}),
    #"Tabella POINT 2X.main peak espansa" = Table.ExpandRecordColumn(#"Tabella POINT 2Z espansa1", "POINT 2X.main peak", {"amplitude", "frequency"}, {"POINT 2X main peak amplitude", "POINT 2X main peak frequency"}),
    #"Tabella POINT 2X main peak amplitude espansa" = Table.ExpandRecordColumn(#"Tabella POINT 2X.main peak espansa", "POINT 2X main peak amplitude", {"value"}, {"POINT 2X main peak amplitude value"}),
    #"Tabella POINT 2X main peak frequency espansa" = Table.ExpandRecordColumn(#"Tabella POINT 2X main peak amplitude espansa", "POINT 2X main peak frequency", {"value"}, {"POINT 2X main peak frequency value"}),
    #"Tabella POINT 2X overall espansa" = Table.ExpandRecordColumn(#"Tabella POINT 2X main peak frequency espansa", "POINT 2X overall", {"value"}, {"POINT 2X overall value"}),
    #"Tabella POINT 2Y.main peak espansa" = Table.ExpandRecordColumn(#"Tabella POINT 2X overall espansa", "POINT 2Y.main peak", {"amplitude", "frequency"}, {"POINT 2Y main peak amplitude", "POINT 2Y main peak frequency"}),
    #"Tabella POINT 2Y main peak amplitude espansa" = Table.ExpandRecordColumn(#"Tabella POINT 2Y.main peak espansa", "POINT 2Y main peak amplitude", {"value"}, {"POINT 2Y main peak amplitude value"}),
    #"Tabella POINT 2Y main peak frequency espansa" = Table.ExpandRecordColumn(#"Tabella POINT 2Y main peak amplitude espansa", "POINT 2Y main peak frequency", {"value"}, {"POINT 2Y main peak frequency value"}),
    #"Tabella POINT 2Y overall espansa" = Table.ExpandRecordColumn(#"Tabella POINT 2Y main peak frequency espansa", "POINT 2Y overall", {"value"}, {"POINT 2Y overall value"}),
    #"Tabella POINT 2Z overall espansa" = Table.ExpandRecordColumn(#"Tabella POINT 2Y overall espansa", "POINT 2Z overall", {"value"}, {"POINT 2Z overall value"}),
    #"Tabella POINT 2Z.main peak espansa1" = Table.ExpandRecordColumn(#"Tabella POINT 2Z overall espansa", "POINT 2Z.main peak", {"amplitude", "frequency"}, {"POINT 2Z main peak amplitude", "POINT 2Z main peak frequency"}),
    #"Tabella POINT 2Z main peak amplitude espansa1" = Table.ExpandRecordColumn(#"Tabella POINT 2Z.main peak espansa1", "POINT 2Z main peak amplitude", {"value"}, {"POINT 2Z main peak amplitude value"}),
    #"Tabella POINT 2Z main peak frequency espansa" = Table.ExpandRecordColumn(#"Tabella POINT 2Z main peak amplitude espansa1", "POINT 2Z main peak frequency", {"value"}, {"POINT 2Z main peak frequency value"}),
    #"Ricopiato in alto" = Table.FillUp(#"Tabella POINT 2Z main peak frequency espansa",{"Name", "POINT 2X main peak amplitude value", "POINT 2X main peak frequency value", "POINT 2X overall value", "POINT 2X dateTime", "POINT 2Y main peak amplitude value", "POINT 2Y main peak frequency value", "POINT 2Y overall value", "POINT 2Y dateTime", "POINT 2Z main peak amplitude value", "POINT 2Z main peak frequency value", "POINT 2Z overall value", "POINT 2Z dateTime"}),
    #"Mantenute prime righe" = Table.FirstN(#"Ricopiato in alto",1)
in
    #"Mantenute prime righe"
Query finale di accodamento:

Codice: Seleziona tutto

// json
let
    Origine = Folder.Files("C:\Users\enric\OneDrive\MondoBI\quesiti\json"),
    #"Filtrate righe" = Table.SelectRows(Origine, each [Extension] = ".json"),
    #"Rimosse altre colonne" = Table.SelectColumns(#"Filtrate righe",{"Content"}),
    #"Funzione personalizzata richiamata" = Table.AddColumn(#"Rimosse altre colonne", "dati", each fxJson([Content])),
    #"Rimosse colonne" = Table.RemoveColumns(#"Funzione personalizzata richiamata",{"Content"}),
    #"Tabella dati espansa" = Table.ExpandTableColumn(#"Rimosse colonne", "dati", {"Name", "POINT 2X main peak amplitude value", "POINT 2X main peak frequency value", "POINT 2X overall value", "POINT 2X dateTime", "POINT 2Y main peak amplitude value", "POINT 2Y main peak frequency value", "POINT 2Y overall value", "POINT 2Y dateTime", "POINT 2Z main peak amplitude value", "POINT 2Z main peak frequency value", "POINT 2Z overall value", "POINT 2Z dateTime"}, {"Name", "POINT 2X main peak amplitude value", "POINT 2X main peak frequency value", "POINT 2X overall value", "POINT 2X dateTime", "POINT 2Y main peak amplitude value", "POINT 2Y main peak frequency value", "POINT 2Y overall value", "POINT 2Y dateTime", "POINT 2Z main peak amplitude value", "POINT 2Z main peak frequency value", "POINT 2Z overall value", "POINT 2Z dateTime"})
in
    #"Tabella dati espansa"
Allegati
output power query.xlsx
(19.74 KiB) Scaricato 15 volte
Enrico Galli
Link utili: I nostri tutorial | Come inserire: Immagini - Codice - Risolto
Se il forum ti è stato utile, considera di supportarlo con una libera donazione

Autore del topic
Mr. Table
Messaggi: 4 | Topic creati
Iscritto il: dom 6 nov 2022, 8:51

Estrarre JSON in Tabella

Messaggio da Mr. Table »

Ciao Enrico,
che roba, fenomenale!

Domani provo la query

Scusa l' ignoranza, dove dovrei incollare i codici?
Poi ovviamente me la studio bene perchè voglio capire come funziona ed imparare ad arrangiarmi.

Grazie 1000
Avatar utente

Enrico Galli
Messaggi: 890 | Topic creati
Iscritto il: dom 28 giu 2020, 19:03
Luogo: San Giovanni in Persiceto (BO)
Ringraziato: 325 volte
Contatta:

Estrarre JSON in Tabella

Messaggio da Enrico Galli »

Scusa l' ignoranza, dove dovrei incollare i codici?
No, scusami ma almeno su questo devi documentarti da solo, e non sarà difficile farlo ;)
Enrico Galli
Link utili: I nostri tutorial | Come inserire: Immagini - Codice - Risolto
Se il forum ti è stato utile, considera di supportarlo con una libera donazione

Autore del topic
Mr. Table
Messaggi: 4 | Topic creati
Iscritto il: dom 6 nov 2022, 8:51

Estrarre JSON in Tabella

Messaggio da Mr. Table »

Ok, provvedo! Grazie ancora per l' aiuto
Rispondi