Skip to main content

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!!
Labels:

Comments

Post a Comment

Popular posts from this blog

Flow Custom field to G/L Entry and other Posted tables : From Purchase Order to Posted Purchase invoice

Hi Readers, In this blog we will see how we can transfer data from " Purchase Header" to "Purchase Invoice Header" & "G/L Entry" table in Business Central. In this example, will create "Remarks" field on Purchase Header table and after posting, flow it to Purchase Invoice Header & G/L Entry tables: Firsly, create required fields ("Remarks") in below mentioned tables by creating table extensions: Purchase Header (38) Purch. Inv. Header (122) Gen. Journal Line (81)  G/L Entry (17) For the Purch. Inv. Header table, you can just create copy the field from purchase header and paste it in  Purch. Inv. Header  table. If the field ID No. is same for that field for both the tables then you don't have to code i.e, it automatically transfer that field data from Purch. Header to Purch. Inv. Header . Step-(1)   tableextension   50104  "Purchase Header Ext"  extends  "Purchase Header" {      fields   ...
3 comments
Read more

Calculate Date : Using Date Formula and CalcDate Function in Navision/BC

 Hi Everyone, In this article we will see how we can Calculate Date by adding and subtracting No. of Days / Months based on CalcDate and DateFormula datatype. Here, I define three variables required for: (1) " No of Days " -  To define No. of Days / Months as required. You can also use these units denoted as: <Unit> = D | WD | W | M | Q | Y (D=day, WD=weekday, W=week, M=month, Q=quarter, Y=year). (2) " From Date " and " To Date "  Date type Variables to store calculated date in it. page   50103  "Date from Date Formula" {     PageType = Card;     ApplicationArea = All;     UsageCategory = Administration;     SourceTable =  Integer ;      layout     {          area( Content )         {    ...
Post a Comment
Read more

The Txt2Al Conversion Tool: How to Export Dynamics NAV objects (.txt) and converting to AL

Hi folks, In this article, I will show you how you can use Txt2Al conversion tool that is the part of NAV 2018 and Dynamics 365 Business Central On-Premise. Using this Txt2Al conversion tool you can take existing Dynamics NAV objects that have been exported in .txt format and convert them into the new .al format and use these .al format file directly using Visual Studio Code to develop/build extensions for Dynamics 365 Business Central. Converting the NAV objects consists of following 2 steps: 1. Exporting the objects from C/SIDE [Either from Development Environment or using PowerShell Command] 2. Converting the objects to the new syntax. You can find Txt2Al tool in following location if you installed Dynamics 365 Business Central On-Premise on your machine: C:\Program Files (x86)\Microsoft Dynamics 365 Business Central\140\RoleTailored Client \Txt2Al.exe Steps to use the Txt2Al conversion tool:  Reference Step 1:  Compile your Dynamics NAV DB Step 2: ...
Post a Comment
Read more