Business Central CSV export using Power Automate

A CSV export with Business Central (or NAV previously) is nothing new or exciting. Why blog about it then? Well let’s tackle the topic using a different solution. This is a CSV export using Power Automate with Business Central data. It could be argued that providing you have the relevant licensing this method would stop use needing 3rd party tools.

My scenario will be basic but the application is broad. I’ll use a web service (API page or query is fine too) to expose item data showing some fields. I’ll filter the web service to keep my results condensed and only select the fields I need. The goal: retrieve the required data from BC and create a CSV file on a recurring basis. The file should be added to a chosen storage location.

Power Automate has a recurring type flow which is suitable for this scenario. To ensure better error trapping and subsequent actions I am making use of the “Scope” feature in my flow. I will then add the actions I need. Two more scopes take place after and will only run if the 1st scope succeeds or fails – 1 scope for each possible outcome.

The HTTP call in my case is using the Item Card, but this is adaptable to other data. The main thing I found useful here is the ability to use OData filtering to limit the results. Example of the url and the filtering and the ability to select specific fields: https://api.businesscentral.dynamics.com/v2.0/<tenantID>/Production/ODataV4/Company(<companyName&gt;)/Item_Card?$filter=Inventory%20gt%200&$select=No,Description,Unit_Price,Item_Category_Code,Inventory

I also liked using a header in the HTTP request of “Accept” which has this as the value: application/json OData.metadata=none. It’s not terribly exciting but when you intension is to read data only (I don’t want to insert or modify) it removes some of the data you won’t be using like the etag (typically used for tracking modifications). I’d say the majority of the work in this entire process is with the BC data endpoint. You might find a custom api page is needed. For example, to get all the fields you want from each of the tables you need them from.

Most of what I’ve covered thus far is fairly normal in Power Automate. The worthwhile part of this post comes in explaining the use of a compose action – which from the picture is titled as “Array for CSV”. Why do we need it? Well the action that follows for creating a CSV table (standard feature in Power Automate) expects an array. Now in the case of BC it will typically output a JSON object. Without going into extensive detail on json objects or arrays just think about the fact you need the part in between [ ] (square brackets). A simple expression takes care of converting the JSON object you get from BC to an array, which can be converted to a CSV. Create a compose action and add expression body([‘Parse_JSON’]?[‘value’]). This will extract just the array part which is typically titled as “value” when we retrieve data from a BC web service. Example of this can be seen below:

{
    "value": [
        {
            "No": "150A4444A",
            "Description": "ProBook 450",
            "Item_Category_Code": "LAPTOP",
            "Inventory": 22,
            "Unit_Price": 900
        }
   ]
}

The remainder of the flow is super simple. I’ve used another scope block and adjusted the settings of it to run only if the first BC data block was successful. Use a built in “Create CSV table” action and take the output of the earlier compose action with the array you need – this function only accepts an array. Then you can choose where the file should output to. I chose to create a SharePoint file – ensure you put .CSV at the end of the file name

This is a flexible solution where you can quickly have automated or on demand CSV exports working for your solution. It is much more rapid than the full BC method which would involved job queue tasks. You can of course layer in better notifications as well. This is why I opted for the Scope feature where you can alter the “Configure run after” setting for a previous action.