Decoding Flat Files with Azure Logic Apps Standard

Decoding Flat Files in Azure Logic Apps (Consumption) is nothing new, but they do require you to have an Integration Account to store your schema and transformation (map) files. With the introduction of Azure Logic Apps (Standard) we now have an option to include these artifacts within our solution without the need for an Integration Account.

If we create a new Azure Logic Apps Standard project within VS Code, we will see a new folder called Artifacts. Within this folder we will have separate sub-folders for our Maps and Schemas. We now need to copy the appropriate artifact into the correct folder.

If we are using the Azure Portal to build our logic apps (Standard), we have an upload experience available to us.

Scenario

Let’s build out a scenario to describe how we can leverage the new decoding abilities inside of Logic Apps (Standard). In our scenario, we are going to retrieve a positional flat file from Blob storage that contains 1 or more employee records. We will then take this file and send it to our Flat File Decoding action. From there, we will have an XML document but we want to convert it to JSON so that we can more easily parse it and then create a record in Dataverse for each employee record.

Artifacts

In this post, we are not going to cover how to create a positional flat file. Instead we will leverage our good friend Sandro Pereira’s sample that he has on GitHub. The samples that he has provided come from a BizTalk Project but do work with our solution.

Note: The sample CSV files that Sandro has provided need to be saved in a tool like Notepad ++ to ensure the end of line is recorded as a Windows (CRLF) format.

Solution

We will start with our trigger and use the When a blob is added (properties only) trigger. Since this will only emit the meta-data for the file, we will follow up by including Get blob content (V2) action. Within our Get blog content action, we can link our Blob property to the List of File Paths dynamic content returned from our trigger.

At this point, we have a logic app that has received our flat file message from Azure Blob Storage. We now need to convert this flat file to an XML instance and can do so by using the built-in action called Flat File Decoding where we will pass in our File Content from our previous action. In addition, we can select our schema and assign it to the Name property.

Add a temporary Compose action and then add an expression to it that will convert the output of the Flat File Decoding and pass it through a json() expression. This will convert the XML into JSON which will be used in next step. The expression that we need to add to the Input property is: json(outputs('Flat_File_Decoding')?['body']).

At this point, you can run your logic app and capture the output of the Compose action. We will use it in the next step when we need a sample for our Parse JSON action.

Add a Parse JSON action to your logic app. Use the output from our previous step to Use sample payload to generate schema. We will use the same json(outputs('Flat_File_Decoding')?['body']) expression that we used in the previous step.

We now have a JSON message that we can easily loop through in a For each action. Within our file, we will loop on the Employee record.

Now we will go ahead and insert each row into a customer table inside of Dataverse.

Conclusion

In this post, we learned about how we can use Azure Logic Apps (Standard) to decode a flat file without the need for an Integration Account.