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!
Estrarre JSON in Tabella L’argomento è risolto
Moderatore: Utilizzo_prof_Excel
-
- 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
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
Link utili: I nostri tutorial | Come inserire: Immagini - Codice - Risolto
Se il forum ti è stato utile, considera di supportarlo con una libera donazione
-
- 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
Anzi ho detto una bugia ci ho provato ed ecco il risultato:
Funzione per trasformare i json:
Query finale di accodamento:
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"
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
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 - Messaggi: 4 | Topic creati
- Iscritto il: dom 6 nov 2022, 8:51
Estrarre JSON in Tabella
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
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
-
- 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
No, scusami ma almeno su questo devi documentarti da solo, e non sarà difficile farloScusa l' ignoranza, dove dovrei incollare i codici?
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
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 - Messaggi: 4 | Topic creati
- Iscritto il: dom 6 nov 2022, 8:51