How to Import Database from Old ERP mantaining tables relationships

italjapan · May 9, 2017, 10:52am

Hi there
we are starting planning to import in ERPNExt our old ERP Database.
Just to be clear old ERP Sofwtare is Not ERPNExt but totaly different software that we want dismiss and start using powerful ERPNext.

On that old Relational Database we have several tables like Items , Customers, Suppliers and mny tohers that we want import populating the ERPNext DocTypes. Of course, tables are connected each other by entity relationships related keys.

To import single tables I can see that valuable data import tool is present , but how to proceed to import tables
that are connected each other by key constraint? In the data import tool template is not possible to use old ID we have in the origin Database, as value for “name” DocType field, so how can we import tables mantaining previous DB relations between them (primary/foreign keys)?

I was looking around for a detailed guide/procedure to use to manage this importing and
I feel we need it because the old ERP Database has a quite complex structure. How do you suggest to proceed?

Many thanks for your attention

saidsl · May 10, 2017, 12:18am

@italjapan

You have a few options to import data to the latest version of ERPNext

Option 1 - update existing ERPNext version (Preferred & Suggested Route)

Clone/duplicate existing/old ERPNext version (This is to avoid your system going down)
Commence updating to the latest versions this will update all you tables.
Once you have completed the update you can use/replace this instance with your old one

Option 2 - Export Individual data via ERPNext (More Tedious Process)

Export each data via Data Export/Import Tool
Modify accordingly based on the example doctypes
Import into new ERPNext Instance.

Which version of ERPNext are you currently running now, in how old is your old version?

Regards
Said

creamdory · May 10, 2017, 1:56am

Hi @italjapan! If your old ERP is not ERPNext, I suggest you set up beginning balances instead of importing everything. What most company does is set a cut off date and calculate beginning balances to setup to ERPNext. If in the event that past data needs to be recheck, they will retain old ERP for this purpose. This way, it won’t be too complicated to import. The suggestions above are the ways to do it though but would be timely and needs careful attention to balance accounts.

Regards

revant_one · May 10, 2017, 6:59am

Because ERPNext is free and opensource, it doesn’t have much overheads for hosting one more instance.

There is one more approach, setting up 2 ERPNext instances, one is for reference only where old data is hacked into and other is production setup as mentioned by @creamdory

I came across a company who had invoices of past 3 years, these invoices must be in a database because their customers may return goods any time even after 6 months or a year. Due to some reason we couldn’t maintain old erp to keep track of old data. So we settled for 2 instances. This approach keeps the production server up to date and reference server is never updated keeping the hacks intact.

After 2 years on ERPNext there will be least dependence on old system and obsolete reference server can be deleted.

If in your case you could maintain old system running for reference that will solve the problem.

italjapan · May 10, 2017, 7:04am

Thanks for you reply , anyway Old ERP != ERPNExt
It’ s not a matter of intra-ERPNext migrations but a totally new ERPNext setup from the scratch coming from other different software.

revant_one · May 10, 2017, 7:41am

Detail of data import tool for production system:

Treat creating masters as onboarding a new system and not migrating to ERPNext.
Import masters like Customer, Supplier, Employee, User, with Data Import Tool (It has predefined CSV format which can be filled in)
You can also try importing other DocTypes which are available for import and are relatively independent.

Detail of forcing in data for reference server:

Write script to generate CSVs for Data Import Tool and then use standard Data Import.
Script to read data from custom CSV/file.
Create a script in frappe that reads doctype data from the custom csv/file,
Create a doc with frappe.new_doc()
Rename the Doc if required frappe.rename_doc() (Rename doc will only work if Allow Rename is set on Doctype)

This script was used for force importing reference data on a separate server.

github.com

mntechnique/skynpronaturals_erpnext/blob/testing/skynpronaturals_erpnext/si_import.py#L311


      
          # 		rowmsg.append("Invoice Saved. ({0})".format(si.name))
          # 		rename_doc("Sales Invoice", si.name, voucher_no, force=True)
          # 		rowmsg.append("Renamed to {0}".format(voucher_no))
          # 	except Exception as e:
          # 		rowmsg.append("not saved: {1}, {2}".format(voucher_no, sys.exc_info()[0], sys.exc_info()[1]))
          
          
# 	return "\n".join(rowmsg)
          
          

          
@frappe.whitelist()
          def process_sheet_and_create_si(path_to_sheet, path_to_returns_map, debit_to="Debtors - SPN", income_ac="Sales - SPN", cost_center="Main - SPN"):
          	from frappe.model.rename_doc import rename_doc
          
          
	msgs, out = [], []
          	final_json = csv_to_json(path=path_to_sheet)
          	rows = final_json["data"]
          
          
	#Get returns map dict
          	returns_map_dict = csv_to_json(path_to_returns_map, 0, 1)
          	unique_vouchers = list(set([v.get("Voucher No") for v in rows]))
          	processed_recs = 0

italjapan · May 10, 2017, 10:18am

Thanks for your answer , very kind of you.
Browsing the code I see this useful example line code:
rename_doc(“Sales Invoice”, si.name, voucher_no, force=True)

From your example I can understand that each import and each table will have it’s own specific methodology to apply in order to have an import that make sense. So having quite complex tables relationship , several specific ad-hoc script will be required to move piece of the old DB o the new era Erpnext.