Microsoft Dynamics AX 2012: Uploading data from Excel into our Dynamics Ax table.

In that post I will show you example on how can we import data from excel and than map it on your AX table.

I have created an excel sheet in which I have added some data as you can see this in the image below.

After that I will import that the data into my AX table.

The staging table that I have created on which I will map the Excel data can be seen in the below image.

Now I will show you the process of Importing this Data into our AX tables.

I have just created a class.In the main method of that class I am calling another method and passing path of my excel sheet as a parameter.

You can see that method in the below image.

Now inside the method ImportData() there is a process written which gets the data from excel cells and than map them into the Ax tables.

You can see the code snippet for that method below.

public void ImportData
(Filename _fileName)
{
    SysExcelApplication     application;
    SysExcelWorkbooks       workbooks;
    SysExcelWorkbook        workbook;
    SysExcelWorksheets      worksheets;
    SysExcelWorksheet       worksheet;
    SysExcelCells           cells;
    COMVariantType          type;
    int                     row ;
    Zahidtable lineImport;
    str offsetType;
    str account;
    str offsetAccount;
    str transactionType;

    application = SysExcelApplication::construct();
    workbooks = application.workbooks();



    try
    {
        workbooks.open(_fileName,0,true);
    }
    catch (Exception::Error)
    {
        throw error('@SYS19358');
    }

    workbook = workbooks.item(1);
    worksheets = workbook.worksheets();
    worksheet = worksheets.itemFromNum(1);
    cells = worksheet.cells();

    row = 2;

    lineImport.clear();

    startLengthyOperation();
    do
    {
        row++;

        try
        {
            lineImport.clear();

            transactionType = this.VarientConversionMethod(cells.item(row, 8).value());

            //For acquisition
            if(transactionType == "Acquisition")
            {
                lineImport.TransType = AssetTransTypeJournal::Acquisition;
                account = this.VarientConversionMethod(cells.item(row, 1).value());
                lineImport.Account = account;
                lineImport.LedgerDimension =       AxdDimensionUtil::getMultiTypeDefaultAccountId(129,                LedgerJournalACType::FixedAssets, [account]);
                lineImport.txt = this.VarientConversionMethod(cells.item(row ,2).value());
                lineImport.AmountCurDebit = this.VarientConversionMethod(cells.item(row, 3).value());
                offsetType = this.VarientConversionMethod(cells.item(row, 4).value());
                offsetAccount = this.VarientConversionMethod(cells.item(row, 5).value());
                lineImport.BookId = this.VarientConversionMethod(cells.item(row, 6).value());
                lineImport.AssetBookId = this.VarientConversionMethod(cells.item(row, 7).value());
            }

            if (offsetType == "ledger")
            {
                lineImport.OffsetAccountType = LedgerJournalACType::Ledger;
                lineImport.OffsetLedgerDimension = AxdDimensionUtil::getLedgerAccountId( [offsetAccount, offsetAccount, 0]);
            }

            ttsBegin;
            lineImport.insert();
            ttsCommit;

        }
        catch(Exception::Error)
        {
            info("Error found while importing data from excel");
        }

        type = cells.item(row+1, 1).value().variantType();
    }
    while (type != COMVariantType::VT_EMPTY);

    endLengthyOperation();

}


In the above code. I am basically calling another method i.e VarientConversionMethod(COMVariant _varientValue).

In that method  I am going to type cast the values that I have fetched from the excel based on the fields on the table.

You can see the code of  that method below.

private anytype VarientConversionMethod(COMVariant _varientValue)
{
    str value;
    real value2;
    switch(_varientValue.variantType())
    {
        case COMVariantType::VT_BSTR:
            value = strFmt("%1", _varientValue.bStr());
            break;
        case COMVariantType::VT_DECIMAL, COMVariantType::VT_R4, COMVariantType::VT_R8:
            value = strFmt("%1",_varientValue.double());
            value2 = System.Convert::ToDecimal(value);
            value = System.Convert::ToString(value2);
            if (Global::numOfDec(_varientValue.double()) == 0)
            {
                value = strFmt("%1",real2int(_varientValue.double()));
            }
            break;
        case COMVariantType::VT_I1, COMVariantType::VT_I2, COMVariantType::VT_I4:
            value = strFmt("%1",_varientValue.int());
            break;
        case COMVariantType::VT_UI1, COMVariantType::VT_UI2, COMVariantType::VT_UI4:
            value = strFmt("%1",_varientValue.uLong());
            break;

        case COMVariantType::VT_DATE:
            value = strFmt("%1",_varientValue.date());
            break;
         case COMVariantType::VT_EMPTY:
            value = '';
            break;
        default:
            throw error(strfmt('Unhandled variant type (%1).', _varientValue.variantType()));
    }

    return value;
}

Now I have run that class and I am successfully able to the record inside my table.

You can see the record inside my staging table in the below image.

This was all related to uploading the Data from Excel into our Microsoft Dynamics AX table.

I hope you like that post.

Thanks

Muhammad Zahid.