Dieses Forum nutzt Cookies
Dieses Forum verwendet Cookies, um deine Login-Informationen zu speichern, wenn du registriert bist, und deinen letzten Besuch, wenn du es nicht bist. Cookies sind kleine Textdokumente, die auf deinem Computer gespeichert werden. Die von diesem Forum gesetzten Cookies werden nur auf dieser Website verwendet und stellen kein Sicherheitsrisiko dar. Cookies aus diesem Forum speichern auch die spezifischen Themen, die du gelesen hast und wann du zum letzten Mal gelesen hast. Bitte bestätige, ob du diese Cookies akzeptierst oder ablehnst.

Ein Cookie wird in deinem Browser unabhängig von der Wahl gespeichert, um zu verhindern, dass dir diese Frage erneut gestellt wird. Du kannst deine Cookie-Einstellungen jederzeit über den Link in der Fußzeile ändern.

json api to excel/csv über datum/timestamp
#1
Hola los Todos!

Ich hoffe Ihr seid alle gesund und munter!

Ich würde gerne aus meinem Excel eine json api abrufen und die Daten in Excel nach Spalten verwalten.

Und am besten nur die von einem bestimmten Tag (z.B. gestern) via timestamp 2020-01-07. Dann sind es nicht so viele und jedesmal alle abrufen wäre ja wenig sinnvoll.

Das Ergebnis einer Test-Abfrage sieht (zur Zeit) so aus:


Code:
{"success":true,"submissions":[{"form_url":"https://domain.com/live-demo","form_data":{"name":"xxx2","email":"xxx@xxx.com","message":"xxx2"},"submitted_at":{"date":"2020-07-01 07:00:31.000000","timezone_type":3,"timezone":"UTC"}},{"form_url":"https://domain/live-demo","form_data":{"name":"xxx","email":"xxx@xxx.com","message":"xxx"},"submitted_at":{"date":"2020-07-01 06:59:01.000000","timezone_type":3,"timezone":"UTC"}}]}


Das Ergebnis (in Excel) sollte dann etwa so aussehen:

Code:
form_url    form_data/name    form_data/email    form_data/message    submitted_at/date    submitted_at/timezone_type    submitted_at/timezone
https://domain.com/live-demo    xxx2    xxx@xxx.com    xxx2    2020-07-02 07:00:31.000000    3    UTC
https://domain.com/live-demo    xxx    xxx@xxx.com    xxx    2020-07-02 06:59:01.000000    3    UTC

Wie wäre das möglich?

Danke und Gruss

Peter
Antwortento top
#2
Hallo,

es ist möglich mit VBA einen Import von json-Dateien zu programmieren, aber in Powershell sollte es einfacher gehen.

mfg
[-] Folgende(r) 1 Benutzer sagt Danke an Fennek für diesen Beitrag:
  • PeterN
Antwortento top
#3
Hallöchen,

bislang hab ich json mit purem vba verarbeitet. Du kannst die json z.B. in ein Array packen und dieses dann weiter verschachteln bzw. verarbeiten.
Du nimmst den header weg und ggf. die beide Zeichen am Ende und kannst dann anhand "}};{" erst mal die Datensätze trennen.
Die Feldinhalte stehen dann zwischen ":" und "," ... Den Datensatz trägst DU dann nur ein, wenn das Datum passt.

Könnte auch mit PQ gehen, siehe z.B. dort:
https://stackoverflow.com/questions/4206...ower-query
   \\\|///      Hoffe, geholfen zu haben.
   ( ô ô )      Grüße, André aus G in T  
 ooO-(_)-Ooo    (Excel 97-2016)
[-] Folgende(r) 1 Benutzer sagt Danke an schauan für diesen Beitrag:
  • PeterN
Antwortento top
#4
Hallo Ihr beiden!

Vielen Dank für eure Vorschläge.

Ich habe mit Power Querry für Excel 2013 installiert und bekomme nach dem Einlesen der Url immer die Fehlermeldung ""Das Hervorheben von Tabellen ist deaktiviert, da diese Seite den Kompatibilitätsmodus des Internet Explorers verwendet."

Ich habe schon mehrere Stunden vergeblich verschiedene Lösungsvorschläge probiert.

Also komme ich mit PQ irgendwie nicht weiter.

Den aktuellen Stand meiner Abfrage ist folgender:

Code:
{"success":true,"submissions":[{"form_url":"https://domain.com/form","form_data":{"name":"xxx","email":"xxx@xxx.com"," filename ":"Test.txt"},"submitted_at":{"date":"2020-07-02 19:08:59.000000","timezone_type":3,"timezone":"UTC"}},{"form_url":"https://domain.com/form","form_data":{"name":"xxx","email":"xxx@xxx.com"," filename ":"Test.txt"},"submitted_at":{"date":"2020-07-02 19:07:37.000000","timezone_type":3,"timezone":"UTC"}},{"form_url":"https://domain.com/form","form_data":{"name":"xxx","email":"xxx@xxx.com"," filename ":"Test.txt"},"submitted_at":{"date":"2020-07-02 19:05:10.000000","timezone_type":3,"timezone":"UTC"}},{"form_url":"https://domain.com/form","form_data":{"name":"xxx","email":"xxx@xxx.com"," filename ":"Test.txt"},"submitted_at":{"date":"2020-07-02 19:03:45.000000","timezone_type":3,"timezone":"UTC"}},{"form_url":"https://domain.com/form","form_data":{"name":"xxx","email":"xxx@xxx.com"},"submitted_at":{"date":"2020-07-02 19:01:59.000000","timezone_type":3,"timezone":"UTC"}}]}

Bei filename sind die "" natürlich direkt vor und hinter filename , also ohne Leerzeichen, ich konnte das hier nur nicht anders darstellen.

Eventuell könntet Ihr mir ein Makro erstellen, was das Ganze abfragen und in Spalten sortieren kann. Perfekt wäre immer noch, wenn man nach Datum abfragen bzw. einschränken könnte. Gerne manuell im VBA Code. 

Ich kann euch beiden gerne auch die Original URL per PM schicken, da ich euch kenne und euch vertraue. Ich würde die URL nur nicht hier öffentlich posten.

Danke und Gruss

Peter
Antwortento top
#5
Hallo,

ich kann Dir jetzt nicht sagen, welche Fähigkeiten PQ für Excel 2013 hat (also aktuell ist), aber Du könntest die JSON-Datei erstmal lokal
speichern (z.B. Test.json) und dann versuchen, in PQ zu bearbeiten. Als M-Code anbei ein Beispiel, auf Das Du ggf. aufbauen kannst.
Um den M-Code einzufügen, eine leere Abfrage erstellen, den erweiterten Editor im Home-Tab aufrufen, im M-Code hier den Pfad zur
JSON-Datei anpassen und dann in den erw. Editor einfügen. Später dann durch die URL ersetzen.

Code:
let
    Quelle = Json.Document(Web.Contents("file:///D:\Downloads\test.json")),
    #"In Tabelle konvertiert" = Record.ToTable(Quelle),
    Value = #"In Tabelle konvertiert"{1}[Value],
    #"In Tabelle konvertiert1" = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Erweiterte Column1" = Table.ExpandRecordColumn(#"In Tabelle konvertiert1", "Column1", {"form_url", "form_data", "submitted_at"}, {"form_url", "form_data", "submitted_at"}),
    #"Erweiterte form_data" = Table.ExpandRecordColumn(#"Erweiterte Column1", "form_data", {"name", "email", " filename "}, {"name", "email", " filename "}),
    #"Erweiterte submitted_at" = Table.ExpandRecordColumn(#"Erweiterte form_data", "submitted_at", {"date", "timezone_type", "timezone"}, {"date", "timezone_type", "timezone"})
in
    #"Erweiterte submitted_at"

Gruß
Microsoft Excel Expert · Microsoft Most Valuable Professional (MVP) :: 01/2011 - 06/2019 :: 04/2020 - 06/2021
https://de.excel-translator.de/translator :: Online Excel-Formel-Übersetzer :: Funktionen :: Fehlerwerte :: Argumente :: Tabellenbezeichner (neu)
Antwortento top
#6
Hi Mourad,

Danke für die Infos.

Ich habe die Datei lokal gespeichert, aber PQ kann sie nicht verarbeiten.

Dann habe ich den M-Code probiert, bekomme ihn aber nicht zum laufen.

Könntest Du mit bitte eine Excelliste mit deinem eingebundenen Code hochladen?

Danke und Gruss

Peter
Antwortento top
#7
Hallo Peter,

gerne, anbei die zwei Dateien. Die JSON-Datei habe ich für den Upload hier in peter-n.txt umbenannt, da der Upload hier keine Json's akzeptiert.
Müsstest Du somit wieder in peter-n.json umbenennen. Die Json ist bei mir im Ordner D:\Downloads, müsstest Du dann auch ggf. anpassen.

Gruß


Angehängte Dateien
.xlsx   peter-n.xlsx (Größe: 17,56 KB / Downloads: 4)
.txt   peter-n.txt (Größe: 1.008 Bytes / Downloads: 9)
Microsoft Excel Expert · Microsoft Most Valuable Professional (MVP) :: 01/2011 - 06/2019 :: 04/2020 - 06/2021
https://de.excel-translator.de/translator :: Online Excel-Formel-Übersetzer :: Funktionen :: Fehlerwerte :: Argumente :: Tabellenbezeichner (neu)
Antwortento top
#8
Hi Mourad!

Das sieht ja schon mal super aus.

Genauso wie ich es mir gewünscht habe.

Nur wie und wo ändere ich die Pfade bzw. wie/wo finde ich Deinen M-Code?

Über Code anzeigen hat es nicht funktioniert.

Danke

Peter
Antwortento top
#9
Hallo Peter,

siehe Bildchen anbei - jetzt allerdings aus Excel 365. Jedenfalls den PQ-Editor öffnen, im Home-Tab müsstest Du "Erweiterter Editor"
zu finden sein (ggf. auf woanders), es öffnet sich ein separates Fenster wo Du den Pfad (URL) anpassen kannst. Alternativ, den
ersten Eintrag bei den Schritten anklicken und in der Eingabezeile ändern oder Zahnrad rechts neben dem Schritt.

   
   
   

Gruß
Microsoft Excel Expert · Microsoft Most Valuable Professional (MVP) :: 01/2011 - 06/2019 :: 04/2020 - 06/2021
https://de.excel-translator.de/translator :: Online Excel-Formel-Übersetzer :: Funktionen :: Fehlerwerte :: Argumente :: Tabellenbezeichner (neu)
[-] Folgende(r) 1 Benutzer sagt Danke an maninweb für diesen Beitrag:
  • PeterN
Antwortento top
#10
Super! Klasse! Danke Mourad!

Gruss

Peter
Antwortento top


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste