How to import Data from excel to a table using sysoperation Framework in Ax 2012 R3?


Hi in this Blog we will learn on how to import data from excel to a table using sysoperation framework
For that we will start by creating a new private\shared project(ctrl+shift+p) based on your wish. I will create a shared project named ExcelImportBlog .


Now we will create a table for which we want to import data through excel.
For demonstration purposes I will create a table named ExcelImportTable and I will add 6 fields EmployeeId(String), startTime(hh:mm), endTime(hh:mm), clockedhours(real), Date and WrkCtrId(resourceId).

Now all we need to do is create classes for Contract, Controller, UI Builder and Service class for importing of excel file. We will start with a Contract Class

class ImportExcelDataContract
FilenameOpen fileName;

public FilenameOpen parmFile_Name(FilenameOpen fname = fileName)
fileName = fname;
return fname;
Contract Class

Next we will create a UIBuilder class as ImportExcelDataUIBuilder and add the following codes:

class ImportExcelDataUIBuilder extends SysOperationAutomaticUIBuilder
DialogField dlgFileName;
ImportExcelDataContract contract;

public void postBuild()
contract = this.dataContractObject();
dlgFileName = this.bindInfo().getDialogField(contract, methodstr(ImportExcelDataContract,parmFile_Name));
UIBuilder Class

Next up we will create a controller class ImportExcelDataController and write the import logic for Excel:

class ImportExcelDataController extends SysOperationServiceController
ImportExcelDataContract importExcelDataContract;
Filename fname,filename;
SysOperationProgress progress;
container readCon;
int totalRecords;
Row row;
FormDataSource callerDataSource;
DialogField dialogfileNameOpen;
FilenameOpen fileNameOpen;
Counter inserted;
DialogRunBase dlg;
ExcelImportTable excelimportTable;
COMVariant comVar;
COMVarianttype Comtype;
SysExcelApplication app;
SysExcelWorkbooks Workbooks;
SysExcelWorkbook Workbook;
SysExcelWorksheets Worksheets;
SysExcelWorksheet Worksheet;
SysExcelWorksheet Worksheet2;
SysExcelCells cells;
SysExcelCells cells2;
SysExcelCell cellId;
int record,RecordsImported;
int recordsRead;
int recordsInserted;

public boolean canGoBatch()
return false;

public ImportExcelDataContract parmDataContract(ImportExcelDataContract _importExcelDataContract = importExcelDataContract)
importExcelDataContract = _importExcelDataContract;

return importExcelDataContract;

public void run()
ImportExcelDataContract excelDataContract;
excelDataContract = this.getDataContractObject();
fname = excelDataContract.parmFile_Name();
throw error("Please Choose Excel File");

public void importExcel()
SysExcelApplication application;
COMVariantType type;
System.IO.FileInfo fileinfo;
FileNameType fileExtension;
boolean validateAll;
real start, end;
inserted = 0;
application = SysExcelApplication::construct();
workbooks = application.workbooks();
comVar = new COMVariant();
new InteropPermission(InteropKind::ClrInterop).assert();
fileinfo = new System.IO.FileInfo(fname);
fileExtension = fileinfo.get_Extension();
filename = fname;
validateAll = true;
if(fileExtension == ".xls" || fileExtension == "xlsx")
progress = new SysOperationProgress();
workbook = workbooks.add(comVar);
worksheets = workbook.worksheets();
worksheet = worksheets.itemFromNum(1);
cells = worksheet.cells();
row = 2;
cellId = cells.item(row,1);
cellId = cells.item(row,1);
type = cells.item(row, 1).value().variantType();
progress.setText(strfmt("Importing %1 row", row));
excelImportTable.EmployeeID = cells.item(row,1).value().bStr();
excelImportTable.ResourceId = strLRTrim(cells.item(row,2).value().bStr());
excelImportTable.ExtDate= cells.item(row,3).value().date();
start = cells.item(row,4).value().double();
excelImportTable.StartTime = start*1440*60;
end = cells.item(row,5).value().double();
excelImportTable.EndTime = end*1440*60;
excelImportTable.ClockedHours = cells.item(row,6).value().double();
cellId = cells.item(row,1);
info(strfmt("Imported %1 rows", row-2));

public static void main(Args args)
{ ImportExcelDataController controller;
ImportExcelDataContract dataContract;
identifierName className;
identifierName methodName;
SysOperationExecutionMode executionMode;
executionMode = SysOperationExecutionMode::Synchronous;
[className, methodName, executionMode] = SysOperationServiceController::parseServiceInfo(args);
controller = new ImportExcelDataController(className, methodName, executionMode);
dataContract = controller.getDataContractObject();
controller.parmDialogCaption("Import Excel Data");
if (controller.prompt())

UIBuilder Class

Now we will create a Data Service Class such that we can link it with a menu item
So the class I will create is ImportExcelDataService and add the following codes:

class ImportExcelDataService
ImportExcelDataContract contract;
public void operator(ImportExcelDataContract importExcelDataContract)
DataService Class

Now, we will create an Action menu item to import excel and we will name it as Import From Microsoft Excel
Menu Item

Import Dialog