Hi, Readers.
Today I would like to talk about how to export data from tables to CSV (comma-separated values) file.
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.
In fact, this is similar to the method of exporting to a txt file. 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.
We briefly discussed how to Export and Import Data (Using XMLports) before, this time I made a slight modification.
For example, export Item List to CSV file.
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 "Export Items XMLport"
{
Caption = 'Export Items';
Format = VariableText;
Direction = Export;
TextEncoding = UTF8;
UseRequestPage = false;
FileName = 'ItemsViaXMLport.csv';
TableSeparator = '<NewLine>';
schema
{
textelement(Root)
{
tableelement(Integer; Integer)
{
XmlName = 'ItemHeader';
SourceTableView = SORTING(Number) WHERE(Number = CONST(1));
textelement(ItemNoTitle)
{
trigger OnBeforePassVariable()
begin
ItemNoTitle := Item.FieldCaption("No.");
end;
}
textelement(ItemDescTitle)
{
trigger OnBeforePassVariable()
begin
ItemDescTitle := Item.FieldCaption(Description);
end;
}
textelement(ItemTypeTitle)
{
trigger OnBeforePassVariable()
begin
ItemTypeTitle := Item.FieldCaption(Type);
end;
}
textelement(ItemInventoryTitle)
{
trigger OnBeforePassVariable()
begin
ItemInventoryTitle := Item.FieldCaption(Inventory);
end;
}
textelement(ItemBaseUnitofMeasureTitle)
{
trigger OnBeforePassVariable()
begin
ItemBaseUnitofMeasureTitle := Item.FieldCaption("Base Unit of Measure");
end;
}
textelement(ItemBaseCostisAdjustedTitle)
{
trigger OnBeforePassVariable()
begin
ItemBaseCostisAdjustedTitle := Item.FieldCaption("Cost is Adjusted");
end;
}
textelement(ItemUnitCostTitle)
{
trigger OnBeforePassVariable()
begin
ItemUnitCostTitle := Item.FieldCaption("Unit Cost");
end;
}
textelement(ItemUnitPriceTitle)
{
trigger OnBeforePassVariable()
begin
ItemUnitPriceTitle := Item.FieldCaption("Unit Price");
end;
}
textelement(ItemVendorNoTitle)
{
trigger OnBeforePassVariable()
begin
ItemVendorNoTitle := Item.FieldCaption("Vendor No.");
end;
}
}
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.")
{
}
}
}
}
}
pageextension 50100 ItemExt extends "Item List"
{
actions
{
addafter(History)
{
action(ExportItemsviaXMLport)
{
Caption = 'Export Items via XMLport';
Promoted = true;
PromotedCategory = Process;
Image = Export;
ApplicationArea = All;
trigger OnAction()
begin
Xmlport.Run(Xmlport::"Export Items XmlPort", false, false);
end;
}
}
}
}PS: Walkthrough: Exporting Data from Tables to CSV Files: This walkthrough shows you how to export data from a Dynamics NAV table to a CSV file.
Using TextBuilder Data Type
Using TextBuilder Data Type, you can export data to a txt file very quickly. More details: Using TextBuilder Data Type to export data to a text file. But this looks a bit like hard coding, so adding some setting pages is recommended if you’re going to use this way.
I also make a similar example, exporting Customer Ledger Entry to a CSV file.
Great.
Test video:
Source Code: Github (Please note that the source code is for reference only, you can improve it according to your own needs)
pageextension 50102 CustomerLedgerEntriesExt extends "Customer Ledger Entries"
{
actions
{
addfirst(processing)
{
action(ExportToExt)
{
Caption = 'Export Items via TextBuilder';
ApplicationArea = All;
PromotedCategory = Process;
Promoted = true;
Image = Export;
trigger OnAction()
var
TempBlob: Codeunit "Temp Blob";
InS: InStream;
OutS: OutStream;
FileName: Text;
TxtBuilder: TextBuilder;
CustLedgerEntry: Record "Cust. Ledger Entry";
begin
FileName := 'TestCSVFile_' + UserId + '_' + Format(CurrentDateTime) + '.csv';
TxtBuilder.AppendLine('Posting Date' + ',' + 'Document Type' + ',' + 'Document No.' + ',' + 'Customer No.' + ',' + 'Amount(LCY)');
CustLedgerEntry.Reset();
CustLedgerEntry.SetAutoCalcFields("Amount (LCY)");
if CustLedgerEntry.FindSet() then
repeat
TxtBuilder.AppendLine(AddDoubleQuotes(Format(CustLedgerEntry."Posting Date")) + ',' + AddDoubleQuotes(Format(CustLedgerEntry."Document Type")) + ',' +
AddDoubleQuotes(CustLedgerEntry."Document No.") + ',' + AddDoubleQuotes(CustLedgerEntry."Customer No.") + ',' +
AddDoubleQuotes(Format(CustLedgerEntry."Amount (LCY)")));
until CustLedgerEntry.Next() = 0;
TempBlob.CreateOutStream(OutS);
OutS.WriteText(TxtBuilder.ToText());
TempBlob.CreateInStream(InS);
DownloadFromStream(InS, '', '', '', FileName);
end;
}
}
}
local procedure AddDoubleQuotes(FieldValue: Text): Text
begin
exit('"' + FieldValue + '"');
end;
}END
Hope this will help.
Thanks.
ZHU
コメント