Scraping a website

[madpiet]

Just for fun, I tried doing this using PowerQuery. Just stupid level easy.

let
    Source = Web.BrowserContents("https://www.fedex.com/fedextrack/?trknbr=581190049992&trkqual=12023~581190049992~FDEG"),
    #"Extracted Table From Html" = Html.Table(Source, {{"Column1", "TRK-SHARED-SHIPMENT-FACTS-NEW:nth-child(2) > DIV > DIV.shipment-facts-box.mt-8.mb-7:nth-child(1) > DIV.body > TABLE.shipment-fact-table.fdx-c-table.fdx-c-table\-\-default.mt-2.mb-2:nth-child(5) > * > TR > :nth-child(1)"}, {"Column2", "TRK-SHARED-SHIPMENT-FACTS-NEW:nth-child(2) > DIV > DIV.shipment-facts-box.mt-8.mb-7:nth-child(1) > DIV.body > TABLE.shipment-fact-table.fdx-c-table.fdx-c-table\-\-default.mt-2.mb-2:nth-child(5) > * > TR > :nth-child(2)"}}, [RowSelector="TRK-SHARED-SHIPMENT-FACTS-NEW:nth-child(2) > DIV > DIV.shipment-facts-box.mt-8.mb-7:nth-child(1) > DIV.body > TABLE.shipment-fact-table.fdx-c-table.fdx-c-table\-\-default.mt-2.mb-2:nth-child(5) > * > TR"]),
    #"Changed Type" = Table.TransformColumnTypes(#"Extracted Table From Html",{{"Column1", type text}, {"Column2", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column1] = "ACTUAL DELIVERY")),
    #"Transposed Table" = Table.Transpose(#"Filtered Rows"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"ACTUAL DELIVERY", type text}})
in
    #"Changed Type1"

The super annoying thing is the Excel includes the PowerQuery library or whatever, but Access doesn't. So you'd likely have to run it through Excel, so I guess it's a non- answer. (that's okay... took me like 2 minutes to do)

[Euler]

Just for fun, I tried doing this using PowerQuery. Just stupid level easy.

let
    Source = Web.BrowserContents("https://www.fedex.com/fedextrack/?trknbr=581190049992&trkqual=12023~581190049992~FDEG"),
    #"Extracted Table From Html" = Html.Table(Source, {{"Column1", "TRK-SHARED-SHIPMENT-FACTS-NEW:nth-child(2) > DIV > DIV.shipment-facts-box.mt-8.mb-7:nth-child(1) > DIV.body > TABLE.shipment-fact-table.fdx-c-table.fdx-c-table\-\-default.mt-2.mb-2:nth-child(5) > * > TR > :nth-child(1)"}, {"Column2", "TRK-SHARED-SHIPMENT-FACTS-NEW:nth-child(2) > DIV > DIV.shipment-facts-box.mt-8.mb-7:nth-child(1) > DIV.body > TABLE.shipment-fact-table.fdx-c-table.fdx-c-table\-\-default.mt-2.mb-2:nth-child(5) > * > TR > :nth-child(2)"}}, [RowSelector="TRK-SHARED-SHIPMENT-FACTS-NEW:nth-child(2) > DIV > DIV.shipment-facts-box.mt-8.mb-7:nth-child(1) > DIV.body > TABLE.shipment-fact-table.fdx-c-table.fdx-c-table\-\-default.mt-2.mb-2:nth-child(5) > * > TR"]),
    #"Changed Type" = Table.TransformColumnTypes(#"Extracted Table From Html",{{"Column1", type text}, {"Column2", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column1] = "ACTUAL DELIVERY")),
    #"Transposed Table" = Table.Transpose(#"Filtered Rows"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"ACTUAL DELIVERY", type text}})
in
    #"Changed Type1"

The super annoying thing is the Excel includes the PowerQuery library or whatever, but Access doesn't. So you'd likely have to run it through Excel, so I guess it's a non- answer. (that's okay... took me like 2 minutes to do)

Thanks a lot for your effort. I think the answer might be to read the table with HTML-something I don't know how to do.

[madpiet]

you couldn't pay me to scrape a website with VBA. No way no how.

Just pointed it out as an option. Took me maybe 30 seconds to do. Maybe.

[Welshgasman]

Thanks a lot for your effort. I think the answer might be to read the table with HTML-something I don't know how to do.

Plus if you only have 2007 as I do, that is not an option.
It is for you though?

[Edgar]

Euler, using the Fedex URL, you won't be able to get that data. We have two examples in this thread, Bing and Fedex.

When we use the code in post #4, which is a regular HTTP request:
if you use Bing's URL, you will get some (partially right) data nicely formatted within HTML elements meant to be shown.
if you use Fedex URL, you will get no data, you get scripts not meant to be shown, besides, it's just code, not data.

Then WHY the Fedex URL returns the data? Well, because of those scripts. Those scripts progressively send even more HTTP requests in order to get that data. One of these HTTP requests finally gets the data we need.

Then WHY don't we use that HTTP request? Because that HTTP request needs extra info only a browser can provide. We need a browser. This is the URL that provides that info:
https://api.fedex.com/track/v2/shipments

Power Query also uses a browser, most likely, as I don't use that tool.

Also, this is a query for ship date, the 15th TD tag. Try with other tracking numbers to find out if it's consistent.
document.getElementsByTagName('td')(15).innerText

[NoellaG]

Maybe a tip: https://www.mssqltips.com/sqlservert...m_content=head