How to Import CSV Data to SharePoint List with Power Automate | Tutorial
ฝัง
- เผยแพร่เมื่อ 5 ส.ค. 2024
- Are you tired of manually importing data from CSV files into your SharePoint List? Look no further! In this comprehensive step-by-step tutorial video, I'll guide you through the seamless process of effortlessly importing your CSV data into your SharePoint List using the incredible power of Power Automate flows.
🚀 What You'll Learn:
📂 How to Import CSV data directly into your SharePoint List in a matter of minutes.
📥 Trigger instant cloud flows to quickly upload CSV files and get started.
🔄 Transform CSV data into strings, arrays, and parse through JSON effortlessly.
🧹 Efficiently filter data from your CSV files to import only what you need.
🌟 Supercharge your data import by handling empty values + tips on how to speed up the process.
➡️ Harness the full potential of expressions for maximum effectiveness.
📂 I'll take it a step further and show you how to import data from multiple CSV files in one go to SharePoint Lists, saving you time and effort.
Say goodbye to tedious manual data import tasks and automate your data import process with ease. Dive into the video now and unlock the potential of Power Automate for SharePoint List data import.
⬇️ Download the CSV Files, SharePoint list schema & Power Automate flows showcased in the video:
Exclusive for Channel Members (Silver 🥈 & Gold 🥇 Club)
Find the download link in the Community Tab for Members.
To become a member, click here: th-cam.com/users/rezadorranijoin
🔗 Add & Update Excel Data to SharePoint List using Power Automate | Excel Import using flow
• Add & Update Excel Dat...
#PowerAutomate #SharePoint #CSV #Excel #PowerPlatform
Table of Contents:
00:00 - Introduction to importing CSV file data into a SharePoint / Microsoft List with Power Automate flows
00:33 - Scenario: Import CSV to SharePoint List
01:06 - Import CSV data quickly into SharePoint List (out of the box feature)
05:35 - Use Power Automate to import CSV data to SharePoint List
09:55 - Filter data in CSV file with Power Automate
13:18 - Add Filter Array data JSON output (CSV) to SharePoint List - Import into text, number, choice, date columns
16:54 - Speed up the data import process in flows with apply to each loop pagination
17:27 - Import Person (Email) data from CSV to SharePoint list
17:59 - Import data to Multi Choice SharePoint Column
20:37 - Handle empty data scenarios in CSV file
24:02 - How to Import data rows from multiple CSV files to SharePoint list
25:28 - READY to subscribe to Reza Dorrani’s channel
🤝 Let’s connect on social:
🔗 Reza Dorrani LinkedIn: / rezadorrani
🐦 Reza Dorrani Twitter: / rezadorrani - วิทยาศาสตร์และเทคโนโลยี
This demo is awesome🤘! It shows me how to import CSV data into SharePoint List with Power Automate flows in a very clear and easy way. I love 😍 how the video explains every step of the process, from triggering the flow to filtering the data and using expressions. The tips on how to handle empty values and speed up the process are very helpful too. Error handling FTW. I also appreciate how the video covers importing multiple CSV files at once, which is something I always wanted to learn. This demo is very well structured and informative, thank you 🙏 for sharing it!
Wow! So happy to hear the appreciation for each and every facet of the video 💕
Thank You so much. Im glad to hear that error handling part was useful.
I truly appreciate the way you explain this, step by step, in a way that I can understand and troubleshoot my flow. This is incredibly helpful; I learned something today. You are an excellent teacher, Reza!
Thanks so much
Very common use case and very well demonstrated with exception handling. Take a bow Reza 🎉
Thank you so much. Glad to hear the video covers a common scenario.
i am so glad to be a subscriber of you, really it means a lot for me, whenever i struck somewhere on my work, firstly i refer your videos, and for sure i get clarity and complete my work. really love the way you create your videos with easy explanations and live runs. thank you so much for your efforts to share your knowledge to others.😍
Thank You so much for your kind words 🙏
Thanks so much! This was clear, concise, and very helpful. I especially appreciate you structuring the exercise in such a way that we check and understand the output of each step. Thanks!
Glad it was helpful! Thanks so much for watching.
What an amazing video! As always Reza picks up real practical scenarios. This one had so many e.g. within CSV import. Gives such clarity and sense of direction for people like me. Wish to keep learning more from you!! Best wishes!!!!
Glad you liked it! Thanks so much
This basic tutorial is one of the best learnings on Power Automate fundamentals I have watched. No better way to spend a hot Saturday night in July on the porch than watching Reza open my mind to new flow possibilities.
Wow! That’s some way to spend a Saturday night 😎
Great content sir. Real time scenarios covered in very less time. Thanks for sharing. Keep giving us incredible content.
❤ Many of my long standing issues got resolved ❤
Thank you Reza.
Most welcome!
I will be honest I had no intentions of making this video, but the number of requests I received for this video surprised me. I am glad that its helping many.
Very informative video Reza. Appreciate for all your hard work in making the videos which are very informative and explained in wonderful way.
Thanks so much
Thank you so much. Your video saved me from a big storm actually. Thanks from big heart❤
You're most welcome 😊
Really like your videos and appreciate your dedication to helping people. Keep up the good work and don't worry about the haters!
Thanks so much.
I only worry about maintaining the quality of my content. Haters if any are ignored.
Always learn something new from Reza's video even if you have obtained the techniques from his previous videos!
Glad to hear that! Thanks for watching.
Explained extremely well 👏 👌 There are so many gold nuggets of knowledge I'm going to map it out for myself. Remove empty strings , Remove header row, filter array to add only open, split, split, replace, so much goodness 😁
So glad to hear this! I was hoping folks realize this video is a lot more than just importing data.
Thanks so much 😊
I literally just joined as a silver member to your TH-cam channel. I have never in the history of TH-cam ever enjoyed learning from a channel so much in my life. You are the only TH-cam channel I have ever signed up for a membership to. That's how much I believe in you and your teachings.
@@datadragyn Thank You so much 🙏 for the support & welcome to Memberships.
I will keep trying my best to produce the best content possible.
Another great tutorial. Thanks mate :)
Most welcome
This is AWESOME! thank you for sharing
You are so welcome!
Fantastic!! You're a star 🌟
Wow! Thank You 🤩
Great tutorial Reza !!!
Thank You
Awesome video as always, Thank you :)
Glad you enjoyed it!
Thanks this was very helpful. I was getting a
that was messing things up for me.
I resolved by switching the Split Array Expression to skip(split(outputs('CSVData'),decodeUriComponent('%0D%0A')),1) which removed the
and
This also allowed me to get rid of the "New Line" step.
Great
Thank you both @RezaDorrani and @Dustin.Stubbs😊
Thank you so much, I fight with the problem a long time.
You are my savior!
Amazing video.. Thanks Reza.🎉❤
You're welcome 😊
Very usefull video ❤
Thanks man
Glad it helped
Very informative session, thanks for sharing
Most welcome! Thanks for watching 👍
It’s really helpful, thank you so much sir❤
Most welcome!
Thank you for sharing Reza 🙂
Thanks for watching!
Awesome!!!
Thanks
Good content Reza👍
Thanks
Excellent!!
Thank you! Cheers!
fantastic array! i like array.
😁
Arrays for the win 🥇
Hi Reza, needless to say, this is absolutely remarkable! Thank you very much - I literally watch as many videos from you as I can and I have to say, you honestly do an incredible job of explaining everything!
Btw, I have a (stupid) question, if you don't mind :)
What is the difference between Compose and Initialize/Set variable? Variables are always my pain points because I am just starting out with these tools...
Thank you again for the great work!
Many thanks for the appreciation! So happy to hear the feedback.
I mean this in a good way - "No question is stupid. Not asking questions is stupid"
Compose - Treat it as a static variable. Its value cannot be changed later in flow. Also, a compose action will dynamically set the data type based on the value provided.
Variable - Must be initialized and is strongly typed (must define a data type). They can be changed later in the flow.
I have done a video on variable as well :)
Hey Reza,
Thanks a lot!
Welcome!
Great 🎉
Thanks
Good Job!
Thank you!
That is really great, I had a similar use case recently! Just a small piece of advice for the use of concurrency: I had a large batch of data to import (10K plus lines), but I need to implement some data transformation for a couple of fields from my csv. After a couple of tests, I realized that some SharePoint fields had the wrong value, and these are actually mixed values from other lines in my spreadsheet! When I ran the same tests without concurrency, all went well. I'm guessing (and this is a very wild guess) that the use of concurrency somehow makes Power Automate keep some values from the transformation action between transactions. So, I ended up by decoupling my flow into a child flow that receives some smaller batches of data to work with the data transformation and SharePoint insertion, and just returns to the main flow a list of items created with success or any errors that might occur with the insertion. So, my parent flow runs with concurrency, while the child flow runs without it. But here also I understand that, for this scenario, perhaps Power Automate is not a good fit, but it worked at the end of the day.
Thanks for sharing your scenario.
Only known limitation with concurrency is using variables within it.
@@RezaDorrani yes, but I used some compose actions to create work with my data transformations, but still got a similar behavior. But still going to look forward, this project is not yet over! :) Thanks for always sharing your knowledge and findings!
Thanks reza.
Most welcome
AMAZINGGGGGGG……..
Thanks 🙏
that you so much
Most welcome
Your content is extremely well done. This guide was exactly what I was looking for and very easy to follow. Thank you!
I only have one question about your description column. How would you handle change the flow if someone had entered comma's in that field?
You're very welcome!
Issue is with commas. You would need to ensure the data does not contain commas. I showed that in video, where prior to importing the data, I do replace all commas with spaces.
@@RezaDorrani awesome, thanks for the quick response!
Thank you Reza. I had a similar project use case but users used Power Apps to upload their documents
Great 👍
can you plz share how can i used in Powe Apps
@@programsinfo8974 This video is specific to flows. I would recommend posting your query on forums at powerusers.microsoft.com
@Reza, you are great as always, I've developed similar to this, with some more additional features like taking a dump of a parent SP list in CSV and then creating a new list in the same site with different list name.
But at one place I got struck, while exporting the data to CSV through power automate, what would be the max Row count? And could that be bypassed like how we have option to increase the threshold value?
Great!
This video is about importing data into SP and not exporting.
I have done videos around exporting as well. You would have to paginate through your records to export. Flow is not an ETL tool though. Performance would be an impact for large data sets.
Reza, first of all, thank you for all your videos, we all learn a lot from you!
One question, in my case I have a Description column and inside of it, the data has a lot of break lines, how can I handle with this break lines inside a column to not split as a new row?
Thanks in advance!
Not sure how to handle commas and break lines. As shown in video, I ask to manuallu clean up the file prior to importing so it does not have those special characters.
HI Reza, this is just great content, Love it!
One question on a common scenario: How can I make sure to check if an item (based on an ID) is already present in my sharepoint list and thus skip it ?
I have done a video on excel import to SharePoint. There I have shown adding or updating items. Same logic applies here.
Thank you for this!! Your video is always so informative and concise. Once the CSV file is imported, is there a way to keep the file updated with any new updates? would it be the same as how you update excel files?
It is a one time import. It does not keep info current.
Great tutorial as usual. We recently had to deal with similar use case.
But the problem we faced is there were line breakers and comma's in the data itself and Power Automate considered the data/text after the line breaker as a new row and comma in single column was considered as multiple columns which completely changed the structure and we had to follow a different approach.
Most welcome!
I did call out in video about scenarios where data row contains comma in video and how one must replace comma character with a different delimiter prior to importing.
@@RezaDorrani It is not always straight forward that you can just use a different delimiter; especially dealing with customer data. Let's say you have a process which the customer is uploading the file, via a Power App, then they would have to be instructed to use a different delimiter and then given the knowledge on how to do that. Believe me, this has been a pain, we've been down this road. It really surprises me that Microsoft has not provided a CSV Parser "step" for Power Automate.
Great content in this video, thanks!
@@hobbymanlp One can always get the file from customer. Run some form of office scripts ot PAD over it and replace your delimiter and then work with data.
The first technique shown in video works with comma in data as well I believe. The direct import to SP list.
Great video , demo was very Awesome, please make same video on Excel also
I have already done videos with excel. Check out the videos on my channel.
@@RezaDorrani thank you
Hi @Reza Dorrani, how would i achieve this using an automated trigger. When i used your manual upload approach it works perfectly. When i use: 1) When a file is created(properties only), then 2) Get file content using path, then 3) Compose on the body['$Content'], it just returns another binary looking string instead of what it previously returned when i used your manual approach.
I do show automated trigger in video.
Video description has link to download all the flows (members only - check description for details)
I have not come across the issue you have mentioned.
Hi, Reza. Thanks for this video! It helped me a lot. The only thing is that I'm facing some problems with special characters. Those letters or symbols appear as question marks. Is there anything I can do to solve this encoding problem?
Best would be to avoid those special characters. You may want to try and see if you could use replace function to remove them in flow.
Great video and thank you for the tutorial. Is there a way to schedule the job against a Network Drive? I want to avoid using my OneDrive in the future.
Welcome!
Im not sure about network drive as I have not tried that scenario
I had an issue with Lookup Columns. It looks like the create item action looks for the lookup column's item ID, instead of the Item Name. So I manually did a vlookup in Excel and manually changed the source csv file to replace the lookup column name, to the ID. And it worked. I am sure there is a smarter and better way to do it in PA.
As long as it worked thats good
Reza, again another outstanding video and incredibly easy to follow along with. I do have an additional question. What if your source was not a CSV file but rather 'Run a Query against a Dataset'? What modifications would you need to make at the beginning?
Thanks!
I would have to try it out to know what modifications we would have to make.
@@RezaDorrani If you could that would be outstanding. I know there is a limitation of 500 rows with the Run a query against a dataset; and I have workarounds for that aspect. Just having an issue getting the results from the Dataset query into an array so that Apply to each > Create Item will populate correctly.
@@user-sr9hq5er1d I make videos when I receive multiple requests on a topic. Every quarter I ask subscribers for topic suggestions on Community Tab of my channel. Most voted idea gets added to my backlog. Make sure to post this idea whenever I post the next topic suggestion post.
Hey Reza,
Great video again and it is really helping me with one of my solutions. But I got stuck in one place, what to do when the field also has comma in it. As in I have a summary field in my CSV which has commas in it.
Thats on the of the limitations of this approach as mentioned in video. Data cannot include commas.
Hi Rezza, thanks for the video. Excellent as usual. What can we do if we use the dd/mm/yyyy date format? I could edit them directly in excel but as I have multiple date columns and multiple tables to update this would be time consuming. My flow is failing with the message “…The runtime value '"20/09/2023"' to be converted doesn't have the expected format 'String/date'.” I’m sure there must be a way to change this programmatically but as, a relative newcomer to flows, I don’t have the skills for this.
Most welcome!
Fixing it in excel/csv would be ideal.
There are ways in flows using expressions. I do not have a video reference on it though. I will recommend checking on the forums at powerusers.microsoft.com/ in case someone has done something similar.
Hi, Reza. Thanks for this video! It helped me a lot. can you plz Explain code in PowerApps to Run
Most welcome.
This video has nothing to do with power apps :)
I recommend posting your query on the forums at powerusers.microsoft.com
Thank you for the video. Very understandable and clearly articulated. My concern is how to move an email with an attachment to various folders in a SharePoint library using Power Automate. We are dealing with 300 different segments (I have accordingly created folders in the library). When we receive emails for these segment-specific cases, we need to convert the emails into PDFs and save them in the respective folders with attachments. Since our subject is always segment-specific, I have been thinking about how to automate this process. Can you provide me with tips on how to achieve this?
Thanks for watching and liking the videos.
I do not have a video reference on this scenario and would have to try it out to provide guidance. I recommend checking or posting your query on forums in case someone has done something similar powerusers.microsoft.com
I usually include a check to verify the string of the first row headers ='s the columns I plan to ingest, then error out if it isn't there or has changed in any way.
Thats a good tip
Thanks for your extremely helpful video! I appreciate the step-by-step instructions and all the tips you provided. I have a question for you: If the SharePoint list has calculated columns, will those columns automatically be populated for each row created by the CSV import?
Most welcome!
I have not tested with calc columns but my guess would be yes.
@@RezaDorrani Thanks! I'll let you know my results when I test this over the next few weeks.
Yes, the calculation columns automatically populated for each row created by the CSV import!
Hey, I asked under another video for a similar solution but this seems to be exactly what I needed!
I have done so many videos now that I dont event remember them.
I cannot guess or evaluate expressions here on chat without trying them out.
Best to post your query on forums powerusers.microsoft.com
Great Video thanks! Question: I have a csv file I get once a week with new data that I want to replace all items in a SP List with. Essentially, I would need to delete 30k list items, the load 30k new ones from the new csv. Is there an easier way to do this than the way described in this video?
Thanks!
Im not aware of other options to do this specially with that volume of data.
Hi Reza, thank you for this! I have 2 questions:
1. for blank fields, what if the SP Column type is a hyperlink? I tried the if(equals(xxxx,blank but Im still getting the error
2. When I Compose Array, Im getting the following pattern: "\"Dec 2023\",\"123456\",\"NextColumn \",
- so quotes and backslash around each column, not a quote at the beginning and end of each line, like yours. this doesnt seem correct. any thoughts?
Thanks!
1 - I have not worked with hyperlink column type so not sure.
2 - Does not seem correct. Not something I have experienced
Hi Reza,
I like your video, it helped me a lot.
I have a question, what license or add-on do your users use to download Excel files from that app?
In my work i have an Office 365 license but not all my users have it, so can I request only the pugin to download the Excel report?
This is not an app but a flow. Users will need "Power Automate for office license" in order to run this.
Very informative content thanks for uploading ❤
If every month one column is added like 'October' then next month 'November' how to deal with this ?
If there are Excel files how to achieve the same things?
I have no idea about your specific use case. This is something I have not tried. I recommend checking or posting your query on the forums at powerusers.microsoft.com/ in case someone has done something similar.
Hi Raza, Thank you so much for the tutorial, it's really clear the FOG i have. what I understand from this tutorial that we have to create a List in sharepoint from csv files and use that list in ETL (I am using OData in SSIS)
Q: can we not use csv file directly using hyperlink of the folder at sharepoint in ETL?
Thanks
I have not done any ETL using SSIS so not sure what options are available
Very Useful video.. as always clear and crystal presentation.. I was working around converting the CSV data to JSON schemas and collecting that to PowerApps.. Just one Query.. is it possible to remove duplicates from SharePoint list once the create item action is done.. or just create new items into the SharePoint list by removing the items already created 🙂
You are most welcome.
You could clear and add items.
Or you could update items as shown in this video th-cam.com/video/uEZI_b1Gs-k/w-d-xo.htmlsi=dTLoER83Lz5t2Tl6
Very well explained. What should I do for to update the sharepoint list with CSV?
I have done a video on add and update with excel. Similar concept can be applied here.
Reza, Thanks for the very helpful video. I have a csv file with 12,00 - 15,00 records I need to import into a SharePoint list each morning. I've got the workflow working, but it times out after about ten minutes . Any work around for such an issue?
I have not experienced time out issues.
Probably it has some size limit
@@RezaDorrani I've since learned when testing Power Automate workflows they will display a time out message after ten minutes even though the workflow is still running in the background.
@@KenWarthen I have not come across this.
Hi. Many thanks for the video. The new PowerAutomate designer does not allow blank enter key (NewLine Compose) in the 'inputs' field. What is the solution?
You can swap back to old designer and add it.
I have done a video on new designer and shown how to swap back when needed.
Thanks Reza, it will definitely help me in my project. One question: will this also work if I want to update existing data on SharePoint list or the upload will create a fresh entry
Video description has link to my video on excel which does add and update. Similar logic can be applied here for update.
@@RezaDorraniI see it, thanks man! Working on a project and trying to use these functions on my powerapps project. You won't believe how you are helping people in upskilling them. Thanks once again!
@@desimortal1584 Most welcome and so happy to hear these videos are useful
Hi Reza, thank you very much for video. I have followed all steps but i have issue - every time run the flow it duplicates data five times from excel to sharepoint. Do you maybe have any idea about it? Thank you in advance
I have not come across the mentioned issue and hence not sure what the cause could be.
I recommend posting your issue on forums in case someone has experienced something similar powerusers.microsoft.com
❤
💕
Hi Reza, great video as always! I currently have a problem with an app, which is when I use it on a computer screen, the scrollbar size is fine, but when I use it on my laptop, the scrollbar increases in width and "blocks" out the components on the left side in my gallery... Do you know how to solve this issue?
I have not come across the mentioned issue and hence not sure what the fix for your issue is. I recommend posting your issue with screenshots on the forums at powerusers.microsoft.com
hello
thanks for the content :::!
is it possible to set a key column and to update of remaining ones? part of my datas are created manually in the list and others are uploaded. I need to keep all lines and update informations coming from the new version of the csv file
thanks
best regards
Possible but not something I have tried
Hi Reza, how can PowerAutomate handle a CSV that has 18000+ rows? It gives this error if i use your flow: "Unable to process template language expressions for action 'Apply_to_each' at line '0' and column '0': 'The number of foreach items limit exceeded for action 'Apply_to_each': maximum '5000' and actual '18828'.'.
Check documentation on pagination of flow actions.
Hi Reza!
I have a question hopefully you can answer. I have been working on a flow that gets all employees behind a manager in the organization. Now I have the array with Employee ID, Name and Email for all those employees but I need to get all this information in a sharepoint list... Hopefully you can help me
I cannot provide any assistance here on chat other than referring to videos or if I can quickly point or hint at something you could try.
Hi Reza. Thank you for that video. I have a question. I have a CSV file that is on OneDrive, The file is updated with new every Friday, I would like my SharePoint list to be updated every time the CSV on OneDrive gets updated. will this sample work
This sample is to only add data not update.
I had done an earlier video on add and update data from excel to SharePoint. You may want to check the logic in that one.
@@RezaDorrani I Raza I need you help. I have to download a CSV file every Friday then copy an replace the same file in OneDrive, The list in share point needs to be update because the CSV file may have new records or deleted records. Which example of yours will help me to accomplish this?
@@alejandrolazo3766 I dont think I have a specific video on this. A combination of videos may help. th-cam.com/video/uEZI_b1Gs-k/w-d-xo.html is the best reference I have.
What if re download the list send it to Hotel company for example they add hotel confirmation numbers and we need to upload it back to the sharepoint list adding only a new field so each user can view their travel info in the list and their hotel confirmation ( new field) will that work ?
Not sure as I have not tried this. It will only create new entries (not modify existing ones)
Thanks my Virtual Guru. Iwas able to replicate. However, Let's assume that I have a column "Name" where the data is Lastname, First name. In such case, when we split with "," the name column retains only lastname and text after "," are not captured.. Something to think about? Will wait for your directions. Sire
Welcome!
Check video again. I do talk about data having comma.
Thank you Reza. Can you please help. Is there anyway I can import a CSV into a Sharepoint DOCUMENT LIBRARY???
Welcome!
I do not have a video reference on this and would have to try it out to provide guidance. I will recommend checking or posting your query on forums in case someone has done something similar powerusers.microsoft.com
Hi Reza seeking your help l want to capture a current date in a mmm format when a new entry is created in the SharePoint list. Can you help me how to achieve?
I do not have a video reference on this scenario . I recommend checking or posting your query on forums in case someone has done something similar powerusers.microsoft.com
Please guide if there are multiple sheets in the same file, these sheets need to be stored separately in SharePoint. Example: 3 sheets namely IN, OUT, INVENTORY. Each day it needs to get updated.
I do not have a video reference on this scenario and would have to try it out to provide guidance. I recommend checking or posting your query on forums in case someone has done something similar powerusers.microsoft.com
Hi Reza, Is there a way to remove the comma values inside CSV file directly using automate without manually editing it?
Im not aware of an option to do that
Hi Reza - another fantastic reel. I have followed this, but my flow crashes, as the .csv file contains a UK date format. PA error details state "...The runtime value '"16/01/2022"' to be converted doesn't have the expected format 'String/date'." Can you kindly help?
More than date format, it seems your date has “ next to it.
I have not come across this issue so not sure whats the fix.
Great video Reza. I am doing the same thing but using the trigger when a file is created and get file content. The issue is that get file content returns null when the csv file size is 123 mb . How do I resolve this issue.
I have not experienced the mentioned issue and hence not sure what the cause for it could be.
I will recommend posting your issue on forums in case someone has experienced something similar powerusers.microsoft.com
Hi Reza,
Can you pls share vedio related to powerapps paginated gallery using flow.
If I delete any data in between pagination is not working using flow
I do not have a video reference on this scenario and would have to try it out to provide guidance. I recommend checking on the forums at powerusers.microsoft.com/ in case someone has done something similar.
Hi Reza, I have to move data from a csv file into a table located in an .xlsx file stored on SharePoint. There are more than 5000 rows in the csv file which breaches the apply to each limit. is there a way to split the csv into chunks based on row size and then send these to the apply to each action in groups of 5000?
Apply to each loop has an option in settings for pagination. You can go upto 100k I think but may need a premium license.
Yes trying to avoid the premium license, I was wondering if I could chunk the array and use a working array to loop throug
Also appear to be having a issue with the array filter. when I use "@and(not(equals(item(),'')),startsWith(split(item(),',')?[17],'JST1'))" I'm receiving 4611 records when there should be 4768. I have tried to use @startsWith(split(item(),',')?[17],'JST1') on its own but get an error message "The execution of template action 'Filter_array' failed: The evaluation of 'query' action 'where' expression '@startsWith(split(item(),',')?[17],'JST1')' failed: 'The template language function 'startsWith' expects its first parameter to be of type string. The provided value is of type 'Null'"
@@paddysheeran May be but not something I have tried. Flow is not a good option to ETL large amounts of data. You would run into service limits.
@@paddysheeran I have not come across the mentioned issue and hence not sure what the fix for your issue is. I recommend posting your issue with screenshots on the forums at powerusers.microsoft.com
Hi. How can i handle columns which have multiple lines,
, links, and possibly rich text?
You would need to use expressions for those. I do not have a video reference on it.
one doubt, i saw your video of making the the exporting data from power bi can be made into recurrence , extracting the data as csv . can we make this part of that , or can we make this flow recurrence instead of manually triggering this ?
Not sure I understood the question. I got confused at make this part of that.
@@RezaDorrani 😅 Sorry My bad , can we combine this flow with the extract power bi data as csv (using run a query againt a dataset)
@@arunv1909 This flow is importing data, that flow was exporting data. You could combine.
Hi Reza, want to ask that i had construct a power automate flow to export sharepoint list data to HTML table and CSV table, and able to send the HTML table and send CSV table as excel file to user via email, do you have any ideas that how we can convert HTML / CSV table to piechart/ bar chart and make it as PNG file and send to user via email?
I have not done any work with converting to pie charts or images
@@RezaDorrani Hi Reza, Thank you for your replies! Your videos really assisted me. Looking forward to more sharing from you in the future.
Hey Reza,
First of all, great video as always! It gave me a good idea to build CSV parsing flow.
Do you think it would be possible to use the Select function with a dynamic amount of keys/columns?
So instead of manually building the below Map for the Select function:
{
"key 1": @{item()[0]},
"key 2": @{item()[1]},
"key 3": @{item()[2]},
"": ""
}
Instead it could just dynamically expand to key 4 or down to key 2 while maintaining the item() function with also a dynamic index.
I did manage to get to a point where I can actually generate the above text into the Map, but then it is considered string, so the function won't get evaluated (and any attempt of making it not string, will just evaluate it prematurely while I am building the Map in Compose actions).
Not sure if that is an option. Have not tried that scenario.
@@RezaDorrani Yeah, pretty niche issue. Still I hope I find a solution as it can save us some money on CSV to JSON conversion subscriptions
Question One: I have a CSV file with inserted pictures in a column cells. I want to transfer the CSV file with the images to sharepoint list. Could you kindly help.
Question two: I have a sharepoint list with signature (pen input). How can i transfer the list with the signatures as image attachment to csv or and PDF
I have not done any work with pictures being imported or exported from SharePoint.
I will recommend checking or posting your query on forums in case someone has done something similar powerusers.microsoft.com
Wouldn’t it make more sense to convert it into an excel workbook and read the fields directly and populate them into the corresponding sharepoint field?
I received tons of requests to do a video with CSV file. I have already done a video with excel.
Reza, if we have commas in the column values this method won't work. Ryt?
Right and it is called out in video.
Hey Reza, Thank you, as always awesome demo again. Is there any limitation on this I mean maximum rows that you can import from csv file to SharePoint List...?
I have not tested its limits. If you have a large csv file then try and let me know.
Will do, thanks @@RezaDorrani
Just tried with 5000 records first and see how long it takes, took 33 minutes with concurrency control turned off though. Will try with 50,000 records next.
@@gilbertpradier Flow is not designed to be an ETL tool. As data grows, performance would be impacted. It would slow down.
@@RezaDorraniyes I know, just trying to see the performance. Thank you.
Hello, is it possible to do the same steps, but data source being Power BI?
I want to create a Power Automate that triggers in scheduled time intervals, hence Power BI Trigger Button would not be ideal.
Thank You!
I do not have a video reference on this scenario and would have to try it out to provide guidance. I recommend checking on the forums at powerusers.microsoft.com/ in case someone has done something similar.
@@RezaDorrani I've tried out many ways, nothing seems to work. Could no find anything that works for me on forums too. Please let me know if you could let me know of anything that can work.
@@yadukrishnankv5265 I have not tried this so cannot be of any assistance.
HI @rezadorrani, thanks This is a good approach, I only see one issue: if the text field value itself contains a a comma ',' then how can we handle those cases?
One option is to replace bit; imagine if ',' needs to be there and the data is coming from a system where no manual intervention is possible, as in my case, how to overcome that, please suggest.
As shared in video, you would need to replace it prior to using it.
You could use PAD to first replace , in data with some delimiter if automation is needed.
would you please show me the full code for the date column where you used replace to remove the return character?
The whole sample is shared with members. Check video description for details.
how to deal with escape codes?? mine has it and it seems affecting the splitting of the items...
Probably with expressions. I would have to try it out to know how it can be done.
I will recommend checking or posting your query on forums in case someone has done something similar powerusers.microsoft.com
Hello Reza,
is there a way to convert an "*.xls" file to CSV in order to process data in a power automate flow?
I am not aware of an option for this in flow.
I have also done a video on importing data from excel to sharepoint - th-cam.com/video/uEZI_b1Gs-k/w-d-xo.html
@@RezaDorrani
Thanks for your reply...
my only problem is that I'm getting a "*.xls" file by email, and i need to convert it to xlsx or to csv to9 be able to make an automation with it's data...
@@user-qz7ie6ot1t I understand your scenario but I dont know how to do it
Does it work with Dataverse Tables or only SP Lists?
Video is specific to SharePoint but can be customized for any data source
Hi Rezza, is this feasible in Dataverse?
Dataverse has an option to directly import data using data flows. Covered it in this video th-cam.com/video/Fy-kdOZRFFE/w-d-xo.htmlsi=XRPrKwC8ahEuhGdu