Power Query Problems

How to Import and Format Variable TXT Files in Excel Using Power Query

Kushani Nimanthika Oct 14, 2026 870 views

Question details

The user needs to import a text file with variable-length sections (e.g., flight simulator briefing data) into Excel and transform it to perfectly fit a specific pre-existing table structure.

How to Import and Format Variable TXT Files in Excel Using Power Query

Product: Excel
Device & OS: not provided
Scenario: Importing and cleaning inconsistently formatted text data from an external source so it can be used in a structured tabular format.
Observed behavior: The source text file has sections that vary in line counts (e.g., some sections have two lines, others have four), making standard delimiter-based import methods fail to align with the target table layout.

Before you start

Ensure you have access to a few sample TXT files to identify the recurring patterns, boundary markers, or delimiters before setting up your automated query.

Solution 1Recommended

Use Power Query to Transform Variable Text Files

Power Query is the most robust and flexible tool for importing text files with variable lengths, allowing you to clean, split, and reshape the data without writing complex code.

Power Query handles inconsistencies better than standard text imports by allowing you to build step-by-step transformation rules that evaluate the structure of the data as it loads.

Load the TXT File

Open Excel, navigate to the 'Data' tab, select 'Get Data', then 'From File', and choose 'From Text/CSV'. Select your variable TXT file and click 'Import'.

Open Power Query Editor

In the preview window, click 'Transform Data' to open the Power Query Editor instead of loading it directly into the sheet.

Identify Boundaries and Split

Use the 'Split Column' feature to separate your text based on known delimiters. Add an 'Index Column' from the 'Add Column' tab to keep track of the original row order.

Reshape Variable Sections

Create a 'Conditional Column' to identify section headers or varying line counts. Use the 'Fill Down' tool to group associated lines together, then filter out empty or redundant rows.

Load to Excel

Once the data matches your existing table structure, click 'Close & Load To...' on the Home tab and choose to output the data to your designated Excel worksheet.

Use Power Query to Transform Variable Text Files

Power Query Automation: Power Query saves your transformation steps. When a new TXT file is generated, simply replace the file and click 'Refresh All' to update the table instantly.

Solution 2

Write a Custom VBA Macro for Parsing Text

If Power Query is not available or you need to integrate the import process into a legacy automated workflow, a VBA macro can parse the file line-by-line.

Transform Data with WPS Spreadsheet

Import and Format Text Files efficiently with WPS Office

WPS Spreadsheet offers powerful built-in tools to import text files, utilize Text-to-Columns, and write custom macros, making it easy to organize variable data files efficiently.

1. Open Data Import: Open WPS Spreadsheet, go to the 'Data' tab, and click on 'Import Data'.
2. Select the TXT File: Choose the variable text file you wish to import from your local directory.
3. Use the Import Wizard: Use the Text Import Wizard to specify delimiters, fixed widths, and data formats for your incoming text.
4. Refine with Text to Columns: Utilize the 'Text to Columns' feature to further split variable data lines into structured cells.
5. Automate with Macros: Run WPS Macros if custom line-by-line parsing is required for highly complex variable sections.

Fully compatible with Microsoft Excel (.xlsx) formatsBuilt-in Text-to-Columns and Data Import wizardsSupports advanced macros for custom text parsing and automationFree, lightweight, and user-friendly interface

microsoft office alternative - wps office

Frequently Asked Questions

Why use Power Query instead of VBA for variable text files?

Power Query provides a visual interface to apply step-by-step transformations, making it easier to handle inconsistencies, view data previews, and troubleshoot without writing complex code. It also updates automatically when the source file changes.

How do I handle text files where some sections have 2 lines and others have 4?

In Power Query, you can add an Index column, use Conditional Columns to identify section headers, and apply the 'Fill Down' feature to group lines correctly before pivoting the data into a structured table.

Can I automate the data import for future TXT files?

Yes. Once you have built your Power Query steps, you can save the query. When a new text file is generated, simply replace the old file (or point your query to a folder) and click 'Refresh All' in Excel to update the table instantly.

Does WPS Spreadsheet support importing variable TXT data?

Yes, WPS Spreadsheet features a robust Data Import wizard and supports macros, allowing you to parse, split, and format complex text files effectively.

Excel & Data Problems