Import Excel file using Excel Buffer in Business Central

Hi Readers, 

In Last article we have seen how we can import data (.csv format) in Business Central.

In this article we will see how we can Import Data (Excel format - .xlsx) in Microsoft Dynamics 365 Business Central. Here i am using Excel Buffer as a record variable to read data from Excel file for temporary basis and then will store it to Master/required table.

Let us take a scenario to Import Excel file to Employee Table.

Step-(1) First analyze the format/ structure of the csv data file as shown below:

Step-(2) Secondly based on the data format and columns, create a new Procedure/function on Codeunit to choose and import file using the dialog box to read the data.

Codeunit 50001 "Excel Buffer Import"

{

    trigger OnRun()

    begin

    end;

    var

        TempExcelBuffer: Record "Excel Buffer";

    local procedure ReadExcelSheet()

    var

        FileName: Text[100];

        SheetName: Text[100];

        UploadExcelMsg: Label 'Choose the Excel file to Import';

        FileMgt: Codeunit "File Management";

        IStream: InStream;

        FromFile: Text[100];

    begin

        if UploadIntoStream(UploadExcelMsg, '', '', FromFile, IStream) then begin

            if FromFile <> '' then begin

                FileName := FileMgt.GetFileName(FromFile);

                SheetName := TempExcelBuffer.SelectSheetsNameStream(IStream);

            end else

                Error('Excel file Not found!');

        end;

        TempExcelBuffer.Reset();

        TempExcelBuffer.DeleteAll();

        TempExcelBuffer.OpenBookStream(IStream, SheetName);

        TempExcelBuffer.ReadSheet();

    end;

    procedure ImportExcelData()

    var

        RowNo: Integer;

        ColNo: Integer;

        MaxRowNo: Integer;

        RecEmployee: Record Employee;

    begin

        ReadExcelSheet();

        TempExcelBuffer.Reset();

        if TempExcelBuffer.FindLast() then begin

            MaxRowNo := TempExcelBuffer."Row No.";

        end;

        for RowNo := 2 to MaxRowNo do begin

            RecEmployee.Init();

            Evaluate(RecEmployee."No.", GetValueAtCell(RowNo, 1));

            Evaluate(RecEmployee."First Name", GetValueAtCell(RowNo, 2));

            Evaluate(RecEmployee."Middle Name", GetValueAtCell(RowNo, 3));

            Evaluate(RecEmployee."Last Name", GetValueAtCell(RowNo, 4));

            Evaluate(RecEmployee.Initials, GetValueAtCell(RowNo, 5));

            Evaluate(RecEmployee."Job Title", GetValueAtCell(RowNo, 6));

            Evaluate(RecEmployee."Phone No.", GetValueAtCell(RowNo, 7));

            Evaluate(RecEmployee."Search Name", GetValueAtCell(RowNo, 8));

            Evaluate(RecEmployee.Address, GetValueAtCell(RowNo, 9));

            RecEmployee.Insert();

        end;

        Message('Excel is successfully imported.');

    end;

    local procedure GetValueAtCell(RowNo: Integer; ColNo: Integer): Text

    begin

        TempExcelBuffer.Reset();

        If TempExcelBuffer.Get(RowNo, ColNo) then

            exit(TempExcelBuffer."Cell Value as Text")

        else

            exit('');

    end;

}

Step-(3) Now create new Employee List Page Extension, and add new action button and call the required procedure:

pageextension 50001 "Employee List Ext" extends "Employee List"

{

    actions

    {

        // Add changes to page actions here

        addlast(Processing)

        {

            action("Import Employee Via Excel Buffer")

            {

                ApplicationArea = All;

                PromotedCategory = Process;

                Promoted = true;

                PromotedIsBig = true;

                Image = ImportExcel;

                trigger OnAction()

                Var

                    ExcelBufferImport_CU: Codeunit "Excel Buffer Import";

                begin

                    ExcelBufferImport_CU.ImportExcelData();

                end;

            }

        }

    }

}

Output- Open Employee List Page and click on action button "Import Employee Via Excel Buffer" and Choose the file and click on OK button:

Thanks for Reading!!