cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
schwibach
Resolver I
Resolver I

Create an array from excel with cells that contain comma separated values

Hello,

I would like to take this input from excel

schwibach_1-1631799997592.png

 

and create this output in a SP list

schwibach_2-1631800023801.png

 

1. manual trigger,

 

2. get rows from table

 

3. Apply to each (for every row)

 

4. ??? 

I know that 'Split' will give me an array from a string, and I can separate the content with commas.

But I don't know how to turn each of those elements into a "row" that reference in the 'Create items'-action. 

 

5.  Apply to each (for every  "e-mail"-"Klassen"-combination)

6. "Create Item" (Sharepoint) inside the "Apply to each loop" 

 

Thank you for any help you could give me to crack this.

 

Cheers,

 

Andreas

1 ACCEPTED SOLUTION

Accepted Solutions
ekarim2020
Super User
Super User

Here is one approach to the problem, where we read the rows from the Excel file:

ekarim2020_1-1631911118170.png

and create items in a SharePoint list:

ekarim2020_0-1631910976210.png

 

ekarim2020_4-1631911462028.png

 

ekarim2020_3-1631911400049.png

The split expression:

 

split(items('Apply_to_each')?['Klassen'],',')

 

Below, we read the first row from the Excel file, split the Klassen string in individual classes - which is stored as an array:

ekarim2020_5-1631912502953.png

Then for each class in the array (starting with 5a) we create a SharePoint list item record:

ekarim2020_6-1631912530990.png

ekarim2020_7-1631912582343.png

The Excel file is processed and the list items are created in SharePoint:

2021-09-17_22-20-40.gif

Hope this helps.

Ellis

View solution in original post

4 REPLIES 4
ekarim2020
Super User
Super User

Here is one approach to the problem, where we read the rows from the Excel file:

ekarim2020_1-1631911118170.png

and create items in a SharePoint list:

ekarim2020_0-1631910976210.png

 

ekarim2020_4-1631911462028.png

 

ekarim2020_3-1631911400049.png

The split expression:

 

split(items('Apply_to_each')?['Klassen'],',')

 

Below, we read the first row from the Excel file, split the Klassen string in individual classes - which is stored as an array:

ekarim2020_5-1631912502953.png

Then for each class in the array (starting with 5a) we create a SharePoint list item record:

ekarim2020_6-1631912530990.png

ekarim2020_7-1631912582343.png

The Excel file is processed and the list items are created in SharePoint:

2021-09-17_22-20-40.gif

Hope this helps.

Ellis

schwibach
Resolver I
Resolver I

I have one more question if I may.

How did you do the screenshot of all the fields displaying the code in Power Automate?

I can only show one by hovering over it and it goes away when I try to do a screenshot.

How do you display all of them and get to do a screenshot? That would be immensely helpful.

ekarim2020
Super User
Super User

Ahh! That's my secret!

I'm actually planning to provide some staff training on the Power Platform and for that I will need to produce some visual training materials. Visuals really help people to understand, absorb and retain the information - especially if Power Automate is something you only need to work with occasionally. 

 

I have been experimenting with some simple tools and image editing software: PowerPoint, SnagIT and an image editor (eg. Paint.Net). Answering queries here on this platform helps me learn and practice new skills too.

 

Capturing the hover-over text is a problem and there is no magic solution.  I hover over an action, capture the hover-over text (Print Screen or use SnagIT), then paste it into the image editor and crop, paste it into the main image of the flow. There is no easy way to do this. SnagIT was sometimes able to capture the hover-over text - but it's not very reliable for this. So for every hover over text, you will need to capture them one by one.

 

Ellis

Thank you for the explanation.

And anothe thank you for taking the time to copy the screenshots together. It really is very helpful. 

Helpful resources

Announcements
MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

PA Survey Carousel Image.png

We want to hear from you!

If you are a small business ISV/Reseller, share your thoughts with our research team.

Users online (1,207)