X++ job to export data using custom delimiter using commaIO in Ax2012
Views (10904)
Hi,
Following job when executed exports open sales order data(based on AOT query - SalesUpdate) from AX with pipe "|" as delimiter and also provides the record count along with time consumed for the process to complete.
static void CG_ExportData(Args _args)
{
SalesTable salesTable;
SalesLine salesLine;
InventDim inventDim;
InventLocation inventLocation;
Query salesTableQuery = new Query(queryStr(SalesUpdate));
QueryBuildDataSource qbdsSalesTable, qbdsSalesLine;
QueryBuildRange qbrSalesStatus, qbrSalesType;
QueryRun salesQueryRun;
CommaIo commaIO;
Dialog dialog = new Dialog();
DialogField dialogField;
int startTime, endTime, recCount;
container line;
FileIoPermission perm;
Filepath filename;
#File
dialog.caption("File Browser");
dialogField = dialog.addField(extendedTypeStr(FilenameSave), "Choose File", "Choose file to export");
dialog.filenameLookupFilter(['csv','*.csv']);
if(dialog.run())
{
filename = dialogField.value();
startTime = WinAPI::getTickCount();
recCount = 0;
perm = new FileIoPermission(filename, "W");
perm.assert();
commaIO = new CommaIo(filename, "W");
commaIO.outFieldDelimiter("|");
if( !fileName || commaIO.status() != IO_Status::Ok)
{
throw error("File Cannot be opened");
}
// Writes header to the file
commaIO.write("CompanyID", "SalesId", "Customer", "Type", "Location", "ItemId", "size", "Qty");
qbdsSalesTable = salesTableQuery.dataSourceTable(tableNum(SalesTable));
qbdsSalesLine = salesTableQuery.dataSourceTable(tableNum(SalesLine));
// Filters the sales orders that have order types as Sales orders
qbrSalesType = SysQuery::findOrCreateRange(qbdsSalesTable, fieldNum(SalesTable, SalesType));
qbrSalesType.value(queryValue(SalesType::Sales));
// Filters the sales orders that are in open status
qbrSalesStatus = SysQuery::findOrCreateRange(qbdsSalesTable, fieldNum(SalesTable, SalesStatus));
qbrSalesStatus.value(queryValue(SalesStatus::Backorder));
// Filters the sales order lines that are in open status
qbrSalesStatus = SysQuery::findOrCreateRange(qbdsSalesLine, fieldNum(SalesLine, SalesStatus));
qbrSalesStatus.value(queryValue(SalesStatus::Backorder));
salesQueryRun = new QueryRun(salesTableQuery);
while (salesQueryRun.next())
{
salesTable = salesQueryRun.get(tableNum(SalesTable));
salesLine = salesQueryRun.get(tableNum(SalesLine));
inventDim = salesQueryRun.get(tableNum(InventDim));
line = [salesTable.dataAreaId, salesTable.SalesId, salesTable.CustAccount, enum2str(salesTable.SalesType), inventDim.InventLocationId,
salesLine.ItemId, inventDim.InventSizeId, salesLine.SalesQty];
commaIO.write(line);
recCount++;
}
}
else
{
return;
}
CodeAccessPermission::revertAssert();
endTime = WinAPI::getTickCount();
info(strFmt("Executed for %1 minutes and exported %2 sales order lines", ((endTime - startTime)/1000)/60, recCount));
}
INPUT:
OUTPUT:
Regards,
Chaitanya Golla

Like
Report
*This post is locked for comments