How to Import EXCEL data in Business Central D365

Import EXCEL data in Business Central D365:

(1) In the post, we will explain how to import excel data in Microsoft Business Central D365. For this, developed a new report (already explained in the previous blog “Report Creation In Microsoft Dynamics 365 BC”). In BC D365 there is a “Configuration Package” feature that helps to import and export data from BC D365 without any customization.

(2) If customization is required in Import Excel functionality, then we know about the import excel function functionality so that we can achieve the required result.

(3) Let’s take an example to create a bulk Sales Order document from an excel sheet. For doing this follow the below steps.

(4) Define Template in excel as shown.

define template in excel

(5) Develop a new Report, as shown.

Request page: Request pages is required for selecting an Excel file and their related sheet.

Request pages is required for selecting an Excel file and their related sheet

OnPreReport: Call procedure in Onprereport for export to see the excel template and import excel sheet in the desired tables as per the requirement.

Call procedure in Onprereport

MakeHeader: For export blank excel format.

export blank excel format

CreateExcelBook: Create Excel Book for seeing the excel template or format required for this excel importer.

GetLastRowandColumn: Count numbers of rows and columns in the excel sheet.

GetLastRowandColumn

GetValueAtCell: Read data form Excel Cell.

GetValueAtCell

InsertData: Insert Data into Sales Header and Sales Line table according to the data in excel.

export to excel code
bc export excel code
BC D365 CODE
D365 CODE

(6) After following the above steps, attach the report in the menu suite, publish the code and import the data as per the excel format as per the business requirement.

Source Code:

report 50003 ExcelReader
{
UsageCategory = Administration;
ApplicationArea = All;
 
    dataset
    {
dataitem(Integer; Integer)
        {
DataItemTableView = sorting(number) where(number = const(1));
 
        }
    }
requestpage
    {
        layout
        {
area(Content)
            {
group(Options)
                {
field(ServerFileName; ServerFileName)
                    {
ApplicationArea = All;
                        Caption = ‘File Name’;
                        trigger OnAssistEdit()
                        begin
UploadIntoStream(UploadExcelMsg, ”, ”, FromServerFileName, Istream);
                            if FromServerFileName<> ” then
ServerFileName := FileManagement.GetFileName(FromServerFileName)
                            else
ERROR(‘File does not exit’);
                        end;
 
                    }
field(SheetName; SheetName)
                    {
ApplicationArea = All;
                        Caption = ‘Sheet Name’;
                        trigger OnAssistEdit()
                        begin
SheetName := ExcelBuffer.SelectSheetsNameStream(Istream);
                            IF SheetName = ” THEN
ERROR(”);
                        end;
 
                    }
field(ExportTemplateFormat; ExportTemplateFormat)
                    {
ApplicationArea = All;
                        Caption = ‘Export Template Format’;
 
                    }
                }
            }
        }
 
        actions
        {
            area(processing)
            {
action(ActionName)
                {
ApplicationArea = All;
 
                }
            }
        }
    }
    trigger OnPreReport()
    begin
        IF NOT ExportTemplateFormat THEN BEGIN
ExcelBuffer.DELETEALL;
ExcelBuffer.LOCKTABLE;
ExcelBuffer.OpenBookStream(Istream, SheetName);
ExcelBuffer.ReadSheet;
GetLastRowandColumn;
            FOR X := 2 TO TotalRows DO
InsertData(X);
ExcelBuffer.DELETEALL;
        END;
 
        IF ExportTemplateFormat THEN BEGIN
TempExcelBuffer.DELETEALL;
MakeHeader;
        END;
    end;
 
    trigger OnpostReport()
    begin
        IF ExportTemplateFormat THEN
CreateExcelBook;
    end;
 
    var
myInt: Integer;
ExportTemplateFormat: boolean;
SheetName: Text;
ServerFileName: Text;
FromServerFileName: Text;
ExcelBuffer: record “Excel Buffer”;
FileManagement: Codeunit “File Management”;
TotalColumns: Integer;
TotalRows: Integer;
TempExcelBuffer: Record “Excel Buffer” temporary;
        X: Integer;
Istream: InStream;
UploadExcelMsg: Text;
 
    local procedure MakeHeader()
    var
myInt: Integer;
    begin
TempExcelBuffer.NewRow();
TempExcelBuffer.AddColumn(‘Document No.’, FALSE, ”, TRUE, FALSE, TRUE, ”, TempExcelBuffer.”Cell Type”::Text);
TempExcelBuffer.AddColumn(‘Sell-to Customer No.’, FALSE, ”, TRUE, FALSE, TRUE, ”, TempExcelBuffer.”Cell Type”::Text);
TempExcelBuffer.AddColumn(‘Bill-to Customer No.’, FALSE, ”, TRUE, FALSE, TRUE, ”, TempExcelBuffer.”Cell Type”::Text);
TempExcelBuffer.AddColumn(‘Ship-to Customer No.’, FALSE, ”, TRUE, FALSE, TRUE, ”, TempExcelBuffer.”Cell Type”::Text);
TempExcelBuffer.AddColumn(‘Posting Date’, FALSE, ”, TRUE, FALSE, TRUE, ”, TempExcelBuffer.”Cell Type”::Text);
TempExcelBuffer.AddColumn(‘Location Code’, FALSE, ”, TRUE, FALSE, TRUE, ”, TempExcelBuffer.”Cell Type”::Text);
TempExcelBuffer.AddColumn(‘Payment Terms’, FALSE, ”, TRUE, FALSE, TRUE, ”, TempExcelBuffer.”Cell Type”::Text);
TempExcelBuffer.AddColumn(‘Type(Option must be same as in Application)’, FALSE, ”, TRUE, FALSE, TRUE, ”,
TempExcelBuffer.”Cell Type”::Text);
TempExcelBuffer.AddColumn(‘No.’, FALSE, ”, TRUE, FALSE, TRUE, ”, TempExcelBuffer.”Cell Type”::Text);
TempExcelBuffer.AddColumn(‘Quantity’, FALSE, ”, TRUE, FALSE, TRUE, ”, TempExcelBuffer.”Cell Type”::Text);
TempExcelBuffer.AddColumn(‘Unit Price’, FALSE, ”, TRUE, FALSE, TRUE, ”, TempExcelBuffer.”Cell Type”::Text);
    end;
 
    Local procedure CreateExcelBook();
    begin
TempExcelBuffer.CreateNewBook(‘Sales Order/Invoice Creation’);
TempExcelBuffer.WriteSheet(‘Sales Order/Invoice Creation’, CompanyName, UserId);
TempExcelBuffer.CloseBook();
TempExcelBuffer.SetFriendlyFilename(‘Sales Order/Invoice Creation’);
TempExcelBuffer.OpenExcel();
    end;
 
    local procedure GetLastRowandColumn()
    var
myInt: Integer;
    begin
ExcelBuffer.SETRANGE(“Row No.”, 1);
TotalColumns := ExcelBuffer.COUNT;
 
ExcelBuffer.RESET;
        IF ExcelBuffer.FINDLAST THEN
TotalRows := ExcelBuffer.”Row No.”;
    end;
 
    local procedure GetValueAtCell(RowNo: Integer; ColNo: Integer): text
    var
myInt: Integer;
    begin
        IF ExcelBuffer.GET(RowNo, ColNo) THEN;
EXIT(ExcelBuffer.”Cell Value as Text”);
    end;
 
    local procedure InsertData(RowNo: Integer)
    var
DocumentNo: Code[20];
SellToCustomer: Code[20];
BillToCustomer: Code[20];
ShipToCustomer: Code[20];
PostingDate: Date;
LocationCode: Code[20];
PaymentTremsCode: Code[20];
SourceType: Text[20];
SourceNo: Code[20];
SourceQty: Decimal;
SourceUnitPrice: Decimal;
SalesHeader: Record “Sales Header”;
SalesLine: Record “Sales Line”;
TempDocumentNo: code[20];
LineNo: Integer;
    begin
 
        IF GetValueAtCell(RowNo, 1) <> ” THEN
DocumentNo := GetValueAtCell(RowNo, 1)
        ELSE
ERROR(‘Document No. must not be blank’);
 
        IF GetValueAtCell(RowNo, 2) <> ” THEN
SellToCustomer := GetValueAtCell(RowNo, 2)
        ELSE
ERROR(‘Sell To Customer must not be blank’);
        IF GetValueAtCell(RowNo, 3) <> ” THEN
BillToCustomer := GetValueAtCell(RowNo, 3);
        IF GetValueAtCell(RowNo, 4) <> ” THEN
ShipToCustomer := GetValueAtCell(RowNo, 4);
        IF GetValueAtCell(RowNo, 5) <> ” THEN
evaluate(PostingDate, GetValueAtCell(RowNo, 5))
        else
Error(‘Posting Date must not be blank.’);
        IF GetValueAtCell(RowNo, 6) <> ” THEN
LocationCode := GetValueAtCell(RowNo, 6)
        else
Error(‘Location code must not be blank.’);
        IF GetValueAtCell(RowNo, 7) <> ” THEN
PaymentTremsCode := GetValueAtCell(RowNo, 7);
        IF GetValueAtCell(RowNo, 8) <> ” THEN
SourceType := GetValueAtCell(RowNo, 8)
        else
Error(‘Source Type must not be blank.’);
        IF GetValueAtCell(RowNo, 9) <> ” THEN
SourceNo := GetValueAtCell(RowNo, 9)
        else
Error(‘Source No must not be blank.’);
        IF GetValueAtCell(RowNo, 10) <> ” THEN
evaluate(SourceQty, GetValueAtCell(RowNo, 10))
        else
Error(‘Source Quantity must not be blank.’);
        IF GetValueAtCell(RowNo, 11) <> ” THEN
Evaluate(SourceUnitPrice, GetValueAtCell(RowNo, 11))
        else
Error(‘Source Unit price must not be blank.’);
 
 
        if TempDocumentNo<>DocumentNo then begin
LineNo := 0;
SalesHeader.init;
SalesHeader.”Document Type” := SalesHeader.”Document Type”::Order;
SalesHeader.”No.” := DocumentNo;
SalesHeader.insert;
SalesHeader.validate(“Sell-to Customer No.”, SellToCustomer);
            if BillToCustomer<> ” then
SalesHeader.validate(“Bill-to Customer No.”, BillToCustomer);
            if ShipToCustomer<> ” then
SalesHeader.validate(“Ship-to Code”, ShipToCustomer);
SalesHeader.Validate(“Posting Date”, PostingDate);
SalesHeader.Validate(“Location Code”, LocationCode);
SalesHeader.Validate(“Payment Terms Code”, PaymentTremsCode);
SalesHeader.modify;
LineNo += 10000;
SalesLine.init;
SalesLine.”Document Type” := SalesLine.”Document Type”::Order;
SalesLine.”Document No.” := SalesHeader.”No.”;
SalesLine.”Line No.” := LineNo;
Evaluate(SalesLine.Type, SourceType);
SalesLine.validate(“No.”, SourceNo);
SalesLine.insert;
SalesLine.validate(Quantity, SourceQty);
SalesLine.validate(“Unit Price”, SourceUnitPrice);
SalesLine.Modify()
        end else begin
LineNo += 10000;
SalesLine.init;
SalesLine.”Document Type” := SalesLine.”Document Type”::Order;
SalesLine.”Document No.” := DocumentNo;
SalesLine.”Line No.” := LineNo;
Evaluate(SalesLine.Type, SourceType);
SalesLine.validate(“No.”, SourceNo);
SalesLine.insert;
SalesLine.validate(Quantity, SourceQty);
SalesLine.validate(“Unit Price”, SourceUnitPrice);
SalesLine.Modify();
        end;
TempDocumentNo := DocumentNo;
    end;
}

How to Check Installed and Uninstalled Extension in D365 BC
Usage of TESTFIELD in D365 BC
Usage of TRANSFERFIELDS in D365 BC
Record.TransferFields(var Record [, Boolean]) Method– Microsoft Docs

Leave a Reply