Well done Shane! The combination of Compose and Parse JSON are my personal favorite steps. These give you the flexibility to convert the data and use it however you see fit.
Hi Daniel Can you reply here... By any chance can we create excel file instead of CSV ..? Yes as per requirement we need to create .xslt excel file ....kindly help me if possible
You are a Goldmine of nuggets! Worked on this same kind of problem yesterday and got really close but no cigar. You provided the key information once again. Thank you.
I watch many of your videos and was looking for a solution on Google and this video came up without knowing it was yours! Knew right away i wouldn't have to look any further.
Love this process but can you change the order of columns in a collection, as they are alphabetical and that's not very useful when they go into my CSV?
Hi Shane, Thanks for the great video. Do you know if you can use the same CSV file to continue to store collection? In other words, every time you export, can you make it add onto an existing CSV file? This way you can continue to collect a library of information all on the same CSV file? Thanks in advance!
Hi Shane! You're videos have been so helpful, thank you! I am running into an issue with doing something like this from a SharePoint list to a simple CSV file. My "assigned" column is a multi-person selector option. Whenever my data goes from the List to the CSV file, I get a really long OODATA string it looks like. Would you be able to help point me in the right direction with parsing the JSON in the right way?
HI Shane. Really like your videos. got me out of many a mess. I am trying to export an already filtered (using a searchbox) set of data and want to export the filtered data to a CSV. I come unstuck when Im trying to drop columns as 'IsChosen' doesn't exist its just filtered. Looked all over for help but cant find exactly the help I need.
You can't use IsChosen. You will need another method to select the records you want. Something like the concept from this video. th-cam.com/video/0xZ4fMJbLvk/w-d-xo.html
Making an Excel file is possible but way hard! You need to have an Excel file that is a template, make a new file from the template, and then write your data to the table that was already in the new file.
Hey Shane! Loving the videos, though I have a question about this one. I have SP List data source with a lot of drop down lists and these are all populated in this manner, {"Value":"High"} instead of the hoped for High. Have you ran into this before? Keep up the good work, Thanks!
Hi Shane, thank you for so many great videos. I am currently using this type of flow for an inventory power apps program I designed. Currently I am using the flow to send an email of a collection of received inventory items. My received items have pictures. As we cannot patch a picture to a share point list, is there a way to write a flow to patch the picture from this collection to the same share point ID of received items?
Hi Shane, Thanks again. I implemented this in my app and works like a charm. One question: in your example the csv data is auto delimited into columns. In my csv export I still have to do the manual text to columns action. Is this an excel setting or am I missing something in the JSON to csv process?
This is really a nice feature and knowledge to get. Thanks as always Shane. Just a quick question. How do you reduce the number of columns exported to the csv.?
@@ShanesCows You can also use ShowColumns to build the collection. I do this for different user permissions as to what they can see based on permissions. I had issues when dropping some of the columns using DropColumns, it worked out great by just using ShowColumns and entering the columns I wanted to show up in the CSV file.
Great video as always, Shane. I am still waiting for CDS Permission and Security video (Part 2). It's difficult to find any simple resource which exlainps how to manage entities permission in CDS. Thanks very much!
Hi Shane, First of all, thanks for all the great work and effort you've provided here. I have some problem that I hoped I could solve with this example, but I don't need a CSV file, I need the data to be saved into existing Excel table instead and I hope you'll be able to help me with. In my app I am loading the data from Excel table and use them in the Gallery with checkboxes, creating a collection based on checked items and I need to save the data from collection(or Gallery) into a different Excel table along with some other stuff in the app (text boxes, labels etc.). I've tried various different things but every time hitting the wall when it comes to write collection into Excel table. It would be great if you could make a video on this subject. Thanks,
Export it to a CSV first and then save it somewhere on SharePoint or OneDrive. That's your first piece. Then your problem isn't "Get all of this data into an excel file", it's "Get this CSV into an online Excel file automatically". Then you can use Azure Functions or Visual Basic or something else (run Azure Functions from a flow?) in order to get that CSV info, dump it into Excel, and then delete the CSV. Then you have a self-resetting system which puts the latest data in Excel. Or just use a SharePoint list and create items in SharePoint from your CSV flow. Then you can set up a data connection in Excel to said list and you just need to hit refresh to get the data down to Excel. Hope I helped. J
hello sir, thank you for the video. this is what i need for my app. however when naming the created file is there a way that the filename of the csv file will be based from a value of an object in powerapps?
Great video thanks Shane. But what if I pass a single JSON record and want to stop for each in flow. I guess you got round it with an action that expects multiples. I want to pass a single Jason record of values for a stored procedure but that darn for each....
It will always be a table even if it is a single line I think. You might be able to parse the JSON, Apply To Each through it, and then write that info to a text variable so you don't have to deal with the table. I kind of show it here th-cam.com/video/QcWjAt7QVn4/w-d-xo.html where I only want one table but still have Apply to Each.
Hi shane, thanks for this video it help a lot. my only concern is once the file is exported the formatting of the date is different. how can we format the date when the file is exported according to our liking. Thanks.
If you are collecting from a data source then it will only retrieve up to the delegation limit. If you are adding them other ways there is no hard limit I know of.
@@ShanesCows What are the other ways to get all the records from powerapps data table into power automate flow and create CSV. Do you have any related videos.
Thanks for the video Shane. I was able to export data to CSV. A quick help on this will be really helpful. Is there a way in Power Automate to format output of List Record connector with close to 10 K records to a CSV. Thanks in Advance.
Hi Shane, thanks for the world of knowledge. I am facing an issue in many of my applications. Issue: My Applications are single-page applications (no landing page!) and while loading, Looked up items in my gallery or my sub-gallery (which is filtered from my parent gallery item) misbehave and do not populate properly for the first or second item. The issue resolves itself when I use any of the filters or some other action which refreshes the gallery. So I assumed this had something to do with the OnStart functions. My Resolution(1): Create a landing page. A pretty obvious solution, it allows the Application OnStart to run while loading landing page and the gallery to load when I open the next tab. My Resolution(2): Instead of creating a landing page, (since I have a search input text) I set the default for the search to a random string "Xyz" or "Loading Data" and set it back to "" after 2 seconds, all this while showing a Loading shape on the front end. This solves the inconsistency and allows my users to land on the page after 2 seconds. Any clues, or anything I might be doing wrong? (other than not having landing page- I am going to have them going forward anyways) Additional Info: I am using search and filters on Excel data backend (buzzKill)
Thanks Shane, I have a flow using similar to this and the CSV is being emailed out and stored in Sp docs currently, what would you recommend as the best way to take the CSV and get it onto my companys network? Map the sharepoint site it is stored in on the server? Or a flow using a gateway to copy the files to the server as and when they're created? End goal is it automatically loads back into our ERP system. Thanks
Hey Shane, nice video! My collection probably has too much info to pass to the flow. Often the generated Word file missing some data. However, all selected record ID in the collection are in the generated Word file. Just some text (large text) fields are missing data. My second thought is just pass the selected record ID to the flow, then in the flow, use the IDs to retrieve the other fields data in the flow to generate the Word file. My app uses DataVerse. Do you have any video that demo the use of DataVerse to retrieve data, record by record? Greatly appreciated is you can provide me some ideas what I need to do in my flow!! Thanks!
Hi Shane, Love the stuff on PowerApps. Would love to see an app video based on CRM security role model like editing of a field only for specific CRM role... Thanks and keep posting!!!
@@ShanesCows I am actually using ShowColumns yet the collection is sorted alphabetical order. ClearCollect(dbaseone, ShowColumns(Table1,"EID","EMAIL","Title","REGION","UNIT","GENDER","DESIGNATION","LOCATION"));
Hi Shane, Great Video! I currently ran through the steps however when I did the second step in the MS Flow and got the Inputs/Outputs, I guess they were a large number of records and hence had to download the output file however I was unable to paste that as my sample schema and it kept giving me errors. I was unable to use the IndentFour JSON format though for my dataset and had to instead use IncludeBinaryData as my format. would that now change how I parse the JSON?
Hello Shane, thanks so much for this great video. Really helped out. I have some fine tuning and additional things to do for the export based on user permissions, though this is a great start. I do have one question, how would you go about making the naming of the CSV file dynamic so that the Name can be system generated via what the user selects to filter the employee list? Or is there a better way? Another question, is can you use the items in the gallery list for the records to be exported to a csv file?
John you could make a string of the file name you want in POwer APps. Then set the flow up to "ask in power apps" for the filename. Pass the string and you are all set.
@Shane Young - figured out the filtering of the data based using the filter on the gallery to filter the employee list in the ClearCollect statement. I did try using ClearCollect(colEmployee, Gallery.AllItems) and kept getting an error in that statement. No issues with the filter being used in the ClearCollect(colEmployee, Filter(Employee, If(varActiveOnly, Accountenabled, !IsEmpty(AccountEnabled)), "DisplayName", "EmailAddress", "Title", "Location") works great. I can add all of the filters that the user can select in the viewing of the employee list for the gallery.
Hi Shane, complete beginner here but managed to create a data custodian app all through using your videos. Thank you so much for such great videos and clear explanations! However, after selecting a record the input is not working and is coming back with a 'click to download' button, what am I missing here? Any ideas? It seems like it should be so simple to select one record and export the data within that record to a .csv but after a week of various attempts I can't figure it out!
@@ShanesCows No problem Shane, thank you for taking the time to read and reply to my comment. For the moment I found a work around by clicking into said item and creating a csv with each datacardkey and still working on selecting each product in a gallery. Makes it so much more satisfying when I figure it out!
Thanks Shane! This is helpful! How do you keep the order of the parsed JSON objects? I'm passing the fields from the collection in a specific order, however the compose action puts this in alphabetical order which does not display in the correct order on the csv file.
I managed to fix this by selecting "Custom" instead of "Automatic" under Columns in advanced options. Thereafter it allows you to specify custom formatting. Thanks again for this Shane. Your content is always extremely informative :)
Hi, Shane! Another great video. Thank you for the materials and ideas shared! Can you please advise if it is possible to extract data from PDF file using Powerapps PDF connector, transform the data and transfer it to XLSX/CSV format and then use the query in Power Automate? That would be very useful. Warm regards!
Great video! Got it working finally as I ran into trouble when the Schema for the Parse JSON step in the Flow didn't like empty fields (as the Output just put "null" where a field was empty) so I found a fix where for any fields where you could expect it to be empty to add ["null"] to the "type". However. I've got another question regarding the actual CSV output. When I open the file, it's turned boolean fields into simple true/false and where a field is populated from a "Choices", it just puts in a numerical code rather than the text itself. Is there any way of having the CSV file show the actual text in the field?
Hi Shane, this is very useful and helps me out a lot. The problem I have is once I export a file in SharePoint I can't do it again as the file name already exists. Is it possible to create a CSV file that then adds an incremental ID to the filename?
Thanks Shane for the video. It worked fine for the first time when I am writing csv file to OneDrive but if re-run it doesn't overwrite the previous file . Any idea what should i do for that?
hi shane appreciate your videos and help, im facing some issue with the check boxes, that when i check 2 or 3 all get rested and i have to check them again, have you faced this behavior before? can you help?
Hi Shane. Thanks for the video. Would it be possible to export the data to excel AND put the picture into a cell as well? Could it be possible to use the absolute URL for the picture? (Also, would you still do this the same way as it's been a number of years since the video was produced?)
Martin, I am not sure. I have never tried to put an image in Excel. Everything in this video should still be accurate but the stuff with Images I am just not sure.
Shane, I've been watching your videos and you are awesome, got all my filters and sorts working because of you. One thing I am banging my head against the wall about is not how to export records so much (thanks to this video) but how to export ONLY the currently filtered records in the gallery. I'm beginning to think there isn't a way. Is there a video you could point me to that would help me with this? Thank you so much.
Hello sir, great video as always, but I have a question. I have a gallery and it contains data of multiple people when I export it should export multiple excel file in one go and it should contain respective users data. Is it possible to do?
2 ways to think about it. Create a separate collection in Power Apps for each group and send those over 1 at a time to flow. OR Send over the whole collection and then filter and parse the data in the flow. Both are possible, both are a pain in the butt to do. :) So whichever makes moer sense to you I would say go that way.
@@ShanesCows Hi sir i am trying for last one month but it's not possible for me export. so a gentle request to you sir can you please make a video how to export SharePoint list to excel. But have a condition if I am having list of 3 months date value the flow should export 3 excel file and that of respective months data in one go. I tried using loop, until now but facing error in several things. Please sir a request to you will thank you forever.
This works brilliantly for my needs as a real newbie to powerapps but... I have one final nagging little problem which I can't seem to solve by myself. In the CSV output file, the optionset field values are all preceded with the word "value" when really all I want is the actual option selected in my sharepoint list. Any ideas what I would need to configure in the JSON example to drop the word "value" from the CSV file?
hi, I have a question: is it possible to export the file with all the items of the gallery and at the same time is it possible to export with the check boxes selected???
Yea, sounds possible. You will need to work through the logic of selecting what you want but then you are good to go. Power Apps doesn’t care what table you send.
This is a great video Shane, I'm wondering if there's a way to Parse blank information to a csv? I currently have a form with non mandatory questions. When these are left blank the export fails. When all questions are completed the export works perfectly.
Hi Shane, been using this for a while now, however recently both of my apps that use it started reporting “JSON function cannot serialize…”{Attachments}””. I added attachments to the varDroppedRecordColumns and it fixed the issue for one of my apps but the other is still saying the same error. Wondering if you’ve encountered this recently and how you’ve worked around it?
I would go back up stream and look at your collection and make sure it has columns, then make sure the JSON shows columns. Guessing the issue is earlier in your app/flow.
That's a great video. Just one question, Every time , I am hitting button, CSV file is getting overwritten. IS there any way to append the exported CSV ?
@Shane By any chance can we create excel file instead of CSV ..? Yes as per requirement we need to create .xslt excel file ....kindly help me if possible
The very end of the video I talk about you have all of the info in Flow. So I don't see why you couldn't use that info to populate an Excel file. Not sure about XLST, I have never tried.
Hi, Thank you again for such an amazing video, can you please tell me is there a data limit for this, I have some 6-7k data rows with 19-20 columns, It is giving me error if I add all columns, if I add only 4-5 columns, it works.
HI Shane, Great Video. thank you. I am facing a challenge when i am passing a combined collection. The parsing fails when i pass the combined collection output. it says expected object but array was passed. How do i fix it?
Great content as usual! Has anyone figured out a way around the size limit of the JSON function in PowerApps? It is a size limit issue not a record count issue which I have confirmed. Seems to raise it's head around the 4000 record size. So frustrating...
Hey Shane, I have a flow that creates a csv file that is generated from columns in a SharePoint List. Some of the columns have a text data type and some have a number data type. I am trying to separate the columns in the csv file so that only the columns that have a text data type have quotes around them. Any ideas?
Hi Shane, thanks for the video. I have a problem when generating the csv file. the header is correctly in one row, but the value of each column is in a different row. (Ex. Column : Year is in the first row, Value : is in the second row; Column : Report Number is in the first row, Value : is in the third row; etc) I expect, value of each column is in the same row
Hi Shane, great Video as always. Just this time i am having Problems. My CSV File just writes each record in a singel cell. Not like your Demo CSV where everything is in a extra Cell. Could you help me on this topic?
@@ShanesCows Hi, and yes i am. I have narroed the issue down to the point of creating the csv. My Power Automate does not give me the choice "Body". I live in germany (incase that causes some sort of issue), and the only thing i can choose without the apply for all ist "text".
Dear Shane, I already prepared my flow and it can export data in csv successfully. But I also facing some issues, if the column has null value "Parse JSON" giving me some error like "Required properties are missing from object:" If I edit the JSON Schema like "type": ["string","null"] , it can create the JSON Schema but in my csv that column is coming full blank. Could you please guide me to resolve this part. I want to export that column with DATA and null value both.
Shane, thanx a lot for this complete solution. I met some difficulties with final csv file. My problem is wrong coding text (windows 1251) and russian words look like Ð-акваÑка. It would be grate if u can reply for this. where should i change coding to unicode or utf-8. thanx in advance
Hi sir Thanks for the video, Kindly can you explain how we can send the CSV file to different email addresses based on text input in-app. Thanks in advance
hii sir , i am taking sql table and making a collection then i am converting into Csv format with this method . and i m getting right output but if i add one more column in sql table and again create collection and run flow again then i am getting error i know because i m passing one extra column in JSON PARSE , so my question is "how can we convert a sql table which contains dynamic columns Adding into CSV ??"
hi is really a nice feature . Thanks Shane. Just a question. How can i show my data with forgien charactere exemple in my csv file i have name "Frédric" it show for me after export "fr@Adric" .?
Hi Shane, I was able to export a gallery to CSV file. Is there a way to format this CSV file(converting the range into table) so that I won't need to format the file every time the I open this CSV file.
Hi, thank you very much for your wonderful video, this is just what I am looking for. I have a little problem. I followed step by step the video and my csv is created but it is not properly formatted. All the data appears just in one column. Also the data appears with strange symbols. I use Spanish language with "Ñ" and accents. Could you please help me with that? Thank you very much!
Sorry for the delay. I am not sure. :( If you try it by using English do you get multiple columns? I wonder if the problem is Spanish characters or you have a bug. Trying English will at least help you confirm where the problem is.
Do you have any idea how to collect more than 1,000 records from Gallery1.Items? I use ForAll with Gallery1.AllItems and want to export the data to SQL, but the inserted rows are only 1,000. My data contains 10k rows. Is there any solution to avoid the limitation?
Nice video Shane, my problem is when i create the file the (.csv) then i open it with excel but it create just 1 Column with all the information, does not create a column by every comma like in your video. any idea? i followed the steps in your video.
@@jessicapinheirosilva2692 yes! You should change the region format in your operative system ( the person who open the .CSV file). Control panel -> clock and region (change date format, time number) -> additional configuration -> list separator. the value here probably is in “; “Your should change it to “ , “ then the excel should open the CSV file in a right way.
Hey I'm having a little trouble with the last part of this. My Flow works, and it's created the file in the SharePoint site, but it won't open properly. Is there a way to make it into an Excel workable CSV?
Very useful video.I have a requirement where i want to convert the created csv to excel and send as a attachment via mail.please let me know how to convert it to excel witout storing it somewhere.
Where do you originally go through the JSON function in detail? It says it wants data, but it doesn't accept collections or the original SharePoint data source.
Hello @Shane Young, while exporting EXCEL from PowerApps I am getting unrefined values. I have used SharePoint's collection. Can u Kindly help on this. It would be a great help.
Hey it’s a great video... but I am facing a problem when I create file in Sharepoint. First time it runs but then consequently when we export it shows failed run... failed to crest file... it works fine with onedrive but I want it to run in share point so can u suggest anything that can be done?
Hi, my sharepoint table has richtext format on some fields, and if i use this method i get all the richtext info in the csv. Can i export the data without the richtext formatting?
Use the create file action. If you give it text and set the extension to txt should work. I have never tried but have made html files which are same and they worked.
Super input you Are making. Q? can you make a video(power app) where you import 3 col from a Excel sheet 1. Then in the app insert 4 New values and a date in the same Excel file but in sheet 2
Well done Shane!
The combination of Compose and Parse JSON are my personal favorite steps. These give you the flexibility to convert the data and use it however you see fit.
Hi Daniel
Can you reply here...
By any chance can we create excel file instead of CSV ..?
Yes as per requirement we need to create .xslt excel file ....kindly help me if possible
Agree! Ever since I added Compose into my toolbelt I do so much with it now. Very much a core action of building a flow.
You are a Goldmine of nuggets! Worked on this same kind of problem yesterday and got really close but no cigar. You provided the key information once again. Thank you.
Awesome Peter!
I watch many of your videos and was looking for a solution on Google and this video came up without knowing it was yours! Knew right away i wouldn't have to look any further.
Thanks Carlos!
Fantastic Video. It's the only one on the subject that worked for my system. THANK YOU!
Happy to help. Have a great day. 🐶
OMG this is the final piece for my app I've been looking for - Thank you!!!!!
I knew you needed it. 😉
Love this process but can you change the order of columns in a collection, as they are alphabetical and that's not very useful when they go into my CSV?
That is a great question Kevin... I don't know... I haven't tried that one.
Hello Shane... Hope you are doing great.. Can we achieve same functionality with out using onedrive Or SharePoint create file action??
You need to create the file somewhere. YOu could add the file as an attachment to an email I guess.
I have a project that has this requirement. Thanks for saving me a lot of time and headache!
Glad I could help! Now go build something awesome Bob.
Hi Shane, Thanks for the great video. Do you know if you can use the same CSV file to continue to store collection? In other words, every time you export, can you make it add onto an existing CSV file? This way you can continue to collect a library of information all on the same CSV file? Thanks in advance!
I haven't tried. I guess you could grab the old contents of the CSV, add the new stuff, and then make a CSV from the combination of the two. Not sure.
Hi Shane! You're videos have been so helpful, thank you! I am running into an issue with doing something like this from a SharePoint list to a simple CSV file. My "assigned" column is a multi-person selector option. Whenever my data goes from the List to the CSV file, I get a really long OODATA string it looks like. Would you be able to help point me in the right direction with parsing the JSON in the right way?
HI Shane. Really like your videos. got me out of many a mess. I am trying to export an already filtered (using a searchbox) set of data and want to export the filtered data to a CSV. I come unstuck when Im trying to drop columns as 'IsChosen' doesn't exist its just filtered. Looked all over for help but cant find exactly the help I need.
You can't use IsChosen. You will need another method to select the records you want. Something like the concept from this video. th-cam.com/video/0xZ4fMJbLvk/w-d-xo.html
@@ShanesCows Thanks Shane. Looks v in depth. Will give it a go.
Hi Shane. Thanks a lot for the video. Awesome. One question, can we create an excel file instead of csv?
Making an Excel file is possible but way hard! You need to have an Excel file that is a template, make a new file from the template, and then write your data to the table that was already in the new file.
Hey Shane! Loving the videos, though I have a question about this one. I have SP List data source with a lot of drop down lists and these are all populated in this manner, {"Value":"High"} instead of the hoped for High. Have you ran into this before? Keep up the good work, Thanks!
I haven’t. If I did I would try some string manipulation to remove the extra characters. Maybe replace? Though there a lot of ways to do so
Hi Shane, thank you for so many great videos. I am currently using this type of flow for an inventory power apps program I designed. Currently I am using the flow to send an email of a collection of received inventory items. My received items have pictures. As we cannot patch a picture to a share point list, is there a way to write a flow to patch the picture from this collection to the same share point ID of received items?
Hi Shane,
Thanks again. I implemented this in my app and works like a charm. One question: in your example the csv data is auto delimited into columns. In my csv export I still have to do the manual text to columns action. Is this an excel setting or am I missing something in the JSON to csv process?
I figured it out . In my excel language settings ; is the delimiter. I put a replace on the csv table output to replace all commas with semicolons.
Glad you got it Lars. 😎
@@larsvanginkel3438 How did you do it?
This is really a nice feature and knowledge to get. Thanks as always Shane. Just a quick question. How do you reduce the number of columns exported to the csv.?
Remove them from your collection. The DropColumns function can help.
@@ShanesCows You can also use ShowColumns to build the collection. I do this for different user permissions as to what they can see based on permissions. I had issues when dropping some of the columns using DropColumns, it worked out great by just using ShowColumns and entering the columns I wanted to show up in the CSV file.
Great video as always, Shane. I am still waiting for CDS Permission and Security video (Part 2). It's difficult to find any simple resource which exlainps how to manage entities permission in CDS. Thanks very much!
Coming soon I hope. 😍
Great video! Another way to grab the schema that I've seen is by setting a label in Powerapps to that json variable and copy/pasting from there.
Cool. Thanks for sharing.
Hi Shane, First of all, thanks for all the great work and effort you've provided here.
I have some problem that I hoped I could solve with this example, but I don't need a CSV file, I need the data to be saved into existing Excel table instead and I hope you'll be able to help me with. In my app I am loading the data from Excel table and use them in the Gallery with checkboxes, creating a collection based on checked items and I need to save the data from collection(or Gallery) into a different Excel table along with some other stuff in the app (text boxes, labels etc.). I've tried various different things but every time hitting the wall when it comes to write collection into Excel table. It would be great if you could make a video on this subject.
Thanks,
Export it to a CSV first and then save it somewhere on SharePoint or OneDrive. That's your first piece. Then your problem isn't "Get all of this data into an excel file", it's "Get this CSV into an online Excel file automatically".
Then you can use Azure Functions or Visual Basic or something else (run Azure Functions from a flow?) in order to get that CSV info, dump it into Excel, and then delete the CSV. Then you have a self-resetting system which puts the latest data in Excel.
Or just use a SharePoint list and create items in SharePoint from your CSV flow. Then you can set up a data connection in Excel to said list and you just need to hit refresh to get the data down to Excel.
Hope I helped. J
We have done it for a customer. It is hard. Not sure it will be a video anytime soon but I will note it. Sorry I don’t have an easy solution
Thanks Shane! Great video. Is there a way to populate an Excel template with some formatting? Thanks!
There are some actions but I haven’t ever tried 😑
@@ShanesCows Thanks Shane!
hello sir, thank you for the video. this is what i need for my app. however when naming the created file is there a way that the filename of the csv file will be based from a value of an object in powerapps?
You can “ask in Power Apps” in your flow for the filename from Power Apps
Hey Shane, thanks for the video! Is there anyway to include complex data types (specifically fields of type choice) in a CSV export?
I think you would have to drill down to get their value. Maybe use AddColumns to create a column with what you want?
Thank you Shane!
One question, I don’t have any dynamic values available. Do you know why that might be?
Thanks!
Great video thanks Shane. But what if I pass a single JSON record and want to stop for each in flow. I guess you got round it with an action that expects multiples. I want to pass a single Jason record of values for a stored procedure but that darn for each....
It will always be a table even if it is a single line I think. You might be able to parse the JSON, Apply To Each through it, and then write that info to a text variable so you don't have to deal with the table. I kind of show it here th-cam.com/video/QcWjAt7QVn4/w-d-xo.html where I only want one table but still have Apply to Each.
Hi shane, thanks for this video it help a lot. my only concern is once the file is exported the formatting of the date is different. how can we format the date when the file is exported according to our liking. Thanks.
You would have to write logic in Power Apps or Flow to make it your format. Save it as text not date at that point.
Great video Shane! Is there any limitation regarding the number of rows the clearcollect function can gather?
If you are collecting from a data source then it will only retrieve up to the delegation limit. If you are adding them other ways there is no hard limit I know of.
@@ShanesCows What are the other ways to get all the records from powerapps data table into power automate flow and create CSV. Do you have any related videos.
Thanks for the video Shane. I was able to export data to CSV. A quick help on this will be really helpful. Is there a way in Power Automate to format output of List Record connector with close to 10 K records to a CSV. Thanks in Advance.
Not sure I have never tried
Hi Shane, thanks for the world of knowledge. I am facing an issue in many of my applications.
Issue: My Applications are single-page applications (no landing page!) and while loading, Looked up items in my gallery or my sub-gallery (which is filtered from my parent gallery item) misbehave and do not populate properly for the first or second item. The issue resolves itself when I use any of the filters or some other action which refreshes the gallery. So I assumed this had something to do with the OnStart functions.
My Resolution(1): Create a landing page. A pretty obvious solution, it allows the Application OnStart to run while loading landing page and the gallery to load when I open the next tab.
My Resolution(2): Instead of creating a landing page, (since I have a search input text) I set the default for the search to a random string "Xyz" or "Loading Data" and set it back to "" after 2 seconds, all this while showing a Loading shape on the front end. This solves the inconsistency and allows my users to land on the page after 2 seconds.
Any clues, or anything I might be doing wrong? (other than not having landing page- I am going to have them going forward anyways)
Additional Info: I am using search and filters on Excel data backend (buzzKill)
I blame Excel. 😀 I would show a spinner until all of your loading finishes and make the user wait.
Thanks Shane, I have a flow using similar to this and the CSV is being emailed out and stored in Sp docs currently, what would you recommend as the best way to take the CSV and get it onto my companys network? Map the sharepoint site it is stored in on the server? Or a flow using a gateway to copy the files to the server as and when they're created? End goal is it automatically loads back into our ERP system. Thanks
I think gateway and have the flow do it.
Hey Shane, nice video! My collection probably has too much info to pass to the flow. Often the generated Word file missing some data. However, all selected record ID in the collection are in the generated Word file. Just some text (large text) fields are missing data. My second thought is just pass the selected record ID to the flow, then in the flow, use the IDs to retrieve the other fields data in the flow to generate the Word file. My app uses DataVerse. Do you have any video that demo the use of DataVerse to retrieve data, record by record? Greatly appreciated is you can provide me some ideas what I need to do in my flow!! Thanks!
Hi Shane,
Love the stuff on PowerApps. Would love to see an app video based on CRM security role model like editing of a field only for specific CRM role...
Thanks and keep posting!!!
I need to do more CDS content for sure.
Great video and really helpful. however how do you sort the columns as Age was first column on the export which is is awkward
I think if you use ShowColumns you can specify the order. Not sure though.
@@ShanesCows I am actually using ShowColumns yet the collection is sorted alphabetical order.
ClearCollect(dbaseone, ShowColumns(Table1,"EID","EMAIL","Title","REGION","UNIT","GENDER","DESIGNATION","LOCATION"));
Hi Shane,
Great Video!
I currently ran through the steps however when I did the second step in the MS Flow and got the Inputs/Outputs, I guess they were a large number of records and hence had to download the output file however I was unable to paste that as my sample schema and it kept giving me errors. I was unable to use the IndentFour JSON format though for my dataset and had to instead use IncludeBinaryData as my format. would that now change how I parse the JSON?
You need to make a small test so you can’t get small output to define the schema
Really cool! How did you create the first table with the checkbox? Can you point to the other video, please? Thanks!!
I think it was this video th-cam.com/video/0xZ4fMJbLvk/w-d-xo.html
Hello Shane, thanks so much for this great video. Really helped out. I have some fine tuning and additional things to do for the export based on user permissions, though this is a great start. I do have one question, how would you go about making the naming of the CSV file dynamic so that the Name can be system generated via what the user selects to filter the employee list? Or is there a better way? Another question, is can you use the items in the gallery list for the records to be exported to a csv file?
John you could make a string of the file name you want in POwer APps. Then set the flow up to "ask in power apps" for the filename. Pass the string and you are all set.
@Shane Young - figured out the filtering of the data based using the filter on the gallery to filter the employee list in the ClearCollect statement. I did try using ClearCollect(colEmployee, Gallery.AllItems) and kept getting an error in that statement. No issues with the filter being used in the ClearCollect(colEmployee, Filter(Employee, If(varActiveOnly, Accountenabled, !IsEmpty(AccountEnabled)), "DisplayName", "EmailAddress", "Title", "Location") works great. I can add all of the filters that the user can select in the viewing of the employee list for the gallery.
Hi Shane, complete beginner here but managed to create a data custodian app all through using your videos. Thank you so much for such great videos and clear explanations! However, after selecting a record the input is not working and is coming back with a 'click to download' button, what am I missing here? Any ideas? It seems like it should be so simple to select one record and export the data within that record to a .csv but after a week of various attempts I can't figure it out!
Not sure Tom. You are right should be possible but there are so many moving parts not sure how to nudge you along. 😐
@@ShanesCows No problem Shane, thank you for taking the time to read and reply to my comment. For the moment I found a work around by clicking into said item and creating a csv with each datacardkey and still working on selecting each product in a gallery. Makes it so much more satisfying when I figure it out!
Thank you Sr Shane, you help-me for my work..
Glad to help
@@ShanesCows
Shane, how do I do the excel document and send it to the attached email? Could you make a video teaching?
Thanks Shane! This is helpful! How do you keep the order of the parsed JSON objects? I'm passing the fields from the collection in a specific order, however the compose action puts this in alphabetical order which does not display in the correct order on the csv file.
I managed to fix this by selecting "Custom" instead of "Automatic" under Columns in advanced options. Thereafter it allows you to specify custom formatting. Thanks again for this Shane. Your content is always extremely informative :)
Glad you got it. 😀
Thank you so much Shane, this was very much needed!!
Any chance of exporting record columns? I have multiple people picker columns.
Not that I have figured out. I think on the Power Apps side you would need to extract the people as text using Concat. That is what I would try.
Thanks for the video, is it possible to populate a specific excel file from SharePoint (the file is like a quiz form)?
You can only write to files in OneDrive
Awesome! Can you do this other way round... CSV into PowerApps without using a 3rd party connector or xlsx table import action?
This is pretty close 😀 th-cam.com/video/QcWjAt7QVn4/w-d-xo.html
Hi, Shane!
Another great video. Thank you for the materials and ideas shared! Can you please advise if it is possible to extract data from PDF file using Powerapps PDF connector, transform the data and transfer it to XLSX/CSV format and then use the query in Power Automate? That would be very useful.
Warm regards!
Not the free connector. Maybe one of the third party ones.
Great video! Got it working finally as I ran into trouble when the Schema for the Parse JSON step in the Flow didn't like empty fields (as the Output just put "null" where a field was empty) so I found a fix where for any fields where you could expect it to be empty to add ["null"] to the "type". However. I've got another question regarding the actual CSV output. When I open the file, it's turned boolean fields into simple true/false and where a field is populated from a "Choices", it just puts in a numerical code rather than the text itself. Is there any way of having the CSV file show the actual text in the field?
You would have to write If logic to change the results to show what you want. 😑
Hi Shane, this is very useful and helps me out a lot. The problem I have is once I export a file in SharePoint I can't do it again as the file name already exists. Is it possible to create a CSV file that then adds an incremental ID to the filename?
Nevermind, I've worked out I can use the Createfile_Filename function from the Dynamic Content and then specify the filename from within the app.
Awesome. Glad you got it. 😊
Nice video (as usual). Is the only way to avoid problems with JSON and blank fields to use [“string”,”null”]?
Thanks Shane for the video. It worked fine for the first time when I am writing csv file to OneDrive but if re-run it doesn't overwrite the previous file . Any idea what should i do for that?
Generate a dynamic file name so each one is unique
Hi Apra. I just inserted a step before the file create to delete the existing file. Hope this helps. 🙂
hi shane appreciate your videos and help, im facing some issue with the check boxes, that when i check 2 or 3 all get rested and i have to check them again, have you faced this behavior before? can you help?
I haven't but check the Default property of the Checkbox and make sure it is ThisItem.CheckedColumnYouUsed
@@ShanesCows worked thanks alot! i added a text box also to make users write the name of the output file :)
Super... kindly show to convert to excel format without using SharePoint...like using Azure functions etc
I haven't done that before. Sorry.
@@ShanesCows possible kindly do in near future
Hi Shane. Thanks for the video. Would it be possible to export the data to excel AND put the picture into a cell as well? Could it be possible to use the absolute URL for the picture? (Also, would you still do this the same way as it's been a number of years since the video was produced?)
Martin, I am not sure. I have never tried to put an image in Excel. Everything in this video should still be accurate but the stuff with Images I am just not sure.
A quick question, is there a way that the columns exported will not be arranged alphabetically? I would like my columns to be in the sequence I like.
You could try wrapping it in ShowColumns and specify them in the order you want. I am not sure it works but that is what I would try.
thanks for sharing, if possible to export 2 lists(master and detail) to 1 csv at the same time?
Sorry I am behind on comments. Yes, you would need to nest your Concat functions to do so.
Shane, I've been watching your videos and you are awesome, got all my filters and sorts working because of you. One thing I am banging my head against the wall about is not how to export records so much (thanks to this video) but how to export ONLY the currently filtered records in the gallery. I'm beginning to think there isn't a way. Is there a video you could point me to that would help me with this? Thank you so much.
try to create a collection while filtering and export the collection after.
Hi shane great video Thanks! Is there a way to load more than 2000 items into a collection? When I tried this it returned only first 2000 items
Not directly. There are ways to trick it into loading more but I would push back and ask why.
Hello sir, great video as always, but I have a question. I have a gallery and it contains data of multiple people when I export it should export multiple excel file in one go and it should contain respective users data. Is it possible to do?
2 ways to think about it. Create a separate collection in Power Apps for each group and send those over 1 at a time to flow. OR Send over the whole collection and then filter and parse the data in the flow. Both are possible, both are a pain in the butt to do. :) So whichever makes moer sense to you I would say go that way.
@@ShanesCows 😅Thank you sir, appreciate the response. But surely I will let you know once my pain is relieved.
@@ShanesCows Hi sir i am trying for last one month but it's not possible for me export. so a gentle request to you sir can you please make a video how to export SharePoint list to excel. But have a condition if I am having list of 3 months date value the flow should export 3 excel file and that of respective months data in one go. I tried using loop, until now but facing error in several things. Please sir a request to you will thank you forever.
Excellent video as always. Thanks Shane!
Thank you Javier!
This works brilliantly for my needs as a real newbie to powerapps but... I have one final nagging little problem which I can't seem to solve by myself. In the CSV output file, the optionset field values are all preceded with the word "value" when really all I want is the actual option selected in my sharepoint list. Any ideas what I would need to configure in the JSON example to drop the word "value" from the CSV file?
I haven’t tried. Guessing it will be a pain in the butt to do
hi, I have a question: is it possible to export the file with all the items of the gallery and at the same time is it possible to export with the check boxes selected???
Yea, sounds possible. You will need to work through the logic of selecting what you want but then you are good to go. Power Apps doesn’t care what table you send.
This is a great video Shane, I'm wondering if there's a way to Parse blank information to a csv? I currently have a form with non mandatory questions. When these are left blank the export fails. When all questions are completed the export works perfectly.
I got the same error. I got around this by scrubbing my data and entering a placeholder for all blank fields.
Hi Shane, been using this for a while now, however recently both of my apps that use it started reporting “JSON function cannot serialize…”{Attachments}””. I added attachments to the varDroppedRecordColumns and it fixed the issue for one of my apps but the other is still saying the same error. Wondering if you’ve encountered this recently and how you’ve worked around it?
I found the solution, changed jsonformat.indentfour to jsonformat.ignorebinarydata & jsonformat.ignoreunsupportedtypes 🎉
Awesome Robin, glad you got it!
Hey Shane, Ur the best man! I did the same and my table has not seppared, all the info is on 1 field seppared by commas, did I mess something up ?
I would go back up stream and look at your collection and make sure it has columns, then make sure the JSON shows columns. Guessing the issue is earlier in your app/flow.
That's a great video. Just one question, Every time , I am hitting button, CSV file is getting overwritten. IS there any way to append the exported CSV ?
I don't know how to append it. You might have to get creative and grab the content of the old CSV and then crate a new CSV that is Old + New
Thank you for your wonderful videos. I am not able to get the JSON format, in the flow the input and the output shows null. What am I doing wrong?
I am not sure. SOrry
@Shane
By any chance can we create excel file instead of CSV ..?
Yes as per requirement we need to create .xslt excel file ....kindly help me if possible
The very end of the video I talk about you have all of the info in Flow. So I don't see why you couldn't use that info to populate an Excel file. Not sure about XLST, I have never tried.
Hi, Thank you again for such an amazing video, can you please tell me is there a data limit for this, I have some 6-7k data rows with 19-20 columns, It is giving me error if I add all columns, if I add only 4-5 columns, it works.
It might be the type of columns you are adding, not the number. But I am not positive.
HI Shane, Great Video. thank you. I am facing a challenge when i am passing a combined collection. The parsing fails when i pass the combined collection output. it says expected object but array was passed. How do i fix it?
Great content as usual! Has anyone figured out a way around the size limit of the JSON function in PowerApps? It is a size limit issue not a record count issue which I have confirmed. Seems to raise it's head around the 4000 record size. So frustrating...
Interesting. I haven’t ran into that. 😑
Hey Shane, I have a flow that creates a csv file that is generated from columns in a SharePoint List. Some of the columns have a text data type and some have a number data type. I am trying to separate the columns in the csv file so that only the columns that have a text data type have quotes around them. Any ideas?
Thankyou very much for making this video
You are welcome 🤗
Hi Shane, thanks for the video.
I have a problem when generating the csv file. the header is correctly in one row, but the value of each column is in a different row.
(Ex. Column : Year is in the first row, Value : is in the second row;
Column : Report Number is in the first row, Value : is in the third row; etc)
I expect, value of each column is in the same row
I am not sure, I would try again and see if you can find the issue in your setup. Sorry, not much help
Hi Shane, great Video as always. Just this time i am having Problems. My CSV File just writes each record in a singel cell. Not like your Demo CSV where everything is in a extra Cell. Could you help me on this topic?
Daniel I am not sure. Are you using the JSON function before sending your collection to Power Apps?
@@ShanesCows Hi, and yes i am. I have narroed the issue down to the point of creating the csv. My Power Automate does not give me the choice "Body". I live in germany (incase that causes some sort of issue), and the only thing i can choose without the apply for all ist "text".
@@danielhampson5918 Hi Daniel, Can you solved your issue? Because I have the same problem and i dont know how i can solve that
Does it need to be in gallery or collection to be able to export data from Power Apps to ex. Excel?
Doesn’t have to be. But table data was what I was thinking about here.
Dear Shane, I already prepared my flow and it can export data in csv successfully. But I also facing some issues, if the column has null value "Parse JSON" giving me some error like "Required properties are missing from object:" If I edit the JSON Schema like "type": ["string","null"] , it can create the JSON Schema but in my csv that column is coming full blank. Could you please guide me to resolve this part. I want to export that column with DATA and null value both.
I had a doubt after creating the flow in power automate we do not have to share that to the users right? Sharing the app will be sufficient right?
COrrect, if the flow is triggered by Power Apps then you do not have to share it with the user.
instead of saving the data, is is possible to have the spreadsheet just open with the information?
Not really. You could save the spreadsheet with flow, return the link, and then have Power Apps Launch the URL.
Shane, thanx a lot for this complete solution. I met some difficulties with final csv file. My problem is wrong coding text (windows 1251) and russian words look like Ð-акваÑка. It would be grate if u can reply for this. where should i change coding to unicode or utf-8. thanx in advance
Sorry, I wish I knew what the correct answer is but I don’t 😔 I would tell you to try each setting and see if you can find one that works.
Hi sir
Thanks for the video, Kindly can you explain how we can send the CSV file to different email addresses based on text input in-app.
Thanks in advance
Use this video that gets the user email from Office 365 th-cam.com/video/vpYkOccwn4Y/w-d-xo.html and then pass that as part of the flow.
Hi Shane, is there any way to avoid duplicate records while exporting data from powerapps to flow and storing it in excel?
hii sir , i am taking sql table and making a collection then i am converting into Csv format with this method . and i m getting right output but if i add one more column in sql table and again create collection and run flow again then i am getting error i know because i m passing one extra column in JSON PARSE , so my question is "how can we convert a sql table which contains dynamic columns Adding into CSV ??"
I think you can make the fields not required but I haven’t tried this so it super sure
hi is really a nice feature . Thanks Shane. Just a question. How can i show my data with forgien charactere exemple in my csv file i have name "Frédric" it show for me after export "fr@Adric" .?
I am not sure. Sorry haven’t tried.
Awesome video! Very informative for the project I have going on atm :)
Awesome. Glad to help. 😀
Hi Shane, is it possible to use JSON function in PowerApps to send data to flow to create a word document?
If you look in flow there are several Word actions you can use. :)
Hi Shane, I was able to export a gallery to CSV file. Is there a way to format this CSV file(converting the range into table) so that I won't need to format the file every time the I open this CSV file.
Not that I know of. I am sure it is possible but I got nothing to help here.
Hi, thank you very much for your wonderful video, this is just what I am looking for.
I have a little problem. I followed step by step the video and my csv is created but it is not properly formatted. All the data appears just in one column.
Also the data appears with strange symbols. I use Spanish language with "Ñ" and accents. Could you please help me with that?
Thank you very much!
Sorry for the delay. I am not sure. :( If you try it by using English do you get multiple columns? I wonder if the problem is Spanish characters or you have a bug. Trying English will at least help you confirm where the problem is.
Hello Shane! I try to the same as you. But i cant find an action "compose". Is it possible that i havn't Sharepoint. I only have dataverse?
Do you have any idea how to collect more than 1,000 records from Gallery1.Items? I use ForAll with Gallery1.AllItems and want to export the data to SQL, but the inserted rows are only 1,000. My data contains 10k rows. Is there any solution to avoid the limitation?
Gallery.AllItems only gets the loaded data. What if you use the same table that feeds the gallery instead of AllItems
Amazing. Can you please tell us the code how count is increasing when you are selecting items in gallery
This is what is in the Label: "Number of Chosen Records: " & CountRows(Filter(colEmployees, IsChosen))
@@ShanesCows thank you Shane, IsChosen will work if we are selecting items of gallery using toggle too right ?
I noticed that the columns has been sorted out from A to Z automatically? Is there any workaround on that to use your own Column ordering?
Hi mardith, were you able to do it ? i have the same question
I solved renaming the columns in the moment i create the collection. Hope it help.
Nice video Shane, my problem is when i create the file the (.csv) then i open it with excel but it create just 1 Column with all the information, does not create a column by every comma like in your video. any idea? i followed the steps in your video.
Not sure Edgar. I would like at the flow and see what the data looks like that is coming from Power Apps
Hi, Edgar. I’m with the Samsung problem. How did you solve it?
@@jessicapinheirosilva2692 yes! You should change the region format in your operative system ( the person who open the .CSV file). Control panel -> clock and region (change date format, time number) -> additional configuration -> list separator. the value here probably is in “; “Your should change it to “ , “ then the excel should open the CSV file in a right way.
Hey I'm having a little trouble with the last part of this. My Flow works, and it's created the file in the SharePoint site, but it won't open properly. Is there a way to make it into an Excel workable CSV?
Check your computer to see what CSV file defaults to. My computer opens these files in Excel automatically
Thank you very much Shane
Glad to help. 😀
Very useful video.I have a requirement where i want to convert the created csv to excel and send as a attachment via mail.please let me know how to convert it to excel witout storing it somewhere.
Where do you originally go through the JSON function in detail? It says it wants data, but it doesn't accept collections or the original SharePoint data source.
Hello @Shane Young, while exporting EXCEL from PowerApps I am getting unrefined values.
I have used SharePoint's collection.
Can u Kindly help on this.
It would be a great help.
Not sure why. 😑
I didn't undestand a part. Do I need have two equal sources? ColEmployee (excel) and Enployee (Sharepoint)?
They wouldn’t have to be the same. Once you have the CSV you can do anything you want with it in flow.
Awesome as always...thanks a lot
My pleasure!
Hey it’s a great video... but I am facing a problem when I create file in Sharepoint. First time it runs but then consequently when we export it shows failed run... failed to crest file... it works fine with onedrive but I want it to run in share point so can u suggest anything that can be done?
Not sure. What is the error. If it runs once, it should run more.
Shane Young the error is file not created and the last step of creating the file fails
Shane Young I guess it’s because the file cannot overwrite so I guess we have to delete previous one and add new...
Hi, my sharepoint table has richtext format on some fields, and if i use this method i get all the richtext info in the csv. Can i export the data without the richtext formatting?
how to give an header to the csv file. like i want to name ur list as employee details with user logged in and then the list need to be printed
Is it possible to export to a TXT file instead of a CSV? I've been weeks trying to find a solution for this
Use the create file action. If you give it text and set the extension to txt should work. I have never tried but have made html files which are same and they worked.
Super input you Are making. Q? can you make a video(power app) where you import 3 col from a Excel sheet 1. Then in the app insert 4 New values and a date in the same Excel file but in sheet 2
Yikes Jens. I will have to think about that but I have never done anything like that so not sure I am much help with a video.