Data Migration from Legacy Systems to Modern ERP | Blog

Why Data Migration Is the Hardest Part

Ask anyone who has been through an ERP implementation, and they will tell you the same thing: the software configuration was manageable, the training went reasonably well, but the data migration was painful.

The reason is simple. Your legacy data reflects years of inconsistent practices, undocumented workarounds, and accumulated mistakes. Product codes that nobody standardized. Customer records with three different spellings. Inventory quantities that stopped matching reality two years ago. Migrating this data without cleaning it first means contaminating your new system on day one.

This guide walks through a proven approach to data migration that minimizes risk and maximizes the quality of your ERP foundation.

Phase 1: Data Assessment

Inventory Your Data Sources

Before migrating anything, catalog every system and spreadsheet that holds business data:

Accounting software (general ledger, AP, AR)
Inventory management spreadsheets or standalone apps
CRM or customer databases
HR and payroll systems
Production planning tools
Any department-specific spreadsheets or Access databases

For each source, document:

Source	Data Type	Record Count	Format	Owner
QuickBooks	Financial transactions	45,000	QBW export	Finance
Excel	Product master	2,300	XLSX	Operations
Excel	Customer list	1,800	XLSX	Sales
Custom Access DB	Production records	12,000	MDB	Production

Assess Data Quality

For each data source, evaluate:

Completeness: Are required fields populated? What percentage of records have missing data?
Accuracy: Do the values reflect reality? When was the data last verified?
Consistency: Are the same entities recorded the same way across sources?
Uniqueness: How many duplicate records exist?
Currency: How recent is the data? Is it still relevant?

A data quality score helps prioritize cleaning efforts. Focus on high-impact, low-quality data first.

Phase 2: Data Mapping

Define the Target Structure

Your new ERP has a specific data structure. Foxtro ERP, for example, requires:

Customer Master:

Required: customer_code, name, tax_id (NPWP)
Address: street, city, province, postal_code
Financial: payment_terms, credit_limit, currency
Contact: primary_contact_name, email, phone
Classification: customer_group, industry, territory

Product Master:

Required: product_code, name, unit_of_measure
Inventory: valuation_method, reorder_level, warehouse
Financial: sales_account, purchase_account, cogs_account
Classification: category, brand, product_group
Pricing: standard_price, minimum_price

Create Field Mappings

For each target field, identify where the source data comes from:

Target Field	Source System	Source Field	Transformation
customer_code	Excel	Column A	Prefix with “CUST-”
name	Excel	Column B	Title case
tax_id	Excel	Column F	Remove spaces and dashes
payment_terms	QuickBooks	Terms	Map to ERP term codes
credit_limit	Manual	N/A	Set default Rp 50,000,000

Document every mapping, including fields that need transformation, default values, and fields that have no source and must be populated manually.

Phase 3: Data Cleaning

Master Data Cleanup

Master data (customers, vendors, products, employees) forms the foundation. Clean it thoroughly:

Deduplication: Identify and merge duplicate records. Common duplicate indicators:

Same tax ID (NPWP) with different company names
Same phone number across multiple records
Slight name variations (“PT Maju Jaya” vs “PT. Maju Jaya” vs “Maju Jaya, PT”)

Standardization: Apply consistent formats:

Phone numbers: +62-XXX-XXXX-XXXX
Addresses: standardized province and city names
Product codes: uniform length and structure
Units of measure: kg (not KG, Kg, kilogram, kilo)

Validation: Cross-check against external sources:

Verify NPWPs against DJP records
Confirm active customers versus those with no transactions in 24 months
Validate inventory quantities against recent physical counts

Transaction Data Decisions

You rarely need to migrate every historical transaction. Consider:

Opening balances only: Migrate summary balances as of cutover date. Simplest approach.
Current year transactions: Migrate the current fiscal year for comparison reporting.
Full history: Migrate multiple years for trend analysis. Most complex and highest risk.

For most Indonesian SMEs, migrating opening balances plus the current fiscal year provides the best balance between effort and value.

Phase 4: Migration Execution

Build the Migration Pipeline

Create a repeatable, automated migration process:

Step 1: Extract from source systems
  → Export to CSV with consistent encoding (UTF-8)

Step 2: Transform data
  → Apply field mappings
  → Execute cleaning rules
  → Validate against business rules

Step 3: Load into staging environment
  → Import to ERP test instance
  → Run validation reports

Step 4: Verify
  → Compare record counts
  → Verify totals and balances
  → Spot-check individual records

Trial Migrations

Never run a migration directly into production. Execute at least three trial migrations:

Trial 1: Structure validation

Does the data import without errors?
Are all fields mapped correctly?
Do lookups and references resolve?

Trial 2: Business validation

Do financial totals match source systems?
Can users find their customers and products?
Do inventory quantities reconcile?

Trial 3: Full dress rehearsal

Execute on a weekend with the actual cutover timeline
Measure how long each step takes
Identify bottlenecks and plan mitigations

Cutover Checklist

On migration day:

Freeze transactions in the legacy system (no new entries after cutover time)
Run final data export from all source systems
Execute the migration pipeline
Verify record counts and financial totals
Run key reports and compare to legacy
Get sign-off from department heads
Open the new ERP for transactions

Phase 5: Validation

Reconciliation Reports

After migration, run these validation checks:

Financial reconciliation:

Trial balance in ERP matches trial balance from legacy system
Sub-ledger totals (AR, AP, inventory) match control accounts
Bank balances match actual bank statements

Master data verification:

Total active customers in ERP matches expected count
Total active products matches expected count
Employee records are complete with all required fields

Inventory reconciliation:

ERP stock quantities match most recent physical count
Inventory valuation matches financial records
Warehouse locations are correctly assigned

User Acceptance

Have key users from each department verify:

Can they find their regular customers and vendors?
Do product descriptions and pricing look correct?
Are opening balances reasonable?
Can they run their standard reports?

Common Migration Pitfalls

Character Encoding Issues

Indonesian business names often include characters that cause encoding problems. Always use UTF-8 encoding for export and import. Test with company names that include “PT.”, ampersands, and special characters.

Date Format Mismatches

DD/MM/YYYY versus MM/DD/YYYY is a classic migration error. A date like 03/04/2026 could be March 4 or April 3 depending on the format. Standardize on ISO 8601 (YYYY-MM-DD) for all migration files.

Missing Reference Data

Master records must exist before transaction records that reference them. Migrate in this order:

Chart of accounts
Customer and vendor masters
Product master
Opening balances
Open transactions (outstanding invoices, purchase orders)

Underestimating Effort

Data migration typically consumes 30% to 40% of total ERP implementation effort. If your project plan allocates less, you are likely underestimating.

After the Migration

Keep your legacy systems accessible in read-only mode for at least 12 months after cutover. Historical reference needs will arise that your migrated data does not cover. Having the old system available prevents panic and supports the transition.

Document every migration decision, mapping rule, and transformation applied. This documentation is invaluable during audits and when questions arise about how historical data appears in the new system.