Power Query Problems

How to Import Raw Text File Contents in Power Query Instead of XML

John Wilson Oct 9, 2026 869 views

Question details

The user needs to extract timestamps and raw text from XML files across multiple subfolders without Power Query automatically interpreting and parsing the data as XML.

How to Import Raw Text File Contents in Power Query Instead of XML

Product: Microsoft Excel
Device & OS: not provided
Scenario: Importing files from multiple subfolders where the file content contains XML, but the user wants to extract the raw text and file timestamps rather than structured XML data.
Observed behavior: Power Query and Power Pivot automatically interpret the files as XML during the combination process, preventing the extraction of raw text strings and folder metadata.

Before you start

Ensure all the target files are organized within a single parent folder or its subfolders, and verify that you have the correct folder path ready for import.

Solution 1Recommended

Use Power Query to Import File Contents as Plain Text

Override the default XML parsing by explicitly extracting the binary file content as text during the folder import process.

When importing files from a folder, Power Query analyzes the first few bytes of the files. If it detects XML, clicking 'Combine Files' will automatically trigger an XML parsing step. To prevent this, you must handle the binary content manually using a custom column.

Get Data from Folder

Open Excel, navigate to the 'Data' tab on the ribbon, click 'Get Data' > 'From File' > 'From Folder', and select the parent folder containing your subfolders.

Open Power Query Editor

In the preview window that appears, click 'Transform Data' (do not click 'Combine'). This opens the Power Query Editor and displays a list of your files along with their metadata, including timestamps.

Filter and Select Columns

Select the 'Content' column (which holds the binary data) and any metadata columns you need, such as 'Date modified' or 'Date created'. Right-click the selected column headers and choose 'Remove Other Columns'.

Extract Binary as Text

Go to the 'Add Column' tab and click 'Custom Column'. Name the new column (e.g., 'RawText') and enter the formula: Text.FromBinary([Content]). This forces Power Query to read the binary data strictly as plain text instead of XML.

Clean Up and Load

Right-click the original 'Content' column and select 'Remove'. You should now have your raw text column alongside your timestamps. Finally, go to the 'Home' tab and click 'Close & Load' to import the data into Excel.

Use Power Query to Import File Contents as Plain Text

Data Extraction Successful: By extracting the binary data directly using a custom formula, you bypass Power Query's automatic XML detection, allowing full access to raw tags and textual content.

Free Microsoft Office alternative

Try WPS Office for Lightweight Data Processing

If complex Power Query setups in Microsoft Excel are slowing down your workflow, WPS Office offers a free, lightweight, and highly compatible alternative. WPS Spreadsheets provides intuitive data import tools and seamless support for standard Excel files.

Fully compatible with Microsoft Excel formats (.xlsx, .xls, .csv, .xml).Lightweight software design for faster file loading and processing.Intuitive data import wizards for text and CSV files without forced formatting.Free to use with a familiar, user-friendly interface for seamless migration.

Frequently Asked Questions

Why does Power Query automatically convert my text files to XML?

Power Query analyzes the first few rows of a file during the import process. If it detects XML structure and tags, it automatically applies an XML parsing step to structure the data into tables. You must manually override this by extracting the binary content as text.

Can I extract the file creation date alongside the raw text content?

Yes. When you use the 'From Folder' connector in Power Query, the initial table includes metadata columns such as 'Date created', 'Date modified', and 'Date accessed'. You can keep these columns in the Power Query Editor before converting the binary content to text.

What if my raw text data exceeds Excel's row limit?

If the combined raw text from all your files exceeds Excel's limit of 1,048,576 rows, you can click the arrow under 'Close & Load', select 'Close & Load To...', and choose 'Only Create Connection'. You can then add the data to the Data Model to analyze it using PivotTables without loading it onto the spreadsheet.

Excel & Data Problems