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

Hi,
This may helpful to you…
daxingwitheshant.blogspot.in/…/importing-data-from-excel-sheet-to.html

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

 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?

Ok Thanks gopi

Can you try using Excel addins? This makes your job easier .

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

You can also use DIXF, see this Blog

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

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

it’s not working kranthi.

Can you explain the issue you have?

Hi,

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

anithasantosh.wordpress.com/…/

hI,
is there any module called DIXF in ur AX??

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

Glad to hear that.

Suggestion - using the available options (like DIXF) will be much easier and will ensure less/no errors.

Hi Akumar
Thanks for your code.
code posted by u works well for inserting records in to tables.
how can we insert those records into form( hcmworkerlistpage)

Please see the tables used in the form and relation between tables…