Importing Multi-Tab Excel Files into Business Central
This post explains how to import multi-tab Excel files into Business Central with a header/line relationship structure. It provides code examples using AL to read separate header and line sheets, maintain relational integrity with a dictionary-based key mapping system, and populate staging tables.
post Aardvark Labs
"With Header/Line, or any other relational data, we have the challenge of relationship management."
Key points
- Use Excel’s sheet-specific tab reading to separate header and line data while maintaining relationships
- Store mapping keys in a Dictionary to connect header records with their corresponding line items without excessive lookups
- Read each sheet independently using TempExcelBuffer.OpenBookStream() with the sheet name parameter
- Process headers first, then lines, using the stored keys to establish parent-child relationships
Quotes
- “With Header/Line, or any other relational data, we have the challenge of relationship management.” (Establishes the core problem that multi-tab Excel imports must solve when dealing with related data structures)
AL objects mentioned
As named in the post. A name that matches one object page by exact type and name links to it; the others stay as named.
- page “ARDMultiTabImport”
- table “ARD_SalesHeader”
- table “ARD_SalesDetail”
Not found in BC28-30: page “ARDMultiTabImport”, table “ARD_SalesHeader”, table “ARD_SalesDetail”.
Context
- Features: Excel file import with multiple sheets, Dictionary-based key mapping, TempExcelBuffer sheet management, File streaming and parsing, Header-line relationship management
Source: Aardvark Labs, community blog. Summary, key points and quotes are derived (CONTENT-NOTICE.md); read the original for the full text.