Daten aus api aus url in Excel

[MichaelH]

Hi!

Ich hoffe es geht euch allen gut.

Ich habe Adressen über eine api in einer datei online.

Wenn ich die URL aufrufe kommt z.B:

{ "success": true, "submissions": [{ "form_url": "https://domain.com/directapp?Id=g426drtr-1144\u0026Company=Talent\u0026Title=Data%20Analyst\u0026Category=IT\u0026City=Greater-Sudbury\u0026Region=Ontario\u0026Country=Canada", "form_data": { "Name": "xxxxxxxxxx", "Email": "xxx@yahoo.com", "Id": "g426drtr-1144", "Company": "Talent", "Title": "Data Analyst", "Category": "IT", "City": "Greater-Sudbury", "Region": "Ontario", "Country": "Canada", "File": "xxx xxx xxx - 20200507.pdf" }, "submitted_at": { "date": "2021-08-15 22:57:03.000000", "timezone_type": 3, "timezone": "UTC" } },
{ "form_url": "https://domain.com/directapp?Id=x26q3u2d-1125\u0026Company=Talent\u0026Title=Business%20Analyst\u0026Category=IT\u0026City=Greater-Sudbury\u0026Region=Ontario\u0026Country=Canada", "form_data": { "Name": "xxxxx", "Email": "xxxx@gmail.com", "Id": "x26q3u2d-1125", "Company": "Talent", "Title": "Business Analyst", "Category": "IT", "City": "Greater-Sudbury", "Region": "Ontario", "Country": "Canada", "File": "xxxxx xxxxxx xxx - 20200507.pdf" }, "submitted_at": { "date": "2021-08-15 22:54:31.000000", "timezone_type": 3, "timezone": "UTC" } },
{ "form_url": "https://domain.com/directapp?Id=r3iohstx-1932\u0026Company=Talent\u0026Title=Analytical%20Chemist\u0026Category=Science%20and%20Engineering%20Professionals\u0026City=Edmonton\u0026Region=Alberta\u0026Country=Canada", "form_data": { "Name": "xxxxxxx", "Email": "xxxxxxx.x.x@gmail.com", "Id": "r3iohstx-1932", "Company": "Talent", "Title": "Analytical Chemist", "Category": "Science and Engineering Professionals", "City": "Edmonton", "Region": "Alberta", "Country": "Canada", "File": "xxxxxxx-xxxxxxx.pdf" }, "submitted_at": { "date": "2021-08-15 21:40:03.000000", "timezone_type": 3, "timezone": "UTC" } }] }


Anfang = { "success": true, "submissions": [

Ein (immer gleicher) Adressblock = { "form_url": "https://domain.com/directapp?Id=g426drtr-1144\u0026Company=Talent\u0026Title=Data%20Analyst\u0026Category=IT\u0026City=Greater-Sudbury\u0026Region=Ontario\u0026Country=Canada", "form_data": { "Name": "xxxxxxxxxx", "Email": "xxx@yahoo.com", "Id": "g426drtr-1144", "Company": "Talent", "Title": "Data Analyst", "Category": "IT", "City": "Greater-Sudbury", "Region": "Ontario", "Country": "Canada", "File": "xxx xxx xxx - 20200507.pdf" }, "submitted_at": { "date": "2021-08-15 22:57:03.000000", "timezone_type": 3, "timezone": "UTC" } },

Ende =  ] }

Ich bräuchte die Daten in (14) Excel spalten getrennt nach:

form_url
Name
Email
Id
Company
Title
Category
City
Region
Country
File
date
timezone_type
timezone

Habt Ihr eine Lösung dafür?

Vielen Dank im Voraus

[volti]

Hallo Michael,

hier eine ggf. ausbaufähige Idee bzw. Anregung dazu....

Code:

Sub Test()
 Dim sData As String, sArr() As String, i As Integer, iSpalte As Integer
 
 sData = "{ 'form_url': 'https://domain.com/directapp?Id=g426drtr-1144\u0026Company=Talent\u0026Title=Data%20Analyst\u0026Category=IT\u0026City=Greater-Sudbury\u0026Region=Ontario\u0026Country=Canada', 'form_data': { 'Name': 'xxxxxxxxxx', 'Email': 'xxx@yahoo.com', 'Id': 'g426drtr-1144', 'Company': 'Talent', 'Title': 'Data Analyst', 'Category': 'IT', 'City': 'Greater-Sudbury', 'Region': 'Ontario', 'Country': 'Canada', 'File': 'xxx xxx xxx - 20200507.pdf' }, 'submitted_at': { 'date': '2021-08-15 22:57:03.000000', 'timezone_type': 3, 'timezone': 'UTC' } },"
 sData = Replace(sData, "'", Chr$(34))
 
' Hier gehts los
 sData = Replace(Replace(sData, "{", ""), "}", "")
 sArr = Split(Replace(sData, Chr$(34), ""), ",")
 
 iSpalte = 1
 For i = 0 To UBound(sArr) - 1
  Cells(1, iSpalte + i).Value = Split(sArr(i), ":")(0)
  Cells(2, iSpalte + i).Value = Split(sArr(i) & ":", ":")(1)
 Next i
 
End Sub

_________
viele Grüße
Karl-Heinz

[MichaelH]

Hallo Karl-Heinz!

Vielen Dank für die schnelle Antwort!

Wie oder wo müsste ich den Code einsetzen?

Ich bin nicht so der Excel Experte.

Danke und Gruss

Michael

[volti]

Hallo Michael.

ich ging davon aus, dass der Text bereits in einer Variablen steht z.B. in (wie ich jetzt lese) "Anfang" und von dort nur gesplittet werden soll.

Wenn er noch irgendwo in einer Datei steht, muss er von dort geholt werden. Da wäre eine Beispieldatei hilfreich.

Oder es hat noch jemand eine andere Idee.

viele Grüße
Karl-Heinz

[MichaelH]

Hi!

Also ich kann den Text aus der Url in eine txt Datei speichern und dachte von dort könnte man das ganze in Excel Spalten schreiben.

Anbei die Beispielldaten in einer Datei und die Ergebnis Excelliste als Beispiel.

Toll wäre, wenn aus dem \u0026 im link ein & werden könnte.

Danke und Gruss

Michael

[Zwenn]

Hallo Michael,

das ist ein JSON und kann mit dem JSONConverter von Tim Hall verarbeitet werden, um die gewünschten Daten auszulesen.
https://de.wikipedia.org/wiki/JavaScript...t_Notation
https://github.com/VBA-tools/VBA-JSON

Das lohnt sich aber nur, wenn Du regelmäßig neue Daten bekommst, die verarbeitet werden müssen. Es ist dabei nicht notwendig, ein JSON in eine Text-Datei zu schreiben. Du kannst es direkt aus dem Internet in eine Variable einlesen. So ein JSON hat eine zwar individuelle, aber ganz klare Struktur. Kopiere mal den Inhalt Deiner Text-Datei in das Textfeld der folgenden Seite und klicke auf "Process". Dann kannst Du das (fast) wie Fließtext lesen. In jedem Fall kannst Du so den Aufbau genau nachvollziehen, was für die Arbeit mit dem JSONConverter wichtig ist:
https://jsonformatter.curiousconcept.com

Solltest Du das nur für ein paar Dateien brauchen, bietet sich auch die Methode an, die Karl-Heinz gezeigt hat. Der JSONConverter hat (für jemanden wie mich) den Vorteil, dass er als Werkzeug für unterschiedliche JSONs immer gleich funktioniert. Man muss nur den jeweiligen Aufbau so einer Datei kennen.

Viele Grüße,

Zwenn

[volti]

Hallo Michael,

falls Du Zwenn's Version nicht nutzen kannst/möchtest, hier noch mal eine Ergänzung, wie das Ganze aus einer textdatei eingelesen werden kann zur Information und weiteren Verwendung.

Code:

Sub Test()
 Dim sFilename As String
 Dim sData As String, sArr() As String
 Dim iFF As Integer, i As Integer, iSpalte As Integer
 
 sFilename = ThisWorkbook.Path & "\API_Text.txt"    ' anpassen.....
 
 iFF = FreeFile
 If Dir(sFilename) <> "" Then                       ' Ist Datei vorhanden?
    Open sFilename For Input As iFF                 ' Datei öffnen
    sData = Replace(Replace(Input(LOF(iFF), iFF), "{", ""), "}", "")
    Close iFF
    sArr = Split(Replace(sData, Chr$(34), ""), ",") ' Daten in Array einlesen
 
    iSpalte = 1                                       ' Beginn Spalte festlegen
    For i = 0 To UBound(sArr) - 1
        Cells(1, iSpalte + i).Value = Split(sArr(i), ":")(0)
        Cells(2, iSpalte + i).Value = Split(sArr(i) & ":", ":")(1)
    Next i
    MsgBox "Die Datei '" & sFilename & "' wurde eingelesen!", vbInformation, "Daten einlesen"
  Else
    MsgBox "Die Datei '" & sFilename & "' wurde nicht gefunden!", vbCritical, "Daten einlesen"
  End If
 
End Sub

_________
viele Grüße
Karl-Heinz

[MichaelH]

Hallo Karl-Heinz!

Vielen Dank für den erweiterten Code!

Wie oder wo müsste ich den denn jetzt Deinen Code einsetzen, um das Ganze zu testen?

Wenn ich das richtig deute/verstehe gehört der Code zu meiner anfangs leeren Beispieldatei und holt dann die Daten spaltenweise aus der txt Datei richtig?

Aber wie bekomme ich den Code in die Exceldatei?

Kannst Du mir das mal bitte als Paket schicken, also meine "Beispieldatei" mit Deinem Code?

Danke und Gruss

Michael

---

Hallo Zwenn!

Danke für den Link!

Das Ergebnis sieht dann so aus wie wenn ich "rechte Maustaste, Quelltext anzeigen" nutze.

Werde ich mich bei Gelegenheit mal mit auseinandersetzen.

Gruss

Michael

[volti]

Hallo Michael,

hier die Beispieldatei.

Habe einen Startbutton eingebaut, mit dem Du das Makro starten kannst und einen Dateiauswahldialog. Dann kannst Du bequem eine beliebige (Txt-)Datei auswählen.

PS: Makros können nur in den Excel-Version XLSM und XLSB, aber nicht in XLSX laufen.....


  Daten-aus-api-aus-url-in-Excel.xlsm (Größe: 30,64 KB / Downloads: 4)

viele Grüße
Karl-Heinz

[MichaelH]

Hallo Kar-Heinz!

Vielen Dank! Funktioniert doch schon fast super!

Kannst Du das auch so einstellen, das ein Datensatz immer in einer Zeile mit den 14 Spalten wie in der Beispieldatei steht?
Aktuell stehen alle Daten aus x Datensätzen in einer einzigen Zeile.

Von der jeweiligen url wie z.B. https://domain.com/directapp?Id=g426drtr...try=Canada beleibt leider nur das https übrig und nicht z.B. https://domain.com/directapp?Id=g426drtr-1144&Company=Talent&Title=Data%20Analyst&Category=IT&City=Greater-Sudbury&Region=Ontario&Country=Canada

Und beim ersten Datensatz wird success": true, und "submissions als Daten eingelesen und dafür die letzten beiden "timezone" und "] }" weggelassen.

Danke

Michael