How to export table data into csv file in AX 2012

Export data into csv file using X++ code in AX 2012

Hi,

A CSV is a comma separated values file, which allows data to be saved in a table structured format. Traditionally they take the form of a text file containing information separated by commas.

You can export table data outside of AX into file which separates value of field with a comma. e.g., “Mohammed”, “Raziq”, “Ali”, “Hussain”,”OU”, “MCA”,”27″. Row data are separated by commas.

Lets proceed with an example to understand how to export table into CSV file. The following code writes selected table data into file with proper validations.

CommaTextIo class plays major role in writing/reading file csv in AX 2012

Case-1: In case, to export data in a single csv file

static void Job36(Args _args)
{
    CommaTextIo     file;
    container       line;
    boolean         csvHeader = false;
    VendParameters  vendParameters;
    FilePath        filePath;
    Filename        filename;
    Email           vendorEmail;
    VendAccount     vendAccount;
    DBVendPaymentJournalTmp_NA  vendPaymentJournalTmpLoc;

    select vendParameters;
    {
        filePath = vendParameters.DBPaymProposalExportPath;
    }

    if (filePath == '')
    {
        error("Please set up the location for export file.");
        return;
    }

    #define.filename('\\%1')
    #File
    while select vendPaymentJournalTmpLoc
        order by MainAccountId asc
    {
        if (!file)
        {
            filename = filePath + strFmt(#filename, vendPaymentJournalTmpLoc.MainAccountId) +#CSV;
            new FileIOPermission(filename, #io_write).assert();
            file = new CommaTextIo(filename, #io_write);
        }
        if( !file || file.status() != IO_Status::Ok)
        {
            throw error("@SYS19358");
        }

        if (!csvHeader)        
        {
            //Put headers
            line = ['@SYS17795','@SYS6303','@SYS1711','@SYS114255','@SYS24500','@SYS9624'];
            file.writeExp(line);
            csvHeader = true;
        }
        line = [vendPaymentJournalTmpLoc.JournalNum,vendPaymentJournalTmpLoc.Name,vendPaymentJournalTmpLoc.Posted,
                vendPaymentJournalTmpLoc.NumberOfJourChecks,vendPaymentJournalTmpLoc.MainAccountId,
                vendPaymentJournalTmpLoc.AccountName];
        file.writeExp(line);
        vendAccount = vendPaymentJournalTmpLoc.MainAccountId;
        vendorEmail = VendTable::find(vendPaymentJournalTmpLoc.MainAccountId).email();        
    }
        info(strFmt('File exported successfully. Please find it at %1',filePath));
}

Case-2: In case, to export data and save csv file per Vendor account

static void Job36(Args _args)
{
    CommaTextIo     file;
    container       line;
    boolean         csvHeader = false;
    VendParameters  vendParameters;
    FilePath        filePath;
    Filename        filename;
    Email           vendorEmail;
    VendAccount     vendAccount;
    DBVendPaymentJournalTmp_NA  vendPaymentJournalTmpLoc;

    select vendParameters;
    {
        filePath = vendParameters.DBPaymProposalExportPath;
    }

    if (filePath == '')
    {
        error("Please set up the location for export file.");
        return;
    }

    #define.filename('\\%1')
    #File
    while select vendPaymentJournalTmpLoc
        order by MainAccountId asc
    {
        //if (!file)
        if (vendPaymentJournalTmpLoc.MainAccountId != vendAccount)
        {
            filename = filePath + strFmt(#filename, vendPaymentJournalTmpLoc.MainAccountId) +#CSV;
            //new FileIOPermission(filename, #io_write).assert();
            file = new CommaTextIo(filename, #io_write);
        }
        if( !file || file.status() != IO_Status::Ok)
        {
            throw error("@SYS19358");
        }

        //if (!csvHeader)
        if (vendPaymentJournalTmpLoc.MainAccountId != vendAccount)        
        {
            //Put headers
            line = ['@SYS17795','@SYS6303','@SYS1711','@SYS114255','@SYS24500','@SYS9624'];
            file.writeExp(line);
            csvHeader = true;
        }
        line = [vendPaymentJournalTmpLoc.JournalNum,vendPaymentJournalTmpLoc.Name,vendPaymentJournalTmpLoc.Posted,
                vendPaymentJournalTmpLoc.NumberOfJourChecks,vendPaymentJournalTmpLoc.MainAccountId,
                vendPaymentJournalTmpLoc.AccountName];
        file.writeExp(line);
        vendAccount = vendPaymentJournalTmpLoc.MainAccountId;
        vendorEmail = VendTable::find(vendPaymentJournalTmpLoc.MainAccountId).email();        
    }
        info(strFmt('File exported successfully. Please find it at %1',filePath));
}

Happy DAXing...