I work with a company that is migrating from Sage50 (on-prem) to ERPnext. The tutorial videos were ok, but I am looking for specifics on how to migrate (export/import) data from Sage50 to ERPnext (specifiacally Customers, Vendors, and Inventory items list).
Any insights, or helpful tips?
You can export data from Sage50 in Excel and then import it via Data Import:
https://erpnext.com/docs/user/manual/en/setting-up/data/data-import
every time I have attempted to import data from Sage I get errors. I have watched the same 3-4 videos everyone posts ad-nauseum, so another link to that does not help.
Starting from scratch in ERP, what are the steps I need to do in order to setup and migrate a company from Sage50? (what order do theses steps need to be taken?)
The issue seems to be required fields that (I assume) that need to already exist in ERp before importing data…is that correct? (i.e.: warehouse default, or tax info)
Just in inventory items, alone, there are over 10,000 items…can’t enter those by hand. I have, obviously, missed something. I continue to rewatch the getting started/Setup videos, but some direction would certainly be appreciated.
What has to be done BEFORE I attempt more data imports?
Best thing to do would be to make an entry via the UI, export it and use it as a reference to add new items. This is the best way to minimize the errors.
Also make sure that some of the other dependant data (such as Unit of Measures, Item Groups) are added correctly first. If not, they’ll produce more errors.
There are around 3 fields which are mandatory to add an item:
To resolve errors check the forum for pointers or post them here for ideas.
Since DocTypes relate and refer to eachother, the import order of a given DocType set is key to build and preserve these references.
Note this dependency diagram! ERPNext DocType Relationships
This may help Data import - from other ERP systems
In case anyone else has the same problem, I recently developed a REST API importer, and most of Sage’s services should be compatible. Get in touch if you’d like us to help!
Hi,
based on your latest comment:
I am having much trouble with importing our historical data (Invoice / Payment / Bank transaction). So these data is not coming from Sage50, so your importer might not be a 100% fit, but it probably could help us out to gather more information about the destination target values etc.
Did you use it for v13?
Would be great if you could help me out.
I think if I get some depths about the correct data mapping, this might help
Best
Philipp
Hi Philipp,
The API importer should be able to help if it’s on a REST API source.
However if the main problem is which document and field to put the values it probably won’t help because you have to map each source field to a destination field for each API.
Yes it’s developed on v13.
Could you post more details on where the data is coming from, and/or what specific problems you’re having?
Regards,
Richard
It’s coming from a custom implementation, so I am relatively free how to structure the data.
I am currently exporting them as csv with the intention to import them in nexterp.
I imported
At this point it’s getting strange for me, as the bank transactions are linked to the invoices, but not “really” (in the menu, you find the link to it, but no payment association).
Also the transactions are not known in the reconcilation tool (which I expect is good, as they should be mapped to the transaction).
Perhaps I am importing it with a wrong state … I made it “Settled”, because I didnt think the “Reconciled” would be good, as I thought the system will set this probably automatically after establishing the association.
However as this didn’t work, I thought, the Payment Entries might be missing (as actually I think, those should just have been created).
Anyways - I created another csv for them (7.). But after uploading, nothing is associated. The Payment Entries are there, but without any use.
So this is where I am not able to follow up - and I cant find any good docs … so I was researching the forum and came up to this thread
Hmm yeah it’s all a bit complicated because the Bank Transactions, Payments, Invoices & Journal Entries are all linked.
My first question would be do you actually need all the docs in ERPNext? If the years are past and all your accounting is done and closed off, the normal way would be start from scratch with opening journal entries.
But also there are a number of ways to import them.
You could try to do the whole lot and link them, which will be quite difficult (as you’ve found).
Or you could import the Bank Transactions & Invoices as draft, and then submit them afterward. Then reconcile. This will create all the necessary journal entries and link everything…
Actually it’s my intention, to get all the data, as this is always useful to keep them, and we don’t want to maintain a legacy system, just because it owns the data.
But in the end, if we have the migration for 2 years (which is at least mandatory), then the import could work also for the other years… as long as there are no manual steps left.
That’s of course the goal 
It would really help, if there would be those informations anywhere accessible:
At least these last (reference-) parts are pretty hard to try/error or to find any information about a possible solution.
I’d really appreciate, if somebody has a hint for me
Thanks so far!
Before I start I’d just like you to carefully think again if you actually need the data. How often will you actually refer to past invoices? It may be a lot of work for not much gain.
Also, since you want to import everything from pre-prepared data, you are most likely going to need to create other objects, definitely GL Entries which populate the chart of accounts. This is afterall one main feature of an ERP system, to maintain the accounts centrally.
Okay, so the way I would approach this is to create an example Invoice, Bank Transaction and Payment Entry by:
This will give you an example to follow.
But in general you should import in the order they would be normally be created:
Payment Entries just allow you to pay several invoices at once or several payments for a single invoice.
Bank Transaction has a child table Payment Entries that provide the linkage to the Payment Entries
Payment Entry has a child table Payment References to provide the linkage to the Invoices
Handy tip:
If you go to the 3 dots at the top of any document in ERPNext and click Customize you will get the list of fields, and if the field type is Link, you will get the other doctype it is linked to. The reference is always the name field of any doctype. This is the unique primary key for the doc.
Good luck!
^ Agree with this completely. In any data migration (not just ERPNext) migrating the semi-static data is the easier task. Data such as Customers, Suppliers, Items, Accounts, Addresses.
Migrating transactional data (GL history, AR/AP, Invoices, SO/PO) is always the trickiest part. There is always a ton of metadata and relationships to figure out.
If you can, definitely leave the transactional data behind. Save it somewhere for reporting later.
That said…I often face a challenge with how to handle these cases:
Option 1: Do not migrate the transactions. As those orders reach completion, you record them in the legacy system. The challenge? You have to somehow synchronize the GL & Inventory into your new ERPNext. For example, if you receive and invoice a PO in the legacy system, you have to update the Stock and ledger postings in ERPNext to reflect that.
Option 2: Key into ERPNext Manually. If you have a low volume of Open Orders, it’s better to manually type them into ERPNext, Submit, and process later. By manually typing the data, you don’t have to worry about mapping, enumerations, relationships, and business logic. Yes, it will take time. But how much time, versus developing and writing an electronic Data Migration process?
Option 3: Learn how ERPNext works, and migrate yourself.
I also agree with what @casesolved-co-uk said about creating examples. That’s the best (only?) way to really learn how to perform a manual migration. Create examples, then go inspect the data. See what fields were populated. What was left blank. See how they relate to other tables.
It is extremely time-consuming. You will make mistakes during migration. The only way to catch them? Testing, testing, testing. Try to use your migrated data in the browser. Run reports. Try to Submit and Invoice activity.
Thanks alot @casesolved-co-uk (especially for the “Handy tip”, this helps alot!)
Perhaps you have another handy tip for me regarding the downloadable examples.
I already handled that manually for all other imports as well, but what is really time extensive for me, is, to match the downloaded Column translations with the “real” technical ones.
So for example I export the sample data for payment_entry
and get the CSV with the following columns (just 2 for this example):
These are the translated column names, which work also for import, but are unhandy on a technical mapping basis.
So afterwards I need to open the column mapping in the import and enter those translated labels to get the technical correspondings column by column. And sometimes it’s not a 100% match, as “…” are rendered if the label is too long.
→ So do you know, if there is an option somewhere to Opt-Out from the translated Column names?
Also thanks to @brian_pond for your effort. Luckily your heavy use-cases are very rare, as we dont deliver physical goods.
So indeed it feels like, I am not very far away from the correct migration.
ahh … I found a way … If I just upload the downloaded sample, it directly displays the technical names in the mapping:
It’s still a bit of manual work, as I always need to map those, when discovering the correct values, but much easier at all.
So if there is somewhere an option to directly use them, it would be still great, but what I discovered yet also helps
I’m a bit confused why you would need the real technical names (aka database field names)?
If you can export the translated names and import the same sheet to create your new entries, why do you need to know?
Maybe you’re just missing the “Attach” button on the Data Import page?
no, it’s just as the technical names are much clearer and are matching our old data better.
So when reading (for example) “Exchange Rate” (perhaps it’s just personally) it is not really intuitive for me, which kind (or side) of exchange rate is meant… so in my opinion source_exchange_rate is much clearer.
Okay, I think you’ll just have to use the Customize page to check the translation.
You could try the Data Import Legacy tool which has a different export/import format with more headers? I’m fairly sure that has the database field names.
ok, it’s good enough with the re-upload “trick”
In case this is helpful. Sometimes when I want to see all the DocField metadata at once? I run a SQL query:
SET @TableName = 'tabCustomer';
SELECT
COLUMN_NAME AS Column_Name
,DATA_TYPE AS SQL_Data_Type
,CASE
WHEN CustomField.label is NOT NULL THEN 1
ELSE 0
END AS Is_Custom
,IFNULL(tabDocField.label, CustomField.label) AS DocField_Label
,IFNULL(tabDocField.fieldtype, CustomField.fieldtype) AS DocField_FieldType
FROM
information_schema.`COLUMNS`
LEFT JOIN
tabDocField
ON
tabDocField.parent = TRIM(LEADING 'tab' FROM TABLE_NAME )
AND tabDocField.fieldname = COLUMN_NAME
LEFT JOIN -- SELECT * FROM
`tabCustom Field` AS CustomField
ON
CustomField.fieldname = COLUMN_NAME
AND CONCAT('tab', REPLACE(CustomField.dt, ' ', '')) = TABLE_NAME
WHERE
TABLE_NAME = @TableName
ORDER BY
COLUMN_NAME;
Results look like this:
Helpful if I need to do something with the data, and don’t want to sift through the Web UI.
