XML Mapping
XML mapping constitutes the heart of the import process: it indicates to Business Central how to identify each Excel table column and what data type (text, integer, date, boolean, etc.) to associate with it. In this paragraph, I will show how to extract, modify, then re-import this mapping in order to adjust the Excel file to package requirements.
It sometimes happens that the file transmitted to the client does not reflect exactly the order of fields as defined in the configuration package. In this case, the most direct solution consists of using the field movement arrows (up or down) available in the package interface: by reordering fields, Business Central automatically regenerates an Excel template whose XML mapping integrates the new order. One can then copy client data into this template without having to manually recreate the table.
However, when a table involves a large number of fields, manual rearrangement can become very time-consuming. It is then often faster and simpler to master the creation and modification of XML mapping: by directly editing the XML file, one aligns the order of XML tags with existing columns, adjusts types if necessary, then re-imports the file. This approach avoids back-and-forth in the package interface and allows quickly preparing a compliant Excel file, even for the most voluminous tables.
Exporting the Mapping
To export the XML mapping, simply select the node corresponding to the table, located immediately after the main node named DataList, and click on "Export" in the Developer tab, XML section.
By opening the XML file, one observes that table data are also exported; the file therefore contains not only the mapping but also the actual content of the rows.
To export only the mapping without including data, follow this little trick:
- Delete the link between each mapping field and its column;
- Create a new table by establishing links between the mapping and columns again;
- Fill a single record with any value;
- Launch the mapping export.
Thus, you preserve original data, avoid exporting the entire dataset, and retrieve only the mapping accompanied by a single dummy record.
Removing Links
Select the node corresponding to the table, right-click "delete element".
Creating a New Table
Simply choose the node corresponding to the table, then drop it onto an empty cell of the sheet. This manipulation automatically generates a new, blank table, where each column already possesses its associated mapping.
Creation of a new data table
Inserting a Record
Before exporting the XML file, it is necessary to insert at least one datum.
By applying the three steps described in the "Exporting the Mapping" section, the generated XML file will contain only the mapping accompanied by a single value.
It remains only to eliminate the value "TEST" from the <Code> element, or rather replace it with the xml tag: <Code/>.
Now that we master the export of an XML mapping, we can modify it: add new fields, reorganize column order, then re-import it and attach it to the table containing original data.
There are some rules to know regarding how Business Central generates field names in XML mapping:
- Dots (".") are removed.
- Spaces are withdrawn.
- All special characters (for example "<", ">", "&", etc.) are eliminated.
In practice, a field label is "cleaned" of these characters before being used as an XML identifier. Thus, the field titled:
Bal. Account No.
becomes simply:
BalAccountNo
Importing a New Mapping
In the conclusion of this article, I will re-import into the Excel workbook a mapping whose fields I have reordered.
To grasp the interest of these successive manipulations, one must first understand the context: it is a common case where the client provides us with an Excel file where the order of data table columns differs from that of the XML mapping. Some columns were added without being mapped, others lost their association.
Rather than starting from a template file exported from Business Central – which would then have to be manually adapted to this new arrangement – I show you here how to directly import a new mapping that reflects the order desired by the client. This approach avoids rebuilding the template from zero and guarantees that columns are correctly aligned with the mapping.
In the image above, we see that the Excel file no longer follows the original mapping order. The new mapping displayed on the right is the one I adjusted to correspond to this new layout, and it is the one I will re-import.
Mapping import is launched via the "XML Mapping" button located at the bottom of the mapping window.
When the small dialog box opens, displaying the list of mappings present in the Excel workbook, you can create a new one by clicking on "Add".
Once the XML file is chosen, Excel automatically inserts a new entry in the mapping list.
It remains only to delete the old mapping.
Close the XML mapping window, then drop the new mapping onto the data table.
One must not forget to map also the two fields TableID and PackageCode on the header, by drag and dropping them onto the two concerned cells.
If, by placing the TableID field back on the concerned cell, Excel automatically creates a header row, it must be deleted. For this, open table creation options, deactivate "Header Row" (choose "No") in the Style Options section, then validate.
The manipulations are finished: the Excel workbook now features a data table whose mapping is perfectly aligned with column order. This file can be imported into a configuration package, even if the package uses a different field order.
In the presented example, data import from the Excel file to the chosen package will proceed correctly, since Business Central will recognize the columns.
With this three-part series, you now have a complete overview—from methodology to XML mapping—to secure your Business Central migrations. Now it's your turn to act, but also to share: let's connect on LinkedIn to keep the discussion going. Whether it's to correct me, suggest new topics, or simply let me know if this content helped, your messages and encouragement will be the driving force behind my future articles.