Hi, Readers.
Today I’d like to talk about a question I’ve been asked recently, how to bulk import big/large text (length exceeds 250 or 2048) from external files via AL.
We have previously discussed several methods for importing data from external files, such as
More details: Dynamics 365 Business Central Blog Series: Import and Export data
But so far, there is a problem when using Excel Buffer or CSV Buffer, that is, the length of the imported field cannot exceed 250. For example,
table 370 “Excel Buffer”:
However, the text length supported by the system is 2048, not 250.
The maximum length for a field of type ‘Text’ is 2048 AL AL0154
And, for example, the Note or Work Description field on the Sales Header is Blob Data type, which can store longer text.
If you use Excel Buffer or CSV Buffer to import, the part exceeding 250 will be automatically deleted. If the text is relatively short, this is not a problem. But if I want to import a very long text, is there any way? Yes, here I introduce a simple way, using XMLport object.
PS: If it is not a system table, starting from Business Central 2024 wave 1 (BC24), you can also import Blob Type fields through Configuration Package, but the standard code cannot be referenced because many DotNet plug-ins are used.
Business Central 2024 wave 1 (BC24): Export, import multiline text with Configuration Packages (Use BLOB type fields in Configuration Packages)
As an example this time, I added a Blob field to the Customer table and displayed it on the Customer Card as Rich Text. More details: Business Central 2023 wave 2 (BC23): Use the built-in rich text editor to enter data
For example,
The import template this time is as follows, a CSV file (XML and TXT files are also available). Since the Customer table has only one primary key, I only added two columns, , key (Customer No.) and value (Rich Text). You can see that the length far exceeds the allowed range.
In customer 10000, I will import a base64 text. This is very long.
Next let’s look at the import logic.
Create an XMLPort for import.
Set up a temporary tableelement. Because I don’t need any fields in the table and don’t actually insert into this table, I use table 2000000026 Integer. Then add the imported fields as textelement. There are only two fields in my template, so there are also two here.
Please note that the textelement with long text must set TextType Property to BigText (To display large text).
If you don’t have it set, you will not be able to import text longer than 2048 characters, which will result in the following error. Of course, if your length is 250~2048, there is no problem.
Then comes the import logic. First, if there is a header in your template, skip the header.
Then insert the table you want to insert before actually inserting the record (trigger OnBeforeInsertRecord). Don’t forget to add currXMLport.Skip() at the end, because you don’t actually insert into the tableelement’s table. In addition, this is the logic for the Blob field. If it is Text, you can just assign the value directly.
Finally, add an action on the Item List page to execute the XMLPort for this import.
Let’s do a quick test.
Done.
Test video:
Very simple, give it a try!!!😁
PS: Dynamics 365 Business Central: How to bulk import marketing text to items – Customization
Source code: Github (Please note that the source code is for reference only, you can improve it according to your own needs)
tableextension 50112 CustomerExt extends Customer
{
fields
{
field(50100; RichText; Blob)
{
Caption = 'Rich Text';
DataClassification = CustomerContent;
}
}
}
pageextension 50101 CustomerCardExt extends "Customer Card"
{
layout
{
addafter(General)
{
group(RichTextGroup)
{
Caption = 'Rich Text Group';
field(RichText; RichTextVar)
{
Caption = 'Rich Text';
MultiLine = true;
ExtendedDatatype = RichContent;
ApplicationArea = All;
ShowCaption = false;
trigger OnValidate()
begin
SetRichText();
end;
}
}
}
}
var
RichTextVar: Text;
trigger OnAfterGetRecord()
begin
GetRichText();
end;
local procedure GetRichText()
var
RichTextInS: InStream;
begin
Rec.CalcFields(RichText);
Rec.RichText.CreateInStream(RichTextInS, TextEncoding::UTF8);
RichTextInS.Read(RichTextVar);
end;
local procedure SetRichText()
var
RichTextOutS: OutStream;
begin
Rec.RichText.CreateOutStream(RichTextOutS, TextEncoding::UTF8);
RichTextOutS.Write(RichTextVar);
Rec.Modify(true);
end;
}
pageextension 50118 CustListExt extends "Customer List"
{
actions
{
addafter(Email)
{
action(ImportRichText)
{
ApplicationArea = All;
Caption = 'Import Rich Text';
Promoted = true;
PromotedCategory = Process;
Image = Import;
ToolTip = 'Import Rich text from a CSV file.';
trigger OnAction()
begin
Xmlport.Run(Xmlport::"Import Customer Rich Text", false, true);
end;
}
}
}
}
xmlport 50101 "Import Customer Rich Text"
{
Caption = 'Import Customer Rich Text';
Format = VariableText;
Direction = Import;
TextEncoding = UTF8;
UseRequestPage = false;
FileName = 'CustomerRichText.csv';
TableSeparator = '<NewLine>';
schema
{
textelement(Root)
{
tableelement(Integer; Integer)
{
XmlName = 'RichText';
textelement(CustomerNo)
{ }
textelement(RichText)
{
TextType = BigText;
}
trigger OnAfterInitRecord()
begin
if IsFirstline then begin
IsFirstline := false;
currXMLport.Skip();
end;
end;
trigger OnBeforeInsertRecord()
var
Cust: Record Customer;
InStream: InStream;
EntityText: Record "Entity Text";
OutStream: OutStream;
begin
if Cust.Get(CustomerNo) then begin
Clear(Cust.RichText);
Cust.RichText.CreateOutStream(OutStream, TextEncoding::UTF8);
RichText.Write(OutStream);
Cust.Modify();
i += 1;
end;
currXMLport.Skip();
end;
}
}
}
var
IsFirstline: Boolean;
i: Integer;
Msg: Label 'Imported Rich text for %1 customer(s).';
Test: BigText;
trigger OnPreXmlPort()
begin
i := 0;
IsFirstline := true;
end;
trigger OnPostXmlPort()
begin
Message(Msg, i);
end;
}END
Hope this will help.
Thanks for reading.
ZHU
コメント