Abruf von Daten aus Web in Serie
#1
Hallo zusammen,

ich versuche gerade ein Makro zu erstellen, um Tabellen aus dem Web herunter zu laden. Mit Daten aus dem Web abrufen kein Problem, soweit es nur eine Tabelle betrifft. Aber daraus in einer Schleife mehrere Seiten abzurufen klappt nicht.

Fakten:

Url zum Abruf:    https://www.xxxxxx.net/de/yyyy_ danach folgen überwiegend fortlaufende Nummern, die in einer Tabelle vorliegen und aus dieser Zeilenweise ausgelesen werden sollen.
Aufgezeichnetes Makro ergibt für eine einzelne Seite bspw. diesen Code:

Code:
ActiveWorkbook.Queries.Add Name:="Tabelle 1", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Quelle = Web.BrowserContents(""https://www.xxxxxx.net/de/yyyy_"")," & Chr(13) & "" & Chr(10) & "    #""Aus HTML-Code extrahierte Tabelle"" = Html.Table(Quelle, {{""Column1"", ""TABLE[id='abcde'] > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(5), TABLE[id='abcde'] > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(3), TABLE[i" & _
        "d='abcde'] > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(5)""}, {""Column2"", ""TABLE[id='abcde'] > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(5) + TH:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(4), TABLE[id='abcde'] > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(3) " & _
        "+ TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(2), TABLE[id='abcde'] > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(5) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(4)""}, {""Column3"", ""TABLE[id='abcde'] > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(5) + TH:not([colspan]):not" & _
        "([rowspan]):nth-child(2):nth-last-child(4) + TH:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(3), TABLE[id='abcde'] > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(3) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(2) + TD[colspan=""""3""""]:not([rowspan]):nth-child(3):nth-last-child(1), TABLE[id='abcde'] > * " & _
        "> TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(5) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(4) + TD:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(3)""}, {""Column4"", ""TABLE[id='abcde'] > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(5) + TH:not([colspan]):not([rowspan]):nth-child(2):n" & _
        "th-last-child(4) + TH:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(3) + TH:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(2), TABLE[id='abcde'] > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(3) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(2) + TD[colspan=""""3""""]:not([rowspan]):nth-child(3):nth-" & _
        "last-child(1), TABLE[id='abcde'] > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(5) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(4) + TD:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(3) + TD:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(2)""}, {""Column5"", ""TABLE[id='abcde'] > * > TR > TH:not(" & _
        "[colspan]):not([rowspan]):nth-child(1):nth-last-child(5) + TH:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(4) + TH:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(3) + TH:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(2) + TH:not([colspan]):not([rowspan]):nth-child(5):nth-last-child(1), TABLE[id='abcde'] > * > TR > TD:not([colspa" & _
        "n]):not([rowspan]):nth-child(1):nth-last-child(3) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(2) + TD[colspan=""""3""""]:not([rowspan]):nth-child(3):nth-last-child(1), TABLE[id='abcde'] > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(5) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(4) + TD:not([colspan])" & _
        ":not([rowspan]):nth-child(3):nth-last-child(3) + TD:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(2) + TD:not([colspan]):not([rowspan]):nth-child(5):nth-last-child(1)""}}, [RowSelector=""TABLE[id='abcde'] > * > TR""])," & Chr(13) & "" & Chr(10) & "    #""Geänderter Typ"" = Table.TransformColumnTypes(#""Aus HTML-Code extrahierte Tabelle"",{{""Column1"", type text}, {""Column2"", " & _
        "type text}, {""Column3"", type text}, {""Column4"", type text}, {""Column5"", type text}})," & Chr(13) & "" & Chr(10) & "    #""Entfernte Spalten"" = Table.RemoveColumns(#""Geänderter Typ"",{""Column1""})," & Chr(13) & "" & Chr(10) & "    #""Entfernte oberste Zeilen"" = Table.Skip(#""Entfernte Spalten"",2)," & Chr(13) & "" & Chr(10) & "    #""Spalte nach Trennzeichen teilen"" = Table.SplitColumn(#""Entfernte oberste Zeilen"", ""Column4"", Splitter." & _
        "SplitTextByDelimiter(""#(00A0)"", QuoteStyle.Csv), {""Column4.1"", ""Column4.2""})," & Chr(13) & "" & Chr(10) & "    #""Geänderter Typ1"" = Table.TransformColumnTypes(#""Spalte nach Trennzeichen teilen"",{{""Column4.1"", type text}, {""Column4.2"", type text}})," & Chr(13) & "" & Chr(10) & "    #""Entfernte Spalten1"" = Table.RemoveColumns(#""Geänderter Typ1"",{""Column4.2""})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Entfernte Spalten1"""
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Tabelle 1"";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Tabelle 1]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Tabelle_1"
        .Refresh BackgroundQuery:=False
    End With
Versuche ich dann, diesen Code in eine Schleife zu legen, dann stürzt der Code in der drittletzten Zeile:

Code:
.ListObject.DisplayName = "Tabelle_1"
 ab.

Wie kann ich mehrere Tabellen nacheinander abrufen und untereinander stapeln?
Gruß

Edgar

Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
Antworten Top
#2
Hallo Edgar,

- Du legst dir einfach deine Zieltabellen auf einem getrennten Arbeitsblatt an.
- am Ende des Durchlaufs kopierst du die PQ Ausgabe in die jeweilige Zieltabelle. Entweder in Zieltabelle Anfügen was neu ist oder Databodyrange leeren und einfügen (was praktikabler ist). 
- Am Schluss des Schleifendurchlaufs löschst du zuerst die Verbindung und dann das Tabellenblatt mit dem von PQ erzeugten Listobjekt. 
Dann der nächste Schleifendurchlauf.

Es ist sicherlich die Vorschlaghammermethode, aber dafür der geringste Aufwand.

Gruß Uwe
Antworten Top
#3
Hi Edgar,

wäre zwar gut, wenn man die korrekte Adresse kennen würde, aber versuch mal folgendes:

ersetze
Quelle = Web.BrowserContents(""https://www.xxxxxx.net/de/yyyy_"")

durch:
Quelle = Web.Page( Web.Contents("https://www.xxxxxx.net/de/yyyy_ "))

Ich persönlich würde es allerdings direkt mit PQ lösen. Die einzelnen Seiten könnte man dann z. Bsp. via List.Generate einlesen lassen. Genaueres lässt sich aber in Unkenntnis der Website nicht sagen. 

Denkbar wäre auch folgendes Konstrukt:

let

Quelle = Web.Page(Web.Contents("https://www.xxxxxx.net/de/yyyy_")),
AllTables = Quelle[Data],

//Konvertieren aller Spalten in allen Tabellen in Text
ProcessedTables = List.Transform(
AllTables,each
Table.TransformColumnTypes( _,List.Transform(Table.ColumnNames(_), each {_, type text}))),

//Optional: Kombinieren in einer großen Tabelle (wenn die Strukturen gleich sind)
CombinedTable = Table.Combine(ProcessedTables)
in
CombinedTable
Der sicherste Ansatz für einen Irrtum ist der Glaube, alles im Griff zu haben.
Nur, weil ich den Recorder bedienen kann, macht mich das noch lange nicht zum Musiker.

Ciao, Ralf

Antworten Top
#4
Damit du sehen kannst, wie es geht, habe ich mal von hier das Excel-Forum importiert.

Mit der Abfrage: "Excel_Forum_Page_1" wird die Erste Seite des Forums importiert. Aus dieser Abfrage habe ich dann die Funktion: "fx_import_cef_excel_forum" erstellen lassen und dann in dieser de statischen Abruf der Ersten Seite in einen dynamischen geändert. 

Dann habe ich eine Tabelle erstellt, in die ich die Seiten eintrage, deren Inhalt ich importieren möchte. das entspricht: " danach folgen überwiegend fortlaufende Nummern, die in einer Tabelle vorliegen und aus dieser Zeilenweise ausgelesen werden sollen."

Mit der Abfrage "Import_excel_Forum" wird dann der Import der für die definierten Seiten durchgeführt.


Angehängte Dateien
.xlsx   cef - Import Excel Forum.xlsx (Größe: 34,42 KB / Downloads: 8)
Antworten Top
#5
Hallo Uwe,

PQ ist für mich feindliches Land, ich habe davon keine Ahnung.
Es ist auch kein Problem, händisch eine Seite nach der anderen abzurufen und dann diese Seiten untereinander in einem Blatt anzuordnen, aber nicht als Schleife.

@Ralf,

das Problem ist nicht der Abruf an sich, sondern die Schleife.

URL lautet immer 
https://www.xxxxxx.net/de/yyyy_100
https://www.xxxxxx.net/de/yyyy_101
usw.

Der Fehler tritt immer in der Zeile
 
Code:
.ListObject.DisplayName = "Tabelle_1"


auf.
Mein Versuch, die URL aus  https://www.xxxxxx.net/de/yyyy & Zellinhalt mit Nummer zusammenzusetzen, klappt bei einer Seite, aber nicht bei mehreren, weil der DisplayName nicht richtig ist.


@ws-53
sieht zwar gut aus, aber ist mir um Einiges zu hoch. Ich weiß noch nicht einmal, wo ich anfangen soll einen Code zu suchen, geschweige denn nachzuvollziehen.
Ich glaube ich stecke das Ganze und mache es händisch.
Gruß

Edgar

Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
Antworten Top
#6
Hi Edgar;

Zitat: Mit Daten aus dem Web abrufen kein Problem, ...

das ist Power Query. Mit: Daten > Abfragen und Verbindungen werden dir dann rechts die in einer Mappe vorhandenen Abfragen gezeigt, die du zur Bearbeitung öffnen kannst.

Anfangs wirkt Power Query für viele etwas schwierig, aber i.d.R. legt sich das schnell.
[-] Folgende(r) 1 Nutzer sagt Danke an ws-53 für diesen Beitrag:
  • Jockel
Antworten Top
#7
Hi ws,

okay, dann weiß ich ja offennsichtlich schonmal, wie man PQ einsetzt! 19
Allerdings hört es dann schon auf. Der Unterschied zu Deiner Abfrage ist bei mir aber, das ich aus ca. 500 verschiedenen URL´s jeweils eine einzige Tabelle brauche. Alle URL sind dabei  bis zum Unterstrich gleich, danach folgt eine Zahl. 
Mittlerweile beherrsche ich das soweit, dass ich eine Seite nach der anderen öffnen, die Daten auslesen und untereinander speichern kann. Die Befehle dazu habe ich auch in einem Makro gespeichert. Problem ist, die Zahlen am Ende der URL in einer Schleife aus einer Datei auszulesen und das ganze automatisiert zu erledigen.
Gruß

Edgar

Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
Antworten Top
#8
Hallo Edgar,

Zitat:Alle URL sind dabei  bis zum Unterstrich gleich, danach folgt eine Zahl. 


Das entspricht ja genau dem was ich für das Excel-Forum im Tabellenblatt "Tabelle1", mit den Inhalt 1 bis 5, realisiert habe.

Die Tabelle wird gelesen und für jeden Eintrag der Tabelle wird die Funktion "fx_import_cef_excel_forum" aufgerufen mit Übergabe des Inhalts der Jeweiligen Zelle aus der Spalte "Seite".

Hier nun mal der Code etwas übersichtlicher:

let
    Source          = Excel.CurrentWorkbook(){[Name="Tabelle2"]}[Content],
    Change_Type      = Table.TransformColumnTypes(Source,{{"Seite", type text}}),
    Call_Function    = Table.AddColumn(Change_Type, "Forum", each fx_import_cef_excel_forum([Seite])),
    Expand          = Table.ExpandTableColumn(Call_Function, "Forum", {"Column1", "Column3", "Column4", "Column5.1", "Column6", "Column7", "Column8"},
                                                                      {"Column1", "Column3", "Column4", "Column5.1", "Column6", "Column7", "Column8"}),
    Rename_Cols      = Table.RenameColumns(Expand,{{"Column1", "Titel"}, {"Column3", "Erstellt von"}, {"Column4", "Erstellt am"}, {"Column5.1", "L.Antwort am"},
                                                  {"Column6", "L.Antwort von"}, {"Column7", "Antworten"}, {"Column8", "Ansichten"}}),
    Change_Types    = Table.TransformColumnTypes(Rename_Cols,{{"Erstellt am", type datetime}, {"L.Antwort am", type datetime},
                                                              {"Antworten", Int64.Type}, {"Ansichten", Int64.Type}}),
    Keep_needed_Cols = Table.SelectColumns(Change_Types,{"Titel", "Erstellt von", "Erstellt am", "L.Antwort von", "L.Antwort am", "Antworten", "Ansichten"}),
    Sort_Table      = Table.Sort(Keep_needed_Cols,{{"L.Antwort am", Order.Descending}})
in
    Sort_Table


In der Funktion "fx_import_cef_excel_forum" 


let
    Quelle = (Seite as text) => let
//      Source                = Web.BrowserContents("https://www.clever-excel-forum.de/Forum-Excel?page=1"),
        Source                = Web.BrowserContents("https://www.clever-excel-forum.de/Forum-Excel?page="&Seite),

        Extract_tbl_from_Html = Html.Table(Source, {{"Column1", ".subject_new *"}, {"Column2", ".author"}, {"Column3", ".author *"},
                                                    {"Column4", ".thread_start_datetime"}, {"Column5", ".lastpost"}, {"Column6", ".lastpost A + *"},
                                                    {"Column7", ".inline_row TD:nth-child(4)"}, {"Column8", "TD:nth-child(5)"}, {"Column9", ".forumdisplay_regular BR + *"},
                                                    {"Column10", ".subject_new + *"}, {"Column11", ".forumdisplay_regular:nth-child(3) A:nth-child(2)"},
                                                    {"Column12", ".forumdisplay_regular:nth-child(3) A:nth-child(3)"}, {"Column13", ".tborder A:nth-child(4)"}},
                                                    [RowSelector=".subject_new *"]),
        Split_Col            = Table.SplitColumn(Extract_tbl_from_Html, "Column5", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), {"Column5.1", "Column5.2"}),
        Remove_Cols          = Table.RemoveColumns(Split_Col,{"Column10", "Column11", "Column12", "Column13", "Column9", "Column5.2", "Column2"})
    in
        Remove_Cols
in
    Quelle


werden dann mit dem Schritt "Source" die einzelnen Seiten gelesen. Dass ist somit der Loop denn du suchst. 

Direkt in einer Power Query gibt es keine Möglichkeit, Schleifen zu definieren.

Vllt. wird es so etwas klarer.
Antworten Top
#9
(24.06.2025, 10:14)BoskoBiati schrieb: ...Der Unterschied zu Deiner Abfrage ist bei mir aber, das ich aus ca. 500 verschiedenen URL´s jeweils eine einzige Tabelle brauche. Alle URL sind dabei  bis zum Unterstrich gleich, danach folgt eine Zahl. 

...das ist überhaupt kein Problem. Hab mir mal erlaubt, Wilfrieds Funktion zu übernehmen, aber die Seitenanzahl dynamisch auslesen oder festlegen zu lassen.
 In der Abfrage Einlesen wird das hier im Schritt AlleSeiten festgelegt:

PHP-Code:
//AlleSeiten = Table.FromList({1..Seiten}, Splitter.SplitByNothing(), null, null, ExtraValues.Error),       //Hier würden alle 1800 Seiten eingelesen, deshalb...

    AlleSeiten Table.FromList({1..10}, Splitter.SplitByNothing(), nullnullExtraValues.Error),             //...kürze ich das auf die ersten 10 Seiten... 

Die 1. Zeile ist auskommentiert, weil es über 1800 auszulesende Seiten im Forum gibt. Das würde natürlich entsprechend lange dauern. Wenn Du es trotzdem so haben möchtest, dann die Kommentarzeichen // entfernen und vor die 2. Zeile setzen. Es kann immer nur einen Abfrageschritt mit gleichem Namen geben.

Vermutlich würden alle Zeilen aller Seiten das Excellimit von > 1.000.000 Zeilen sprengen. In diesem Fall kannst Du trotzdem alle Datensätze einlesen, darfst sie aber nur ins Datenmodell laden und könntest das dann mit PowerPivot auswerten lassen. Hab das im letzten Abfrageschritt mal mit eingebaut (also die Zeilenbegrenzung)

Denke daran, je dynamischer man Abfragen gestaltet, umso mehr Zeit benötigen die Abfragen. Es wäre also zu überlegen, ob Du wirklich alle 500 Seiten brauchst.

Die Adresse der Website kannst Du in der Funktion fx_import_cef_excel_forum im Schritt Source anpassen.


.xlsx   cef - Import Excel Forum-dynamischeSeitenanzahl.xlsx (Größe: 38,08 KB / Downloads: 3)
Der sicherste Ansatz für einen Irrtum ist der Glaube, alles im Griff zu haben.
Nur, weil ich den Recorder bedienen kann, macht mich das noch lange nicht zum Musiker.

Ciao, Ralf

Antworten Top
#10
Hi Ralf,

Edgar will ja nicht das Excel-Forum auslesen! Dies habe ich nur verwendet, um zu zeigen wie es geht. Normalerweise würde ich auch einen Von-/Bisbereich definieren. Aber da es sich bei Edgar wohl nicht um eine lückenlose Nummerierung, sondern um eine Liste von Nummern handelt, habe ich die Tabelle gewählt, über die ich festlege, welche Seiten ausgelesen werden sollen.
Antworten Top


Gehe zu:


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