How to Parse a Text File into Rows and Columns in WPS Office Excel
Question details
The user needs to import a structured text file into WPS Office Excel and convert its header, subheader, and data row hierarchy into a consistent table with separate rows and columns.
- Product
- WPS Office Excel (WPS Spreadsheets)
- Device & OS
- not provided
- Scenario
- A user has a text file with mixed section headers, subheaders, and data rows and wants a repeatable import that produces a normalized table.
- Observed behavior
- The source file is not yet in a uniform tabular layout because header, subheader, and data records are stored at different hierarchy levels.
Open the text file in a plain-text editor and identify the delimiter, such as comma, tab, pipe, or fixed width, and note which rows represent headers, subheaders, and data. Decide the final table layout you need before starting the import so the Power Query steps remain repeatable.
Parse the text file with Power Query in WPS Office Excel
Use this method when you need a repeatable import that separates header, subheader, and data rows into a clean row-and-column table from a text or CSV source.
Power Query records the import and cleanup steps, which makes it the best option when the text file has structural rows such as section titles and subheaders. You can refresh the query later instead of repeating manual text-to-columns work.
In WPS Office Excel, open a blank workbook, select the Data tab, and click Get Data > From Text/CSV.
Locate the text file, click Import, and in the preview dialog choose Transform Data to open the Power Query Editor.
Click Use First Row as Headers to promote the real column headers, then remove rows that contain section titles, subheaders, or repeated header text.
Select the combined column and use Split Column > By Delimiter or By Number of Characters to create a separate column for each data field.
Click Close & Load To, choose Table, and select a new worksheet so the parsed rows and columns are loaded as a refreshable table.
Reshape imported data with a LET and WRAPROWS dynamic array formula
Use this method when the text content is already loaded in a worksheet and you only need to restructure selected columns into row-and-column pairs.
Parse Text Files into Rows and Columns with WPS Office Excel
WPS Office Excel provides the Data tab, Power Query import tools, and formula support to parse structured text files into clean tables. Load the source once, record the header and column transformations, and refresh the result whenever the text file changes.
- 1. Download and open WPS Office Excel: Download WPS Office from the official WPS site, install it, and open WPS Office Excel from the home screen.
- 2. Launch the text import: Select the Data tab and click Get Data > From Text/CSV to locate and load the source text file.
- 3. Build the parser in Power Query: Promote headers, filter out subheader and section rows, and split fields by delimiter or fixed width in the Power Query Editor.
- 4. Load to a worksheet: Click Close & Load To, choose Table, and place the result in a new sheet.
- 5. Refresh the imported table: Edit the source text file and click Data > Refresh All to reapply the parsing steps.
Frequently Asked Questions
Can WPS Office Excel parse fixed-width text files as well as delimited files?
Yes. In the Power Query Editor, select the column and use Split Column > By Number of Characters or By Positions, then set the widths that match your fixed-width source.
How do I keep subheaders from becoming column headers?
Promote the first real data header row with Use First Row as Headers, then filter out section title and subheader rows before loading. You can also add a conditional column to tag data rows if subheaders must be preserved separately.
Does the formula method refresh automatically?
Yes, the LET-based dynamic array formula recalculates when the source range changes, but it is best for reshaping data already placed in a sheet. Power Query is more repeatable for external text files.
Can I refresh the parsed table after editing the original text file?
Yes. Load the data through Power Query and then click Data > Refresh All in WPS Office Excel to re-run the recorded import and transformation steps.
Which file formats can I import with this method?
Use From Text/CSV to import .txt, .csv, .tsv, and similar text-based files. After import, you can save the result as .xlsx for full Microsoft Excel compatibility.
