How to Open and Extract Nested ZIP Files in Power Query
Question details
The user is attempting to import data from a web address into Power Query, but the process fails because the downloaded archive contains multiple nested ZIP files.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Importing complex data archives (such as NSE India market data) from a web source directly into Excel using Power Query.
- Observed behavior
- Power Query is unable to natively open or extract the nested ZIP archives from the web link, often pulling the HTML page or redirect instead of the underlying CSV data files.
Ensure you have a standard archive extraction tool (like Windows File Explorer, 7-Zip, or WinRAR) installed to manually unpack nested folders before importing data.
Manually Extract Nested Archives Before Importing
Download the file to your computer and manually extract all layers of ZIP files to access the raw data files (like CSVs) before using Power Query.
Power Query cannot automatically drill down into multiple layers of zipped folders downloaded directly from a web link. The most reliable method is to unpack these layers locally.
Navigate to the source website and download the primary ZIP archive directly to your desktop or a designated local folder.
Right-click the downloaded ZIP file and select 'Extract All...' (or use your preferred archive utility) to unpack the first layer of files.
Open the newly extracted folder. If it contains additional ZIP files, right-click and extract those as well. Repeat this process until you reveal the actual raw data files, such as .csv or .txt files.
Open Excel, navigate to the Data tab, select 'Get Data', choose 'From File', and then click 'From Text/CSV'. Locate your fully extracted data file and import it.
Verify the Web URL for Direct ZIP Downloads
Ensure the URL used in the 'From Web' feature points directly to the ZIP file rather than an HTML webpage or a download redirect.
Import Extracted Data Easily with WPS Spreadsheet
After extracting your nested ZIP files, you can seamlessly import your CSV or text data into WPS Spreadsheet. It offers a fast, lightweight, and highly compatible environment for managing large market datasets without complex queries.
- 1. Open a new workbook: Launch WPS Spreadsheet and create a blank workbook.
- 2. Access the Data tab: Navigate to the 'Data' tab on the top ribbon.
- 3. Launch the import wizard: Click on 'Import Data' and select the 'Import Data' option to start the process.
- 4. Select the extracted file: Browse your local computer for the completely extracted CSV file and click 'Open'.
- 5. Format and finish: Follow the Text Import Wizard prompts to set the correct delimiters (like commas or tabs) and load the data accurately into your spreadsheet.
Frequently Asked Questions
Why does 'Get Data From Web' return an HTML page instead of my ZIP file?
This happens when the copied URL points to a dynamic download page or a redirect script rather than the direct file path of the ZIP archive. Consequently, Power Query reads the webpage's structural code instead of initiating the file download.
Can Power Query automatically unzip nested ZIP files?
Natively, Power Query does not support extracting multi-level nested ZIP files directly. While advanced users can write custom M code to parse single ZIPs, nested archives generally require manual extraction or third-party scripting.
How do I automate the extraction of daily nested ZIP downloads?
For recurring tasks, you can use Python or PowerShell scripts to automatically download and extract the nested ZIP archives to a designated folder on your PC. Power Query can then be set to monitor and import data from that specific folder.
Does this nested ZIP issue only affect NSE India market data?
No, this issue applies to any web data source that packages files within multiple layers of ZIP archives to save bandwidth. It is a common practice for bulk financial datasets and public government data distributions.
