X++ to read data from a spreadsheet

Often there are times when data needs to be ready from a spreadsheet.  An easy way to handle this is to copy/paste from the spreadsheet to a text file (which will be tab delimited by default) and then use this code (modified as needed) to read that data and do whatever is needed with it.  It includes a dialog to select the file, if the file has a header row, and a progress bar displayed to the user.

static void importScriptTemplate(Args _args)
{
    #File
    TextIo                  io;
    container               c;
    FileIOPermission        permission;
    Filename                filename;
    Dialog                  dialog;
    DialogField             filenameField;
    DialogField             headerRowField;
    container               filetypes = [#AllFilesType, #AllFiles];
    boolean                 containsHeaderRow;


    SysOperationProgress    progress;
    int                     i;
    int                     total;

    dialog = new Dialog("Select file to load");
    filenameField = Dialog.addField(extendedTypeStr(FilenameOpen));
    headerRowField = dialog.addFieldValue(extendedTypeStr(NoYesId), NoYes::Yes, "Contains header row");
    dialog.filenameLookupTitle("Select file to load");
    dialog.filenameLookupFilter(filetypes);
    if (dialog.run())
    {
        filename = filenameField.value();
        containsHeaderRow = headerRowField.value();

        permission = new FileIOPermission(Filename, #io_read);
        permission.assert();

        io = new TextIo(Filename, #io_read);
        if (!io)
        {
            throw error("Error reading file");
        }
        io.inFieldDelimiter('\t');
        io.inRecordDelimiter(#delimiterCRLF);

        //Read through file once to determine number of lines (this is quick if no processing is done per line)
        while (io.status() == IO_Status::Ok)
        {
            c = io.read();
            if (io.status() != IO_Status::Ok)
            {
                break;
            }
            total++;
        }

        //Set up progress bar
        progress = new SysOperationProgress();
        progress.setAnimation("104.avi");
        progress.setCaption("Reading file");
        progress.setTotal(total);

        io = new TextIo(Filename, #io_read);
        if (!io)
        {
            throw error("Error reading file");
        }
        io.inFieldDelimiter('\t');
        io.inRecordDelimiter(#delimiterCRLF);

        //Now process the file
        ttsBegin;
        while (io.status() == IO_Status::Ok)
        {
            progress.incCount();
            i++;
            progress.setText(strFmt("Reading line %1 of %2", i, total));

            c = io.read();
            if (i == 1 && containsHeaderRow) //burn header row
            {
                c = io.read();
            }
            if (io.status() != IO_Status::Ok)
            {
                break;
            }

        }
        ttsCommit;

        CodeAccessPermission::revertAssert();
    }
    info("Finished");
}

Comments

Post a Comment

Popular posts from this blog

Posting Purchase Order Invoices via X++

In the scenario where you need to post purchase order invoices on a per packing slip basis, you will want to make use of the VendInvoiceInfo series of tables: VendInvoiceInfoTable, VendInvoiceInfoSubTable, VendInvoiceInfoLine, and VendInvoiceInfoSubLine.  A few notes about using these tables from code.  Since there will be no form front end, you won't specify a ParmID in your code.  Also there can only be one invoice line per PO line in each invoice, so if you have multiple receipts to invoice for one PO line, each different packing slip goes in the subLine table.  Also, if you need to use charge codes to apply to the invoice, MarkupTrans table and MarkupAllocation class are your tools.  Below are some code snippets to refer to - they are not intended to be copy/pasted and that's it.  They are just some examples of initializing the tables.  in the code below, psJour is VendPackingSlipJour and psTrans is VendPackingSlipTrans.  You will use class Pu...
Read more

Using X++ to get a list of files in a directory

If you have the need to read a specific directory to get a list of files from it, the following code uses System.IO objects which will work in batch if needed. static container findMatchingFiles(         str _folderPath,         str _filePattern   = '*.*') {     System.IO.DirectoryInfo     directory;     System.IO.FileInfo[]        files;     System.IO.FileInfo          file;     InteropPermission           permission;     str                         fileName;     counter                     filesCount;     counter                     loop;     container        ...
Read more

Split a production order with X++

Below is code for splitting a production order in a Scheduled status based on an input quantity from a dialog.  Note that if this code is put in a class, that class must be set to run on the server, as the AX split code is run on the server.  Note that the status decrease is run twice to get the status to Created, and then the Estimation and Scheduling is run for both the old and new production orders. public void run() {     ProdParmStatusDecrease  prodParmStatusDecrease;     ProdUpdStatusDecrease   prodUpdStatusDecrease;     ProdTable               splitProdTable;     ProdUpdCostEstimation   costEstimation;     ProdParmRelease         prodParmRelease;     ProdUpdScheduling       scheduling;     ProdParmScheduling      prodParmScheduling;     ProdParmSplit  ...
Read more