PowerApps Paste from Excel Table, update, and then upload to data source

in today's show we'll talk how to paste data from excel or your clipboard into powerapps that's right we have one big text input we're going to paste the data in and then we're going to do magic to turn it into rows and columns break it up in individual cells all that type of crazy stuff that you guys have been thinking about or if you haven't been thinking about shows a lot of the mechanics of powerapps but first here's our intro hi my name is shane young with powerapps 9-1-1 those guys and today we're going to talk about how to paste data into powerapps so whether it's in your clipboard or something you went and copied out of excel and you wanted the rows and the columns and you wanted to have it spliced right we've talked about importing csvs but sometimes you just have it open you just want to copy and paste it into an input and then have that data broken back out into the rows and columns so we're going to talk through that and then once we kind of understand how that works then we're going to throw the data to make it editable because why not and then save all those records over to our data source which in our case will be sharepoint and this has been a popular question i've gotten the last week i don't know why it's popular for this week but it is but the idea here is that you want to just you know you've got some data you want to like paste in a bunch of employees we're going to do and then we're going to set some header information that we want to be the same for all of them and push that over okay that's enough blah blah blah let's switch over to my desktop and just demo no magic here on my desktop we can see we've just got some nice app here waiting on this little hat here i wish i'd make a rabbit come out i didn't get that far and so if i open up my excel file what i'm going to do is i'm just going to grab these three rows copy them all right i like it so notice it was rows and columns i think it's three by three so if we paste this in all right it's three by five and so we got the data sitting here but then i say hey load that into magic so here you can see that we're using a data table which you know i hate but i know because a lot of people are doing this from excel they want to see it in a data table so i embraced it for you guys and so then down down here at the bottom i just showed you also you could do with html table because if you're thinking about turning this data into a pdf that's what you'd want to do so we're not going to build this one though to start because this one's very very very very complicated and i don't like but if we go over here also a little bit of a simpler approach so let's just wipe out all these real quick we'll go back to our excel and so in our excel let's just grab a single column right because this was the original use case that i was set out to do so we're going to solve this one in in great detail and then we'll talk a little bit about how this one because this one's infinitely harder this one makes a lot of sense i think so copy those that row or those rows of data we paste that in and they show up over here in a gallery notice also here that we're setting some defaults what department are they in let's make everybody executives and let's make everyone's favorite color blue as you can see that over here on the right we've got a gallery and then the idea is i'm like oh you know what actually chewie didn't join executive he joined accounting and ferguson joined i.t and ferguson like screen right we can change the dates all these controls are editable very cool so i could edit everyone's record i'm not going to and then if i say create employees then if we jump over to sharepoint and scroll to the bottom oh if we hit refresh now if we scroll the bottom yay look we added the people you can see the different departments the data i didn't add all the data i think i also did the email addresses so we're going to build through how all that works because it's magic no it's not magic all right so before we dive into how this all works so what we're going to do is we're going to we're going to dive in how it works we're not going to break this one down we're just going to spin up a new screen so the first thing you need is a text input so grab a text input i'm gonna get the default back to nothing and then i'm gonna go over here and change it to multi-line not a requirement but i think it makes a lot more sense so we do that okay now if we go into play mode to paste in our data there it is so we've got data in there and if we were just to throw a label or something over here right we know that if we just used text input what is that three dot text we would see all the data you inputted and it shows up like that but it's not broken out so the first thing i had to do was i had to figure out okay well how am i going to break this apart i want to turn this into not text but into a table so it would show up in a gallery so let's add a gallery right here and then let's just pull this thing over and then let's just say hey gallery just show me title and subtitle there you go okay so for the items it needs to be a table so obviously we can't just do this i wish we could right copy paste this in it has no idea because we're giving it text and if you hover it'll tell you expects table fair enough so what we're going to do is we'll use the function called split so split text input three dot text and so then now the hard thing is what do you want to split it on i guessed and believe it or not i guess right on the very first try so i a phrase i'll spend an hour guessing but it is the ascii character for a new line which is 10. so char 10 is a function that you give it a ascii number number and it translates it so if we do that we'll see that this becomes a table if we expand out is a single column table so split will always produce a single column table and so there'll be a result column and then all of the data between each one of the character tins which is a new line uh that was copied and pasted in there but you couldn't see it right you can't visualize character 10 you just knew it was there so that gives us all the data interesting so then now if we go over here and say hey you i want you to be this item dot result now we see all of the people that you pasted over here in the gallery what's also important to note is if we go over here and edit this it is live right so if we just go up here and add a new one at the front so we'll add a daniel lemay because he was the one that helped me figure out a bunch of this stuff so by adding daniel it just shows up because the text is changing and the gallery's items it just says say wherever there's an enter split interesting so then now the problem is is all right so that got me halfway there but now i want to split these on the space so the way that i do it and then we'll show you daniel's way in a bit is i went right here so we know that we have result or so we have this item.result and so that's the whole text but i want to show just the first name right so what we're going to do here is i'm going to use the left function say hey give me the left of the string so of this string so start with daniel may give me the left and how many characters do i want to go well there's six characters in his name so six but right but ferguson's got more than six and adeline's got more and right chewie's only five so this works but it's hard-coded so what you want to do now is make this six baby steps so if you use the find function the find function says find the text i want to find the space like so and within the text this item dot result and then that gives me look at that that is everyone's first name split out interesting now you're all right well that works so that's the first names cool cool cool so what you could do insert another label and so let's get the last name so the last names are very similar but instead of using left we're going to use mid so mid this item.result where do you want to start we want to start with that same find the space and then this item dot result and with font emit if you don't give it a third parameter then it will just take the rest of the line so this should give us everyone's last names now if you look carefully though you're going to notice that there's a little space in the front right because in reality it's not starting it's showing a space lemme space young so there's two different ways to do get rid of this the way that i do it is i just do a plus one so it says find that where the space is move over one character which will be the first letter and the last name and then put it in there um what we'll see in a minute when i show you the other code is that daniel uses the trim function which just says take off any spaces on the front or back of any of these strings so that way he doesn't have to worry about accounting for it either way it's fine that's it's a fun game daniel and i play like i show him what i did like i show him the end result and then he goes and builds it and he's like this is how i did it and we always find that we do things slightly differently and neither way is ever right or wrong it's just different but look at that so now we have pasted a single column from excel into powerapps and then broke it broke it out right and it doesn't have to be excel so if you have the data in notepad or an email right or just if you have flat text like this with lines between them right so someone hit enter every time you know i think let's try this i'm going to be brave so open up notepad one sec i'll be honest i did not practice this part but if we do um so let's do chewie and then allison boswell and then who do we not pick on much steven there you go so i grabbed these three people so once again copy go back over here we hit play because you have to be in play to paste paste this in kind of cool now you might have also noticed here's paste from excel again so if we copy this if we paste in so notice down here we have a blank row so this happens a lot when i've been cutting and pasting so if you have that come up which i need you to do in those cases is you can just filter this data right so this items returns a table what does filter one filter wants a table so we just say filter where not is blank result and so then that will filter out the blank records interesting notice this new way of writing this as well so i usually just do the exclamation point out here in front daniel actually tripped over this last week or two weeks ago i don't know that you could just write not as blank and it would behave the same way as putting it in parentheses so i don't know that way i usually do it but he showed me that i thought i would show you guys that but that's how i usually write it same thing so so there you go so that's how you take pasted data and split it so splitting on character 10. interesting right now the other one um or so now i guess now that we've kind of seen how this works now let's go back over to this one that is fully working and just talk about what this has so if we look at our gallery you should have an idea what we're going to see there but what we should we see here is that daniel did it a little differently but the same type of thing so he used a for all and then his filtered split data and he filtered it all right so he kind of did everything into one fail swoop but notice the way that he did this because if you didn't realize it for all outputs a table that's why a gallery can take for all as an input right right you probably didn't realize that but it can so it's saying hey generate this table in this manner and then just pass that in the gallery and that's why the gallery shows us the data but so let's talk about it so for all so for every record in this table what is this table it's a filtered version of our split out information from input names and input names is the big box over here so for every row in that table we're going to generate a row and so the reason daniel did it this way is he wanted to actually have column names right so now it has first name last name department favorite color and date you can see that for first name he did a split and trim like i talked about but he said hey split it remember this returns a table give me the first record which would happen to be the first name the result from the first record then to get the last name split it give me the last record in that table and then the results then department is the drop down selected department from right here and favorite color is the um color from right here interesting so once again same thing same result as we did a minute ago he just did it this way because now if you know if you remember over here right when we just did mine so i had to i don't have first name and last name stored separately i just kind of rendered them each for a label if you look at daniel's what did he do he broke them out so if we go into this text input it is this item.first name this one is this item last name this one is chevron no go to default title right you get the idea he was able to reference all of these also notice that he used controls here because what we wanted to be able to do was edit the data so instead of chewy young it can be chewy da moose young right and you know like we did earlier so we'll put him in dog caretakers and let's go delete the sharepoint data so we don't confuse it let's just do this shift create my sharepoint list up i said delete try again delete okay empty so but so you can come over here you can modify this data and so as you modify the data we're not saving the data which is not always the way we do it but it's why we did it for this one so then how do we then take this data and push it to sharepoint glad you asked we went to on select and so this is for showing my my loading icon this is for stopping my loading icon so we did it for all and so we said for all gallery 10. all items 10th gallery before we got this right um so as items this just lets us more directly reference the values but i want you guys to realize that you can for all through a gallery is all items because what's interesting about that let's pull this up a little bit before i show you that go up is that not only are the columns there which you knew and expected but look our drop downs and our inputs are all here so this is how you reference data in a gallery if you're not saving it right like in the other in the repeating table like info path video i packaged it all up but here we're just using the controls so when we look here we're going to say hey for all the items patch we hard coded all the titles to be newbies but we set the first name to be items dot input first which is a control dot text so this is how i did that input and then that input and then that one was a drop down that one was a drop down or a date and another drop down and then on the fly i hard coded the email address by concatenating the first name a period last name and then at powerapps911 so you are able to work through all of this um but that's the interesting thing about using gallery.all items now one of the most common mistakes you'll make though is you're like shane i don't need items i just do input.firstname if you write it like that what will happen is all it'll use the first first name so they would all have i don't remember who the first person was they would all have my first name how lucky for them but so don't do this this is a common mistake when you're looping through a gallery instead it's items oh you got to spell items correctly though items dot input first name dot text and then that will give you the input field from the specific row you're on right kind of opens up some new ideas that's the beauty of this video right even if you're not trying to do any of this there's a lot of little techniques in here that you can take and extrapolate into more cool awesome things that you want but there you go so that should all work so then once again if we just hit create employees we get my new little spit my little gif and then now down here if we refresh and scroll to the bottom look chewie the moose he's a dog dog caretakers i don't know why chewie is taking care of himself i wish he could i mean a lot easier he's currently in trouble where is he he's he's laying on the floor he's pouting because he was barking when i was trying to record it's very rude anyway you don't care about chewie being in trouble maybe you do but there you go so that is how all this works kind of fun right i said a few different people have asked about this this week and remember if you want this app you can go out to the curated library right so if you go to powerappstraining or training.powerapps91.com and sign up for the curated library you can just download this working app with all this crazy code in it you could also sign up for one of my upcoming live classes while you're out here anyway so now as a bonus what i want to do is take a minute so now that you kind of understand some of that let's talk about going the next step i we're not going to go all the way down that rabbit hole it is way too complicated for this video probably too complicated for any video maybe my advanced classes where it belongs but so over here we went a step further right so i pasted in three columns we got three columns let's see if it's really eight let's just paste a single column in this case i only have one column no big deal with the data oh look everything refined down to one column yay and then as you can probably guess if we do two columns just to show that it works as well load the data i know i'm a dork it's fine okay so the important thing to understand here so we talked about that the new lines are all separated by um character 10 right which is the new line the inner i forget what its technical name is so when you paste data from excel the way that it what really happens is all the data is tab delimited which is character nine so if we jump over here for a second let's just go to another new screen and if we insert a label and then we say hey i want to have a gallery again i told you we use a lot of galleries for this stuff pull this over here set you just to be tight on subtitle oh not a text text input chain come on get it together multi-line and so then if we just paste in our two lines so we hit play to paste that in so the way that this breaks up same type of thing right so you would you can say splits and then textinput4.text and what we want to do so first you want to split on character 10 like so so then now if we set this to this item dot result like we did earlier so that gives you the whole row okay so that is how you get the whole row which we've already done now you want to split again on um instead of character um 10 we can use character 9 as how we would split on um [Music] the the tabs and so if you do this now you can see shane right so every column ends up with its own thing but powerapps gets pretty confused right because because you've got like multiple piece multiple things going on so what i had to do was take that and then i believe let's try this on the fly we should be able to add a column and so then we'll just call this uh first name yeah column so first name and so then you know if we're doing like we did earlier we could split this time but so instead of splitting on um our split results and then what are we splitting on we're splitting on character 09 something like that so then that should give us um i think i'm parenthesis short there we go so then now if we go down here and edit the formula bar this could be this item dot first name oh expected text and i'm giving it a record so it tells me i screwed up split let's see oh oh because we want the first name so it's split that and we want the first record from that like what did i do wrong i always do something wrong i'm so split that and then that would be dot results there you go and so then that splits out the first names and so then you're like well shane how do i do the last name and so it's the same type of thing right so um i'll copy this formula copy and then last name and then that and so then now if we go down here we'll shrink this insert another label and we'll say hey you show me last name oh i didn't change my formula so instead of first here this is last so now we have broken on tabs and we just i use the add columns just a different way of doing this right i wanted to kind of show you guys different thought processes how to do it and if you do format text this is probably a lot easier to read so what are we doing we're first we're splitting this out that's generated a table then once we have this table we're using the add columns function to add columns to that table so right this would just have one column called result then in the first name column by taking that result column splitting it and just getting out the first value the thing on the left that's the first and then the last name is do the same type of thing but do the last and so then now if you show it right you get first name last name and result if you really wanted to be you know fancy you could drop the result column if you just want it split out so that's how you think about breaking up if you want to have multiple columns commands they are tab delimited which is an 09 now the last thing we're not going to go in much detail through here but i will show you this real quick so this is the chaos right so in order for me to do what you see over here where i needed to uh break it apart split it up and then put it all back together in the right form and i want to have dynamic columns i spent i don't know two hours writing all of these but you can see i have to kind of break it up in a bunch of steps get it i had some row numbers once i had row numbers i compute how many columns i have based on the number of columns i have i added column numbers and then finally once i did all that then i patched all that back together into this cold final which had the three columns which had the different values and so in this case even though i was only pasting in two right even though i only pasted into it still knows to pull in three i'm sorry it still has three columns it's just showing two because it knows there's only two columns that have information in it so this is not one we're going to go through today this is i don't know an hour worth of talking to explain to you how i wrote all this but remember if you signed up for that curated library you can download the app and you can get that working code and once you've got that working code right because really what this does is puts it all in the end into that perfect collection called coal final and then over here we're just you know the items property for this is just cold final and then finally the items property for this right just an html text controllers html text right there um i'm just using a concatenate here to you know if there's all the columns build a dynamic html table okay i don't know what you but that was that was a lot so i i've been working on this thing for hours i will also remind you all that i did a lot of this work yesterday and then denmark and england came on and i forgot to hit save and then my browser reset so i'm gonna do all the work a second time so just a pro tip as well remember when you're building these apps save early save often don't lose your data powerapps auto saves every two minutes after you do the first save this guy forgot to do the first save okay questions comments ideas ways that you want to see this go further right i think this is really exciting and awesome being able to paste data in like this and then split it and work it but it's a lot of moving pieces so but anyway if you haven't even throw any feedback leave them below i'm always game and with that i'm gonna say thanks and have a great day before you go be sure to click on the subscribe button over here so that way you'll be notified when new videos come out if you need any help or you want to work together whether your problem is big or small check us out at powerapps911 we do it all i rhymed or if you're looking for more formal training offerings we have those linked up here somewhere so check them out thanks and have a great day

Channel: Shane Young

Views: 6,749

Rating: 4.9499998 out of 5

Keywords: Shane Young, powerapps911, PowerApps, Power Apps, powerapps paste from clipboard, powerapps paste from excel, powerapps excel table text, powerapps split, powerapps left, powerapps mid, powerapps char function, powerapps copy and, powerapps copy and paste, Powerapps excel table import, powerapps create multiple sharepoint items, powerapps update multiple records, powerapps update collection, powerapps text input, powerapps text input to sharepoint list, powerapps textinput clear

Id: foGaOrBzVLc

Length: 26min 5sec (1565 seconds)

Published: Thu Jul 08 2021