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.
(5) Develop a new Report, as shown.
Request page: 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.
MakeHeader: For 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.
GetValueAtCell: Read data form Excel Cell.
InsertData: Insert Data into Sales Header and Sales Line table according to the data in excel.
(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; } |
