Importing and exporting data

Path: Development > Programming in the AL language > Tables and data > Importing and exporting data · tier official · system development · narrative reviewed (checked by Opus)

Overview

This section covers two ways to move data in and out of Business Central from AL code. XMLports import and export XML data between external sources and Business Central. Excel Buffer is a temporary table with methods for building Excel workbooks.

For XMLports, start with the “XMLport object” and “XMLport Overview for Business Central” pages. They explain what an XMLport is and describe its schema, request page, triggers, methods and properties such as Format and Direction. Then use “Defining an XMLport Schema in AL” to map XML elements to tables and fields. Use “Use Namespaces with XMLports in AL” when the external system needs XML namespaces. Use “Using request pages with XMLports” to let users set options and filters before a run.

For Excel, “Exporting data to Excel using ExcelBuffer” shows how to create a workbook, write rows, columns and cells, and either download it or save it to a stream.

Key points

• XMLports export and import XML data between external sources and Business Central, and can include a request page for filtering, sorting, and choosing export or import.
• An XMLport schema is built from textelement, textattribute, tableelement, fieldelement and fieldattribute nodes. The SourceTable property ties table elements to tables.
• XMLport namespaces are set with the Namespaces, NamespacePrefix, DefaultNamespace and UseDefaultNamespace properties. They avoid element name conflicts with external systems.
• XMLport request pages are configured with properties such as RequestFilterHeading, RequestFilterFields, AboutTitle, AboutText, ContextSensitiveHelpPage and SaveValues. The pages mention 2019 release wave 2 and 2023 release wave 1.
• The XMLport overview covers the Format and Direction properties, triggers and methods.
• Excel Buffer is a temporary table. You build workbooks with EnterCell, NewRow, AddColumn, CreateNewBook, WriteSheet and CloseBook.
• Excel output can be downloaded with OpenExcel or exported to a stream with SaveToStream.

Learn pages

• Defining an XMLport Schema in AL: An XMLport schema determines which data is exported from or imported to Dynamics 365 Business Central database tables and the format and structure of the files used.
• Exporting data to Excel using ExcelBuffer: Learn about the ExcelBuffer functionality and how to use it to copy data from AL to Excel.
• Use Namespaces with XMLports in AL: Learn how to declare namespaces on an XMLport in Business Central and apply namespace prefixes to text, table, and field elements in the XML schema.
• Using request pages with XMLports: Introducing how to work with request pages for Business Central XMLports.
• XMLport object: XMLport objects are used to export and import data between an external source and Business Central.
• XMLport Overview for Business Central: Get an overview of the XMLport object in AL, which is composed of an XMLport schema, a request page, and properties, triggers, and code that you can extend.

Videos, posts and code changes

Linked by a Haiku matcher with a grounding quote from the item’s summary (link/topics.ts); machine-generated.

• #10331 [Master]-Missing VAT Identifier in Peppol XML for Zero-Value Invoice (code change): “PEPPOL XML export now includes the VAT identifier for zero-value invoices”
• #11968 Bug 648782: omit the buyer PartyTaxScheme when the company has no VAT registration (code change): “PartyTaxScheme block is skipped entirely when VAT Registration No. is empty”
• #12051 [Peppol] Fix Remittance Advice XML line count, Note order and invoice reference (code change): “Peppol remittance advice XML export now correctly reports”
• #12309 Fix infinite recursion in Config. Package Field XML name generation (code change): “Prevents stack overflow when adding tables to Configuration Packages”
• #9073 Data Exchange Definition export performance improvement (code change): “Data Exchange Definition exports now use in-memory mapping with bulk saves”
• Best Practices for Handling Delimited Data Imports into Business Central (community post): “import comma-separated values (CSV) customer data into Business Central by parsing delimited files”
• Guide to Handling Header and Detail Imports into Business Central with AL (community post): “Data is parsed into separate staging tables before creating actual sales orders”
• Importing Multi-Tab Excel Files into Business Central (community post): “Importing Multi-Tab Excel Files into Business Central”
• Importing XML into Business Central with AL (community post): “Importing XML into Business Central with AL”
• How to Export CSV Files from Business Central Using CSV Buffer (Developer Guide) (community post): “export data from Business Central to CSV format using the CSV Buffer”

Source: Microsoft Learn (CC BY 4.0). Descriptions are Learn’s own.