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

So much fun and so much to learn. Paste rows and columns from Excel (or other text) and then have Power Apps parse it. Then you can edit the data and use it to make bulk updates to a data source like SharePoint or Dataverse. So much to learn.
Get the download by subscribing at training.powerapps911.com/cou...
Power Apps Consulting and training at www.PowerApps911.com

[@tenisviejos]

Great video, Shane. This opens the door to so many options: we can ask the user to drop massive inputs created in excel directly in powerapps. Thanks a lot!

[@dainiuszivelis4153]

Thank you Shane for an Idea. I made it! Any number of columns to a Gallery and then to any data source. Yes!!! You are my hero!

[@ShanesCows]

Awesome!

[@tedbabcock5887]

Awesome, Shane! Works just as advertised. But it also made me think of other capabilities I need, such as returning an array from a flow (whose data ultimately comes from SQL Server). Request/Response works great for this, but you can have only one JSON schema returning anything to Power Apps from a flow, and I wanted to have some flexibility with the returned data. I played around a while. Returning a stringified CSV table from flow and Substituting Char(09) for the commas doesn't work if you have commas in any of the text fields. Then I tried a stringified HTML table, and it worked! I have to Substitute Char(09) and Char(10) for certain HTML tags, and empty strings for the others. Then your magic formula does the rest. I can even pull out the column headings, because they're tagged differently than the data rows. I'll have to deal with the data types, because the HTML table returns text. But that shouldn't be too hard. Your formula is hard-coded for up to three columns. Next step: making the number of columns dynamic. You left that for us on purpose, right? So we could exercise our own muscles a bit? :-) Many thanks, Shane. You are the man!

[@asimrehan73]

Shane, After watching this video, i was able to build a great app. Big Kudos to you…. Thanks, thanks, thanks …

[@ShanesCows]

Nice work!

[@AmyCustomer]

Love it! Thanks so much.

[@martinponce8674]

I just happened to need this for an app a few hours ago. Perfect timming posting the video Shane :O

[@ShanesCows]

Awesome Martin! 😁

[@darlenesuarez7542]

Once again, you have exactly what I was looking for. Thanks

[@ShanesCows]

Happy to help. Have a great day. 🐶

[@angelmurillo7579]

Thanks Shane,you're awesome

[@GutoRos]

Haven't tested yet, but this is probably what is going to save my projects from doing crazy staff haha. Thank you!

[@ShanesCows]

Awesome, glad to help.

[@capucinevetter8904]

Thank you Shane ! If like me some, other people are look to take middle column (not only using First and Last ), I found the following formula: Last(FirstN(Split(Result,Char(09)),2) --> For example this will take the 2nd column of the pasted data.

[@Alpacastan21m]

Replace 2 with 3,4,5 etc for 3rd 4th and 5th columns?

[@juanspottorno5615]

That one works just great and rounds the circle!!!! thanks Shane! and thank you very much Capucine vetter too!!

[@markmckeon4764]

I found this AFTER coming to this conclusion but with a copy/pasted tab value in "" like " " vs Char(09) works too.

[@moyura2]

Thanks for sharing love the idea flowing from client back to the server. I would love to see you solving this problem via building Components which allow user input CVS or Excel file then output into a list view e.g. Gallery, make some changes if it's needed, then push data to the server e.g. PowerApps.

[@stephendarier1390]

Thank you for pushing through after it not saving. Great video mind blowing 🤯

[@ShanesCows]

No problem! It can be frustrating at times making content but usually worth it in the end.

[@juandavila4035]

Thanks, I love your videos, all are very useful

[@liktorjanos3376]

Thanks man, you are a time saver function in the big machine :D

[@ShanesCows]

Glad to hear that!

[@oseiyere2713]

This is definitely helpful. Thank you

[@ShanesCows]

I'm so glad!

[@rthoughts7232]

Awesome awesome and just awesome 👍🏼

[@ShanesCows]

THanks!!!🐶

[@terryhughes1005]

Of course what I came here to learn is what is "too complex" for the video. Please start making videos on these complex concepts as these are what the real world applications require ("low code, no code" my @$$ in order to do true app development) Oh and also needed since things like .Result are deprecated. Thanks!!!!

[@ShanesCows]

Remind me, what did I say was too complicated? I can probably point you in a direction.

[@franknielsen3219]

Wow - my head just exploded. That is what I call black magic :)

[@ShanesCows]

Sorry for your head Frank. 🤯

[@georgegualda4262]

Excellent Video... I'm going to try it Dataverse and see how it works.

[@ShanesCows]

Awesomeness! Should be fine. 😊

[@rrq177738]

Hi Shane, Great video! Love your content! At 22:50 I used that code for my table and it worked perfectly but how can I do this for a third column I can’t figure it out?

[@victordauden9173]

Hi Shane, for some reason I keep coming back to this video. I do have a video idea related to this video but using the new modern table. Do you think you it is possible copy & pasting excel date into modern table and then saving it into a databe? Thx for your insights

[@Y0urN3m3sis]

Hi Shane, Great video indeed. This seems like an alternate solution when uploading an excel workbook which seems to be taking too much memory power. How many rows do you think, PowerApps can load at a go?

[@MSKah]

U'r awesome!

[@ShanesCows]

You too!

[@misc_trash]

This is great, but I'm struggling with the patching back to my data source. I used the data table version (where multiple columns are pasted in) and it's showing in the table itself beautifully but I can't seem to work out the ForAll/Patch to then submit this data - do you have any advice?

[@MrDionmar]

Tks Shane!!!! How can I do that, to paste data with more than three columns ? Thankyou for share !!!!

[@peterwilliams8759]

how would you split up a csv file with multiple tab columns and spaces ?

[@sundrapillay6668]

Thanks Shane

[@ShanesCows]

Happy to help. This one was a lot of fun for me!

[@Nadia-we3ht]

Hi Shane, Very helping tutorials. I have a question about Power Automate Desktop. I need help in getting information of logged in user in Power automate Desktop . I want to create a flow in Power Automate Desktop that captures information about logged in users ( such as full name, UPN, sign in and sign out) from window > these Users / PCs are Azure AD Joined PCs.

[@fabienlaunay1869]

great! I think I'll use it to update sharepoint lists ;-)

[@ShanesCows]

Awesome

[@marcelonavarro3586]

Hi Shane. This is a great solution. Is it still available for download? Thank you!

[@JoshAutomates]

Hi Shane. Is it possible to send Microsot API generated graph as image in power apps? By base64 or json?

[@gouravladha]

Hi Shane, this is a fantastic video thanks a lot, changed the way I asked my users to enter data into SharePoint. I have used the learning from the video but get stuck with a problem related to having Multiple words/Sentences in a single column. In my App, I have 10 columns and in most columns, user inputs are single words like you shown in the video. But in a few cases, it is like "My Name is XYZ". I am splitting the data from Excel using " " as a delimiter and in these cases, it breaks the sentence and moves it to the next column impacting the data of other columns. I tried using Char (09) & Char (10) but does not work. Can you please advise how to fix this issue.

[@soumyendupaul9556]

Thank you Shane for your awesome videos on power apps. Since this video is related to excel , just a weird idea -can we build like excel pivot table in power apps?

[@ShanesCows]

I have never tried. I think you would have more luck trying to do it in Power BI.

[@pooptommy123]

This is great, but is there anyway to allow a user to import/load an excel sheet to use within the powerapp? We do not want users to copy and paste.

[@coolcaliguy]

Shane, can you do a video on inventory ordering where you should be able to select items in a gallery and the selected items then show up on orders screen listed by supplier, TIA.

[@ShanesCows]

Something like this? kzbin.info/www/bejne/kKXRZGyBqrSKZsk

[@cabralx]

Excelent Video! I have one question: if we import and the items already exist in the list how to only update these records?

[@ShanesCows]

That would make things much harder. YOu can check out this video on avoiding duplicates. kzbin.info/www/bejne/d6GWkHiXbq1rmJI It would be a way to find if the record already existed. Then you would need to write different logic to react. Good luck :)

[@rpremaza]

Hey Shane.... Do you have a video that explained the Multi Dynamic Columns.... I have that exact requirement. I have a 12 column spreadsheet I need to paste into a powerapp

[@ShanesCows]

I don't think I do Rob. 😐

[@bexjuice]

Hi Shane, I can split data with two columns like a pro now :) thanks to you. But I cannot do it when I paste data with three columns. There is FIRST( and LAST( formulas but no MIDDLE. How do you do that? Can you drop your formula for splitting three or more columns?

[@MrDionmar]

I have the same doubt!!!

[@npc118]

I have the dumbest question in getting so lost too many videos and not sure where to start. So can this be used to create a form. When you first launch PowerApps what am I selecting to even mimic what you are doing?

[@billmartin786]

Is there a way to add to this and map the columns to a specific column in sharepoint? perhaps the data being pasted has different column names, or the columns aren't always in the same order.

[@ShanesCows]

The Split is outputting a table so you could loop through the table and do anything you wanted with it. In the video I show using ForAll and patch to save to the data source. What do you want to do different? I don't think you could have the data in different order and make it work, you would need consistency in what you paste. Maybe with crazy logic you could be more flexible nothing easy comes to mind.

[@billmartin786]

@@ShanesCows Thanks for the quick response. what I'm really looking to do is be able to use header rows and map specific columns to specific columns in the share point. I have a use case where the data we need to import is formatted differently, I need to be able to take those different excel files and import them into sharepoint lists. essentially I want to create an import data wizard within my app. Paste data with headers in a text box, or upload an excel workbook > map the columns to the relevant columns in sharepoint. create/update records for each record in the imported data. other languages there's methods to refer to columns within records dynamically, which would make this easy. that's not an option in powerapps. any suggestions would be great!

[@unetseulement]

any idea if an excel column have spaces in its value, example excel: Column1: Shane, Column2: Young, Column3: 123 Home Address, Column4:Love dogs. How do you go about putting this into a Collection/Gallery?

[@unetseulement]

should have watched the entire video :) char(09) it is

[@gjovalinnikolli592]

I have made the subscription but i didnt find the link to download this fantastic application

[@ShanesCows]

You subscribed to training.powerapps911.com Curated Library? If so, you can email training@ PowerApps911.com to get assistance. 😀

[@user-nh2tu2ym3v]

How can I acess the App to see the other part of the code?

[@ShanesCows]

training.powerapps911.com/courses/video-library you can subscribe and then you can download the working app.

[@rthoughts7232]

Is there any video, when we import Excel or CSV in collections in PowerApps??

[@paulkroon]

You could check out this video I made. It is on data from external source, bur handels just what you want. kzbin.info/www/bejne/i4XYlpefYpqJirM

[@ShanesCows]

This one kzbin.info/www/bejne/n57Lqaxph6qajsU takes a collection and exports it to CSV. This one kzbin.info/www/bejne/h5S6m3SqbLaJpJY imports from Excel.

[@rthoughts7232]

@@ShanesCows thnx , HOWEVER my query was little different at the end. For 2nd video. You are reading excel and creating data into SharePoint list, but if it's possible to create collection in PowerApps apps from those excel data row. I wanted to have collection created from Excel data read.

[@rthoughts7232]

@@paulkroon very great video. This was the thing that I was looking for. This helps a lot, doing the same thing without premium connector. Thnx a ton 🥳🥳

[@paulkroon]

@@rthoughts7232 nice to hear it was helpfull ;-)

[@carlosalexleon]

Hey Shane i have a SharePoint column with EMP ID as text with 00000 format but my flow concerts to integer. Any advice?

[@carlosalexleon]

None in the Google searches provided the solution i was looking for

[@sonammishra2964]

How to break if we have multiple columns... U just shown in this video using 1st and last name but if we have more then 10 20 column how to break it Please help .

[@sandysmile1047]

Did you get any reply on this

[@sonammishra2964]

With( {item: [@IssueTracker]}, Filter( item, StartsWith( Title, Last( FirstN( Split( TextSearchBox.Text, “+” ).Result, 1 ).Result ).Result ) || StartsWith( ‘Issue description’, Last( FirstN( Split( TextSearchBox.Text, “+” ).Result, 2 ).Result ).Result ) ) )

[@sonammishra2964]

I did in this way Fist we need to split textbox value into column using char etc Then by referring no position of anytextvalue need to define col 1 col 2 3 etc

[@sandysmile1047]

@@sonammishra2964 thanks yaar