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;
}
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;
}