Dynamics 365 Business Central: How to import files from a zip file (Bulk import attachements and Excel files)

Dynamics 365 Business Central

Hi, Readers.
A few days ago, I was asked a intersting question, is there any way to bulk import attachments for master data in Business Central. For example, for customer master.

Yes, it’s possible, but we can’t do it with standard feature, such as Configuration Package. We need to do a small customization.

Last year we briefly discussed how to add multiple files into a zip file and download it. More details: https://yzhums.com/20030/
Today, I would like to talk about how to import files from a zip file. Both of these features are valid for SaaS.

In fact, there is a very good standard feature for reference, Import Item Pictures. You can find out more about Import Multiple Item Pictures from MS Docs.

You can import multiple item pictures in one go. Simply name your picture files with names corresponding to your item numbers, compress them to a zip file, and then use the Import Item Pictures page to manage which item pictures to import.

Source code you can refer to:

page 348 “Import Item Pictures”:

table 31 “Item Picture Buffer”:

Okay, next let me do a simple sample.

First add an Import button to the customer list.

Then add import logic: Like Import Item Pictures feature, simply name your files with names corresponding to your customer numbers.

And when the customer does not exist, an error will be prompted.

Test Video:

Source Code: GitHub: ImportZipFile

pageextension 80100 CustomerListExt extends "Customer List"
{
    actions
    {
        addafter("Sent Emails")
        {
            action(ImportZipFile)
            {
                Caption = 'Import Zip File';
                ApplicationArea = All;
                Promoted = true;
                PromotedCategory = Process;
                PromotedIsBig = true;
                Image = Import;
                ToolTip = 'Import Attachments from Zip';

                trigger OnAction()
                begin
                    ImportAttachmentsFromZip();
                end;
            }
        }
    }

    local procedure ImportAttachmentsFromZip()
    var
        FileMgt: Codeunit "File Management";
        DataCompression: Codeunit "Data Compression";
        TempBlob: Codeunit "Temp Blob";
        EntryList: List of [Text];
        EntryListKey: Text;
        ZipFileName: Text;
        FileName: Text;
        FileExtension: Text;
        InStream: InStream;
        EntryOutStream: OutStream;
        EntryInStream: InStream;
        Length: Integer;
        SelectZIPFileMsg: Label 'Select ZIP File';
        FileCount: Integer;
        Cust: Record Customer;
        DocAttach: Record "Document Attachment";
        NoCustError: Label 'Customer %1 does not exist.';
        ImportedMsg: Label '%1 attachments Imported successfully.';
    begin
        //Upload zip file
        if not UploadIntoStream(SelectZIPFileMsg, '', 'Zip Files|*.zip', ZipFileName, InStream) then
            Error('');

        //Extract zip file and store files to list type
        DataCompression.OpenZipArchive(InStream, false);
        DataCompression.GetEntryList(EntryList);

        FileCount := 0;

        //Loop files from the list type
        foreach EntryListKey in EntryList do begin
            FileName := CopyStr(FileMgt.GetFileNameWithoutExtension(EntryListKey), 1, MaxStrLen(FileName));
            FileExtension := CopyStr(FileMgt.GetExtension(EntryListKey), 1, MaxStrLen(FileExtension));
            TempBlob.CreateOutStream(EntryOutStream);
            DataCompression.ExtractEntry(EntryListKey, EntryOutStream, Length);
            TempBlob.CreateInStream(EntryInStream);

            //Import each file where you want
            if not Cust.Get(FileName) then
                Error(NoCustError, FileName);
            DocAttach.Init();
            DocAttach.Validate("Table ID", Database::Customer);
            DocAttach.Validate("No.", FileName);
            DocAttach.Validate("File Name", FileName);
            DocAttach.Validate("File Extension", FileExtension);
            DocAttach."Document Reference ID".ImportStream(EntryInStream, FileName);
            DocAttach.Insert(true);
            FileCount += 1;
        end;

        //Close the zip file
        DataCompression.CloseZipArchive();

        if FileCount > 0 then
            Message(ImportedMsg, FileCount);
    end;
}

Update a solution for multiple excel files in zip:

Test Video:

Source Code:

Table:

table 50101 "SO Import Buffer"
{
    Caption = 'SO Import Buffer';
    fields
    {
        field(1; "Batch Name"; Code[10])
        {
            Caption = 'Batch Name';
            DataClassification = CustomerContent;
        }
        field(2; "Line No."; Integer)
        {
            Caption = 'Line No.';
            Editable = false;
            DataClassification = CustomerContent;
        }
        field(3; "File Name"; Text[50])
        {
            Caption = 'File Name';
            Editable = false;
            DataClassification = CustomerContent;
        }
        field(4; "Sheet Name"; Text[30])
        {
            Caption = 'Sheet Name';
            DataClassification = CustomerContent;
        }
        field(5; "Imported Date"; Date)
        {
            Caption = 'Imported Date';
            Editable = false;
            DataClassification = CustomerContent;
        }
        field(6; "Imported Time"; Time)
        {
            Caption = 'Imported Time';
            Editable = false;
            DataClassification = CustomerContent;
        }
        field(7; "Sell-to Customer No."; Code[20])
        {
            Caption = 'Sell-to Customer No.';
            TableRelation = Customer;
            DataClassification = CustomerContent;
        }
        field(8; "Document No."; Code[20])
        {
            Caption = 'Document No.';
            DataClassification = CustomerContent;
        }
        field(9; "Posting Date"; Date)
        {
            Caption = 'Posting Date';
            DataClassification = CustomerContent;
        }
        field(10; "Currency Code"; Code[10])
        {
            Caption = 'Currency Code';
            TableRelation = Currency;
            DataClassification = CustomerContent;
        }
        field(11; "Document Date"; Date)
        {
            Caption = 'Document Date';
            DataClassification = CustomerContent;
        }
        field(12; "External Document No."; Code[35])
        {
            Caption = 'External Document No.';
            DataClassification = CustomerContent;
        }
        field(13; Type; Enum "Sales Line Type")
        {
            Caption = 'Type';
            DataClassification = CustomerContent;
        }
        field(14; "No."; Code[20])
        {
            Caption = 'No.';
            DataClassification = CustomerContent;
        }
        field(15; Quantity; Decimal)
        {
            Caption = 'Quantity';
            DecimalPlaces = 0 : 5;
            DataClassification = CustomerContent;
        }
        field(16; "Unit Price"; Decimal)
        {
            Caption = 'Unit Price';
            AutoFormatType = 2;
            AutoFormatExpression = "Currency Code";
            DataClassification = CustomerContent;
        }
    }
    keys
    {
        key(Key1; "Batch Name", "Line No.")
        {
        }
    }
}

Page:

page 50102 "SO Import Worksheet"
{
    AutoSplitKey = true;
    Caption = 'SO Import Worksheet';
    DelayedInsert = true;
    InsertAllowed = false;
    ModifyAllowed = false;
    PageType = Worksheet;
    SaveValues = true;
    SourceTable = "SO Import Buffer";
    SourceTableView = sorting("Batch Name", "Line No.");
    UsageCategory = Tasks;
    ApplicationArea = All;
    layout
    {
        area(content)
        {
            field(BatchName; BatchName)
            {
                Caption = 'Batch Name';
                ApplicationArea = All;
            }
            repeater(Group)
            {
                Editable = false;
                field("Batch Name"; Rec."Batch Name")
                {
                    Visible = false;
                    ApplicationArea = All;
                }
                field("Line No."; Rec."Line No.")
                {
                    Visible = false;
                    ApplicationArea = All;
                }
                field("Document No."; Rec."Document No.")
                {
                    ApplicationArea = All;
                }
                field("Sell-to Customer No."; Rec."Sell-to Customer No.")
                {
                    ApplicationArea = All;
                }
                field("Posting Date"; Rec."Posting Date")
                {
                    ApplicationArea = All;
                }
                field("Currency Code"; Rec."Currency Code")
                {
                    ApplicationArea = All;
                }
                field("Document Date"; Rec."Document Date")
                {
                    ApplicationArea = All;
                }
                field("External Document No."; Rec."External Document No.")
                {
                    ApplicationArea = All;
                }
                field(Type; Rec.Type)
                {
                    ApplicationArea = All;
                }
                field("No."; Rec."No.")
                {
                    ApplicationArea = All;
                }
                field(Quantity; Rec.Quantity)
                {
                    ApplicationArea = All;
                }
                field("Unit Price"; Rec."Unit Price")
                {
                    ApplicationArea = All;
                }
                field("File Name"; Rec."File Name")
                {
                    ApplicationArea = All;
                }
                field("Sheet Name"; Rec."Sheet Name")
                {
                    ApplicationArea = All;
                }
                field("Imported Date"; Rec."Imported Date")
                {
                    ApplicationArea = All;
                }
                field("Imported Time"; Rec."Imported Time")
                {
                    ApplicationArea = All;
                }
            }
        }
    }
    actions
    {
        area(processing)
        {
            action("&Import")
            {
                Caption = 'Import Excel';
                Image = ImportExcel;
                Promoted = true;
                PromotedCategory = Process;
                ApplicationArea = All;
                ToolTip = 'Import data from excel.';
                trigger OnAction()
                begin
                    if BatchName = '' then
                        Error(BatchISBlankMsg);
                    ImportFromZip := false;
                    ReadExcelSheet();
                    ImportExcelData();
                end;
            }
            action(ImportZipFile)
            {
                Caption = 'Import Zip File';
                ApplicationArea = All;
                Promoted = true;
                PromotedCategory = Process;
                PromotedIsBig = true;
                Image = Import;
                ToolTip = 'Import Attachments from Zip';

                trigger OnAction()
                var
                    FileMgt: Codeunit "File Management";
                    DataCompression: Codeunit "Data Compression";
                    TempBlob: Codeunit "Temp Blob";
                    EntryList: List of [Text];
                    EntryListKey: Text;
                    ZipFileName: Text;
                    FileName: Text;
                    FileExtension: Text;
                    InStream: InStream;
                    EntryOutStream: OutStream;
                    EntryInStream: InStream;
                    Length: Integer;
                    SelectZIPFileMsg: Label 'Select ZIP File';
                    FileCount: Integer;
                    ImportedMsg: Label '%1 excel Imported successfully.';
                    FromFile: Text[100];
                begin
                    if BatchName = '' then
                        Error(BatchISBlankMsg);

                    if not UploadIntoStream(SelectZIPFileMsg, '', 'Zip Files|*.zip', ZipFileName, InStream) then
                        Error('');

                    ImportFromZip := true;

                    //Extract zip file and store files to list type
                    DataCompression.OpenZipArchive(InStream, false);
                    DataCompression.GetEntryList(EntryList);

                    FileCount := 0;

                    //Loop files from the list type
                    foreach EntryListKey in EntryList do begin
                        FileName := CopyStr(FileMgt.GetFileNameWithoutExtension(EntryListKey), 1, MaxStrLen(FileName));
                        FileExtension := CopyStr(FileMgt.GetExtension(EntryListKey), 1, MaxStrLen(FileExtension));
                        TempBlob.CreateOutStream(EntryOutStream);
                        DataCompression.ExtractEntry(EntryListKey, EntryOutStream, Length);
                        TempBlob.CreateInStream(EntryInStream);

                        SheetName := TempExcelBuffer.SelectSheetsNameStream(EntryInStream);
                        TempExcelBuffer.Reset();
                        TempExcelBuffer.DeleteAll();
                        TempExcelBuffer.OpenBookStream(EntryInStream, SheetName);
                        TempExcelBuffer.ReadSheet();
                        ImportExcelData();

                        FileCount += 1;
                    end;

                    //Close the zip file
                    DataCompression.CloseZipArchive();

                    if FileCount > 0 then
                        Message(ImportedMsg, FileCount);
                end;
            }
        }
    }
    var
        BatchName: Code[10];
        FileName: Text[100];
        SheetName: Text[100];
        TempExcelBuffer: Record "Excel Buffer" temporary;
        UploadExcelMsg: Label 'Please Choose the Excel file.';
        NoFileFoundMsg: Label 'No Excel file found!';
        BatchISBlankMsg: Label 'Batch name is blank';
        ExcelImportSucess: Label 'Excel is successfully imported.';
        ImportFromZip: Boolean;

    trigger OnOpenPage()
    begin
        BatchName := 'Test';
    end;

    local procedure ReadExcelSheet()
    var
        FileMgt: Codeunit "File Management";
        IStream: InStream;
        FromFile: Text[100];
    begin
        UploadIntoStream(UploadExcelMsg, '', '', FromFile, IStream);
        if FromFile <> '' then begin
            FileName := FileMgt.GetFileName(FromFile);
            SheetName := TempExcelBuffer.SelectSheetsNameStream(IStream);
        end else
            Error(NoFileFoundMsg);
        TempExcelBuffer.Reset();
        TempExcelBuffer.DeleteAll();
        TempExcelBuffer.OpenBookStream(IStream, SheetName);
        TempExcelBuffer.ReadSheet();
    end;

    local procedure ImportExcelData()
    var
        SOImportBuffer: Record "SO Import Buffer";
        RowNo: Integer;
        ColNo: Integer;
        LineNo: Integer;
        MaxRowNo: Integer;
    begin
        RowNo := 0;
        ColNo := 0;
        MaxRowNo := 0;
        LineNo := 0;
        SOImportBuffer.Reset();
        if SOImportBuffer.FindLast() then
            LineNo := SOImportBuffer."Line No.";
        TempExcelBuffer.Reset();
        if TempExcelBuffer.FindLast() then begin
            MaxRowNo := TempExcelBuffer."Row No.";
        end;
        for RowNo := 2 to MaxRowNo do begin
            LineNo := LineNo + 10000;
            SOImportBuffer.Init();
            Evaluate(SOImportBuffer."Batch Name", BatchName);
            SOImportBuffer."Line No." := LineNo;
            Evaluate(SOImportBuffer."Document No.", GetValueAtCell(RowNo, 1));
            Evaluate(SOImportBuffer."Sell-to Customer No.", GetValueAtCell(RowNo, 2));
            Evaluate(SOImportBuffer."Posting Date", GetValueAtCell(RowNo, 3));
            Evaluate(SOImportBuffer."Currency Code", GetValueAtCell(RowNo, 4));
            Evaluate(SOImportBuffer."Document Date", GetValueAtCell(RowNo, 5));
            Evaluate(SOImportBuffer."External Document No.", GetValueAtCell(RowNo, 6));
            Evaluate(SOImportBuffer.Type, GetValueAtCell(RowNo, 7));
            Evaluate(SOImportBuffer."No.", GetValueAtCell(RowNo, 8));
            Evaluate(SOImportBuffer.Quantity, GetValueAtCell(RowNo, 9));
            Evaluate(SOImportBuffer."Unit Price", GetValueAtCell(RowNo, 10));
            SOImportBuffer."Sheet Name" := SheetName;
            SOImportBuffer."File Name" := FileName;
            SOImportBuffer."Imported Date" := Today;
            SOImportBuffer."Imported Time" := Time;
            SOImportBuffer.Insert();
        end;
        if not ImportFromZip then
            Message(ExcelImportSucess);
    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;
}

END

Hope this will help.

Thanks for reading.

ZHU

コメント

タイトルとURLをコピーしました