From Notepad Tables to Power Query: Fast Ways to Turn Text Files into Clean Reports

Turn Notepad tables into refreshable Excel reports — fast

If your team exports light, plain-text tables from legacy tools or Notepad and every refresh is a manual pain, this guide is for you. In 10–30 minutes you can convert raw text files into structured Excel tables and refreshable Power Query queries that you or non-technical colleagues can update in one click.

Why this matters in 2026

Lightweight exports remain common in 2026: cloud ERPs coexist with older systems that still spit out pipe-, tab- or space-delimited text. Microsoft continues to evolve Windows and Notepad (tables in Notepad are now commonplace), and Power Query has become even more central for low-code ETL in Excel and Microsoft 365. Modern business operations expect reusable, auditable workflows — not a new spreadsheet each month.

What you’ll get from this tutorial

• Step-by-step recipes to load plain-text tables (space, tab, pipe) into Power Query
• Power Query (M) snippets that handle messy whitespace, headers and footers
• VBA examples to automate imports and refresh queries for users who prefer macros
• Practical governance and performance tips for production-ready refreshable reports

Quick overview: Two practical approaches (pick one)

1. Power Query (recommended) — Load the text file with Data > Get Data > From File > From Text/CSV or From Folder, transform with Power Query, and load as an Excel table or data model. Refreshable, low-code, scalable.
2. VBA + QueryTable — For legacy workflow automation or where users lock macros in scheduled tasks, use a short VBA routine to import text and convert to a Table. Good for desktops without Power Query or for tight automation scripts.

Real-world scenario: A legacy system exports a pipe-delimited monthly list

Imagine an old inventory tool exporting a file inventory_202601.txt that looks like this (pasted into Notepad):

ItemID | ItemName | Qty | UnitPrice | LastUpdated
1001 | Widget A | 120 | 2.50 | 15/12/2025
1002 | Widget B | 45 | 5.75 | 17/12/2025
-- End of report --

We’ll convert this into a clean, typed Excel table and a refreshable Power Query. The steps apply to tab-delimited or messy space-delimited exports as well.

Power Query recipe: Robust import for pipe, tab or messy space-delimited text

Follow these steps inside Excel (Microsoft 365 / Excel for Windows):

1. Data > Get Data > From File > From Text/CSV and select the .txt file.
2. In the preview dialog click Transform Data to open Power Query Editor.
3. If your file is cleanly delimited (pipe or tab), set the correct Delimiter in the preview (e.g. | for pipe) and click OK. If preview fails because the file contains inconsistent spacing or embedded delimiters, use the fallback method below.
4. Remove rows that are headers/footers (use Remove Rows > Remove Top Rows or Filter out text like "End of report").
5. Use Use First Row as Headers (Home > Use First Row as Headers).
6. Change data types: Date, Decimal Number, Whole Number as appropriate. For UK dates, set Locale = English (United Kingdom) when changing the data type to Date.
7. Close & Load To... > Table (or Data Model) so users can refresh the query.

Power Query M: fallback for messy space-delimited text

If the export uses variable spaces (common when systems output aligned columns), Power Query can split on spaces and drop blanks. Paste this M code into Advanced Editor and adapt the file path. (If you paste into the editor, replace the path with your File path.)

let
    Source = Text.FromBinary(File.Contents("C:\\Data\\inventory_202601.txt"), TextEncoding.Utf8),
    // Split into lines and drop blank lines
    Lines = List.Select(Text.Split(Source, "\n"), each Text.Trim(_) <> ""),
    // Remove header/footer lines you know by pattern
    Body = List.Select(Lines, each not Text.StartsWith(Text.Trim(_), "--")),
    // Convert lines to list of columns by splitting on spaces, then remove empty tokens
    SplitLines = List.Transform(Body, each List.Select(Text.Split(Text.Trim(_), " "), each _ <> "")),
    // Convert to table and promote first row as header
    TableFromLists = Table.FromList(SplitLines, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    // Expand list into columns
    Expand = Table.ExpandListColumn(TableFromLists, "Column1"),
    // Transpose so rows become columns
    Transposed = Table.Transpose(Table.Group(Expand, {}, {"Rows", each _[Column1]})),
    // Promote headers and set types (adjust column names as needed)
    Promoted = Table.PromoteHeaders(Transposed, [PromoteAllScalars=true]),
    ChangedTypes = Table.TransformColumnTypes(Promoted, {{"ItemID", Int64.Type}, {"Qty", Int64.Type}, {"UnitPrice", type number}, {"LastUpdated", type date}})
in
    ChangedTypes

Notes: The approach above transposes the expanded list to align columns correctly. For very large files, replace Table.Transpose with a more efficient expansion method or import via CSV with a known delimiter.

Power Query: From Folder pattern (multiple exports)

If your export drops a file each day/month into a folder, use Data > Get Data > From File > From Folder. Use the Combine Files option and apply the same transformations to each file. Benefits:

• Single query that ingests new files automatically
• Consistent transformations and types for aggregation
• Simple refresh to bring in new exports

Example: Pipe-delimited import (simple)

let
  Source = Csv.Document(File.Contents("C:\\Exports\\inventory_202601.txt"), [Delimiter="|", Columns=5, Encoding=65001, QuoteStyle=QuoteStyle.None]),
  PromotedHeaders = Table.PromoteHeaders(Table.FromList(Source, Splitter.SplitByNothing(), null, null)),
  ChangedTypes = Table.TransformColumnTypes(PromotedHeaders, {{"ItemID", Int64.Type}, {"Qty", Int64.Type}, {"UnitPrice", type number}, {"LastUpdated", type date}})
in
  ChangedTypes

VBA alternative: Quick import to a Table (for macro fans)

Use this VBA snippet when you want an automated desktop macro that imports a text file into a worksheet and converts it to a named Table. It's useful for older Excel versions or highly customised macros.

Sub ImportTextToTable()
  Dim ws As Worksheet
  Dim qt As QueryTable
  Dim fname As String

  fname = "C:\\Exports\\inventory_202601.txt"
  Set ws = ThisWorkbook.Worksheets("Raw") ' ensure sheet exists
  ws.Cells.Clear

  Set qt = ws.QueryTables.Add(Connection:="TEXT;" & fname, Destination:=ws.Range("A1"))
  With qt
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierNone
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = False
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = False
    .TextFileOtherDelimiter = "|" ' change as needed: "\t" for tab
    .TextFileColumnDataTypes = Array(xlGeneralFormat, xlTextFormat, xlGeneralFormat, xlGeneralFormat, xlMDYFormat)
    .Refresh BackgroundQuery:=False
  End With

  ' Convert imported range to a Table
  Dim tbl As ListObject
  Set tbl = ws.ListObjects.Add(xlSrcRange, ws.UsedRange, , xlYes)
  tbl.Name = "InventoryRaw"
End Sub

Adjust .TextFileOtherDelimiter and .TextFileColumnDataTypes to match your file. This approach gives a Table you can run further macros against.

Automating refresh for Power Query via VBA

To programmatically refresh a Power Query query from VBA (e.g. part of a scheduled workbook update), use this snippet. Replace QueryName as required.

Sub RefreshQuery()
  Dim connName As String
  connName = "Query - Inventory" ' find exact connection name in Workbook Connections
  ThisWorkbook.Connections(connName).Refresh
End Sub

Common messy patterns and fixes

• Headers embedded mid-file — Filter out rows by content (e.g. remove rows where Column1 = "ItemID" after combining files).
• Thousands separators in numbers — Use Text.Replace to remove commas before converting to number.
• Different date formats — Use Date.FromText with Locale or parse manually with Date.FromText(Text.Middle(...)).
• Trailing summary rows — Filter out rows with known footer text or non-numeric IDs.

Performance and governance best practices

1. Landing folder: Have one network or SharePoint 'landing' folder where exports land. Use From Folder so new files are automatically picked up.
2. Staging queries: Keep a raw staging query (disable load) and perform transformations in a separate 'presentation' query. This improves maintainability and debugging.
3. Data types last: Set data types near the end of the query to avoid expensive type coercion during transformations.
4. Name your queries and tables: Use a consistent naming convention like Src_Inventory_Raw and Dim_Inventory_Clean to communicate purpose.
5. Document transforms: Add a step comment in Power Query (right-click on a step > Properties) describing why you removed a row or split a column. Consider a QA checklist similar to link-quality QA when you standardise transform steps.
6. Locale awareness: For UK users, set the query Locale when parsing dates and numbers to avoid dd/mm/yyyy vs mm/dd/yyyy issues.
7. Security: If the exports contain sensitive data, use SharePoint or OneDrive links with limited permissions rather than open network folders.

Advanced tips — make it resilient in 2026

• Use Power Query parameters for file path, delimiter and date format so one report can be reused across countries or systems.
• Combine Power Automate with Microsoft 365 automation to move exports into the landing folder automatically when a legacy system writes a file.
• Use a scheduled refresh on Power BI or Fabric if you need centralised refresh without desktop Excel.
• For very large files, prefer From Folder and server-side processing (Power BI or Fabric) rather than client-side Excel to avoid memory limitations.

Mini case study: How a small distributor saved 6 hours/month

Challenge: A UK distributor received a monthly stock extract as a notepad text file. Every month a junior operator opened the file, copied columns into Excel, fixed dates and prices, and rebuilt a pivot. The process took ~90 minutes.

Solution: We built a From Folder query (combine files) with robust trimming, header removal and type conversion. We then added a Presentation query that calculated margins and loaded clean data into an Excel table with a PivotTable dashboard. A VBA button runs a refresh-and-export macro for the finance team.

Result: Monthly time dropped from 90 minutes to 5 minutes (mostly waiting on refresh). Errors from manual reformatting fell to zero. The solution used Power Query, a tiny VBA trigger, and a consistent landing folder.

Checklist before you deploy

• Is the landing path stable and accessible to all users who refresh?
• Have you documented expected delimiters, header row patterns and footer markers?
• Are query names and table names meaningful and version-controlled?
• Have you set data types and locale to match your reporting standards?
• Is there a fallback plan for malformed files (notify via email or place in an error folder)?

Tip: When in doubt, import raw text into a staging query and build transformations incrementally. Keep raw data unchanged so you can revert quickly.

What’s changed recently (late 2025 – early 2026) and why it matters

Power Query keeps getting performance and connector improvements, and Notepad/table exports have become a more regular source as small utilities adopt lightweight table features. In organisations using Microsoft 365, the trend is to centralise ETL logic in Power Query or Fabric, not ad-hoc spreadsheets. That makes this approach — converting Notepad tables into refreshable Power Query queries — a future-proof first step toward centralised reporting.

Wrap-up: Your quick action plan

1. Identify one export file that’s manually processed today and copy it into a landing folder.
2. Use Excel > Data > From Text/CSV to build an initial Power Query, or From Folder for many files.
3. Apply trimming, split columns, remove footers, promote headers and set data types.
4. Load as a Table and build a Pivot or dashboard; add a VBA refresh macro if desired.
5. Document the workflow and hand it to a teammate for testing. Move to a scheduled refresh if needed.

Resources and quick snippets

• Power Query: Use From Folder for repeated exports
• VBA: Use ThisWorkbook.Connections("Query - YourQueryName").Refresh to refresh queries
• M tip: For inconsistent spaces, Text.Split + List.Select removes empty tokens before expanding

Next steps — free sample and templates

Want a ready-made workbook with sample Power Query steps and VBA buttons tailored for UK formats? Get our free sample workbook that includes:

• Pipe-, tab- and space-delimited sample files
• Pre-built Power Query recipes you can copy & paste
• Simple VBA macros to automate refresh and export

Ready to stop copying from Notepad and start refreshing with a click? Download the sample workbook, adapt the folder path, and test with your exported files. If you need a bespoke template or a short workshop for your team, our Power Query templates and coaching are built for UK small businesses.

Call to action: Try the sample notebook now, or contact us for a 30-minute setup session to convert your first export into a production-ready query.

Related Reading

• Free hosting platforms adopt edge AI and serverless panels — what it means for creators (2026)
• Serverless Edge for tiny workloads and low-cost hosting patterns (2026)
• Cowork on the Desktop: securely enabling agentic AI for non-developers
• Monitoring and observability for caches — tools, metrics and alerts
• Meme-Driven Microfiction: 'You Met Me at a Very [X] Time' Prompt Pack
• How to Launch a Cozy Winter Subscription Box (and What to Include)
• Mocktail Makers’ Guide to Safe Herbal Add-Ins: Interactions to Watch For
• Host a 'Behind the IP' Night: How to Turn Transmedia Projects into Group Storytelling Sessions
• AI in the Field: Using Foundation Models to Help Identify Plant Species from Photos