Importing an Excel file hosted on an FTP server to FnO

Sometimes, among the customer’s requirements we find the use of an FTP server in which to host files for further processing in the ERP. When this use case occurs, there are three key points to analyze:

+The first would be to find a way to access the files on the FTP server.
+After obtaining the document, the second key point is to find a way to send it to the ERP.
+And finally, we must study how to process the Excel file in the system.

El objetivo de este artículo no es más que proponer un posible abordamiento para este caso de uso. El ejemplo consistirá en la importación de información relativa a una flota de vehículos. Un detalle a tener en cuenta de esta propuesta es que la mayor carga de trabajo la vamos a delegar al ERP.

To solve the first point that we find in this use case, we are going to use Power Automate, one of the Power Platform tools.

The most important feature of this tool is its great capability to integrate with other systems. This is possible thanks to the use of connectors. An example of a system that the Power Platform can integrate with would be an FTP server.

To connect to our server, the first thing we must do is configure the connection. To do this, we must enter the corresponding address, port, user, and password.

Once the connection is established, we can get the file we want through the actions of the connector. In our case, the document to be processed will be stored in a folder on the server.

After getting the document through the Power Automate FTP connector, we need to send it to our ERP system.
The information we receive through the connector consists of a JSON consisting of the content type and the Base64-encoded content. Since we know the type of file we are going to process, we only need to send the Base64 text string to Dynamics 365 F&O.

To send the content of the document we take advantage of the integration capacity that OData provides to our system. By using OData actions, we can execute logic on the target system adding parameters if necessary. For this use case, we send the Base64-encoded string as a parameter.

In order to use OData, we need a public Dynamics 365 F&O data entity.

And to be able to execute an OData action we need to create it in that entity.

Once the OData action is created in the target system, by using the Power Automate Dynamics 365 F&O connector, we can execute it by selecting the instance, action and parameters, if necessary.

The last key point of the use case consists of processing the document after receiving it through the OData action.

To carry out this task, we use the JATExcelHelper class (MSDyn365FO/JATExcelHelper.xml at master · jatomas/MSDyn365FO · GitHub) created by Juan Antonio Tomás (Technical Lead at Axazure and MVP in the BizzApps category) and publicly available in his GitHub repository. In the following link we can obtain information about how to use it: Generate and Import Excel files with X++ in #MSDyn365FO – Juan Antonio Tomás (jatomas.com). By using this class, we can convert a MemoryStream object to a Map collection.

The first step then is to transform the Base64-encoded string into a MemoryStream object. For this we use the BinData class to obtain a Byte Container and the Binary class to translate this Container into a MemoryStream.

Once we get the object we need from the OData action parameter, we use the JATExcelHelper class to convert it to a Map collection. This map will use the spreadsheet row number as key and Container objects with the values of the different columns of the corresponding row as value.

In this proposal, we go through the map by using its enumerator and create records from the values of each row of the Excel file.

The result can be observed in the form fed by the table from which we have created the new records.

I hope you find this article useful, and you can apply it in your future developments. See you next time! 😊