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

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
ErrorInfo data type & Collectible Errors

ErrorInfo data type & Collectible Errors

A major change to AL Language development is here! Collectible errors can hugely improve any validation procedures. And what's new? Everything is about a new data type ErrorInfo. ErrorInfo data type ErrorInfo is a new data type introduced in Microsoft Dynamics 365...

read more
Exploring CosmosDB with Business Central, part 2

Exploring CosmosDB with Business Central, part 2

This part explains how to connect to Azure Cosmos DB from your Business Central extension using AL Language and how to get some values from the database.------------------------------------------------------------------------------------------------------------------...

read more
DynamicsCon 2021 session recording available!

DynamicsCon 2021 session recording available!

Finally, recordings from DynamicsCon 2021 Autumn edition are available! Check the official youtube channel. It was a great conference with over 250 people during my live session! I definitely hope I will be able to attend the next in-person DynamicsCon as a speaker...

read more
Directions EMEA day 0 ;)

Directions EMEA day 0 😉

Directions4Partners EMEA 2021 is still in progress here in Milan. I want to share all details about each day, but unfortunately, there are so many amazing things to see, watch and try I have almost no time. So today just a few words about the day before the...

read more
Working with Translation Files (XLIFF)

Working with Translation Files (XLIFF)

How does the translation file work? In Microsoft Dynamic 365 Business Central, displaying different languages is done using the XLIFF files extension (.xlf). The generated XLIFF files have strings that are specified in properties like Captions, Tooltips......

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

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