The data migration for a new ERP is always a challenging task. It starts with the collection of the data from the legacy system, the cleansing of the data , the data modelling to the desired new format and the upload to the new ERP.
There are several studies that states "The data migration phase of a project can consume up to 30% of the total project resources".
This post will describe a second free snippet to migrate master data to Dynamics 365 BC (Open Documents). As an example, it will be described the migration of Open Purchase Orders from Excel files to D365BC.
The most common approaches are: use Rapid Start Services (now named Configuration Packages) or create a bespoke xmlport.
This post will challenge these most common approaches and suggest another one (in line with the first “snippet”): Use the option Edit in Excel available in D365 Business Central.
This option will open a new Sheet in Excel and will allow the End User to update all the records in Excel and upload the changes to D365BC.
To use this option, you should have Excel 2016 version as minimum and be able to run Excel add-ins.
The first step should be to access to Purchase Orders Menu. After viewing the Purchase Orders List, click Page > Edit in Excel.
It will open an Excel spreadsheet with all Purchase Orders (one per line). Before updating data, guarantee that you are viewing the desired columns (in my case, I choose Document_Type (Key), No (Key), Buy_from_Vendor_No, Document_Date, Posting_Date, Purchaser_Code, Order_Date, Vendor_Order_No, Location_Code and Status).
Add new lines in Excel following the column formats. Before pushing the button "Publish", guarantee that the No. Series for Purchase Orders and Posted Purchase Invoices (Purchase & Payables Setup) allow manual numbering. Otherwise, you will get an error after clicking in “Publish” button.
After publishing, all the new lines will be uploaded to D365BC creating new headers for new Purchase Orders.
Simple as this!!!!
The next step should be a repetition of this same process but for Purchase Lines.
Select one Purchase Order and open it. Position at line level and click Page > Edit in Excel.
It will open an Excel spreadsheet with all the lines for only this Purchase Order.
Before updating data, guarantee that you are viewing the desired columns (in my case, I choose Document_Type (Key), Document_No (Key), Line_No(Key), Type, No, Variant_Code, Location_Code, Quantity, Unit_of_Measure_Code, Direct_Unit_Cost, Line_Discount_Percent, Line_Amount, Requested_Receipt_Date and Promised_Receipt_Date).
Remove the filters to be able to see all the lines for all the Purchase Orders and not only the lines for the Purchase Order selected.
Add new lines in Excel following the column formats. In my case I populated the columns Document_Type (Key), Document_No (Key), Line_No(Key), Type, No, Variant_Code, Quantity, Unit_of_Measure_Code, Direct_Unit_Cost and Requested_Receipt_Date.
"Publish" and all the new lines will be uploaded to D365BC creating new lines for the selected Purchase Orders.
With the Publish, all the indirect fields like Location Code, Description for example will be populated automatically by D365BC using the inherent rules.
Simple as this!!!!
In my view, you should try and consider this free “snippet” to migrate Master Data to D365BC (Open Documents):
the step-by-step is simple
the performance is faster than using Configuration Packages
and No Code needed.
Not convinced? Try yourself. This "snippet" is free to use.