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
コメント