From Spreadsheets and Paper to ERPNext: The Migration Checklist I Used for a Municipality
Frappe — — Ali Raza
Move from paper and spreadsheets to ERPNext without losing data: the eleven-step migration checklist I used for a Guatemalan municipality.
The Municipalidad de Fraijanes in Guatemala ran permits, business licences, citizen requests, revenue collection and public works on paper files and manual registers when I started. A wholesale footwear client ran its entire inventory on a shared workbook. They had nothing in common except the migration problem, and this checklist is the one I refined across both.
Most ERPNext failures I am asked to rescue were not caused by the software. They came from moving messy data and undocumented habits into a clean system and expecting the system to fix them. Migration is where a project is won or lost, and it starts long before anyone opens the Data Import tool.
Step one is a process inventory, not a data inventory. For Fraijanes I sat with each department and wrote down every process as a sentence: who starts it, what document exists at each stage, who signs it, and what the citizen or the treasury gets at the end. For the footwear company it exposed that "receiving stock" meant different things depending on who was in the warehouse.
Each sentence becomes a candidate DocType or a standard ERPNext transaction. A permit application maps to a custom DocType with a Workflow; a footwear purchase maps to a standard Purchase Receipt. I do not design anything in Frappe until this list is agreed and signed, because it is also the scope of the fixed quote.
Step two is deciding what not to migrate, and this is where I save clients the most money. Closed permits from years ago do not need to become records in a live DocType; they need to stay findable. We scanned what was legally required, attached the files to a lightweight archive DocType with a few searchable fields, and left the rest in the physical archive.
The footwear workbook had tabs nobody opened, formulas referencing suppliers who no longer existed, and price columns that contradicted each other. None of that crossed over. My rule: migrate open transactions, current masters and opening balances. History stays in a read-only reference or the old system, and the quote gets smaller every time a client agrees to that.
Step three is master data cleanup, the longest line in every project plan I write. The masters that matter are Customer, Supplier, Item, Employee and the Chart of Accounts, plus the lists behind them: Item Group, Warehouse, Department, Territory, Payment Terms. I export the Data Import template for each DocType first so the client cleans data in the exact columns Frappe expects, not the spreadsheet's original shape.
The cleanup itself is unglamorous:
- Deduplicate on a real key: tax ID for customers and suppliers, national ID or employee number for employees, a proper item code for items. - Standardise names and casing so Link fields resolve on import and reports group correctly. - Fix units of measure before anything else; an item recorded in pairs on one sheet and boxes of twelve on another will break every stock report. - Decide naming series and item code conventions now, because renaming with the Rename Tool is possible but painful once transactions reference the records.
For footwear the biggest design decision was Item Variants. Every shoe model became an Item Template with Size and Colour attributes, and the variants were generated from that rather than imported as unrelated items. That one choice cut the item list to something a purchaser could browse.
For the Chart of Accounts I use the Chart of Accounts Importer while the company still has no transactions, working from a spreadsheet that mirrors the client's own budget classifications. Once a single Journal Entry is posted that door closes, so this step is scheduled before any transactional import.
Step four is opening balances, kept strictly separate from master data. Stock opens through a Stock Reconciliation with purpose Opening Stock, posted against the Temporary Opening account, after a physical count on an agreed date. Receivables and payables open through the Opening Invoice Creation Tool so each outstanding invoice exists as its own document and can be settled individually. Remaining general ledger balances go in as a Journal Entry flagged as an opening entry.
For the municipality the receivables were outstanding licence fees and permit charges, treated the same way: one opening invoice per obligation, so the revenue department could collect and reconcile in the system from day one instead of a ledger book.
Step five is the Data Import tool and validation runs, plural. I never import into production first. I restore a copy of the site, run every import there using Insert New Records, and read the Import Log line by line. Frappe tells you which row failed and why, and the reasons repeat: a Link value that does not exist yet, a blank mandatory field, a date in the wrong format.
The order matters because of those Link fields: groups and warehouses before items, territories and customer groups before customers, departments and designations before employees. After each round I validate with reports, not by eyeballing lists. Stock Balance against the physical count sheet, Trial Balance against the old ledger, Accounts Receivable against the outstanding list the client signed. When the numbers tie, I wipe staging and run the sequence again to prove it is repeatable; that is what makes cutover day boring.
Step six is mapping approvals to Frappe Workflows. Every signature on the paper form becomes a Workflow State, every "hands it to the next office" becomes a Transition with an allowed Role. Then I cut. Paper processes accumulate signatures for historical reasons, and a workflow with more than four or five states gets bypassed. For Fraijanes we collapsed review steps into fewer states with a clear owner each, and used Workflow Actions so approvers get an email with an approve link.
Where a rule is a check rather than a signature, it becomes a Server Script: block a permit from moving to Approved if the applicant has unpaid fees, or block a Delivery Note if a customer has exceeded the credit limit finance set. Server Scripts run on DocType events like Before Save and Before Submit, are editable without a deployment, and need to be enabled in site config on a self-hosted bench.
Step seven is role and permission design in the Role Permission Manager, done before any user is created. I define one role per real job rather than per person, and grant read, write, create, submit, cancel and amend per DocType. User Permissions then restrict each user to their own department or cost centre so a public works clerk cannot browse licensing files. I test every role with a throwaway account before training starts, because a permission gap discovered during training destroys trust.
Step eight is a pilot department. I never switch an entire organisation over in one weekend. I pick the department whose process has the clearest beginning and end, where a request comes in, gets approved and gets paid, and run only that department live for a few weeks while the rest stay on the old method. In a municipality that is usually permits or licensing. The pilot surfaces the states nobody understood and the fields nobody fills, and fixing those before the wider rollout is cheap.
Step nine is the cutover plan, a dated checklist agreed with the client: a data freeze after which no edits go into the old spreadsheets, a final extract and import following the sequence proven in staging, opening balances posted as of the cutover date, a go or no-go meeting with the report reconciliations attached, and a named person with authority to say no. For the footwear client we ran the workbook and ERPNext in parallel briefly, comparing stock counts, then locked the workbook.
Step ten is training by role, not by module. Clerks learn the three or four forms they will touch and nothing else. Approvers learn the workflow email, the Approve button and list view filters. Finance learns Payment Entry, reconciliation and the reports that replaced their ledgers. Administrators learn the Role Permission Manager, the Data Import tool and how to read the Error Log. Training every user on every module is the fastest way to convince people the system is complicated.
Step eleven is hypercare. For the first few weeks after go-live I hold a short daily call with department heads, review the Error Log and Import Logs, and fix small things the same day: a missing Item Group, a Transition that should have allowed a second role, a print format missing a field the treasury needs. Most hypercare work is configuration, not code, and it is where a monthly retainer usually starts: the fixes are continuous and someone has to be accountable for them.
On cost, the drivers are visible from this checklist. Dirty master data and the number of custom DocTypes and Workflows move the quote more than anything else; hosting is a smaller line, and for most clients Frappe Cloud's published plans settle it quickly. I price the work as a fixed quote with milestones matching these steps, so the client pays for a clean master data import, a working pilot and a completed cutover, not for hours.
If you are running a department or a business on spreadsheets and paper and wondering how much of it would survive a move to ERPNext, book a free 45-minute audit call with me. We walk through your process inventory together, and if it makes sense I follow up with a written Blueprint and a fixed quote for the migration.