Convert Notepad Tables to a Refreshable Ledger: Power Query Workflow + Template

Stop copying and pasting Notepad tables: turn exports into a refreshable ledger with Power Query

If you run a UK small business, you know the frustration: your point‑of‑sale, supplier or payroll system spits out text files or Notepad tables, and every week someone pastes them into Excel, manually cleans columns and hopes they line up. That slow, error‑prone process wastes hours and introduces reconciliation headaches. In 2026 the solution is simple: let Power Query watch a folder, automatically import each export and append it to a master ledger workbook you can refresh in seconds.

What you get in this article (and the downloadable bundle)

• A clear Power Query workflow that watches a folder for CSV or text/table exports and appends them into a master ledger.
• Practical steps for handling Notepad tables and CSV quirks (delimiters, encodings, headers).
• Automation options to refresh and archive (VBA, Task Scheduler, Power Automate + Office Scripts).
• An explanation of 2026 trends that make this pattern even more useful (Notepad tables, Power Query improvements, low‑code automation).
• Access to the downloadable Folder Watch + Ledger Power Query template bundle tailored for UK finance, invoicing, payroll and inventory.

The reality in 2026: why folder‑watch matters now

Microsoft's 2024–25 updates brought tables to Notepad and improved export options across many desktop apps. By late 2025, more UK SMEs were receiving plain‑text tables and CSVs as standard exports. At the same time, Power Query in Excel has matured with better performance, AI‑assisted suggestions and deeper integration into Office Scripts and SharePoint—making folder‑based ETL (extract, transform, load) a practical, low‑cost automation for bookkeeping and inventory.

Why use a folder watch pattern? Because it separates data capture from data processing. Export scripts or devices drop files into a shared folder (local, network or OneDrive). Power Query reads every file in that folder, standardises the data, tags the source and appends to your master ledger. No manual copying, consistent structure, and a single refresh to update reports.

Quick overview: how the template bundle works

1. Set up a dedicated folder (eg C:\Data\Exports or a synced OneDrive folder) for your text/CSV exports.
2. Drop new exports there. Files can be .txt, .csv or Notepad tables saved as .txt.
3. The Excel workbook using the supplied Power Query connects to that folder, combines the files, cleans and normalises columns, and appends to a master staging table.
4. Validation steps flag duplicates, errors and mismatched types. A ledger table receives validated rows.
5. Reports (PivotTables, dashboards) update when you refresh the workbook — or automatically via scheduled automation.

Step‑by‑step: build the Folder Watch + Append Query in Power Query

Below is a practical build you can replicate in Excel for Microsoft 365 (desktop). The downloadable bundle includes a ready‑made workbook with these steps already implemented and documented.

1. Create the Folder connection

1. Data > Get Data > From File > From Folder.
2. Browse to your export folder and click OK. Power Query shows a list of files and metadata.
3. Filter file types: add a filter for extensions so you only include .csv and .txt (Text.Lower([Extension]) = ".csv" or ".txt").

2. Use the Combine function (tweak for Notepad tables)

The combine binaries UI is convenient, but Notepad tables sometimes use inconsistent delimiters. Use a custom transform function that attempts a delimiter list (comma, tab, pipe) and promotes headers.

// simplified M pseudocode used by the template
let
  Source = Folder.Files(folderPath),
  Filtered = Table.SelectRows(Source, each Text.Lower([Extension]) = ".csv" or Text.Lower([Extension]) = ".txt"),
  AddBinary = Table.AddColumn(Filtered, "Binary", each File.Contents([Folder Path] & [Name])),
  Parse = Table.AddColumn(AddBinary, "Table", each try Csv.Document([Binary], [Delimiter="," , Encoding=1252]) otherwise try Csv.Document([Binary], [Delimiter="#(tab)"]) otherwise Csv.Document([Binary], [Delimiter="|"])),
  Expanded = Table.ExpandTableColumn(Parse, "Table", List.Transform({0..20}, each "Column" & Number.ToText(_)))
in
  Expanded

Notes: the template uses a robust version of this logic. It reads each file's binary, tries common delimiters, detects or promotes headers and returns a consistent column set. It also adds a SourceFile column so you can audit where each row came from.

3. Normalise types and columns

• Use Change Type with locale‑aware settings (UK date formats: dd/MM/yyyy). In Power Query choose Using Locale when changing type to Date with English (United Kingdom).
• Trim text columns, remove stray control characters (Text.Clean), and parse currency (remove £, commas then Number.FromText).
• Apply a column mapping step so different export shapes map to the same ledger schema (eg map POS_Name, Customer to CustomerName).

4. De‑duplicate and validate

• Add a hash column (Text.Combine of key columns, hashed) to detect duplicates across files.
• Use conditional columns to flag missing mandatory fields (eg InvoiceNumber or SKU).
• Send invalid rows to a separate QA table in the workbook so someone can correct and reprocess.

5. Append to the master ledger table

Once normalized and validated, load the query to a Table in Excel (Load To > Table on a sheet named Ledger). Use the Power Query option Append Queries to union with historical data, or better: keep the ledger as the loaded result and treat each refresh as a full replacement of the ledger (incremental logic in the template can be enabled for very large datasets). See our notes on templates and modular delivery for patterns that help manage complex refresh logic.

Tip: For most small businesses, the fastest approach is full replace: Power Query reads all files in the folder, combines and produces the ledger table fresh. This avoids state‑management complexity. For >100k rows, enable the template's incremental mode.

Automation: how to refresh the ledger automatically

Refreshing manually is fine, but the delight of folder‑watch is automatic updates. In 2026 you have three practical options depending on your environment:

Option A — Simple: Workbook VBA + Windows Task Scheduler (Local)

• Create a small Workbook_Open() macro that refreshes all queries and saves the workbook.
• Use Task Scheduler to open the workbook daily/nightly under the relevant user account.

Sub AutoRefresh()
  ThisWorkbook.RefreshAll
  Application.Wait Now + TimeValue("00:00:15") 'adjust for refresh time
  ThisWorkbook.Save
End Sub

Private Sub Workbook_Open()
  AutoRefresh
End Sub

Option B — Cloud: Power Automate + Office Scripts (OneDrive/SharePoint)

• If your workbook is stored in OneDrive/SharePoint, use Power Automate to run an Office Script that refreshes queries via Excel Online connectors. This route avoids keeping a desktop machine running.
• In 2026 Power Automate supports better Excel refresh actions and error handling—use retry policies and email alerts on failures.

Option C — Enterprise: Dataflows/Power BI or Fabric

• For businesses using Power BI/Fabric, move the folder query into a Dataflow or OneLake pipeline for scheduled cloud refresh and distribution. This centralises refresh and provides role‑based access; for governance and observability patterns see our note on observability‑first lakehouses.

Common challenges and how the template solves them

Inconsistent delimiters and Notepad tables

Notepad tables can be tab delimited or use a custom pipe or space. The bundle's parsing function attempts common delimiters and uses heuristics to detect header rows.

UK date and currency formats

The template contains locale-aware type conversions. Use Using Locale (English (United Kingdom)) for dates and a currency parser that strips £ and thousands separators.

Duplicate exports and reprocessing

The template adds a SourceFile and ImportTimestamp column. A hash column detects duplicate rows across files, and a small audit sheet summarises which files contributed new rows.

Large file sets & performance

For very large histories the template supports an optional incremental load pattern: store a processed files table and only process new files each run. This is disabled by default because full replace is simpler and often fast enough for small businesses; read about modular approaches to manage scale in our templates-as-code notes.

Practical examples: bookkeeping and inventory

Example A — Retail POS daily sales

• POS exports a text table nightly (sales_YYYYMMDD.txt) into the folder. If you run market stalls or micro‑events, the same pattern works for pop‑up sales — see our maker pop‑ups playbook for inventory tips.
• Power Query imports, normalises SKUs, maps POS field names to your ledger schema and appends to SalesLedger.
• Daily pivot reports and stock usage dashboards update automatically — no manual copy/paste.

Example B — Supplier invoice exports for bookkeeping

• Suppliers send plain text invoices or your AP system exports a batch of .txt files.
• Folder Watch aggregates them, flags missing invoice numbers and standardises VAT amounts for Making Tax Digital (MTD) reports.

Security, governance and auditability

Small business data must be handled carefully. The template includes:

• An audit sheet listing processed files with timestamp and row counts.
• Validation rules that prevent loading rows with missing mandatory fields into the ledger.
• Optional encryption and device identity advice: if files contain sensitive payroll data, prefer a secure SharePoint folder and Power Automate with enterprise connectors rather than an open network share.

2026 trends that affect this workflow

• More apps output plain‑text tables: Notepad's table feature and lightweight export options mean text tables are a growing integration point for small business systems.
• Power Query continues to evolve: Improved parsing, AI suggestions and better Office Script integration make automation easier and more reliable in 2026.
• Low‑code automation proliferation: Power Automate + Office Scripts are now standard for SMB automation; your folder‑watch pattern fits neatly into this ecosystem.

How our downloadable bundle helps you deploy fast

The Folder Watch + Ledger bundle includes:

• Prebuilt Excel workbook with Power Query folder connector, robust parser, validation and ledger sheets.
• Detailed setup guide for local, OneDrive and SharePoint scenarios.
• VBA macro and Power Automate Office Script examples for scheduled refresh.
• Sample exports and a walkthrough video (10 minutes) showing common edge cases.

Use the bundle to standardise reporting across the team, remove manual steps and create a single source of truth for bookkeeping, payroll reconciliation and inventory management.

Quick checklist: getting started in under 30 minutes

1. Create your export folder and drop two example files into it.
2. Open the template workbook and set the folder path parameter (Data > Queries & Connections).
3. Refresh the queries to load sample data; inspect the Audit and QA sheets.
4. Decide your automation method (Task Scheduler or Power Automate) and enable the corresponding script in the template.
5. Test with a new export file and confirm the ledger updates and pivot reports refresh.

Real result: a UK case study

Case: A small garden centre in Kent received daily Notepad table exports from its tills. Manual pasting took 3 hours per week. After deploying the Folder Watch template, they automated imports and reduced reconciliation time to 15 minutes weekly. They found three recurring vendor coding errors via the QA sheet and corrected supplier mappings. The owner estimates a 60–80% time saving on reporting tasks.

Final thoughts — why this saves money and risk

Manual spreadsheet work is costly and error‑prone. By using Power Query to watch a folder and append exports into a refreshable ledger you: reduce repetitive admin, increase accuracy, create an auditable import trail, and free up staff for higher‑value work. In 2026 this approach is accessible—no heavy infrastructure required—and scales from sole traders to growing SMEs. For marketplace and fraud considerations around data imports and distribution, see our Marketplace Safety & Fraud Playbook.

Next steps — get the template and start automating today

Download the Folder Watch + Ledger bundle from our store at excels.uk to get the preconfigured workbook, setup guide and automation scripts. The pack is tailored for UK businesses (MTD‑aware date/currency parsing, invoice validation rules and inventory SKU mapping). If you prefer hands‑on help, we also offer a one‑hour setup call to connect your export folder and schedule automation.

Want a custom variant for payroll or a large‑scale incremental pipeline? Contact our team for a bespoke build. Automate your exports once and never copy‑paste Notepad tables again.

Call to action: Click through to download the template bundle or book a setup session. Get the folder‑watch ledger working in under an hour and stop wasting time on manual imports.

Related Reading

• Retention, Search & Secure Modules: Architecting SharePoint Extensions for 2026
• Creative Automation in 2026: Templates, Adaptive Stories, and the Economics of Scale
• Future-Proofing Publishing Workflows: Modular Delivery & Templates-as-Code (2026 Blueprint)
• Weekend Market Sellers’ Advanced Guide (2026): Inventory, Micro‑Events and Creator Commerce
• Air Quality, Robot Vacuums and Sensitive Skin: Building a Low-Irritant Home
• How to Make Shelf-Stable Herbal Cordials: Preservation, Sugar, and Alternatives
• Custom Paw Pads? A Vet’s Take on Scanned Insoles and Fancy Pet Footwear
• Pitching Your Creator Content to AI Marketplaces: A 6-Step Contract Checklist
• Build Micro Apps That Non-Engineering Hiring Managers Can Evaluate Quickly