Write TSV file in X++

Purpose:

The purpose of this blog post is to demonstrate how can we write a tsv (tab delimited) file in X++

• The code actually exports system database log to a TSV file
• Uses standard SysDatabaseLogReport query
• Adds a range for Warehouse table
• Uses formatted datetime string for filename

Product:

Dynamics AX 2012

Code:

Class declaration.

class MAKSysDatabaseLogService
{
    #File
    #define.EmptyString('')

    FileIOPermission IOPermission;
    SysDataBaseLog sysDataBaseLog;
    container cont;
    TextIo file;
    FieldName fieldNameText;
    str oldValueText;
    str newValueText;
}

Run method. The main method of the class.

public void run()
{
    container tmp;
    Query query;
    QueryBuildDataSource qbds;
    QueryRun queryRun;
    List list;
    ListEnumerator enumerator;
    fieldId fieldId;
    str fileName;
    boolean written;
    System.DateTime dateTime;
    str dateTimeStr;

    ;
    dateTime = System.DateTime::get_Now();
    dateTimeStr = dateTime.ToString('yyyyMMddHHmmss');

    query = new query(queryStr(SysDatabaseLogReport));
    qbds = query.dataSourceTable(tableNum(SysDatabaseLog));
    qbds.addRange(fieldNum(SysDatabaseLog, Table)).value("158");    // Table Warehouse
    queryRun = new QueryRun(query);

    try
    {
        fileName = strFmt("%1\\Databaselog_%2.csv", ICH_DatabaseLogSetup::find().FilePath, dateTimeStr);
        IOPermission = new FileIOPermission(fileName, #IO_WRITE);
        IOPermission.assert();
        file = new TextIo(filename, #IO_WRITE);
        file.outRecordDelimiter(#delimiterCRLF);
        file.outFieldDelimiter("\t");
        this.writeHeader();

        while (queryRun.next())
        {
            sysDataBaseLog = queryRun.get(tablenum(SysDataBaseLog));
            fieldNameText = #EmptyString;
            newvalueText = #EmptyString;
            oldValueText = #EmptyString;
            written = false;

            list = sysDataBaseLog.getDataAslist();
            enumerator = list.getEnumerator();

            while (enumerator.moveNext())
            {
                tmp = enumerator.current();
                fieldId = conpeek(tmp, 1);
                fieldNameText = fieldid2pname(sysDataBaseLog.Table, fieldId);
                newValueText = sysDataBaseLog.contents2Str(conpeek(tmp, 2), fieldId);
                oldValueText = sysDataBaseLog.contents2Str(conpeek(tmp, 3), fieldId);

                this.writeLine();
                written = true;
            }

            if (!written)
            {
                this.writeLine();
            }
        }

        info(strFmt("%1", "Database log exported successfully."));
    }
    catch(Exception::Error)
    {
        error("ERROR");
    }

    CodeAccessPermission::revertAssert();
}

Writes header to the file.

private void writeHeader()
{
    cont = connull();
    cont = conins(cont, 1, "Table name");
    cont = conins(cont, 2, "Description");
    cont = conins(cont, 3, "Log type");
    cont = conins(cont, 4, "Created by");
    cont = conins(cont, 5, "Field name");
    cont = conins(cont, 6, "Value");
    cont = conins(cont, 7, "Previous value");
    cont = conins(cont, 8, "Created date and time");

    file.writeExp(cont);
}

Writes a line to the file.

private void writeLine()
{
    cont = connull();
    cont = conins(cont, 1, sysDataBaseLog.tableName());
    cont = conins(cont, 2, sysDataBaseLog.Description);
    cont = conins(cont, 3, sysDataBaseLog.LogType);
    cont = conins(cont, 4, sysDataBaseLog.createdBy);
    cont = conins(cont, 5, fieldNameText);
    cont = conins(cont, 6, newValueText);
    cont = conins(cont, 7, oldValueText);
    cont = conins(cont, 8, sysDataBaseLog.createdDateTime);
    
    file.writeExp(cont);
}