Export CSV from Power Apps

[Music] hey everyone this is Daniel and in today's video I'm going to talk about how you can export CSV from a power amps so even though this sounds like a simple things like hey all you're doing is exporting a CSV believe it or not this functionality is actually not available but if you need it here's how you do it so what I'm going to do is I actually have a power app and we'll go ahead and build a really quick one but then from there we'll create a gallery and then from the gallery they're gonna go an export what we will do is we'll use power automate which is build a flow and well then I will use the onedrive data connector to actually do the conversion so that's basically the whole premise of this video right here and this recording so enough of the talking let's go jump into the video okay so I'm going to end with this presentation and we're gonna jump straight into creating an app create a blank canvas one all the old CSV this has already have its psp2 to demo create that creating it the whole idea for this one is I'll just create a sample app add a gallery to it get some data to that gallery and then we'll go ahead and export that the exporting to CSV is the whole main premise of this one but I gotta have something to show you how to do that so that's that's what we're gonna do all right make sure I got that over there give it a you know different colors make sure it's all saved and then once it's done saving we'll go back and start building the app so first thing I like to do is I go and build a header and a footer so it kind of gives it a little bit of a depth so that make it a little darker made this one light call it X whoops call it export CSV Center give this a 28 nice copy that paste it down my favorite footer is powered by power naps also let's change the theme of this a little bit yeah nice and dark okay so now that we've got that let's go and get some data and I have a connector already for sequel with some data in here so that's what I'm gonna do two tables I'm gonna get I have the state for all of us you know states in our country and then all the cities and counties over there so that's what I'm gonna do so I'm gonna give the state capitol and then the state cities these are the two that I'm gonna grab okay now let's go and create the gallery so I'll grab this one here and I'm gonna change it to these three cuz I'm gonna see is text here I can remove that icon and I'm gonna change things around a little bit and this will actually be the state City County so let's do the city we'll keep the city aside over there a little bit we do need the state so we'll keep that over here that's just the county and move that around I'll make a copy of that and this is going to be the state stayed long I'll also go grab the state short that baby can just have a little bigger looking table just so that it looks like there's some data over here that then we had city cities good I'm gonna go grab the county so I think I mean we don't need that so we've got some amount of data now to work with what you can do is if you want to make it a little bit more nicer you can add some more labels over here you know give it that same color the fill will be the exact same yeah and this is to tell you that this is the state basically you're just putting headers over here this is something that I do is I actually use galleries instead of the data tables because then I can make the data table look exactly like a gallery or the data table yeah like the gallery look exactly like a data table but it's just so much more easier to work with galleries over here so again this is just a little tidbit that I'm showing this way here is the county okay so we got some data right now let's add a little bit taken to the next level is also providing the options to filter it that way you know we're not exporting too much data so for that I'm gonna do quickly drop down this one here I'll go grab the state and video and that's just gonna be state yep and we can you know sort do a sort by columns and it's going to be state here make sure yeah the state okay got that and now I'm going to drop another one another drop down and that's how we will get our counties as well so let's start with dbo state cities and then just to make sure over here I get County okay and then now what I'm gonna do for the county is I want to make sure that the counties are filtered based on the state that I've selected so for that I'm gonna call our favorite filter function which is filter here on the filter there is a column called state long and that's state long I'm going to use the state long filter based on the selection I've done over here so normally I would go ahead and tag these labels to something to make sense so I would actually call this as state you know DD for drop-down whatever that works for you but make sure you get it correctly because it really helps I'm gonna do that don't select it and this is the state so that filtering should work because now based on whatever I selected over here these will change let's just make sure that I'm actually getting County all right not what I want to do though is I also want to do a sort by columns cuz I wanna sort based on the actual County so now it'll be nicely sorted but I also want to get distinct because right now I should be getting a lot of duplicates as well just way too many see that there's too many duplicates coming in so I just want to get a distinct value of each so that's why in two distinct again again this thing based on the County and now when I look at it these are all the nice counties in the state of Alaska right so that was the easy part you went down and got at least the basic app ready I'm going to save it and now let's work on the fun part so this is how I recommend you go ahead and build the export piece to CSV first of all you don't want to get get get all the data over here what you want to do is you based on this filtering you want to take all of this and you want to go ahead and dump that into a collection so what I'll show you is it's get a button over here I'll just call that button for submit because we're submitting the data into a collection and really makes it easier because of two things one of them is you've limit the collection only with the data that you want that's one second thing is this over here will always be blank and the data only shows up after you've filtered it and that always the end users like that because they will you know be able to understand that hey the data that I'm seeing away here is only based on the filtered data and that doesn't have any confusions so it always kind of makes sense when you do that in fact many of the apps that I build this is how I do it let me kind of walk you through on submit or unselect I'm going to do a clear collect and I'm gonna call this as a temporary Co alpha collection and what I'm gonna do is I'm actually going to get the filtered data so I'll do the filtering filter and in this case I will filter it based on yeah the gallery whatever is the gallery that I get the gallery dot I can do all items and then I'm going to the city that's what it is the other state actually the state long equals this filled yeah ranked you just do that in fact let me just change that I spent a little bit time showing you guys how to do this it really wasn't that necessary what I can do is this submit button over here in fact this guy here it's items yeah will change that will take that to the clear collect all right you know what I just said let's let's start right over here again clear collect and I'm going to do the temporary collection collection to the filter filter and now I'm going to get that one here comma you know the state loan equals the selected item that I do over here drop-down select the dot state and then and I also want to filter it based on the county the county value comes from this one over here this drop-down they're cool and now what finally I just take this one over here and I change it by the temporary collection so watch what happens and I do that you will ask as it is this one if I had submit the data is coming in goes and takes the filtered data from the data source dumps it in the collection and I'll see that any second we're showing up which lab works like a charm same thing you know pick California go ahead and pick any other cities hit submit filters the data shows it up over here so whatever you're seeing over here is always the temporary data sitting in a collection but again that's not the whole premise of this one let me so let's continue over there what I'm gonna do now is I'm gonna drop another button because that's where all we're gonna go and get some more data so the first thing away here I'm gonna call this as the export button and the export button I'm going to now take up this data and I'm going to start creating the in other collections that's not going to work with variables over here so the first thing I want to do over here is I'm gonna actually call set a variable now the thing about that variable is very interesting because a variable actually temporarily stores all the data in a very specific format and that specific format is actually what is literally the comma separated value format but I'm temporary storing all that data the ones we've seen the gallery in that variable over here the global variable and then once I've saved all the data were there again in that specific formula format I'm gonna send it that variable and all the data over to flow and then flow with one drives their connections does the magic of exporting that as a CSV file but the way the data has to be presented in that format that I'm going to show you that's key that has to be done on the power apps over here because if you send any other format it's not gonna work it has to be send in this format over here so watch carefully how I do that piece so first thing is like I said I'm going to create a I'm gonna use a global variable using the set function so I'm gonna go ahead and create that variable how about that variable here and I'm just gonna create the variable right so that that part is done over here again remember shift-enter takes you to the next line and then for all gallery 1 items I do that I'm gonna do a collect and I'm going to create a temporary collection I'm gonna call that as the export all the way call that CSV collection this is Coll that right and then over here I'm gonna start giving some names which is gonna be in this new temporary collection so what's the connection gonna be I'm gonna call it a state long and that state long is gonna come from this gallery over here so let's actually make things a little easier for us I'm gonna call that as state yeah actually let's call that as a state yeah state law okay I'll call that as gonna be a state long yeah I'm gonna call this one a state short yeah this was City the reason I put in the guy later on it so that it makes it easier for you to understand which columns are actually in the gallery as against which one isn't got it state long and that is going to be the state long cow dot text next one is going to be state short and that's going to be the state short cow dot text comma what else did I take I take city and I'll do the city yeah also text and then finally it was the county and I'm gonna call it at the county gaol dot txt alright so those brackets two double brackets and that's that temporarily created for all the items in the gallery that you're seeing over there all the items in the gallery I'm gonna collect all that and I'm gonna put it temporarily in a collection over there alright so that just the reason I'm doing that is so that I can have that collection and now I'm going to take that collection and put all the data from the collection into the set variable so here's now the important piece of the formula is the set variable so I'm gonna call that set I'm gonna call this as success and I'll explain what is right here not you know except export CSV variable the one which we just created on the top that's the one that I'm going to use and now watch this it's not concatenate its content that's the one we are using concat and I'm gonna get the data from this export CSV collection and gotta make sure that I have all the data from there so export CSV collection the first one I'm gonna do is I'm gonna take the state long and now these things you're seeing over here they are coming based on all these column names that we put in the collection that's where this is coming from so the state law I do that in the comma separated value what do you need you need commas so that's what I'm putting in double quotes comma double quotes I'm gonna be doing a lot of that and remember there's the ampersand before so this combination of the ampersand double quotes ampersand this is gonna be a key thing that should be doing a lot to get that comma separated value this this is how that CSV comes in so let's continue because I got stayed I got short again ampersand double quotes comma and double quotes + % see kind of getting the hang of it this this is basically how it works all right so we got stayed long stage short next thing was city got the city again ampersand double quotes comma in double quotes did I put double quotes double quotes and then another ampersand and then finally there was the count County and any time you see a squiggle line like that which means you've done something wrong that in my case I put in the comma and so that's what happened Celie otherwise the intellisense would have not shown up now we've got the one line now I needed to come to the next line and the way I do the next line which is as simple as enter the entered I can't hit an enter over there so I gotta tell what that enter is and to do that it's chr or char CH r10 that is how it'll come to the next line and then I go to double brackets okay it is County and sure that's how I was missing one over there okay so now that we've gone ahead and created the the variable all the data is going to be in the variable so we're going to pause over here on the power app side we're gonna switch gears going over to the flow piece so let me go and save that saving and saving it the next thing we'll jump over to is flow come on go ahead and publish it alright so I'm going to get to flow I was my app was built in workflow on the workshop tenant so I'm gonna jump in and make sure my flow is on that button as well so I'm gonna instant from blank this one is going to be export CSV demo and this is gonna come from power apps go ahead and create that and then the next thing I'm gonna do is from onedrive business onedrive from business onedrive for business I am going to create a file and I just like to rename that to in between I'm creating a CSV file CSV file write unique path I'll keep it at the root file name I'm gonna ask in the power apps what that file name is I'm gonna ask in power apps over here I'm also going to get the content from fire labs the file reps I'm gonna ask in power apps but here once the file name comes I want that file name to be a dot CSV so do that dot CSV and then I'm going to respond back in power apps power F just won back the power ups and I'm gonna say something away and power ups I'm gonna say completed and I'm gonna give the value true and it'll make sense because what I'm doing is it is when I get the response back with completed equals true that means the flow lands successfully that is one big reason around doing it because the file is already created it's gonna create in onedrive and will sit over there and we'll call that file but how do I know if it was successful or not how did I know that the flow actually ran successfully and the way I know it is because of this completed equals true so me go ahead and click on save over here saving and we should have this ready now I can you know stay over here or I can get out of it they're gonna call this one over now any of you guys have used this before just realize that Daniel you can't actually made a mistake because they're gonna call the flow in this on select button that formulas gonna disappear nope didn't make a mistake over there I intentionally showed this for two reasons cuz I got to show you how I wrote the formula but secondly is now you'll see what the problem happens so one of the good things to do is just copy this all and open up your notepad or OneNote I use notepad plus plus you can use OneNote either one but you gotta have some way to save this data because I need to save it so I'm gonna now click over here and I'm gonna go to actions I'm gonna go on to flow and then the flow I am gonna call that form flow we just load export CSV demo okay so it's adding if everything is done well on the flow it should just go ahead and connect straight to that button over there and there should not be any errors another interesting thing we're here to see on the flow side if the connection is good on the flow to the from the power ups of the flow and even written in the form correctly these calls should name the exact same ones you put in the flow side remember those two variables that we added on the flow side or the Express on the flow side these are the exact names we came in over here so this is kind of just one way for you to quickly check to see yep it's it's all working so far everything's good okay but I'm gonna go back hit the home button over here because I'm gonna go and quickly paste IDs I'm gonna go outside shift enter and I'll go and get my data back I mean my remaining part of my formula okay right here I'm gonna send two things I need to send this file name so I'm gonna go ahead and already just come up with the file name CSV file all right you can just do that over here you can come up with any other ways to go and get a file name you could go and put in another you know text control over they said you know give me the end users the option to put the file in that's all the easy part so I'm not focusing on that I'm just focusing on I need a file name and that's how I'm gonna get it over here now the file content the file content is the actual data that's which I'm gonna send to the flow now you remember that data is all being stored over here now in the CSV piece over here so that's that's how it is coming in so let me go now and get the data and the data was the export CSV collection no it was the export CSV variable CSV variable that's the data that I'm sending over here now remember in the flow piece we've written it such that it is sending a value back as well that completed is true so I gotta make sure now I create another variable and what I'm gonna do is I'm gonna say it's call that as a success variable and you spell success this way success variable dot and completed no it's important that I do this because what I'm gonna do is once I'm gonna run this I will have that PDF file sitting over there but the reason I'm putting that completed is because I'm gonna create an if condition if condition will tell me is that hey if this ran successfully only then should it go ahead and download the file this completed really helps me from the power ups I'd know that the flow ran successfully it did its job and now we can go ahead and download that the next expression that I write this is very critical because initially I did not do this and sometimes of whatever reason if flow failed this would still attempt to download it and it would download the previous data which you had and that can actually cause a confusion to the end users they can you know this app will not be successful so what I'm doing is I'm kind of you know doing a foolproof process that the data that you're getting is the most updated data and this this is how you do that all right so I'm going to first let me just go ahead and run this because I gotta run it at least once to have my PDF file they'll be actually a PDF file which will sit away a PDF file a CSV file that'll sit over here which flow will create so let's just run this once do that we've got the data so I'm going to go and export see the ants running over there should everything run the button is completed let's go take a look at a flow reset a refresh flow ran successfully now if I come to onedrive yep there's my file if I you click on it you'll see the data is coming through nice CSV format alright see the data came through nicely so next thing is another critical thing is we gotta get that link so I'm going to click on this I'm gonna get copy link and just to be a little extra careful I'm going to change this to only view I'm not gonna do the allow editing okay just you know me being extra careful now I go ahead and copy this alright now that I've copied it I come back to my power app and on the export piece over here I add the final finishing touch to the formula I'm gonna say if success variable equals true go ahead and launch using the launch function double coats here is the URL I just got from the from the onedrive and then I'm going to go ahead here and I'm gonna add a question mark actually it's not question mark and I am person download equals one once again you need to know what this is it was this initial URL was the one we got from onedrive but you need to add this ampersand or this and download equals one that's what makes it download save that alright because it didn't like something let's see what didn't like oh you know they're brackets to do that now let's go ahead and do another place submits it you know the data comes from nicely go ahead and export that it's exporting it open up temporarily another bracket downloaded the file and the bra I mean answer it what temporarily opened up another tab download the file and the tab closed now here is the data my cells opening up on the side and it went ahead and grabbed us our data over here see it was just as simple as that the CSV file came through over here so one of the things that I want us to do is here we did the clear collect over here we did the submit that the export all the data is coming through one thing is let's come back over here if you refresh it it goes ahead and gets the data if I go ahead and run it again it'll just be refreshed again now key things I want you guys to know of is do not delete the CSV file you delete that CSV file and you just lost that link this CSV file has two always temporary be there so you can come up with some plans right you can come up with creating another folder over here and then you can save that file over there but whatever it is do not delete that because if you remember we came over here and we hadn't got that link that is the link we are referencing to get that CSV file so that's one thing over here second thing is when you're going ahead and writing to that entire writing to the entire gallery I mean sorry the the CSV was right of the variable over here you can go ahead and make that variable such that initially on select it becomes blank so that's that's basically what I'm doing it becomes blank and then after that I'm going to go ahead and then dump in the remaining data over there the becoming blank is what's handy because that will go ahead and take the it basically is the same thing as a clear collect because what happens is if I go ahead and pull other data in it's going to go ahead and or you know not keep appending if I want fresh data coming in and fresh T let me CSV so kind of don't forget that you're doing all this over here as well and basically that is it so let's just do another test let's go and get anything Connecticut get something else submit submit get me clear data over here I can do export and I see the CSV file too is because it's keeping track of all the files that you have so that's why you've already downloaded two before that's why you have this over here and then when I come back in see it just refresh to fresh new seconds which means that's a fresh copy and if I hit over here I should see at least three and there you go this is basically how you do it as a quick recap you had to build the app wherever you're building it to get the gallery my recommendation was once you get the gallery save it in a temporary collection kick the data from that collection and put it in this one set variable in a very specific format format which is the CSV format which was open the ampersand open brackets open you know double quotes comma double quotes ampersand that's how you're building the CSV format all saved in that one variable and then you're taking that variable sending it to via flow in flow we're using onedrive don't forget to put the dot CSV over here and that gets saved into the onedrive folder location and then we're sending a response back to power app saying that hey this was true and it was completed now the final thing is on the onedrive do not delete that temporary file because you're gonna be referencing that file over there that's why I did two things I didn't I said I changed it from edit to only view but the one thing you can do is actually save it in another folder and say in that folder call that folder name do not delete so nobody actually deletes it because you're gonna be constantly referencing that but that's basically it hopefully this was helpful to you as always go you know I'll put the link to the the blog it's actually on the community power community over there so you can get some of these formulas over there definitely subscribe so that you can see all my other videos over there and keep power rapping bye

Channel: Daniel Christian

Views: 28,384

Rating: 4.9612589 out of 5

Keywords: powerapps tutorial, power apps, microsoft powerapps, export csv from power apps, export csv from powerapps, Export PowerApps Gallery to CSV, Export data from PowerApps, Power Apps to CSV, CSV from PowerApps, CSV from Power Apps, Convert Power Apps gallery to CSV, Convert PowerApps gallery to CSV, PowerApps to CSV, PowerApps CSV, Power Apps CSV

Id: QTbVMu6DIfQ

Length: 32min 32sec (1952 seconds)

Published: Fri Nov 22 2019