AX 2012 - Import data from excel (.xslx) file

Today we will learn on how to import .xslx file into AX 2012 using a job.

1)     Create a table TEC_Import:

Emp_Age        int

Emp_Dob       date

Emp_Id           str 20

Emp_Name     str 20

2)     Write a AX 2012 job to import data into the above created table:

static void ImportFromExcel(Args _args)

{

#AviFiles

SysOperationProgress progress = new SysOperationProgress();

SysExcelApplication application;

SysExcelWorkbooks workbooks;

SysExcelWorkbook workbook;

SysExcelWorksheets worksheets;

SysExcelWorksheet worksheet;

SysExcelCells cells;

COMVariantType type;

FileIoPermission perm;

Name name;

FileName filename;

TEC_Import importTable;   //your table name

int row;

boolean first = true;

TEC_AGE _EmpAge;     //EDT

TEC_ID _EmpID;      //EDT

TEC_NAME _EmpName;  //EDT

TEC_DOB _DOB;       //EDT

 #define.Filename('c:\\Book2.xlsx')     //specify the file path that you want to read

 #define.FileMode('R')

;

 perm = new FileIOPermission(#FileName, #FileMode);

 perm.assert();

 application = SysExcelApplication::construct();

 workbooks = application.workbooks();

   

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

progress.setCaption("Sales target data import...");

progress.setAnimation(#AviTransfer);

ttsBegin;

do

{

row++;

    if(row==1)

    {

       first = false; //to skip the header

    }

    else

    {

_EmpID   = cells.item(row, 1).value().bStr();

_EmpName = cells.item(row, 2).value().bStr();

_DOB     = cells.item(row, 3).value().date();

_EmpAge  = any2int(cells.item(row, 3).value().toString());

importTable.Emp_Id          = _EmpID;

importTable.Emp_Name        = _EmpName;

importTable.Emp_Dob         = _DOB;

importTable.Emp_Age         = _EmpAge;

importTable.insert();

         }

type = cells.item(row+1, 1).value().variantType(); 

}

while (type != COMVariantType::VT_EMPTY);

ttsCommit;

application.quit();

}