Cleaning Your Data

Here’s the uncomfortable truth: you will spend 60% of your migration time on data cleaning.

Garbage in, garbage out. No import tool fixes bad data.

Get ERPNext Templates First

Don’t guess the format. Go to Setup → Data Import, select your DocType (e.g., “Customer”), click Download Template. Select “Excel” and check “Mandatory” columns, but also include other columns that exist in QuickBooks and need to migrate. This is essential for column mapping.

Data Hygiene

Open your QuickBooks export (source) and ERPNext template (destination) side by side.

Remove Duplicates

Excel’s Data → Remove Duplicates. Watch for near-duplicates: “John Doe” and “John Doe” (double space) are different records to a computer.

Standardize Naming

• =TRIM() removes hidden spaces
• =PROPER() fixes capitalization (“APPLE INC” becomes “Apple Inc”)

Address Fields

QuickBooks often stuffs entire addresses into one field. ERPNext wants Address Line 1, City, State, Zip separately. Use Text to Columns in Excel.

Create a Mapping Key

Make a separate spreadsheet called “Mappings.” This is your translation dictionary.

Unit of Measure

QB uses “Ea” for each; ERPNext uses “Nos” or “Unit.” Decide on one standard.

Tax Mapping

Match QB tax codes (e.g., “VAT 20%”) to ERPNext Tax Templates.

Conditional Logic

If your data needs rules like “wholesale customers get Net 30; retail gets Due on Receipt,” manual Excel work gets risky fast. This is where our Python scripts come in. Programmatic cleaning ensures 100% accuracy on conditional transformations.