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.
![](https://yzhums.com/wp-content/uploads/2022/01/image-114-1024x518.png)
![](https://yzhums.com/wp-content/uploads/2022/01/image-115-1024x518.png)
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.
![](https://yzhums.com/wp-content/uploads/2022/01/image-116-1024x518.png)
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.
![](https://yzhums.com/wp-content/uploads/2022/01/image-117-1024x518.png)
Source code you can refer to:
page 348 “Import Item Pictures”:
![](https://yzhums.com/wp-content/uploads/2022/01/image-118.png)
table 31 “Item Picture Buffer”:
![](https://yzhums.com/wp-content/uploads/2022/01/image-119-695x1024.png)
Okay, next let me do a simple sample.
First add an Import button to the customer list.
![](https://yzhums.com/wp-content/uploads/2022/01/image-120.png)
![](https://yzhums.com/wp-content/uploads/2022/01/Snipaste_2022-01-18_16-46-48-1024x309.png)
Then add import logic: Like Import Item Pictures feature, simply name your files with names corresponding to your customer numbers.
![](https://yzhums.com/wp-content/uploads/2022/01/image-121-770x1024.png)
And when the customer does not exist, an error will be prompted.
![](https://yzhums.com/wp-content/uploads/2022/01/image-122-1024x518.png)
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
コメント