Agile leadership and more

Microsoft Flow: Insert data into list from a csv file

Sven Sommerfeld -

Exchanging data between two systems typically requires some scripting to pull the data from one system and push it into the other one. A lot of times the target platform is Sharepoint in order to e.g. display sales information to everyone in an aggregated level even though there is no access to the CRM.

In my case I am using this mechanism to bring data into Sharepoint lists which are then the basis to PowerBI reports. There are way better ways of achieving this (like direct connections, using PowerBI online or PowerBI datasets) but given the fact that I want to share the reports with non-technical staff and also wanted to try it for free first, this was the best option to go ahead.

Microsoft Flow is an easy mechanism on how you can insert data extracted as a csv file into Sharepoint.

It will make sense that you read my blog post about deleting items of a Sharepoint list first as it will be refered to later.

Preparing the list

For this example I am using the same list as in my blog post to deleting items of a Sharepoint list.

It is a very simple list containing two elements:

  • Title: Single line of text
  • Name: Single line of text

The name of the list is test_list.

Preparing the csv file

The csv file matches the test_list specification and contains two columns: Title and Name

The sample demo looks like this:

Title,Name
Ab0.429609662057675,Ab0.911200537825853
Ab0.968489198446414,Ab0.000603728323921393
Ab0.269730270909648,Ab0.792652234382984
Ab0.505160226882183,Ab0.0317935790002092
Ab0.0326685450534869,Ab0.291614329847159
Ab0.0985046000392541,Ab0.362178783921398
Ab0.35449963899083,Ab0.936765917062308
Ab0.256731550622817,Ab0.576326180434059
Ab0.817304176397695,Ab0.174424364452494
Ab0.0424408010541958,Ab0.413404022607951
Ab0.725289471254771,Ab0.22072782471442

You can download the sample file here.

Let's get it started: Defining the flow

The final which will be defined looks like this:

In the picture you do not see the trigger. For the sake of this example you can set a manual trigger. My recomendation for this is to set a file content modified trigger. This way whenever the content of the file changes, it will update the Sharepoint list.

Let's break it down step by step and see what is going on:

  1. Get items Items of list: At first the whole list should be emptied, which means you have to delete each item. This step is to get all items of the list.
  2. Apply to each Delete all items in list: This steps loops over all items of the list and deletes them.
  3. Get file content: Here the contents of the csv file are loaded.
  4. Initialize variable LINE_BREAK: Initializer of the variable LINE_BREAK with a, you guess it, line break. This is needed later to split the csv file as you cannot enter a line break in the split() function.
  5. Initialize variable ALL_ROWS: Initializer for the variable ALL_ROWS which is holding all rows of the csv file as an array.
  6. Initialize variable ITERATOR: Initializer for the variable ITERATOR which is needed later to iterate over the ALL_ROWS array. Initial vlaue is 0.
  7. Initialize variable CURRENT_ROW: Initializer for the variable CURRENT_ROW which will hold the array of the currently worked on row in the later process.
  8. Apply to each Insert data: Insert the data of the csv file into the Sharepoint list.

Next I will break these steps down into detail for you so that you have all the information to replicate it.

Get file content

The get file content is pretty straight forward. You only have to define the file you want to load. In my case I am using a One Drive folder to load the file.

Initialize variable LINE_BREAK

The LINE_BREAK variable is initialized with a line break (enter, CRLF)

Initialize variable ALL_ROWS

The variable ALL_ROWS is initialized with the array of the rown. The function formula is split(outputs('Get_file_content')?['body'],variables('LINE_BREAK'))

Initialize variable ITERATOR & CURRENT_ROW

The variable ITERATOR is initialised as an integer with the value 0 and CURRENT_ROW is an empty array.

Apply to each Insert data

This section is becoming more interesting, as here the magic of the insert happens.

Let's also break this down step by step.

  1. The Apply to each takes ALL_ROWS as variable to loop over
  1. As next steps the CURRENT_ROW is set with the array of the current row. The function formula for this is split(variables('ALL_ROWS')[variables('ITERATOR')],',')

3. Then a Condition is addd to check if the flow is working with the header (ITERATOR = 0) or values. The condition is ITERATOR is not equal to 0.

4. If the ITERATOR is 0 (which means the IF statement returns false), simply do nothing as nothing has to be done with the header

5. If the ITERATOR is not 0 (which means the IF statement returns true), an Create Item executed with the data to be filled in the list.

After selecting yiur site address and the list name, you have to key in a formula for each column of the list. Since CURRENT_ROW is an array containing all the data you can access it using the array selector [].

For the title the formula is variables('CURRENT_ROW')[0] and for the Name it is variables('CURRENT_ROW')[1]. If you have more data you now go on and on with this.

Last but not least you have to increment the ITERATOR as otherwise you store the same data everywhere.

That's it. Now you can save and test your flow.

Hope this was hopeful for you and of course feedback is more than welcome.