How to import Excel files using Excel Buffer

by Mar 10, 2021AL Language

Home 9 Development 9 AL Language 9 How to import Excel files using Excel Buffer

Last week, I saw a question about importing data from Excel file to Business Central using AL Language. A few years ago, the only solution was to use DotNet integration. However, with the Business Central, there is a much quicker and better approach.

All functions for working with Excel files are available in a table called “Excel Buffer” (table 370 “Excel Buffer”). This table contains many useful functions to work with the Excel file, even functions for working with formulas or column style.

For importing data to Business Central, there are three crucial functions.

SelectSheetsNameStream(…)

The first of them is called SelectSheetsNameStream(). This function opens the Excel file and finds the sheet name. If the file contains only one sheet, the sheet is selected automatically. If there are more sheets defined, the user is asked to select the one to import.

OpenBookStream(…), ReadSheet()

The remaining functions are ReadSheet() and OpenBookStream().

The ReadSheet function reads the file’s content (that must be initialised using OpenBookStream at first) and stores loaded values into the Excel Buffer table.

How to load data from Excel

Once we know all these functions, we can create a procedure to load an Excel file quickly. Firstly, we need to upload the file (we can do it using the BLOBImportWithFilter function from the Codeunit 419 “File Management”). Then we need to choose the sheet we want to upload (SelectSheetsNameStream(…)) and prepare the Excel buffer table (OpenBookStream(…), ReadSheet(…)).

After that, we can just go through records in the Excel buffer table and process the table as usual. Besides row number, column number or the cell value, we have available information about cell style (bold, italic, underlined) and cell data type (date, number, text, …).


     local procedure ImportExcelFile()
     var
         TempExcelBuffer: Record "Excel Buffer" temporary;

         FileManagement: Codeunit "File Management";
         TempBlob: Codeunit "Temp Blob";

         SheetName, ErrorMessage : Text;
         FileInStream: InStream;
         ImportFileLbl: Label 'Import file';
     begin
         // Select file and import the file to tempBlob
         FileManagement.BLOBImportWithFilter(TempBlob, ImportFileLbl, '', FileManagement.GetToFilterText('', '.xlsx'), 'xlsx');

         // Select sheet from the excel file
         TempBlob.CreateInStream(FileInStream);
         SheetName := TempExcelBuffer.SelectSheetsNameStream(FileInStream);

         // Open selected sheet
         TempBlob.CreateInStream(FileInStream);
         ErrorMessage := TempExcelBuffer.OpenBookStream(FileInStream, SheetName);
         if ErrorMessage <> '' then
             Error(ErrorMessage);

         TempExcelBuffer.ReadSheet();
         if Rec.FindSet() then
             repeat
                 Message('%1, %2: %3', TempExcelBuffer."Row No.", TempExcelBuffer."Column No.", TempExcelBuffer."Cell Value as Text");
             until TempExcelBuffer.Next() < 1;
     end;

Recent Articles from the category

Connect to Azure Function in BC 2022 wave 2 (v21)

Connect to Azure Function in BC 2022 wave 2 (v21)

The new version of the Microsoft Dynamics 365 Business Central brought a new system module "Azure Functions" that makes integration with Azure Functions much easier and straightforward. The typical scenario, why to use Azure Functions together with Business Central,...

read more
Collectible Errors?! Is it already in use?

Collectible Errors?! Is it already in use?

Collectible Errors?! Is it already in use? This is the second part of my new article series about Collectible Errors. Let's check out the first part here: Collectible Errors?! | Microsoft Dynamics 365 - Ing. Tomáš Kapitán (kepty.cz) or you might be interested in my...

read more
Collectible Errors?!

Collectible Errors?!

Collectible Errors?! It has been already almost a year since ErrorInfo datatype & CollectibleErrors were introduced (I already have an article about basic structure: ErrorInfo data type & Collectible Errors). This article was released for the first time in...

read more
Substituting standard reports

Substituting standard reports

Report objects cannot be extended in versions previous to the Business Central 2021 release wave 1 version when ReportExtensions object was introduced. Since then, many of changes we need to do in reports can be done without creating a new copy of object. On the other...

read more
Isolated events

Isolated events

With Business Central 2022 wave 1, a new setting for event publishers was introduced. Until this version, any error in any event subscriber caused interruption to the current running process and stopped the whole activity. In some cases (such as log-in), this is...

read more
Custom Filter Tokens

Custom Filter Tokens

As a user of the Business Central you have some constants you can use to filter or insert values. These constants contain useful values for data manipulation such as t / today for date field - return current dateq / quarter for date field filters - return range of the...

read more
1D & 2D Barcodes in Business Central 19.1

1D & 2D Barcodes in Business Central 19.1

Business Central (cloud-only!) includes one-dimensional (1D) barcode fonts since Microsoft Dynamics 365 Business Central 2020 wave 2 (17.0) was released in October 2020. It is a great improvement to how developers can handle requirements from their clients to print...

read more

Sign Up for News

Certifications

Highest certification
Microsoft Data Management and
also in D365 Business Central

Microsoft Certified: Dynamics 365 Business Central Functional Consultant Associate

See other certifications here