cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
HSheild
Level 8

Use Flow to integrate CSV data with the Common Data Service (or Dynamics 365)

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

 

The Scenario

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.

  1. The source system drops a CSV file into cloud file storage. This example uses SharePoint. However, OneDrive, DropBox and Google Drive connectors could also be used.
  2. The CSV file dropped into SharePoint triggers Flow to run.
  3. Flow picks up the CSV file, processes it and updates the data into Dynamics 365.

Dynamics 365 CSV Integration with Microsoft Flow Animation

The screenshot below shows the Billing section on the Account form and the fields that will be updated from the CSV file, via Flow.

 

Billing section of the Account form in Dynamics 365Billing section of the Account form in Dynamics 365

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.

FieldType
Account NumberText
CurrencyLookup
Credit LimitCurrency
Payment TermsOption Set
Credit HoldTwo Option Set

 

Sample CSV file to integrate into Dynamics 365
Sample CSV file of billing details to integrate into Dynamics 365.
 

Document Location

A document location was set up in SharePoint to hold the CSV file. This is the location that the Flow trigger monitors.

CSV file on SharePointCSV File on SharePoint.
 

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.

 

Important

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.

"@split(outputs('CSV_Data'),'\r\n')"

 

Flow compose actionsFlow Compose actions to get the CSV data from the file.

The output from the Array of Rows looks like the following.

Flow -Array of Rows output

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.

Initialise Flow Variables4. 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…

skip(outputs('Array_of_Rows'),1)

 

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…

"AF3HN2S4,AUD,10000,Net 45,No"

 

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.

Transform data and update Dynamics 365 Flow Scope actionFlow 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.

CSV data transform Flow actionsFlow 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 ValueMatching Dynamics 365 Value
Net 301
2% 10, Net 302
Net 453
Net 604

 

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.

split(items('Apply_to_each_CSV_row'),',')[3]

 

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.

"AF3HN2S4,AUD,10000,Net 45,No"

 

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.

split(items('Apply_to_each_CSV_row'),',')[4]

 

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.

Flow - Set Currency IDSetting 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'),',')[1]'

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.

empty(body('List_Currency_records')?['value'])

 

Flow - check Condition that the Currency record exists

 

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.

Flow - update Account in Dynamics 365Find 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'),',')[0] '

 

 

Again, similarly to the Currency lookup, Flow checks that the Account record is found. The expression used is…

empty(body('List_Account_records_with_matching_Account_Number')?['value'])

 

Flow - check that the account exists in Dynamics 365

 

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.

split(items('Apply_to_each_CSV_row'),',')[2]

 

Update the Account record in Dynamics 365.

 

And we are done!

Overview Microsoft Flow – CSV to Dynamics 365Overview Microsoft Flow CSV Dynamics 365 integration Overview of the whole Flow.
Numbers correspond to the headings in the steps above.

 

Conclusion

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.

 

 

Helpful resources

Announcements
firstImage

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Users Online
Currently online: 296 members 6,238 guests
Please welcome our newest community members: