2 examples of importing multiple files

In AX, the typical way to import files via the UI is to do it one file at the time. If you have multiple files to import there are several options on how to do this via the AIF.

Every once in a while, it could be useful for the end-user to have the option of importing several files through the UI.

I have found two similar examples on how to achieve this:

Example 1)
static void MultiFileSelectDlgTest_1(Args _args)
{
    System.Windows.Forms.OpenFileDialog ofd;
    System.String[]                                          s2;
    int                                                               counters;
    str                                                               imageValaue;
    int                                                               i;
    ;
    ofd = new System.Windows.Forms.OpenFileDialog();
    ofd.set_Title("Select files");
    ofd.set_Multiselect(true);
    if (ofd.ShowDialog() == System.Windows.Forms.DialogResult::OK)
    {
        s2 = ofd.get_FileNames();
        counters=System.Convert::ToInt32( s2.get_Length());
        for(i = 0 ; i <= counters-1; i++)
        {
            imageValaue = System.Convert::ToString(s2.GetValue(i));
            info(strFmt('%1', imageValaue));
            //do whatever you want ;)
        }
    }

}
The dialog will show this:

and the output in the infolog shows the individual files processed:

Example 2) The second example is just another flavour of the first, adding a filter option on the input content.

static void MultiFileSelectDlgTest_2(Args _args)

{

    int             idx;

    int             cnt;

    boolean         result;

    System.String[] files;

    Microsoft.Win32.OpenFileDialog dlg = new Microsoft.Win32.OpenFileDialog();

    dlg.set_Multiselect(true);

    dlg.set_DefaultExt(".txt");

    dlg.set_Filter("Text documents|*.txt|All files|*.*");

    result = dlg.ShowDialog();

    if (result)

    {

        files = dlg.get_FileNames();

        cnt = files.get_Count();

        for (idx = 0; idx <= cnt; idx++)

        {

            info(files.get_Item(idx));

        }

    }

}

Importing an Excel spread sheet with multiple columns

You may have seen it before, but here’s our take:

Case: The end-user has a spreadsheet they want to import, and creating a csv-file is not an option.

Challenge: In Excel the end user needs to set a “ ’ ” in front of the numbers if they are to behave as strings e.g. an account no “123435”. When read in AX the value will be read as 123.435,0000 since is interpreted as a real. To overcome this, the end user must set the ‘ in front of the string e.g. ‘123435. Since there may be several hundred or thousands of records, it is not an option for the end user.

How to import from Excel and format the cells’ content when assigning to variables in AX.

Suggested solution below:

So, you create a dialog etc. for the file import and in the actual method you read the sheet, the code looks like this:

private void readExcelFile()

{

    SysExcelApplication     application;

    SysExcelWorkbooks       workbooks;

    SysExcelWorkbook        workbook;

    SysExcelWorksheets      worksheets;

    SysExcelWorksheet       worksheet;

    SysExcelCells           cells;

    COMVariantType          type;

    COMVariant              variant;

    int                     row=1,errors = 0;

    smmBusRelTable                      smmBusRelTable;

    smmBusRelAccount                    smmBusRelAccount;

    XXX_SortingId                       sortingId;

    container                           errorCon;

    ;

    application = SysExcelApplication::construct();

    workbooks   = application.workbooks();

    ttsBegin;

    try

    {

        workbooks.open(filenameopen);

    }

    catch (Exception::Error)

    {

        throw error("@SYS19358.");

    }

    workbook    = workbooks.item(1);

    worksheets  = workbook.worksheets();

    worksheet   = worksheets.itemFromNum(1);

    cells       = worksheet.cells();

    type = cells.item(row+1,1).value().variantType();

    while (type != COMVariantType::VT_EMPTY)

    {

        row++;

        // find variant of cell

        variant         = cells.item(row, 1).value();

        // set variant type to smmBusRelAccount

        smmBusRelAccount = this.variant2str(variant);

        if(smmBusRelAccount)

        {

            smmBusRelTable = smmBusRelTable::find(smmBusRelAccount,true);

            // if there is a prospect proceed

            if(smmBusRelTable.RecId)

            {

                variant = cells.item(row, 3).value();

                sortingId = this.variant2str(variant);

                if(!sortingId)

                {

                    sortingId = cells.item(row, 3).value().toString();

                }

                if(XXX_Table::exist(sortingId,8))

                {

                    smmBusRelTable.XXX_Field = sortingId;

                    smmBusRelTable.update();

                }

                else

                {

                    errorCon = conIns(errorCon,maxInt(),strFmt("Error with sorting %1",row,sortingId));

                }

            }

            // write to error log

            else

            {

                errorcon = conIns(errorCon,maxInt(),strFmt("Error with prospect %1",row));

            }

        }

        type = cells.item(row+1, 1).value().variantType();

    }

    application.quit();

    ttsCommit;

    info('Import done');

    // Header was counted as successful import. 1 is substracted from row to reflect that headers should not count

    info(strFmt('Number of items imported %1',(row-1)-conLen(errorCon)));

    setprefix(strfmt('@SYS344649',conLen(errorCon)));

    while (errors < conLen(errorCon))

    {

        errors++;

        info(conPeek(errorCon,errors));

    }

}

In the

while (type != COMVariantType::VT_EMPTY)

we check if there is something the cell read.

Then you assign the variant of the cell

variant         = cells.item(row, 1).value();

which you then cast to string:

private str variant2str(COMVariant _variant)

{

    str valueStr;

    ;

    switch(_variant.variantType())

    {

        case COMVariantType::VT_EMPTY   :

            valueStr = '';

            break;

        case COMVariantType::VT_BSTR    :

            valueStr = _variant.bStr();

            break;

        case COMVariantType::VT_R4      :

        case COMVariantType::VT_R8      :

            if(_variant.double())

            {

                valueStr = num2Str0(_variant.double(),0);

                

            }

            break;

        default                         :

            throw error(strfmt("@SYS26908",

                                _variant.variantType()));

    }

    return valueStr;

}