json api to excel/csv über datum/timestamp

[PeterN]

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

[Fennek]

Hallo,

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

mfg

[schauan]

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

[PeterN]

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

[maninweb]

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ß

[PeterN]

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

[maninweb]

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ß

[PeterN]

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

[maninweb]

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ß

[PeterN]

Super! Klasse! Danke Mourad!

Gruss

Peter