Import data with the Excel Buffer

In this video, I show an old favorite, the Excel Buffer. A great way to get data into Business Central. I create a few handy extra functions to make the experience nicer.

https://youtu.be/1H4aT4AXY0c

In this video, Erik demonstrates how to import data from an Excel file into Business Central using the Excel Buffer table in AL. He walks through the entire process — from understanding what the Excel Buffer is, to uploading a file via streams, reading sheet data into a temporary table, and extracting cell values into a custom table using clean, readable helper functions.

What Is the Excel Buffer?

The Excel Buffer is a table in the Business Central Base Application. You might expect it to live in the System Application (since it’s more of a technical utility than a business object), and Erik notes it will likely move there in the future. But for now, it resides in the Base App.

The Excel Buffer table has fields for Row No., Column No., Column ID, Cell Value as Text, and more. Essentially, each cell in an Excel spreadsheet becomes a single record in the Excel Buffer. The table is not declared as temporary by default, but you should always use it as a temporary table. This is critical — it’s expected that the table will eventually get the TableType = Temporary property.

Setting Up the Target Table and Page

Before importing, Erik created a simple table with four fields to match the four columns in the Excel spreadsheet, along with a page to display the imported data:

table 50100 "Excel Test"
{
    Caption = 'Excel Test';
    DataClassification = ToBeClassified;

    fields
    {
        field(1; Primary; Code[10])
        {
            Caption = 'Primary';
            DataClassification = SystemMetadata;
        }
        field(2; "Text Data"; Text[100])
        {
            Caption = 'Text Data';
            DataClassification = SystemMetadata;
        }
        field(3; "Date Data"; Date)
        {
            Caption = 'Date Data';
            DataClassification = SystemMetadata;
        }
        field(4; "Decimal Data"; Decimal)
        {
            Caption = 'Decimal Data';
            DataClassification = SystemMetadata;
        }
    }
    keys
    {
        key(PK; Primary)
        {
            Clustered = true;
        }
    }
}

Opening an Excel File via Streams

The Excel Buffer has several Open functions: OpenBook, OpenBookForUpdate, OpenBookStream, OpenExcelWithName, and more. Some of these — like OpenExcelWithName — are scoped for on-premises only because they accept a file path as a parameter, which doesn’t work in a SaaS/extension context.

For extension development (which is essentially all modern Business Central development), you need to use OpenBookStream. This function accepts an InStream and a sheet name. To get the InStream, use the UploadIntoStream function, which prompts the user to select a file and connects the resulting data to a stream variable.

if UploadIntoStream('Gimmi the Excel', '', '', Filename, InS) then begin
    Buffer.OpenBookStream(InS, 'Sheet1');
    Buffer.ReadSheet();
    // ...
end;

Note that the second and third parameters of UploadIntoStream (from folder and from filter) are legacy parameters from the Windows client and are no longer used. The Filename variable is passed by reference and returns the name of the selected file.

Understanding the Buffer Data

After calling ReadSheet(), each cell with a value becomes a record in the temporary Excel Buffer table. In Erik’s example spreadsheet, there were four columns and six data rows (plus a header area), totaling 24 populated cells. An empty cell does not produce a record — this is important to understand and handle correctly.

You can navigate the buffer using standard Business Central record operations — FindLast, SetRange, Reset, and Get. For example, to find the last row of data, you can filter on a specific column and call FindLast():

Buffer.SetRange("Column No.", 4);
Buffer.FindLast();
LastRow := Buffer."Row No.";
Buffer.Reset();

Building Readable Helper Functions

Rather than scattering Buffer.Get() calls and evaluations throughout the import logic, Erik creates a utility codeunit with clean helper functions. This approach makes the import code much more readable and maintainable:

codeunit 50100 "Excel Tools"
{
    procedure GetText(var Buffer: Record "Excel Buffer" temporary; Col: Text; Row: Integer): Text
    begin
        if Buffer.Get(Row, GetColumnNumber(Col)) then
            exit(Buffer."Cell Value as Text");
    end;

    procedure GetDate(var Buffer: Record "Excel Buffer" temporary; Col: Text; Row: Integer): Date
    var
        d: Date;
    begin
        if Buffer.Get(Row, GetColumnNumber(Col)) then begin
            Evaluate(D, Buffer."Cell Value as Text");
            exit(D);
        end;
    end;

    procedure GetDecimal(var Buffer: Record "Excel Buffer" temporary; Col: Text; Row: Integer): Decimal
    var
        d: Decimal;
    begin
        if Buffer.Get(Row, GetColumnNumber(Col)) then begin
            Evaluate(d, Buffer."Cell Value as Text");
            exit(d);
        end;
    end;

    procedure GetColumnNumber(ColumnName: Text): Integer
    var
        columnIndex: Integer;
        factor: Integer;
        pos: Integer;
    begin
        factor := 1;
        for pos := StrLen(ColumnName) downto 1 do
            if ColumnName[pos] >= 65 then begin
                columnIndex += factor * ((ColumnName[pos] - 65) + 1);
                factor *= 26;
            end;

        exit(columnIndex);
    end;

    procedure GetColumnName(columnNumber: Integer): Text
    var
        dividend: Integer;
        columnName: Text;
        modulo: Integer;
        c: Char;
    begin
        dividend := columnNumber;

        while (dividend > 0) do begin
            modulo := (dividend - 1) mod 26;
            c := 65 + modulo;
            columnName := Format(c) + columnName;
            dividend := (dividend - modulo) DIV 26;
        end;

        exit(columnName);
    end;
}

Key Design Decisions

  • Pass by reference (var): The Buffer parameter is passed by reference. This is critical when working with temporary tables — if you pass by value, you end up with two separate copies of the temporary data, which leads to confusing bugs where Get calls find nothing.
  • Implicit default returns: Notice that the Get call is wrapped in an if statement. If the cell doesn’t exist (empty cell), the function simply exits without returning a value. In AL, this means it returns the default for the type — empty string for Text, 0 for Decimal, 0D for Date. This gracefully handles missing cells.
  • Column letters instead of numbers: The GetColumnNumber function converts Excel-style column letters (A, B, …, Z, AA, AB, …) to column numbers. It iterates from right to left through the column name, treating each character as a base-26 digit where A=1, B=2, …, Z=26. Each position to the left increases the factor by 26.

The Complete Import Logic

With the helper functions in place, the import action becomes clean and easy to read:

trigger OnAction()
var
    x: Codeunit "Excel Tools";
    Buffer: Record "Excel Buffer" temporary;
    Data: Record "Excel Test";
    InS: InStream;
    Filename: Text;
    Row: Integer;
    LastRow: Integer;
begin
    Data.DeleteAll();
    if UploadIntoStream('Gimmi the Excel', '', '', Filename, InS) then begin
        Buffer.OpenBookStream(InS, 'Sheet1');
        Buffer.ReadSheet();
        Buffer.SetRange("Column No.", 4);
        Buffer.FindLast();
        LastRow := Buffer."Row No.";
        Buffer.Reset();

        for Row := 9 to LastRow do begin
            Data.Init();
            Data.Primary := x.GetText(Buffer, 'D', Row);
            Data."Text Data" := x.GetText(Buffer, 'E', Row);
            Data."Date Data" := x.GetDate(Buffer, 'F', Row);
            Data."Decimal Data" := x.GetDecimal(Buffer, 'G', Row);
            Data.Insert();
        end;
    end;
end;

Reading this code, you can clearly see: “For each row starting at row 9, get column D as the primary key, column E as text data, column F as a date, and column G as a decimal.” The cell references (D9, E9, F9, G9, etc.) map directly to the Excel spreadsheet, making the code intuitive to follow.

A Note on Evaluate and Regional Settings

Erik briefly touches on the Evaluate function’s optional third parameter. You can pass 9 to force evaluation using ISO 8601 format, which makes your code more resilient across different regional settings. However, this only works if the data in the Excel file is actually formatted in ISO format. If the dates in your spreadsheet are formatted according to a specific regional setting, the standard Evaluate (without the format parameter) will use the current user’s regional settings, which may or may not match. Test carefully with your specific data.

Performance Considerations

Since the Excel Buffer creates one record per cell, importing very large spreadsheets can be a memory-intensive operation. For each cell you need to read, you’re performing a Get on the temporary table, which means a lot of table traffic. For small to medium-sized imports, this works beautifully. For very large datasets, you may want to consider alternatives like BCCL (Business Central Configuration Language) or other bulk import mechanisms.

Summary

The Excel Buffer is a versatile and widely-used tool for importing Excel data into Business Central. The key takeaways are:

  1. Always use it as temporary — declare your Excel Buffer variable with the temporary keyword.
  2. Use OpenBookStream with UploadIntoStream for SaaS-compatible file uploads.
  3. Each cell is a record — empty cells don’t create records.
  4. Build helper functions that accept column letters and row numbers for clean, readable import code.
  5. Pass the buffer by reference (var) to avoid duplicate temporary table issues.
  6. Be mindful of performance with large spreadsheets due to the one-record-per-cell design.
Picture of hougaard

hougaard