Excel import in Dynamics AX 365

Now Dynamics AX 365 is running on Web browser so import the data in AX using Excel, CSV, text etc. has been changed. FileName,FilenameOpen extended data type is no more supported to browse the excel file.

True to their mission, “to enable people and businesses to realize their full potential”, Microsoft released Flow in Quarter 4 of last year. What is Flow? Microsoft Flow is a tool that empowers users to automate complicated tasks across multiple applications and services. Flow seamlessly connects to over ninety services, out-of-the-box, including Twitter, Slack, OneDrive.

If we compare Excel file import process between AX 2012 and Dynamics AX 365 then in AX 2012 file was importing from Client (from local system) to AX database directly but now the new AX is running on web server over the IIS so to import file in AX mean first file need to import on server and need to store in File server or SharePoint or Database. And then read the file from stored location may be from File server, SharePoint or Database.

So how we can import data in AX using Excel file? The simple way is via using Data entities and if data entity does not exist then need to create new Data entity for table and then we can import data via using the excel file or any other supported file using that Data entity.

But sometime required to import the data in AX using dialog in that case Data entities will not help, so in this blog I will describe how we can import data in Dynamics AX 365 using excel file.

In ax 2012 we have used the below classes to import data in AX via using excel file:-

SysExcelApplication application;SysExcelWorkbooks workbooks;SysExcelWorkbook workbook;SysExcelWorksheets worksheets;SysExcelWorksheet worksheet;SysExcelCells cells;

But now in Dynamics AX 365 the above classes does not exist anymore to read excel file.

So how we can import data in AX via Excel file? ,

So first step to import the excel file in AX , create a new class (TestExcelImport) and extend with RunBase and add a new button in dialog with upload caption (Same as AX 2012)

Object Dialog()

{

FormBuildButtonControl buttonControl;

DialogGroup dlgGroup;

FormBuildGroupControl buttonGroup;

dialog = super();

dlgGroup = dialog.addGroup(');

buttonGroup = dialog.formBuildDesign().control(dlgGroup.formBuildGroup().id());

buttonControl = buttonGroup.addControl(FormControlType::Button, 'Upload');

buttonControl.text('Upload file');

buttonControl.registerOverrideMethod(methodStr(FormButtonControl, clicked),

methodStr(TestExcelImport, uploadClickedEvent),

this);

return dialog;

}

And below is the upload button click event (click event already registered in above dialog method)

private void uploadClickedEvent(FormButtonControl _formButtonControl)

{

FileUploadTemporaryStorageResult result = File::GetFileFromUser() as FileUploadTemporaryStorageResult;

if (result && result.getUploadStatus())

{

result.getFileContentType();

//result.

fileUrl = result.getDownloadUrl();

info(fileUrl);

}

}

FileUploadTemporaryStorageResult is the class has been introduced in AX and this class is responsible to browse and upload the file on server (On File server, SharePoint or Database). And from here store the file path (for me fileUrl) in a variable to read the file later in run method.

Now next step to read the data from uploaded excel file:-

Public void run()

{

System.Byte[] byteArray;

System.IO.Stream stream;

try

{

stream = File::UseFileFromURL(fileUrl);

this. readExcelData(stream);

//info('Done');

}

catch(Exception::Error)

{

info(strFmt('%1 %2',Exception::Error,fileUrl));

}

}

File class has been used to read excel data from the url (file location from server) and will return the data in stream. Now the task is to read excel Stream data in AX.

So to read stream data of excel file Microsoft have used ExcelPackage (EPPlus ), we can manipulate the file using this package (Create excel,create new worksheet , pivot table , design ,formatting etc.). Developer can access the classes of EPPlus using OfficeOpenXml namespace. the below classes is responsible to read the data from stream.

OfficeOpenXml.ExcelWorksheet;

OfficeOpenXml.ExcelPackage;

And below is the method which is responsible to read the data from stream into container

public container readExcelData(System.IO.Stream _stream)

{

OfficeOpenXml.ExcelWorksheet _worksheet;

OfficeOpenXml.ExcelPackage package = new OfficeOpenXml.ExcelPackage(_stream);

Audible books that make you smarter lyrics. int iRowCount,iCellCount;

anytype anyData;

try

{

if(package)

{

_worksheet = package.get_Workbook().get_Worksheets().Copy('Sheet1','Journal');

var cells = _worksheet.get_Cells();

iRowCount = _worksheet.get_Dimension().get_End().get_Row();

iCellCount = _worksheet.get_Dimension().get_End().get_Column();

for (int i=2;i<=iRowCount;i++)

{

conRow = conNull();

for (int j=1;j<=iCellCount;j++)

{

anyData= cells.get_Item(i, j).get_Value();

if(!anyData && j 1)

break;

if(anyData)

conRow += anyData;

else

conRow += ';

}

if(conRow)

{

conRow += iRowCount;

conData = conIns(conData,i,conRow);

}

}

info(con2Str(conData));

}

}

catch (Exception::CLRError)

{

throw error('@SYS135884');

}

return conData;

}

ExcelPackage class is responsible to create excel package from stream (can be file also in place of Stream), ExcelWorksheet class where the worksheet has been copied with data. And then looping the data of rows from the excel cells.

conData container variable declared in class declaration of class. So now enjoy the excel import in AX.

Note:-Import the data via using CSV or text file still easy and we can use CommaIo or Comma7Io.

For any query kindly let me know.

The two types of unmapped fields are computed and virtual. Unmapped fields always support read actions, but the feature specification might not require any development effort to support write actions.Computed field. Value is generated by an SQL view computed column. During read, data is computed by SQL and is fetched directly from the view.

For writes, custom X code must parse the input value and then write the parsed values to the regular fields of the data entity. In this example, you add a computed field to the FMCustomerEntity entity. For reads, the field combines the name and address of the customer into a nice format. In this example, you add a virtual field to the FMCustomerEntity entity. This field displays the full name as a combination of the last name and first name. X code generates the combined value.

In the designer for the FMCustomerEntity entity, right-click the Fields node, and then click New  String Unmapped Field. In the properties pane for the unmapped field, set the Name property to FullName. Set the Is Computed Field property to No. Notice that you leave the DataEntityView Method empty. In the FMCustomerEntity designer, right-click the Methods node, and then click Override postLoad. Your X code in this method will generate the values for the virtual field.

Imagine that an external system sends the name of a person as a compound value that combines the last and first names in one field that comes into our system. However, our system stores the last and first names separately.

For this scenario, you can use the FullName virtual field that you created. In this example, the major addition is an override of the mapEntityToDataSourcemethod. In the designer for the FMCustomerEntity, right-click the Methods node, and then click Override  mapEntityToDataSource.