From Notepad to Dashboard: Automating Legacy Exports into Real-Time Sales Reports

Stop wrestling with Notepad exports: build a refreshable, real-time sales dashboard

Pain point: your legacy system still spits out plain text tables, sitting in Notepad or as .txt files, and someone on the team spends hours copy-pasting them into Excel every day. You need timely sales insight, not a manual ETL job. In 2026 the good news is this is solvable, reliably and affordably, using Power Query, Office Scripts and scheduled automation.

Why this matters now

The landscape changed in late 2024 and through 2025 as Microsoft continued to invest heavily in the Power Platform and Excel automation. Two trends are important for small and medium UK businesses:

• Low-code automation is mainstream. Power Query plus Power Automate and Office Scripts now let you move data from legacy text exports into a governed, refreshable reporting layer without custom development.
• Hybrid data workflows are the norm. Teams combine on-prem legacy systems with cloud storage, making scheduled refreshes and a lightweight gateway strategy essential.

That means the problem of periodic Notepad-style exports is no longer an excuse for delayed reports. You can create a near real-time sales dashboard that refreshes on a schedule, or on demand, and produces consistent KPIs across the business.

How we approach Notepad-style legacy exports

At Excels.uk we follow a simple, repeatable process. This becomes your automation playbook.

1. Ingest the raw text files into a controlled location
2. Transform the semi-structured text into a clean table using Power Query
3. Model the data inside Excel using the Data Model or linked tables
4. Visualise with PivotTables, charts and a dashboard layout
5. Automate scheduled refresh with Power Automate and Office Scripts
6. Govern and monitor for failures with logging and alerts

Step 1: Ingest the exports reliably

The first decision is how the legacy system will deliver its text export. Common patterns we see:

• Drop files to a network share every night
• Email plain text exports to an inbox
• Save files to an SFTP or local folder
• Generate exports on demand via a button in the legacy app

Best practice is to build a landing zone where the raw files are written consistently. For UK businesses using Microsoft 365, the typical landing zone is OneDrive for Business or a SharePoint document library. Benefits:

• Power Query folder connector reads files automatically
• Power Automate can move files into the folder from email, SFTP or network shares
• Versioning and access controls live in SharePoint

Step 2: Clean the mess with Power Query

Power Query is the workhorse. It can parse:

• Delimited text, such as comma, pipe, semicolon
• Fixed-width reports exported from old systems
• Mixed records where some rows include notes or headers

Core Power Query techniques you will use:

• From Folder connector to combine multiple exports into a single table
• From Text/CSV with custom delimiter and file origin settings to handle encodings
• Fixed width parsing where columns align by position
• Column splitting by delimiter or by position, then trimming and type detection
• Promote headers, remove footers and filter out summary lines
• Unpivot and pivot as needed for tidy, analytic-ready tables

Example workflow, no-code steps:

1. Get Data -> From File -> From Folder, point to the SharePoint/OneDrive folder
2. Combine and Transform: Power Query opens a sample file and launches the editor
3. In the editor select the sample and choose Delimited or Fixed Width parser
4. Use Trim, Clean, Change Type and Remove Rows to remove header/footer noise
5. Rename columns to standard, business-friendly names like Date, Store, SKU, Qty, Value
6. Close and Load to Data Model or Table

Tip: keep a reference table for store and SKU mappings to normalise naming inconsistencies. Power Query merges make this trivial.

Step 3: Build the refreshable data model

Load your clean query to the Excel Data Model if you plan to use PivotTables or Power Pivot measures. Benefits:

• Smaller workbook footprint for large datasets
• Ability to create DAX measures for complex KPIs like YTD sales, moving averages and conversion rates
• Faster refresh and aggregation when combined with PivotTables

Organise your workbook with a clear sheet structure. We recommend:

• Data: queries loaded to model only, not visible tables
• Lookup: mapping and calendar tables
• Reports: PivotTables and charts connected to the model
• Dashboard: a single sheet with KPIs and interactivity

Step 4: Create the dashboard

Design for clarity. Your sales dashboard should show the most important, actionable KPIs first.

• Top-line metrics: Total sales, Orders, Average Order Value, Gross Margin
• Trends: Daily and weekly sales sparkline or chart
• Breakdowns: Sales by store, region, product category
• Alerts: indicators for anomalies such as negative sales or missing data

Use slicers for interactive filtering and configure PivotTables to use the Data Model measures. Keep the visuals straightforward so business users can make decisions quickly.

Step 5: Automate scheduled refresh

This is where modern Office automation earns its keep. You want the Excel workbook to update on a schedule without a person opening it. There are three common approaches, depending on environment and licensing:

Option A: Excel online automation with Power Automate and Office Scripts

Best for cloud-first organisations where the workbook is stored on OneDrive or SharePoint.

• Create a simple Office Script that calls workbook.refreshAll
• Create a Power Automate scheduled flow that runs every X minutes/hours/days
• Flow action: Run script on the target workbook; after the script completes save the workbook
• Optional: Use additional steps to copy the workbook, export PDF snapshots, or send failure alerts

Sample Office Script function:

function main(workbook: ExcelScript.Workbook) {
  workbook.refreshAll();
}

Why this works in 2026: the Office Scripts and Power Automate integration matured through 2024-2025 and is now a reliable pattern for scheduled refresh and light automation tasks. If your organisation needs search and discovery across live content, consider how edge signals and real-time discovery inform refresh cadence.

Option B: Power BI for enterprise refresh

When you need sub-minute latency, or enterprise-level monitoring and row-level security, consider pushing your cleaned query to Power BI. Power BI supports scheduled refreshes, incremental refresh and direct queries with gateways. You can keep core data transformations in Power Query using Power BI dataflows and then either pin visuals back to a live Excel workbook or use Power BI reports for distribution.

Option C: On-prem gateway for local files

If your legacy system writes to a local network share and you cannot move the file to the cloud, use a gateway. Power Automate Desktop or an on-premises data gateway can move files to SharePoint or run local refresh then upload the finished workbook. This preserves security while enabling automation.

Step 6: Monitor, log and recover

Automations fail. Plan for it.

• Build logging into your Power Automate flows: capture file names processed, row counts and duration
• Send alerts to a Teams channel or to an ops email when refresh fails or when data quality checks fail
• Keep the last good snapshot of the workbook for audit and rollback

Three industry use cases and short case studies

Retail: daily POS exports to a live sales board

Scenario: A chain of 25 stores uses an old POS that exports nightly pipe-delimited files. Head office used to wait until morning to aggregate totals.

Solution: Files are dropped to a SharePoint folder. Power Query From Folder combines them, parses the delimiter, maps store codes and loads to the Data Model. An Office Script refresh runs every 30 minutes via Power Automate. Dashboard on SharePoint shows near real-time sales per store and triggers a Teams alert when any store drops below a threshold. For hardware and stall tech considerations that complement POS pipelines, see our vendor review of portable POS and field kits here.

Result: The head office reduced manual processing time by 80 percent and detected 3 supply issues earlier, saving an estimated 12k per quarter in stockouts.

Services: consultancy time-sheets converted to utilisation metrics

Scenario: Consultants submit time entries via a legacy system that exports a fixed-width text report. Managers needed utilisation rates and billable hours by project.

Solution: Power Query fixed-width parser split the file into date, consultant, project, hours and notes. A lookup table normalised project codes. Data loaded to the model and a dashboard calculates billable utilisation per consultant. Power Automate moves the latest export from email to OneDrive and triggers a refresh on upload.

Result: Managers now see weekly utilisation dashboards and reduced billing disputes by 60 percent because of consistent source data.

Finance: end-of-day trading positions from mainframe extracts

Scenario: A small financial services firm receives end-of-day reports from an older mainframe in text format. Traders needed same-day PnL and exposure summaries.

Solution: A scheduled flow retrieves the nightly extract via SFTP and places files in a secure SharePoint library. Power Query transformations normalise currencies and instruments. The Data Model contains base currency conversion tables and time intelligence measures for PnL. Power Automate runs a validate-step that checks row counts and notifies the trading floor on success.

Result: Same-day PnL availability improved from T+1 to same-day, enabling faster hedging decisions and reducing overnight risk. The automation reduced manual reconciliation time by over 70 percent.

Advanced tips and 2026 considerations

• Incremental refresh: For large exports, use Power Query parameters to process only new files since last refresh. This keeps refresh time predictable.
• Data provenance: Add source file name and timestamp columns when combining files. This helps with audits and troubleshooting.
• Encoding and culture: Legacy systems sometimes export with odd encodings or date formats. Explicitly set the file origin and locale in the Power Query source step to avoid silent errors.
• Security: Use SharePoint permissions to limit who can drop files into the ingestion folder, and store credentials in secure connectors rather than in the workbook. For enterprise connector and gateway security guidance see best practices.
• Version control: Keep a release sheet or a change log inside the workbook so stakeholders know when transformations change.
• Fallbacks: Keep a light-weight, human-readable log emailed to data owners if automation fails, with a direct link to the raw file for quick manual remediation.

Common pitfalls and how to avoid them

• Assuming consistent file layout. Fix by adding robust filters for header/footer noise and by including file schema validation steps.
• Using volatile transforms. Avoid Excel formulas that recalc extensively during refresh; prefer calculations in the Data Model using DAX.
• Overcomplicating the dashboard. Start with three core KPIs. Expand only when users rely on them.
• Ignoring licensing. Office Scripts and certain Power Automate connectors can require paid plans for unattended flows. Confirm licensing before deploying at scale.

Checklist to go from Notepad to dashboard

1. Establish a consistent landing zone for exports in SharePoint/OneDrive
2. Build a Power Query pipeline that handles both delimited and fixed-width formats
3. Standardise column names and maintain lookup tables for mappings
4. Load clean data to the Data Model and create DAX measures for KPIs
5. Design a succinct dashboard and configure slicers for common filters
6. Automate refresh with Power Automate and Office Scripts, or use Power BI for advanced scenarios
7. Implement logging, alerts and a rollback snapshot

Final thoughts: why this is a strategic win in 2026

Turning legacy text exports into a refreshable Excel sales dashboard is more than a technical task. It standardises reporting, reduces operational risk and frees your team to act on insights instead of compiling them. In 2026 the tools are mature, the connectors are reliable, and low-code automation is an accessible strategic advantage for UK businesses of all sizes.

At Excels.uk we help teams move from ad-hoc Notepad exports to governed, refreshable dashboards that save time and improve decision-making. The first hour of automation often pays for itself in the first week.

Actionable next steps

Ready to automate your exports? Do this now:

1. Create a SharePoint folder and ask your legacy app owner to write nightly exports there for 7 days
2. Build a Power Query From Folder query in a new Excel workbook and get the sample parsing right
3. Design three KPIs for your dashboard and create simple PivotTables off the Data Model
4. Create an Office Script that runs workbook.refreshAll and test it manually
5. Make a Power Automate scheduled flow to run the script, add logging and a Teams notification

Call to action

If you want a ready-to-use starter pack, weve built a template that includes the Power Query pattern, a dashboard layout and a Power Automate flow example tailored for UK retail, services and finance. Download the template, or book a 30-minute clinic with one of our automation specialists and we will review your legacy export and outline a roadmap to a refreshable sales dashboard.