Search code examples
excelexcel-web-query

Excel: import from web

For some reason, the web import fails with this website! What I am looking for is the table that is generated after choosing the needed filter.

https://support.microsoft.com/en-us/lifecycle/search?alpha=windows%2010

The required table appears in the "New Web Query" window, but when I click import if fails to import returning an Error Msg "This web query returned no data".

Thanks,

Solution

  • These steps work for me:

    • Create a new blank workbook (I'm using Excel 2016.)

    • Go to Data tab → From Web

    • In the From Web window paste: 1https://support.microsoft.com/en-us/lifecycle/search?alpha=windows%20101 and then click OK

    web data

    • Click Table 0 and then Load

    web data load

    • A few seconds later the data is loaded.

    web data loaded

    Update:

    I noticed that after I repeated the same steps 3 or 4 times, the page no longer loads for me, which is likely due to the site detecting scraping and temporarily blocking me (and probably the same for you).

    Anyway here is a link to a workbook via Jump Share with the table attached & loaded for you. Note that it won't work on the web version, you'll need to download with the blue Download button.

    Let me know if it works for you.

    Update #2:

    An alternative option is to use the pages' CSV Export rather than scraping the page:

    • Go to Data tab → From Text/CSV

    • In the Import Data dialog, paste this address:

    https://support.microsoft.com/api/lifecycle/GetProductsLifecycle?query=%7B%22names%22:%5B%22windows%252010%22%5D,%22years%22:%220%22,%22gdsId%22:0,%22export%22:true%7D

    • Leave the defaults as-is assuming they are:

    File Origin: 65001: Unicode (UTF-8)

    Delimiter: Comma

    • Click Load. A moment later, the data appears as a table on a worksheet.

    🇨🇦