Hi, Readers.
Last week we discussed How to export data from tables to CSV (comma-separated values) file, today I would like to further talk about how to import data from CSV (comma-separated values) file.
PS: A CSV (comma-separated values) file is a text file that has a specific format which allows data to be saved in a table structured format. Anyone familiar with spreadsheet programs has very likely encountered CSV files before, they’re easily consumed by Microsoft Excel, and countless other applications.
Generally, there are two ways.
Using XMLport
XMLports are used to export and import data between an external source and Dynamics 365 Business Central. In XMLport, we can set the FileName Property.
FileName Property: Sets the name of the external file to read data from or write data to an XmlPort.
Syntax: FileName = ‘File.txt’;
The FileName property must be set to a valid file name or a run-time error occurs.
For example, we can specify the file name as CSV format.
Then set Direction property to Import or Both.
We briefly discussed how to Export and Import Data (Using XMLports) before, this time I made a slight modification.
For example, import items from a CSV file.
CSV template:
CSV data in VS Code:
Add fields that need to be imported in XMLport.
Because there is a header in the CSV template, we need a little trick to skip it.
It looks good.
Test Video:
Source Code: Github (Please note that the source code is for reference only, you can improve it according to your own needs)
xmlport 50100 "Import Items XMLport"
{
Caption = 'Import Items';
Format = VariableText;
Direction = Import;
TextEncoding = UTF8;
UseRequestPage = false;
FileName = 'ItemsViaXMLport.csv';
TableSeparator = '<NewLine>';
schema
{
textelement(Root)
{
tableelement(Item; Item)
{
XmlName = 'Item';
RequestFilterFields = "No.";
fieldelement(No; Item."No.")
{
}
fieldelement(Description; Item.Description)
{
}
fieldelement(Type; Item.Type)
{
}
fieldelement(Inventory; Item.Inventory)
{
}
fieldelement(BaseUnitofMeasure; Item."Base Unit of Measure")
{
}
fieldelement(CostisAdjusted; Item."Cost is Adjusted")
{
}
fieldelement(UnitCost; Item."Unit Cost")
{
}
fieldelement(UnitPrice; Item."Unit Price")
{
}
fieldelement(VendorNo; Item."Vendor No.")
{
}
trigger OnAfterInitRecord()
begin
if IsFirstline then begin
IsFirstline := false;
currXMLport.Skip();
end;
end;
}
}
}
trigger OnPreXmlPort()
begin
IsFirstline := true;
end;
var
IsFirstline: Boolean;
}
pageextension 50100 ItemExt extends "Item List"
{
actions
{
addafter(History)
{
action(ImportItemsviaXMLport)
{
Caption = 'Import Items via XMLport';
Promoted = true;
PromotedCategory = Process;
Image = Import;
ApplicationArea = All;
trigger OnAction()
begin
Xmlport.Run(Xmlport::"Import Items XmlPort", false, true);
end;
}
}
}
}Using CSV Buffer
The second common method is to use CSV Buffer, which is somewhat similar to Excel Buffer.
Table CSV Buffer: Table to store CSV (comma-separated values).
Methods in the table CSV Buffer: Please note that the scope of some of these methods is OnPrem and cannot be used in SaaS
The key method this time is procedure LoadDataFromStream.
For easy understanding, let me give an example.
I created a new page to display the data from the CSV Buffer table. (Not required)
Then created an action to import the CSV file.
CSV template is the same as above.
After importing, it will be saved in the Business Central in the following format. Line No. indicates the number of lines in the CSV template, Field No. indicates the number of fields in the CSV, and Value indicates the field value in the CSV.
PS:
1. If the imported field value contains symbols such as double quotes, you can use the following method to remove them.
Text.TrimEnd([Text]) Method: Removes all trailing occurrences of a set of characters specified in an array from the current Text object.
Text.TrimStart([Text]) Method: Removes all leading occurrences of a set of characters specified in an array from the current Text object.
2. Use procedure GetNumberOfLines() to get the number of all Lines
Well, all the preparations are over here, let’s get started.
First of all, we can create a function to return a value according to the row number and column number. For convenience, I only created one for Text data type. It is recommended to create different functions according to the type.
Then add an action on the page to import the value, because the first line in my CSV template is the header caption, so we start importing from the second line.
Test Video: Actually looks the same as above….😑
Give it a try!!!😁
Source Code: Github (Please note that the source code is for reference only, you can improve it according to your own needs)
pageextension 50100 ItemExt extends "Item List"
{
actions
{
addafter(History)
{
action(ImportItemsviaCSVBuffer)
{
Caption = 'Import Items via CSV Buffer';
Promoted = true;
PromotedCategory = Process;
Image = Import;
ApplicationArea = All;
trigger OnAction()
var
InS: InStream;
FileName: Text[100];
UploadMsg: Label 'Please choose the CSV file';
Item: Record Item;
LineNo: Integer;
begin
CSVBuffer.Reset();
CSVBuffer.DeleteAll();
if UploadIntoStream(UploadMsg, '', '', FileName, InS) then begin
CSVBuffer.LoadDataFromStream(InS, ',');
for LineNo := 2 to CSVBuffer.GetNumberOfLines() do begin
Item.Init();
Item.Validate("No.", GetValueAtCell(LineNo, 1));
Item.Insert(true);
Item.Validate(Description, GetValueAtCell(LineNo, 2));
case GetValueAtCell(LineNo, 3) of
'Inventory':
Item.Validate(Type, Item.Type::"Inventory");
'Service':
Item.Validate(Type, Item.Type::"Service");
'Non-Inventory':
Item.Validate(Type, Item.Type::"Non-Inventory");
end;
Item.Validate("Base Unit of Measure", GetValueAtCell(LineNo, 5));
Evaluate(Item."Unit Cost", GetValueAtCell(LineNo, 7));
Evaluate(Item."Unit Price", GetValueAtCell(LineNo, 8));
Item.Validate("Vendor No.", GetValueAtCell(LineNo, 9));
Item.Modify(true);
end;
end;
end;
}
}
}
var
CSVBuffer: Record "CSV Buffer" temporary;
local procedure GetValueAtCell(RowNo: Integer; ColNo: Integer): Text
begin
if CSVBuffer.Get(RowNo, ColNo) then
exit(CSVBuffer.Value)
else
exit('');
end;
}END
Hope this will help.
Thanks.
ZHU
コメント