MIgrating Data Into ERPNext

Data migration is the process of moving data from one system to another. For example, moving data from legacy systems into ERPNext. It is a crucial step in your ERPNext journey. The effort required to complete this complex and tedious task is often underestimated. Any delays or issues in data migration can throw your entire ERPNext project off track.

In addition to the complexity and tediouness, you will have a short period of time between the 'Cut-off date' and the 'Go-live date' to complete the data migration. The Cut-off Date is the date after which no new transactions will be posted in legacy system and the Go-live Date is the date on which new transactions will be posted in ERPNext. So it is important to prepare well in advance. Also, you need to know the way data is structured in legacy systems and ERPNext to successfully complete the migration project.

1. Identify Data🔎

Migrating data is an expensive affair. Analyze the data in your legacy systems and move only the required and valuable data. Your accounting and finance teams may be in favor of moving historical data as it helps them in doing comparative analysis in reports. However, there is a cost to moving historical data which may outweigh the benefits. This trade-off requires some analysis. If you choose not to migrate historical data you can move it a 'Data Warehouse' so that it is securly stored is available for users when required.

Over time a lot of redundant data may be accumulated. It is cost-effective to clean up such data in the source if feasible.

1.1 Master Data

The master data (customers, suppliers, items, locations, etc) is the most important data your new ERP system will need. You will need to move this data first before you can move opening balances and historical transactions.

1.2 Opening Balances

The opening balance is the data at the end of the cut-off date. After the cut-off date, the legacy systems will have 'read-only' access, and you cannot post new transactions. The table below shows some of the opening balances you need to import during data migration.

Module	Opening Balance
Accounting	Opening Ledger Balances
Stock	Opening Stock
Sales	Open Sales Order
Purchase	Open Purchase Orders
Manufacturing	Open Production Orders
Human Resources	Opening leave balances

It is important to get accurate opening balances as making corrections is a very time-consuming task.

1.3 Transaction Data

Moving historical transaction data (sales invoices, purchase invoices, journal entries, etc) is often very tedious as such data needs reconciliation after importing. Also, all the necessary master data which may no longer be relevant now needs to be imported. You may also choose to move a portion of certain data. For example, sales and purchase invoices in legacy accounting systems will have details on items. You may choose to skip item details and just move the summary of each transaction. You may be required to retain certain data for a specific time period for compliance, so consult the right people.

2. Map Data Models↔

Software applications use objects to model real-world entities like customers, suppliers, items, sales invoices, etc. And each object has properties that describe the real-world entity. For example, the 'Customer' object has the customer name, tax id, territory, etc.

First map the objects. For example, your existing CRM system may have an object called 'Deal' and in the CRM module of ERPNext, this corresponds to 'Opportunity'. Then map the properties of each object. The 'Item' object in your legacy system may have a property called 'Category', in ERPNext this could be mapped to 'Item Group'.

The structure of data in legacy systems may be very different compared to the structure in ERPNext. For example, the 'Item' object may have a property named 'Category' to indicate if it is a raw material, sub-assembly, or a finished good from a manufacturing perspective. And another property named 'Group' to indicate group from a business perspective. In ERPNext, the 'Item Group' has a hierarchical structure, so both group and category can be mapped as 'Item Group'.

3. Extract Data⚒️

Most applications provide tools to export data. Use the appropriate filters to export the data identified in step one. For example, you may have marked certain items as 'disabled' as you no longer use them. If the legacy applications allow querying the database directly, you can consider writing the SQL queries to extract the exact data. Legacy applicatiosn may support multiple file formats while exporting, choose CSV and Excel formats as ERPNext supports these formats.

4. Transform Data💫

The very first thing to do is to clean up the data. Remove duplicates, remove corrupted data, fix formats, fix incomplete data, etc. Next, the data exported from legacy applications will be in a specific structure. For example, here is the format of BOM exported from a legacy system. The BOM or Bill-Of-Materials for finished good 'FG0101101' contains four raw materials as shown below.

BOM Number	Part No	Item Category	Quantity	Consumed Item	Consumed Qty
1001	FG0101101.	Finished Goods	1	RM0302301	2
1001	FG0101101.	Finished Goods	1	RM0401301	2
1001	FG0101101.	Finished Goods	1	RM0101101	1
1001	FG0101101.	Finished Goods	1	RM0101301	1

If you import this data via the 'Data Import' tool in ERPNext, you will have four BOMs for the same finished goods. Format this data as below to load correctly into ERPNext. .

BOM Number	Part No	Item Category	Quantity	Consumed Item	Consumed Qty
1001	FG0101101	Finished Goods	1	RM0302301	2
				RM0401301	2
				RM0101101	1
				RM0101301	1

Depending on the size of your data, you can pick an appropriate tool like spreadhseet, script, or ETL application to transform the data.

5. Load Data🗳

In ERPs data is connected. So the data needs to be loaded in the right sequence. For example, before you can import items, you need to import item groups and Unit of Measures. Below table shows some examples.

Object	Dependency	Extended Dependency
Item	UOM, Item Group	Warehouse, Price List, Supplier, Quality Inspection Template
Customer	Customer Group, Territory	Price List, Payment Terms, Sales Person, Sales Partner, Accounts Receivable Ledger
Supplier	Supplier Group	Price List, Payment Terms, Accounts Payable Ledger

Once the data is transformed and is ready to load, use the data import tool. Expect some errors as the data may not be perfect. It is best to load a few hundred records at first and then gradually increase the number of records. For large datasets, you may have to use the 'import-csv' tool.

To import the chart of accounts, ERPNext offers a special tool named 'Chart of Accounts Importer'. This will simplify importing the ledgers with the groups and sub-groups.

Happy data importing!🥳

Reference:

1. Data Import Tool
2. Chart Of Accounts Importer
3. Importing Large CSV Files