Wednesday, May 28, 2014

Import HRM Positions from Excel through X++

You can use different options to import data into Dynamics AX 2012 specially the subject line entity (Positions). Data import/export framework would be the best option to import it, you can also use excel addins for this purpose. However, due to large number of tables involved in few import process it might be difficult to judge right tables and their sequence.

You can also use X++ code to import data into AX either from CSV file or from Excel file. The following job in X++ may help you in importing positions from excel file.

static void ImportHRPosition(Args _args)
    SysExcelApplication     application;
    SysExcelWorkbooks       workbooks;
    SysExcelWorkbook        workbook;
    SysExcelWorksheets      worksheets;
    SysExcelWorksheet       worksheet;
    SysExcelCells           cells;
    COMVariantType          type;
    OMOperatingUnit         OMOperatingUnit;
    int                     row=1;

    Name                                name;
    FileName                            filename;
    HcmPosition                         HcmPosition;
    HcmPositionDetail                   HcmPositionDetail;
    HcmPositionWorkerAssignment         HcmPositionWorkerAssignment;
    HcmPositionDuration                 HcmPositionDuration;
    str                                 job;
    OMOperatingUnitNumber               department;

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

    filename = "C:\\import\\HRPosition.xlsx";

    catch (Exception::Error)
        throw error("File cannot be opened.");
    workbook    = workbooks.item(1);
    worksheets  = workbook.worksheets();
    worksheet   = worksheets.itemFromNum(1);
    cells       = worksheet.cells();

        HcmPosition.PositionId  = cells.item(row, 1).value().bStr();
        department              = cells.item(row, 2).value().bStr();
        job                     = cells.item(row, 3).value().bStr();
        select firstOnly OMOperatingUnit where OMOperatingUnit.OMOperatingUnitNumber == department;
            HcmPositionDetail.Department    = OMOperatingUnit.RecId;
            HcmPositionDetail.Job           = HcmJob::findByJob(job).RecId;
            HcmPositionDetail.Position      = HcmPosition.RecId;

            HcmPositionDetail.Title         = HcmTitle::findByTitle(HcmJobDetail::findByJob(HcmPositionDetail.Job).Description).RecId;
            HcmPositionDetail.Description   = cells.item(row, 4).value().bStr();
            HcmPositionDetail.ValidFrom     = DateTimeUtil::newDateTime(cells.item(row, 5).value().date(),timeNow());
            HcmPositionDetail.ValidTo       = DateTimeUtil::maxValue();
            HcmPositionWorkerAssignment.ValidFrom   = DateTimeUtil::newDateTime(cells.item(row, 5).value().date(),timeNow());
            HcmPositionWorkerAssignment.ValidTo     = DateTimeUtil::maxValue();
            HcmPositionWorkerAssignment.Position    = HcmPosition.RecId;
            HcmPositionWorkerAssignment.Worker      = HcmWorker::findByPersonnelNumber(cells.item(row, 6).value().bStr()).RecId;
            HcmPositionDuration.Position    = HcmPosition.RecId;
            HcmPositionDuration.ValidFrom   = HcmPositionWorkerAssignment.ValidFrom;
            HcmPositionDuration.ValidTo     = DateTimeUtil::maxValue();

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


  1. Hi Fareed,

    i am new for Ax so i am confused where should i write this code whether table methods or jobs and how can i map my excel sheet with this code. can you please explain me.

  2. Hi Raghu,

    It completely depends on your requirement, you can write this code in a job (you have to run that job manually either for one time or every time when required) or you can add this code as a method in a class. That class can run in batch to schedule data uploads from excel.

    As you can see this line of code filename = "C:\\import\\HRPosition.xlsx"; you can change this path and give your file path with name.

    Under do { ..... } while loop it is actually reading data from excel file and map into tables. you can map it something like this.

    Hope this helps :)

  3. Thnq so much fareed for your nice explanation i have achieved my requirement with the help of your post :)

  4. Hi add explanation comments please.

  5. HI Fareed, I need to read recId(int64) value from excel file while importing. Which method i need to use to change the datatype.

    Thanks in advance.


I will appreciate your comments !

Download large bacpac (sandbox database) to DEV environment much faster

As the LCS website gets slower and slower and the database backups get bigger and bigger.  Use AZCopy to download objects out of LCS asset l...