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”;
    ttsBegin;


    try
    {
        workbooks.open(filename);
    }
    catch (Exception::Error)
    {
        throw error(“File cannot be opened.”);
    }
    workbook    = workbooks.item(1);
    worksheets  = workbook.worksheets();
    worksheet   = worksheets.itemFromNum(1);
    cells       = worksheet.cells();
    
    do
    {
        row++;


        HcmPosition.PositionId  = cells.item(row, 1).value().bStr();
        HcmPosition.insert();
        
        department              = cells.item(row, 2).value().bStr();
        job                     = cells.item(row, 3).value().bStr();
        
        select firstOnly OMOperatingUnit where OMOperatingUnit.OMOperatingUnitNumber == department;
        if(OMOperatingUnit)
        {
            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();
            HcmPositionDetail.insert();
            
            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;
            HcmPositionWorkerAssignment.insert();
            
            HcmPositionDuration.Position    = HcmPosition.RecId;
            HcmPositionDuration.ValidFrom   = HcmPositionWorkerAssignment.ValidFrom;
            HcmPositionDuration.ValidTo     = DateTimeUtil::maxValue();
            HcmPositionDuration.insert();


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