How to Download PDF and ZIP Files from a Webpage Using Excel Power Query
Question details
The user wants to automate the process of parsing a webpage to find PDF or ZIP file links and subsequently download and extract data from those files using Power Query.
- Product
- Excel Power Query
- Device & OS
- not provided
- Scenario
- Scraping a website that contains multiple versions of documents to extract URLs, locate specific PDF files, and retrieve document content directly into Excel.
- Observed behavior
- The user needs the correct M code approach to navigate the HTML structure, isolate the desired anchor tags, construct valid absolute URLs, and process the downloaded file contents.
Ensure you have a stable internet connection and that the target website allows automated web scraping without requiring advanced authentication or CAPTCHA solving.
Use Power Query M Code to Extract and Import Webpage PDFs
Utilize the Web.BrowserContents and Html.Table functions to scrape the webpage's HTML structure, extract valid file URLs, and load the PDF content directly into Power Query.
This method leverages Power Query's built-in web scraping and PDF parsing capabilities. By targeting specific HTML tags (like anchor tags inside preformatted text), you can dynamically build a table of file URLs and process their contents automatically.
Open Excel, go to the Data tab on the ribbon, click on 'Get Data', navigate to 'From Other Sources', and select 'Blank Query'.
In the Power Query Editor window, go to the Home tab and click on 'Advanced Editor' to input custom M code.
Use the function `Web.BrowserContents("YOUR_WEBSITE_URL")` to load the HTML text from the target webpage.
Convert the HTML text to a table and use `Html.Table` to extract the `href` attributes. For example: `Html.Table([HtmlText], {{"Link", "a", each [Attributes][href]}})`.
If the extracted links are relative, add a custom column to concatenate the website's root domain with the relative path to create a valid absolute URL.
Add a new column using `Pdf.Tables(Web.Contents([Link]))` to download each file and read its tabular data into Power Query.
Looking for a Lightweight, Cost-Effective Spreadsheet Tool?
While advanced Power Query M-scripting is deeply integrated with Excel, WPS Office provides a comprehensive, free, and highly compatible suite for all your standard data analysis, charting, and daily spreadsheet management needs without the heavy subscription fees.
- 1. Download the Installer: Visit the official WPS Office website and download the free installer for your operating system.
- 2. Install WPS Office: Run the setup file and follow the on-screen prompts to complete the installation in just a few minutes.
- 3. Open Your Spreadsheets: Launch WPS Spreadsheet and open your existing .xlsx or .csv files to continue your data analysis work seamlessly.
Frequently Asked Questions
Can Power Query bypass website login screens to download files?
Power Query can handle basic web authentication or API keys via its Data Source Settings. However, it generally struggles with complex login forms, Multi-Factor Authentication (MFA), or CAPTCHAs. For highly secure sites, you typically need to connect via an authenticated API rather than scraping the webpage directly.
Why does Web.Contents return a 404 error when attempting to download PDFs?
This usually happens if the extracted URL is a relative path (e.g., '/documents/file.pdf') rather than an absolute URL. Ensure you concatenate the website's root domain name (e.g., 'https://www.example.com') to the relative path before passing it to the Web.Contents function.
Can I extract data from ZIP files directly using Power Query?
Power Query natively supports extracting data from formats like CSV, XML, or Excel. Unzipping ZIP archives directly requires advanced custom M code functions using `Binary.Decompress` with `Compression.Deflate`, or utilizing third-party custom connectors to handle the decompression natively.
