How to Clean and Prepare CMMS Exports for Analysis (SAP, Maximo, MC & More)

If you want accurate maintenance insights, you must first clean and prepare CMMS exports effectively. Whether you use SAP PM, IBM Maximo, Maintenance Connection, Fiix, UpKeep, or MEX, the exported CSV is only as good as the structure you impose on it. Raw CMMS data is often messy: duplicated assets, inconsistent naming, empty fields, date format issues, and "free text chaos" that hides real trends.

This guide offers a battle-tested, field-proven process used by planners, reliability engineers, and CI/lean teams to transform raw CMMS data into clean, analysis-ready datasets — without spending hours sanitising spreadsheets.

---

Why CMMS Data Requires Cleaning

Most maintenance systems were not designed for analytics. Operators type free-text comments. Planners update fields inconsistently. Vendors configure systems differently. Over time, exports become littered with:

• Missing dates and inconsistent formats
• Assets with multiple names ("Pump 4", "PMP-004", "P4")
• Free-text failure descriptions that make categorisation impossible
• Old status codes that conflict with updated workflows
• Duplicate work orders from synchronisation errors
• Typos, truncation, and inconsistent capitalisation

Without cleaning, your downtime analysis, backlog analysis, PM compliance reports, and cost breakdowns are unreliable. Worse, they lead to bad decisions about labour, spares, and corrective strategy.

---

The 5-Step Process to Clean and Prepare CMMS Exports

This is the exact method used by high-performing maintenance and reliability teams.

Step 1 — Standardise Your Columns

Every CMMS platform exports differently:

• SAP PM: long field names, multiple technical objects
• Maximo: complex MBO table joins
• Maintenance Connection: clean fields but many "optional" blanks
• Fiix / UpKeep: simplified exports with inconsistent date stamps
• MEX: very free-text heavy, especially for failure codes

Start by aligning everything to a universal schema.

Recommended standard fields (minimum viable analysis set):

Remove duplicates. Rename inconsistent headings. Fill missing but required headings with blank columns.

---

Step 2 — Fix Dates, Durations and Time Fields

This is the number one issue across all CMMS platforms.

Common problems:

• SAP exports dates as 20250118, Maximo as 18/01/25, Fiix as 2025-01-18T14:33Z.
• Missing time stamps lead to negative durations when teams calculate MTTR.
• MEX sometimes exports local time without offset.

Best practice:

• Convert everything to ISO format: YYYY-MM-DD.
• Recalculate downtime and labour hours if durations appear inconsistent.
• Drop any rows with impossible durations (negative or > 10,000 minutes).

---

Step 3 — Clean and Normalise Asset Names

Asset naming chaos is one of the biggest blockers to accurate Pareto analysis.

Common inconsistencies:

• "Pump 4", "Pump-04", "PMP-004", "Pump4"
• "Packing Line 1", "Line 1 Pack", "P/L 1"

To clean:

1. Convert to upper case.
2. Trim whitespace and special characters.
3. Standardise hyphens and numbering formats.
4. Create mapping rules (e.g., PUMP, PMP, PMP- → PUMP).
5. Flag assets that appear fewer than 3 times for manual review.

Result: Your top 10 downtime assets become actual insights, not noise.

---

Step 4 — Fix Work Order Status and Priority Codes

Every CMMS has legacy status codes:

• "COMP", "COMPLETE", "Closed", "C"
• "Open", "OPN", "O"
• Priority 1 as "1", "01", "HP", "URGENT"

Create a normalisation table:

Repeat the same process for priorities and work types.

This ensures apples-to-apples comparison across departments and sites.

---

Step 5 — Clean Failure Codes and Free-Text Fields

Most CMMS datasets contain:

• Typos ("BEARING FAIILURE")
• Mixed categories ("Gearbox noisy sound vibration")
• Missing cause codes
• Free text used instead of standard fields

You have three options:

Option A — Standardise manually Slow but accurate. Best for small datasets.

Option B — Use controlled dictionaries Map common terms ("bearing", "seal", "sensor", "belt") to standard categories.

Option C — Use AI-assisted classification (recommended) Modern AI and machine learning models can classify thousands of rows rapidly and accurately. LeanReport uses this method.

---

Data Quality Checks (Your Pre-Analysis Checklist)

Before you begin any Pareto analysis, backlog analysis, or cost modelling, confirm:

Required Checks

• All dates follow YYYY-MM-DD.
• Asset names have been standardised.
• Status codes are uniform.
• No duplicate work orders.
• Downtime minutes show no negative values.
• Free-text fields scanned for junk or placeholders ("???", "test", "N/A").
• Zero-cost work orders for corrective tasks flagged.

Optional (Advanced) Checks

• Validate department → asset alignment.
• Cross-check work types against status codes.
• Spot-check extreme durations for data-entry errors (e.g., a 12-day PM).

---

Common CMMS-Specific Issues (And How to Fix Them)

SAP PM

• Problem: Long field names and cryptic tables (e.g., AUFK, AFVC).
• Fix: Use export-friendly views or pre-built variants with aligned headings.

IBM Maximo

• Problem: Excessive joins lead to duplicates.
• Fix: Ensure export is from a single MBO or deduplicate using WO number.

Maintenance Connection

• Problem: Optional fields often empty.
• Fix: Treat missing fields consistently across exports (fill blanks with NULL).

Fiix / UpKeep

• Problem: Operators use free text heavily.
• Fix: Normalise using controlled dictionaries or AI classification.

MEX

• Problem: Very inconsistent failure codes and naming.
• Fix: Heavy use of mapping rules and asset normalisation required.

---

Example: Fixing a Dirty Export (A Mini Case Study)

A tissue manufacturing plant using SAP PM exported 18 months of breakdown work orders. Initial analysis showed:

• 450+ unique asset names for 110 actual assets
• Multiple date formats
• 14 status codes (only 5 were still used)
• Downtime recorded in comments instead of proper fields

After cleaning:

• Asset list reduced to 112 clean tags
• Status codes reduced to 5 normalised categories
• Downtime calculated for 92% of work orders
• Pareto chart revealed 3 assets responsible for 54% of unplanned downtime

This is the power of proper cleaning.

---

Cleaning Techniques You Can Automate

You can automate:

• Asset name normalisation
• Deduplication
• Date format correction
• Failure classification
• Status/priority standardisation
• Outlier detection

LeanReport already automates all of this across major CMMS platforms.

---

Quick Reference: Cleaning Rules Library (Copy/Paste)

Naming Rules

• Replace underscores with spaces
• Convert all text to upper case
• Standardise hyphens: PUMP 04 → PUMP-04
• Collapse double spaces

Date Rules

• Detect DD/MM vs MM/DD using rule-based inference
• Convert epoch timestamps to ISO date
• Convert empty dates to NULL

Status Rules

Map everything to:

• Open
• In Progress
• Completed
• Cancelled
• Waiting

Priority Rules

Map everything to:

• Emergency
• High
• Medium
• Low

---

What a "Clean" CMMS Dataset Looks Like

A clean file has:

• Consistent headings
• Standard date formats
• Uniform asset categories
• Accurate downtime
• Clear status progression
• Meaningful priority labels
• Zero duplicates
• Failure modes classified cleanly

Once the dataset meets these criteria, CMMS data analysis becomes:

• Faster
• More accurate
• More actionable
• More trustworthy

This is the foundation for reliability engineering, lean programmes, and CapEx justification.

---

Conclusion

Cleaning and preparing CMMS exports is the highest-leverage activity a maintenance planner can undertake. It transforms messy CMMS data into reliable insights that drive decisions on labour, spares, PM reviews, failure modes and capital planning.

The process may seem tedious, but once standardised — or automated — it unlocks the real value trapped in your maintenance history.

---

How LeanReport Can Help

LeanReport automatically cleans and prepares CMMS exports from SAP PM, Maximo, MEX, Maintenance Connection, Fiix, UpKeep and more. It standardises fields, fixes dates, normalises asset names, deduplicates work orders, and classifies free-text failures — all before analysis begins.

Upload your file, choose the dataset type, and LeanReport does the rest.

Start your first report at: https://www.leanreport.io