Exported field names(headers) are blank in CSV

[Udhayakumar]

Hi All,

I am facing another issue on exported CSV by using both classes(approaches) CommoTextIo or AsciiO. Exports works properly and correct data exporting but some of the field name(header) exported as blank. If i use condition dictfield.isSql() in my code; then those blank header fields not exporting but i should be.

For example, i have exported data from PurchLine table using above mentioned code. Exports works but fields(PDSCWQTY, PDACWREMAINNVENTFINANCIAL, PDACWREMAINNVENTPHYSICAL) names(header) are exported as blank values.

I have tried the same in our local AX environment with same code, here field names are exporting properly. I have no idea what causing this issue. Can you suggest me any of approches to overcome this issue?

Please find highlighted screenshot and code which i used to export the data

Code starts here ---------------------------------------------------------------------------------------------------------->

[SysEntryPointAttribute(false)]
public void processOperation(exportCSV _contract)
{
#File
Common tableBuffer;
DictField dictField;
DictTable dictTable;
FieldId fieldId;
FileName fileName;
TableName tableName;
date resultDate;
utcDateTime dateTimeResult;
boolean recordsExist;
int i,j,countTable,executedTable;
container line,line2;
boolean recordExist= false;
FileIOPermission permission;
Days numberDays;
MAX_TablesToExportCSV tablesToExportCSV, tablesToExportCSV2;
str nameOfTable,pathToStore;
MAX_CSVExportPath documentPath;
AsciiIo asci;
str replaceUTC;
int check;

CommoTextIo commoTextIo;
;

countTable = 0;
executedTable = 0;
check = 0;
numberDays = _contract.parmNoofDays();
resultDate = today()-numberDays;
dateTimeResult= DateTimeUtil::newDateTime(resultDate, 0);

select firstOnly documentPath;
if(documentPath)
{
pathToStore = documentPath.CSVFilePath;
}
else
{
throw error (‘Set up file path to store file’);
}

select firstOnly tablesToExportCSV2;
if(!tablesToExportCSV2)
throw error(‘Add one or more tables in the master’);

while select tablesToExportCSV order by tablesToExportCSV.ListOfTables asc where tablesToExportCSV.Export == NoYes::Yes
{
nameOfTable = tablesToExportCSV.ListOfTables;
tableName = nameOfTable;

dictTable = new dictTable(tableName2Id(tableName));
fieldId = dictTable.fieldNext(0);
tableBuffer = dictTable.makeRecord();

select crossCompany tableBuffer where (tableBuffer.createdDateTime >= dateTimeResult) || (tableBuffer.modifiedDateTime >= dateTimeResult) ;

fileName = pathToStore + ‘\’ + tableName + ‘.csv’;
permission = new FileIOPermission(fileName, #io_write);
permission.assert();
commoTextIo= new CommoTextIo(fileName ,#io_write);
commoTextIo.outFieldDelimiter(’|’);
for( i =1; i <= dictTable.fieldCnt(); i++)
{
dictField = new DictField(dictTable.id(), dictTable.fieldCnt2Id(i));

line += [ dictField.name(i) ];
}

commoTextIo.write(line);
line = ConNull();
while select crossCompany tableBuffer where (tableBuffer.createdDateTime >= dateTimeResult) || (tableBuffer.modifiedDateTime >= dateTimeResult)
{
for( j =1; j <= dictTable.fieldCnt(); j++)
{
dictField = new DictField(dictTable.id(), dictTable.fieldCnt2Id(j));

if(dictField.name(j) == ‘CREATEDDATETIME’ || dictField.name(j) == ‘MODIFIEDDATETIME’ )
{
replaceUTC = DateTimeUtil::toStr(tableBuffer.(dictField.id()));
if(replaceUTC && replaceUTC != ‘’ )
{
replaceUTC = strReplace(replaceUTC, ‘T’, ’ ‘);
replaceUTC = replaceUTC + ‘.270’;
}
line2 += [ replaceUTC ];
replaceUTC = ‘’;
}
else
{
line2 += [ tableBuffer.(dictField.id()) ];
}
}
commoTextIo.write(line2);
line2 = ConNull();
recordsExist=true;
}
countTable++;
executedTable++;
CodeAccessPermission::revertAssert();
info(strFmt(‘Exported %1’,fileName));
}
}
info(strFmt(’%1 tables exported out of %2’, executedTable, countTable));
}

[MartinDrab]

Please remove everything from your code that isn’t related to thuis particular problem and post it again through Insert > Insert Code. That should make it much easier to read. Also, maybe you can demonstrate it on just one of a few fields.

Then give us a bigger picture; I can’t read anything from the current one.

[Udhayakumar]

Hi Martin,

Thanks for the immediate response.

Please find below highlighted code which used to insert headers of CSV file. Also see the output data, some of the headers were exported without header. This is the issue. Kindly check.

[MartinDrab]

Find out what the fields are and share this information with us. Then use the debugger to find out what return value you’ll get from name(i) for these fields. If you get empty strings, you’ll know that the problem has nothing to do with CSV, IO classs or anything like that. You’ll be able to reproduce the problem in a job with just two lines (or so). This will make your debugging and testing much easier.

If you find that name(i) does return correct values, you’ll at least know that the problem isn’t in your highlighted code and you must continue your debugging.

[Udhayakumar]

[SysEntryPointAttribute(false)]
public void processOperation(exportCSV _contract)
{
#File
Common tableBuffer;
DictField dictField;
DictTable dictTable;
FieldId fieldId;
FileName fileName;
TableName tableName;
date resultDate;
utcDateTime dateTimeResult;
boolean recordsExist;
int i,j,countTable,executedTable;
container line,line2;
boolean recordExist= false;
FileIOPermission permission;
Days numberDays;
MAX_TablesToExportCSV tablesToExportCSV, tablesToExportCSV2;
str nameOfTable,pathToStore;
MAX_CSVExportPath documentPath;
AsciiIo asci;
str replaceUTC;
int check;

CommoTextIo commoTextIo;
;

countTable = 0;
executedTable = 0;
check = 0;
numberDays = _contract.parmNoofDays();
resultDate = today()-numberDays;
dateTimeResult= DateTimeUtil::newDateTime(resultDate, 0);

select firstOnly documentPath;
if(documentPath)
{
pathToStore = documentPath.CSVFilePath;
}
else
{
throw error ('Set up file path to store file');
}

select firstOnly tablesToExportCSV2;
if(!tablesToExportCSV2)
throw error('Add one or more tables in the master');

while select tablesToExportCSV order by tablesToExportCSV.ListOfTables asc where tablesToExportCSV.Export == NoYes::Yes
{
nameOfTable = tablesToExportCSV.ListOfTables;
tableName = nameOfTable;

dictTable = new dictTable(tableName2Id(tableName));
fieldId = dictTable.fieldNext(0);
tableBuffer = dictTable.makeRecord();

select crossCompany tableBuffer where (tableBuffer.createdDateTime >= dateTimeResult) || (tableBuffer.modifiedDateTime >= dateTimeResult) ;

fileName = pathToStore + '\\' + tableName + '.csv';
permission = new FileIOPermission(fileName, #io_write);
permission.assert();
commoTextIo= new CommoTextIo(fileName ,#io_write);
commoTextIo.outFieldDelimiter('|');
for( i =1; i <= dictTable.fieldCnt(); i++)
{
dictField = new DictField(dictTable.id(), dictTable.fieldCnt2Id(i));
if(dictField.isSql())
{
line += [ dictField.name(i) ];
}
}
commoTextIo.write(line);
line = ConNull();

while select crossCompany tableBuffer where (tableBuffer.createdDateTime >= dateTimeResult) || (tableBuffer.modifiedDateTime >= dateTimeResult)
{
for( j =1; j <= dictTable.fieldCnt(); j++)
{
dictField = new DictField(dictTable.id(), dictTable.fieldCnt2Id(j));
if(dictField.isSql())
{
if(dictField.name(j) == 'CREATEDDATETIME' || dictField.name(j) == 'MODIFIEDDATETIME' )
{
replaceUTC = DateTimeUtil::toStr(tableBuffer.(dictField.id()));
if(replaceUTC && replaceUTC != '' )
{
replaceUTC = strReplace(replaceUTC, 'T', ' ');
replaceUTC = replaceUTC + '.270';
}
line2 += [ replaceUTC ];
replaceUTC = '';
}
else
{
line2 += [ tableBuffer.(dictField.id()) ];
}
}
}
commoTextIo.write(line2);
line2 = ConNull();
recordsExist=true;
}
countTable++;
executedTable++;
CodeAccessPermission::revertAssert();
info(strFmt('Exported %1',fileName));
}

Also, Please find above mentioned full code which is used to export.

[MartinDrab]

The most important reason for using Insert > Insert Code is that it preserves line indentation. But it doesn’t magically format code if you paste it without indentation. If you agree that indentation makes code easier to read, why do you make it complicated for us? Making it harder for us isn’t in your interest, because you’re decreasing your chances to get an answer.

Nevertheless in this case, most of the code seems unrelated to your actual problem, therefore let’s forget it for now. Please see my previus reply to know what do the next. You’ll likely need just a few lines of code to demonstrate the problem.

[Udhayakumar]

Hi Martin,

I have created new job and tried to find what text coming on this missing name columns but it was not getting the name of these fields. Please find some of the missing field names in purchLine Table.

1.PDSCWQTY

2.PDSCWREMAINNVENTFINANCIAL

**3.**PDSCWREMAINNVENTPHYSICAL

Also find below code and it result.

Above mentioned missing field name are should come before ProjWorker field name but it is not coming. Please check highlighted output infolog

[MartinDrab]

Unfortunately you shared your code as an image, which doesn’t allow me to copy it and work with it. Please share your code as text (through Insert > Insert Code).

I suspect that these fields are not enabled in your system at all. Please verify that “Catch weight” configuration field is enabled in your environment. If it isn’t (which I consider likely), the fields are not used, they can’t contain any value and therefore exporting them doesn’t make much sense. You should skip them, most likely.

[Udhayakumar]

Hi Martin,

Please find the below code. I have commented condition( if (dictField.isSql() && !dictField.isSystem()) ) from the code and run the code. Now those fields were shown in the output. What is the reason for it. If those fields not enabled in sql. I am not sure. Please let me know the reason.

Check below code and output.

static void Job42(Args _args)
{
    DictTable    dictTable = new DictTable(tableNum(PurchLine));
    DictField    dictField;
    TreeNode        treeNode;
    FieldId         fieldId = dictTable.fieldNext(0);

    while (fieldId)
    {
        dictField = dictTable.fieldObject(fieldId);

        //if (dictField.isSql() && !dictField.isSystem())
        //{

            info(strFmt("%1", dictField.name()));
        //}

        fieldId = dictTable.fieldNext(fieldId);
    }

    }

[MartinDrab]

I already mentioned a possible reason - please read the second paragraph of my previous reply.

[Udhayakumar]

Thanks, Martin.

I will inform my client. Thanks for your response and answers.