How to Migrate from Excel or Legacy Software to ERP

Most ERP projects don't fail because the software is wrong. They fail because the data going into it is wrong. A new system loaded with duplicate customers, stale item codes and opening balances nobody can explain will lose the trust of its users within weeks, and people drift back to their spreadsheets. If you are planning to migrate from Excel or legacy software to ERP, treat the data migration as a project in its own right, with an owner, a plan and sign-offs.

This guide covers what to take across, how to clean and map it, how to move data out of Tally and older databases, how to test and reconcile it, and which cutover approach suits which kind of business. It ends with a checklist you can adapt for your own project.

Start with a data inventory

Before anyone writes an import script, list every place business data lives today. In a typical Indian SME this is messier than people expect. For example, a hypothetical distributor with three warehouses might have:

• Accounts in Tally, one company file per financial year, plus a separate company for a sister concern
• Stock registers in Excel, one workbook per warehouse, updated by different people
• A customer price list in a shared Google Sheet that sales edits directly
• A decade-old in-house billing tool on SQL Server that nobody wants to touch
• Pending orders tracked on WhatsApp and in the sales manager's notebook

For each source, record who owns it, how current it is, how many records it holds, which fields it contains and whether it is the master copy or a copy of something else. That last point matters. When two sources disagree about a customer's GSTIN or credit limit, you need to know which one wins before migration starts, not during it.

The inventory also shows how much work is ahead: ten thousand clean records in one table is a small job; four thousand items across five inconsistent workbooks is a large one.

Master data, transactional data and opening balances

Not all data is migrated the same way. Separating it into three categories makes the plan much clearer.

Master data

Master data describes the entities your business deals with: customers, suppliers, items, units of measure, chart of accounts, warehouses, employees, price lists, tax codes, bills of materials and routings. It changes slowly and everything else refers to it, so it has to be right first. Most of your cleansing effort goes here.

Transactional data

Transactional data records events: sales orders, invoices, purchase orders, goods receipts, stock transfers, payments and journal entries. The key decision is how much history to bring across. Usually the answer is only open transactions (unfulfilled orders, unpaid invoices, pending receipts) plus summarised history where it is genuinely needed.

Opening balances

Opening balances are the snapshot on your cutover date: ledger balances, customer and supplier outstandings bill by bill, stock quantity and value by item and location, fixed asset values, and advances. These must tie back to your audited or closing figures to the rupee. A clean opening trial balance is the single most important output of the migration.

Cleansing and de-duplication

Data cleansing is unglamorous, slow and essential. Budget more time for it than you think you need, and make business users do it with support from the implementation team, because only they know which "Sharma Traders" is the real one.

Common problems in Indian SME data include:

• Duplicate parties. The same customer entered as "ABC Pvt Ltd", "A.B.C. Private Limited" and "ABC (Delhi)". Match on GSTIN and PAN first, then on phone number and address.
• Inconsistent item names. "MS Pipe 25mm", "M.S. PIPE 1 inch" and "Pipe MS 25" might be one item or three. Decide on a naming convention and an item code structure before cleaning.
• Missing or wrong statutory data. Blank GSTINs for registered customers, invalid state codes, HSN codes at the wrong level of detail. Validate GSTIN format and check that the state code prefix matches the billing state.
• Mixed units. Stock kept in boxes in one workbook and pieces in another. Define base units and conversion factors explicitly.
• Dead records. Customers not billed in five years, items discontinued long ago. Mark these inactive or leave them behind.

Mapping old fields to the new structure

Mapping is where you decide how each field in the source becomes a field in the ERP. It is also where hidden business rules surface. Build a mapping document for every entity, reviewed by the person who owns that data. A simplified extract for customer masters might look like this:

Keep the mapping document under version control and treat it as the specification for your import scripts. When a question comes up three weeks later ("why is this customer in the wrong region?"), the answer should be in the mapping, not in someone's memory.

If the new ERP is being extended with custom fields to capture something the old system tracked informally, decide that during mapping. Our guide on custom ERP vs standard ERP covers when such extensions make sense and when they are better avoided.

Migrating from Tally

Tally is the most common source system for Indian businesses moving to ERP, and it has its own quirks. If you are still deciding whether to move, Tally vs ERP walks through the signs that a business has outgrown accounting software.

Practical points for Tally migrations:

• Export method. Masters and vouchers can be exported to Excel or XML from within Tally, and Tally also exposes data over its XML/ODBC interface, which is useful for scripted extraction of large volumes. Test on a copy of the company data, not the live file.
• Ledger groups. Tally's ledger group hierarchy rarely maps one-to-one to a proper chart of accounts. Use migration as a chance to redesign it, then map old ledgers to new accounts.
• Bill-wise details. Customer and supplier outstandings must come across bill by bill, with original invoice numbers and dates, so that ageing and collection follow-up work from day one. Only possible if bill-wise tracking was maintained in Tally; if not, plan time to rebuild it.
• Stock valuation. Confirm the valuation method in Tally matches what the ERP will use, and reconcile item-wise closing stock value, not just the total.
• Multiple companies and years. Decide whether split company files for different years or entities become one company with multiple branches, separate legal entities, or a mix.

Some businesses keep Tally running for statutory books for a while and integrate it with the ERP rather than replacing it outright. That is a legitimate transition path, covered on our ERP integration services page.

Migrating from legacy databases

Older in-house systems, often built on SQL Server, MS Access or FoxPro, bring different challenges. The data is structured, which helps, but the business logic is often buried in code and stored procedures that nobody documented.

• Get the schema and a data dictionary. If there is none, reverse-engineer it by profiling tables: row counts, null rates, distinct values, and foreign key relationships.
• Find the hidden rules. A status code of 7 might mean "dispatched but not invoiced". Interview the people who use the system daily, and check reports for the logic they use.
• Extract with scripts, not screenshots. Use SQL queries or an ETL tool to pull data into staging tables. This makes each trial run repeatable.
• Use a staging database. Load raw extracts into staging, transform them there, validate, and only then push into the ERP through its import tools or APIs. Never write directly into the ERP's production tables.

Run trial migrations, then run them again

A single migration run, done once on the go-live weekend, is a gamble. Plan at least two or three full trial migrations into a test environment before the real one.

1. Trial 1: Load masters only. Expect many errors; the goal is to find mapping gaps and bad data.
2. Trial 2: Load masters, open transactions and opening balances. Users test real scenarios such as raising an invoice for a migrated customer or receiving against a migrated PO.
3. Trial 3 (dress rehearsal): Run the full process end to end, timed, with the same scripts and people planned for cutover. This tells you how long the real cutover window needs to be.

Record every error, fix it at the source or in the transformation rules, and rerun. By the final trial, the load should be boring.

Reconciliation: prove the numbers match

Reconciliation is how you prove the migration worked. It must be done by finance and operations, not just the technical team, and every check should have a documented sign-off.

• Record counts: number of customers, suppliers, items and open documents in source versus ERP, with explanations for every difference (such as deliberately excluded inactive records).
• Trial balance: every ledger balance in the ERP's opening trial balance matches the closing figures of the old system.
• Sub-ledgers: total of bill-wise customer outstandings equals the debtors control balance; the same for creditors.
• Stock: item-wise and location-wise quantity and value match the closing stock statement, and the total ties to the stock ledger in accounts.
• Spot checks: pick a sample of customers, items and open orders and compare them field by field.
• Statutory fields: GSTINs, state codes, HSN/SAC codes and tax rates on masters are complete and plausible.

Cutover strategies: big bang, phased or parallel run

Cutover is the moment you stop working in the old system and start working in the ERP. There are three common approaches.

In practice, many SMEs choose a big bang for a single entity at a financial period boundary, usually the start of a month or quarter, because it gives clean opening balances. Multi-branch businesses often go phased by location, starting with the head office or one pilot branch. Our multi-branch ERP guide discusses rollout sequencing in more detail.

A few cutover practicalities apply whichever strategy you pick: freeze transactions in the old system at a defined time, take final extracts, load the delta since the last trial, reconcile, and only then release the ERP to users. Keep the old system available in read-only mode for reference. The broader sequence of go-live activities is covered in our ERP implementation guide.

What not to migrate

Moving everything feels safe but usually isn't. Every extra record adds cleansing effort, load time and clutter. Consider leaving behind:

• Closed transactions from past years. Keep them in an archive or the old system in read-only mode, and migrate summaries if needed for comparisons.
• Inactive masters. Customers and suppliers with no activity for several years, discontinued items, obsolete warehouses.
• Workarounds. Dummy ledgers, "misc" items and suspense accounts that exist because the old system couldn't handle something properly.
• Data you can't explain. If nobody can say what a balance or a field represents, resolve it before migration or park it in a clearly labelled account with a plan to clear it.
• Duplicated reports. Spreadsheets that are only summaries of data already in the system. The ERP will regenerate these.

Remember that statutory record-keeping obligations still apply to data you don't migrate. Archive it safely and make sure it stays retrievable for audits and assessments.

ERP data migration checklist

Use this as a starting point and adapt it to your business.

1. Appoint a data migration owner from the business side, not just IT.
2. Build the data inventory: every source, owner, volume and master copy.
3. Decide the cutover date and the history policy (what is migrated, summarised or archived).
4. Define coding structures for customers, suppliers, items and accounts.
5. Freeze master data creation or route it through one controller.
6. Cleanse and de-duplicate masters, validating GSTIN, PAN, state and HSN/SAC data.
7. Prepare and sign off mapping documents for every entity.
8. Build repeatable extraction and transformation scripts using a staging area.
9. Run at least two trial migrations and log every error.
10. Run a timed dress rehearsal with the real cutover team.
11. Reconcile counts, trial balance, sub-ledgers and stock, with written sign-offs.
12. Freeze the old system, load final deltas and reconcile again.
13. Keep the old system read-only and archive what was not migrated.
14. Review data quality again after the first month-end close in the ERP.

Getting help with migration

Your team knows what the data means; an experienced partner knows how to extract, load and reconcile it without surprises. Our ERP migration services page explains how Aptivix approaches Excel, Tally and legacy system migrations, or you can book a consultation to discuss your specific setup.