Power Query Problems

How to Parse a ZIP-Archived TXT File with Excel Power Query

Emma BrownEmma Brown Nov 13, 2026 870 views

Question details

The user wants to import a TXT file stored inside a ZIP archive into Excel Power Query and transform it into the layout required by an Excel template.

How to Parse a ZIP-Archived TXT File with Excel Power Query
Product
Microsoft Excel Power Query
Device & OS
not provided
Scenario
The user has a ZIP archive containing a TXT file and needs to load, parse, and shape its contents for use in an Excel template.
Observed behavior
The goal is to extract and parse the TXT file from the ZIP archive and load a structured query result that matches the Excel template layout.
Before you start

Locate the ZIP archive and note the exact internal TXT file name, delimiter, and encoding you need to parse. In Excel, confirm that the Data tab shows the Power Query tools such as Get Data > From Folder and From Text/CSV.

Solution 1Recommended

Import and Parse the TXT Directly from the ZIP with Power Query

Use this solution when you need a refreshable Power Query process that reads the ZIP archive, selects the TXT file, and applies text parsing without manually unzipping.

Power Query can expose a ZIP archive as binary content when you connect to the containing folder. You then select the TXT entry and pass its binary content through the text/CSV connector so delimiter, encoding, and column layout are handled in one query.

1
Open Power Query from the Excel Data tab

In Excel, click Data > Get Data > From File > From Folder. Browse to the folder that contains the ZIP archive and click Open to list the files in Power Query.

2
Load the ZIP archive into Power Query

In the Navigator or query preview, select the ZIP file row, right-click the Content binary cell, and choose Add as New Query or double-click the ZIP file to open its contents in Power Query.

3
Filter for the required TXT entry

Locate the file name or entry column, filter it to the exact .txt file name inside the ZIP, and select the Binary content cell for that TXT row.

4
Apply the Text/CSV connector to parse the TXT

Use the text/CSV connector or the Get Data > From Text/CSV option on the selected TXT binary to choose the delimiter, such as comma, tab, or semicolon, and set the encoding in the preview.

5
Shape columns for the Excel template

Promote the first row as headers if needed, rename or split columns, remove unwanted rows, and set data types so the query output matches the Excel template layout.

6
Load the query into the template

Click Close & Load, choose Table or Only Create Connection, then place the output in the target worksheet or merge it with the existing template as required.

Import and Parse the TXT Directly from the ZIP with Power Query
Refreshable result: After you load the query, use Data > Refresh All to re-read the ZIP and TXT file when the source file changes.
Free Microsoft Office alternative

Use WPS Office as a Free Alternative for Excel Power Query ZIP/TXT Workflows

If Excel Power Query is unavailable or the ZIP/TXT parsing workflow feels too complex, WPS Office offers a free, lightweight alternative with strong .xlsx compatibility, a familiar Office-style ribbon, and built-in TXT/CSV import tools for preparing data in WPS Spreadsheet.

Opens and saves .xlsx files while preserving Excel-compatible formatting.Free and lightweight Office suite with a familiar ribbon interface.Built-in text/CSV import makes basic TXT data preparation straightforward.Lets you migrate existing Excel workbooks without changing your file format.Lower system resource usage than Microsoft Office for everyday spreadsheet tasks.
QA img-9

Frequently Asked Questions

Does Excel Power Query natively open ZIP archives?

In Excel, use Get Data > From Folder to connect to the ZIP as a binary source and then access the TXT entry. If your Power Query version does not list ZIP contents, extract the TXT first and import it with From Text/CSV.

How do I set delimiter and encoding when parsing the TXT?

In the Power Query editor, select the TXT content and use the Text/CSV connector preview to choose delimiter, encoding, and header promotion before applying column transformations.

Can I refresh the query when the ZIP file changes?

Yes, keep the ZIP file path and TXT file name stable, then use Data > Refresh All to re-run the query and update the loaded output.

Why does my TXT parse into one column?

Check the delimiter settings in the Text/CSV connector and confirm the correct encoding. Then use Split Column or promote headers to separate the data into the required layout.

Does WPS Office have Power Query?

WPS Office does not include Excel Power Query, but it can open XLSX templates and import TXT/CSV files through its spreadsheet tools.