Share
ReportI am importing. Zip File into business central Saas which Contain.xlsx files, while importing I am getting this error.
You’re offline. This is a read only version of the page.
10
I am importing. Zip File into business central Saas which Contain.xlsx files, while importing I am getting this error.
Hi, I tried again in my test solution and it seems to be fine.
You can refer the code from my post. Hope to give you some hints.
[View:/cfs-file/__key/communityserver-discussions-components-files/758/UploadZipfileWithExcelFiles.mp4:1066:800
All responses (
Answers (
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 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;
}
Hope this helps.
Thanks.
ZHU
Hi, I just made an example of exporting to a zip file for another test, hope it helps.
Thanks.
ZHU
yes, it's possible to import multiple files. if you check import Picture Functionality, you will get to know we can import a .zip file.
the client has requested to import multiple .xlsx files in one import how can we achieve that?
Hi Uday Ahir ,
I am not sure if you can import Zip file and then BC will automatically unzip it and extract the Xlsx file. You have to import Excel file directly not through Zip file.
Thanks.
#1
André Arnaud de Cal...
294,459
Super User 2025 Season 1
#2
Martin Dráb
233,066
Most Valuable Professional
#3
nmaenpaa
101,158
Moderator
