How to import the list of employees

Hi

in my excel sheet fields like are FisrtName,PersonalNumber,LegalEntity,EmployeementType,HCM_Employeement_ValidFrom

How to import the data from excel to ax table 

Thanks 

  • In reply to Gopinath M:

    Thanks gopi,
    I am not able to open that above link here please share the code..

  • In reply to AKumar:

     Importing Data from Excel Sheet to Table in AX 2012
    Importing Data from Excel Sheet to Table in AX 2012
    
    static void Esh_ImporttoExcelProductType(Args _args)
    {
    SysExcelApplication application;
    SysExcelWorkbooks workbooks;
    SysExcelWorkbook workbook;
    SysExcelWorksheets worksheets;
    SysExcelWorksheet worksheet;
    SysExcelCells cells;
    COMVariantType type;
    Name name;
    FileName filename;
    Esh_SampleTable esh_sampletable; //Declaring Table Name
    int row;
    str _productTypeId;
    str _productType;
    str _description;
    ;
    
    application = SysExcelApplication::construct();
    workbooks = application.workbooks();
    //specify the file path that you want to read
    filename ='D:\\Test.xlsx'; //ExcelSheet File Name
    try
    {
    workbooks.open(filename);
    }
    catch (Exception::Error)
    {
    throw error('File cannot be opened');
    }
    
    workbook = workbooks.item(1);
    worksheets = workbook.worksheets();
    worksheet = worksheets.itemFromNum(1); //Here 1 is the worksheet Number
    cells = worksheet.cells();
    do
    {
    row++;
    _productTypeId = any2str(cells.item(row, 1).value().toString());
    _productType = cells.item(row, 2).value().bStr();
    _description = cells.item(row, 3).value().bStr();
    
    esh_sampletable.ID = _productTypeId;
    esh_sampletable.Type = _productType;
    esh_sampletable.Description = _description;
    esh_sampletable.insert();
    
    type = cells.item(row+1, 1).value().variantType();
    }
    while (type != COMVariantType::VT_EMPTY);
    application.quit();
    info("Data is Imported");
    }
    

  • Why cannot you use data import export framework?
  • In reply to Gopinath M:

    Ok Thanks gopi
  • Can you try using Excel addins? This makes your job easier .
  • In reply to Anitha.E:

    Hi thanks for reply
    How to use Excel addins can you please tell me the steps
  • In reply to AKumar:

    You can also use DIXF, see this Blog

  • In reply to Kranthi:

    Hi kranthi,
    But here not working DIXF.
    Fields are " DirPersonName.FirstName,HcmWorker.PersonnelNumber, HcmWorker.workerRelationType(),LegalEntity".

  • In reply to AKumar:

    AKumar
    But here not working DIXF.

    You don't want to use it (or) is it not working?

  • In reply to Kranthi:

    it's not working kranthi.

  • In reply to AKumar:

    Can you explain the issue you have?
  • In reply to Anitha.E:

    Hi,

    Basics setups and overview of Excel addins are given in the below blog.

    anithasantosh.wordpress.com/.../
  • In reply to Gopinath M:

    hI,
    is there any module called DIXF in ur AX??
  • In reply to Gopinath M:

    HI gopi/Kranthi/Anitha Thank you so much for giving reply
    This issue has be done
    static void EmpImport(Args _args)
    {
    SysExcelApplication application;
    SysExcelWorkbooks workbooks;
    SysExcelWorkbook workbook;
    SysExcelWorksheets worksheets;
    SysExcelWorksheet worksheet;
    SysExcelCells cells;
    COMVariantType comtype;

    HcmWorker hcmWorker;
    HcmEmployment hcmEmployment;
    DirPerson dirPerson1;
    DirPersonName dirPersonName;
    CompanyInfo companyInfo;

    HcmPersonnelNumberId personnelNumber;
    utcDateTime validFrom;
    HcmEmploymentType employmentType;
    Name firstName, companyName;

    int insertcount=0;
    int row = 1;

    Dialog dialog;
    DialogField fileName;

    str COMVariant2Str(COMVariant _cv, int _decimals = 0,int _characters = 0,int _separator1 = 0,int _separator2 = 0)
    {
    switch(_cv.variantType())
    {
    case (COMVariantType::VT_BSTR):
    return _cv.bStr();
    case (COMVariantType::VT_R4):
    return num2str(_cv.float(),_characters,_decimals, _separator1,_separator2);
    case (COMVariantType::VT_R8):
    return num2str(_cv.double(),_characters,_decimals,_separator1,_separator2);
    case (COMVariantType::VT_DECIMAL):
    return num2str(_cv.decimal(),_characters,_decimals, _separator1, _separator2);
    case (COMVariantType::VT_DATE):
    return date2str(_cv.date(),123,2,1,2, 1,4);
    case (COMVariantType::VT_EMPTY):
    return "";
    default:
    throw error(strfmt("@SYS26908",_cv.variantType()));
    }
    return "";
    }
    dialog = new Dialog();
    dialog.caption("Select File");
    fileName = dialog.addField(extendedTypeStr(FilenameOpen));
    dialog.run();
    application = SysExcelApplication::construct();
    workbooks = application.workbooks();
    try
    {
    workbooks.open(fileName.value());
    }
    catch
    {
    throw error("File cannot be opened");
    }
    workbook = workbooks.item(1);
    worksheets = workbook.worksheets();
    worksheet = worksheets.itemFromNum(1);
    cells = worksheet.cells();
    do
    {
    row++;

    personnelNumber = COMVariant2Str(cells.item(row,2).value());
    firstName = cells.item(row, 1).value().bStr();
    employmentType = str2enum(employmentType, cells.item(row, 3).value().bStr());
    validFrom = DateTimeUtil::newDateTime(cells.item(row, 4).value().date(), 0);
    companyName = cells.item(row, 5).value().bStr();
    try
    {
    ttsbegin;
    select hcmworker where hcmWorker.PersonnelNumber == personnelNumber;
    if(!hcmworker)
    {
    dirPerson1.clear();
    dirPerson1.Name = firstName;
    dirPerson1.insert();

    dirPersonName.clear();
    dirPersonName.FirstName = firstName;
    dirPersonName.Person = dirPerson1.RecId;
    dirpersonName.insert();

    hcmWorker.clear();
    hcmWorker.Person = dirPerson1.RecId;
    hcmWorker.PersonnelNumber = personnelNumber;
    hcmWorker.insert();

    hcmEmployment.clear();
    hcmEmployment.Worker = hcmWorker.RecId;
    hcmEmployment.EmploymentType = employmentType;
    hcmEmployment.ValidFrom = validFrom;
    hcmEmployment.ValidTo = DateTimeUtil::maxValue();
    hcmEmployment.LegalEntity = CompanyInfo::findDataArea(companyName).RecId;
    hcmEmployment.insert();

    info(strFmt('Employee %1 created', hcmWorker.PersonnelNumber));
    insertcount++;
    }
    comtype = cells.item(row+1, 1).value().variantType();
    ttsCommit;
    }
    catch(Exception::Error)
    {
    info(strFmt("Error in row %1", row));
    }
    }
    while(comtype != COMVariantType::VT_EMPTY);
    info(strFmt("%1 Record(s) inserted", insertcount));
    application.quit();
    }
Related
Recommended