This Flow recipe provides an example of how to integrate CSV data into the Common Data Service (or Dynamics 365).
This is an excerpt from the original blog post Microsoft Flow: CSV Integration with Dynamics 365 (Example)
The recipe covers the following steps
For this scenario, a source system needs to provide billing data to Account records in Dynamics 365. This enables the sales reps to have a more complete view of the customer record and empowers them to discuss credit limit or payment terms while visiting the customer.
The integration process is as follows.
The screenshot below shows the Billing section on the Account form and the fields that will be updated from the CSV file, via Flow.
Set Up CSV File
The data has been deliberately chosen for this example as it shows five different Dynamics 365 data types. Each one needs to be handled differently by Flow.
|Payment Terms||Option Set|
|Credit Hold||Two Option Set|
A document location was set up in SharePoint to hold the CSV file. This is the location that the Flow trigger monitors.
Build The Flow 1. Trigger
The Flow starts with SharePoint’s When a file is created in a folder trigger. This means that when a new file is created in a specific folder the Flow will start runnin.
However, other triggers for the SharePoint connector could be used, such as When a file is created or modified in a folder. The trigger you choose depends on how the source system delivers the CSV file.
2. Extract Data From CSV
After the trigger, the data is extracted from the CSV file into a format that can be processed easily by Flow. This is done with two Compose actions. The first one extracts the text from the CSV file and the second puts it into an array of rows using the split function.
There is an issue with Flow when working with the split expression using the new line character (‘\r\n’). For some reason you cannot use the Expression builder but must type the expression by hand into the Input box. See the example screenshot for the Array of Rows compose action below. It must include the double quotes at the start and end. Thanks to Serge Luca for this great post which points out the solution to this issue.
Flow Compose actions to get the CSV data from the file.
The output from the Array of Rows looks like the following.
It would be nice to be able to parse a CSV file more easily. There is a Flow Ideas post here . Vote if you want this feature added to Microsoft Flow.
3. Initialise Variables
Variables are initialised at this stage. This is because the string data types in the CSV do not match the data types of the fields in Dynamics 365.
4. Loop Through The Rows
A. An Apply to each action is used to loop through the rows of the CSV data. The input to the Apply to each action uses the skip function to skip over the first row of CSV data, which is the header. The expression used is…
B. Flow uses a Condition action to check that the CSV data row contains data. I found that Flow seems to think there is an empty row at the end of the CSV data. This causes the Flow to fail.
If the Condition action results in Yes then Current Item will contain a value that is one row of the CSV file. It will look like…
C. I like to use Scope actions to group Flow actions. They are not mandatory but they are good at keeping the Flow clean and add more commentary about what the Flow is doing.
Flow components held within the Scope action.
5. Transform Data
As discussed in a previous post, Dynamics 365 Integration with Microsoft Flow, the different Dynamics 365 field types need to be handled correctly. This means that the data must be transformed from the CSV string values into data types that match Dynamics 365.
The Payment Terms option set, Credit Hold two option set and Currency lookup all have to be transformed from their string value.
Flow Actions to transform the CSV data into the correct format for Dynamics 365
5.a. Payment Terms Option Set
The Payment Terms column in the CSV data is a string value. The Payment Terms attribute in Dynamics 365 requires an integer value. The following table defines the mappings.
|CSV Value||Matching Dynamics 365 Value|
|2% 10, Net 30||2|
A Switch control is used to implement this transformation in Flow, with a case statement for each of the possible Payment Term values. The input to the Switch control is an expression with includes a split function to get the Payment Terms text from the CSV data.
Basically, the split function splits the text by “,” to produce an array of strings, and then returns the 4th string in the array. Note that the first item in the array is 0, hence the value of 3 being used for the array item.
For example, Net 45 is the value that the split function above would return from the CSV row below.
Example of the Switch action configuration for Payment Term.
5.b. Credit Hold Two Option Set
The Credit Hold column in the CSV data is a string value which is either “Yes” or “No”. The Credit Hold attribute in Dynamics 365 requires a Boolean value (true or false).
Again, a Switch control is used to implement this transformation in Flow, with a case statement for each of the possible Credit Hold values. The input to the Switch control is also an expression with includes a split function to get the Credit Hold text from the CSV data.
Example of the Switch action configuration for Credit Hold.
It is important to handle the Default case of the Switch action. This occurs when the input value is neither “Yes”, or “No”, in this instance. This means that the CSV data is not what is expected and an error should be logged and a notification sent to an administrator to resolve.
5.c. Currency Lookup
The Currency column in the CSV data is a string value which represents the currency code. The Currency attribute in Dynamics 365 is a lookup field to a Currency record. Flow must look up the Currency record ID (GUID) from Dynamics 365 in order to set the Currency look up field on the Account entity.
I found the post Alternative approach to check for a single Dynamics record in Flow helpful in getting a single record from Dynamics 365 without knowing the Dynamics 365 GUID. However, the method below is slightly different.
Setting the Currency ID
A. Flow first uses the List Records action from the Common Data Service connector. Note that the Dynamics 365 connector is deprecated and the Common Data Service connector should be used to connect Flow to Dynamics 365.
The currency code is stored in the ISO Currency Code field on the Currency entity in Dynamics 365. Therefore the Filter Query used by Flow to look up the Currency record via the Currency Code is as follows.
isocurrencycode eq 'split(items('Apply_to_each_CSV_row'),',')'
The output of the List Currency records step is a list of Dynamics 365 Currency records. However, the list should only ever contain one record.
B. Flow then determines whether a Currency record is found using an expression that checks whether the output list is empty or not.
C. If a Currency record was found in Dynamics 365 then Flow uses the Apply to each action to loop through the returned Currency records. Of course, there should only be one Currency record returned.
Setting the Currency ID variable.
6. Update Dynamics 365
The data input from the CSV file is now transformed into a format which Dynamics 365 understands. Following this, the Account records in Dynamics 365 can be updated with the Billing data.
Find the matching Account in Dynamics 365 and update.
Flow first looks up the Account record in Dynamics 365 based on the Account Number in the CSV data. Similar to the Currency record look up, using the List records action for the Common Data Service connector. The Filter Query used is…
accountnumber eq 'split(items('Apply_to_each_CSV_row'),',') '
Again, similarly to the Currency lookup, Flow checks that the Account record is found. The expression used is…
If a matching Account record is found in Dynamics 365 then Flow uses the Apply to each action to loop through the returned Account records. Of course, there should only be one Account record returned.
The variables that were initiated and populated earlier in the Flow are used to update the Account. Except for the Credit Limit. The Credit Limit is already in a format that Dynamics 365 can understand (a number) so it does not need to be transformed. Therefore, the Credit Limit is set straight from the CSV data using the following expression.
Update the Account record in Dynamics 365.
And we are done!
Overview Microsoft Flow – CSV to Dynamics 365 Overview of the whole Flow.
Numbers correspond to the headings in the steps above.
In the end this article became quite lengthy to explain what should be a very simple integration. The integration is not perfect and integration specialists\perfectionists will see holes all through it. However, I do believe the Flow is a great integration tool for Dynamics 365 for certain scenarios where low cost and ease of build and maintenance are must have requirements. For instance, prototyping or non-business critical integrations etc.
In addition, check out a previous post Dynamics 365 Integration with Microsoft Flow for my thoughts on when Flow is an appropriate tool for integrations with Dynamics 365.
Fill out a quick form to claim your user group badge now!
We've given our badges an overhaul and also added some brand new ones!
Learn how to build the business apps that you need.
Find out where you can attend!
Watch & learn from the Power Automate Community Video Gallery!