cancel
Showing results for 
Search instead for 
Did you mean: 

Copy and Paste Data from Excel into PowerApps

Looking for the ability to copy and paste data from an excel table with multiple rows directly into PowerApps.

Status: New

How would the app look that would accept the table of data?  Is it a form?  Or a data table / grid?

Comments
Frequent Visitor

Hi everybody,

 

I found a way to achieve this using collection and other commands.

 

ForAll(Split(TextInput3.Text;Char(10));Collect(TotalCollection2;{C1:First(Split(Result;Char(9))).Result;C2:Last(FirstN(Split(Result;Char(9));2)).Result;C3:Last(FirstN(Split(Result;Char(9));3)).Result))

 

TextInput3 is where the data is pasted from Excel.

 

Using char(10) to split the records by rows and char(9) for columns

New Member

Definitely could use this feature. It seems possible given that it is implemented in Sharepoint lists already. If I take a record from excel and post it into a sharepoint list, it pastes each column value perfectly. Given the sharepoint/powerapps integration that seems to be suggested by Microsoft itself it would be awesome to have the 'finessed' front end of powerapps along with the ability to have excel-like data entry capabilities.

Frequent Visitor

@CP0822 ,

 

I'm excited to hear you had success with this, because I am trying to do the same thing.  I'm hoping I could get some clarification, since I am a PowerApps noob.  Here is a description of what I'm doing:

 

I have created a PowerApps form for my team to use when they need to have something printed.  A normal scenario would be a user has an Excel sheet with 20 unique items that need to be entered into the form, and I thought it would be great if they could just copy/paste from excel into the form, then specify a few additional details and press submit.  The form is then linked to Sharepoint where each Row from the PA Collection is entered as a separate list item.

 

Does your code (above) go on the Submit button?  And has the pasted text already been converted into a collection?  Would you mind describing your variables?

 

Thanks in advance!

 

Declan

 

 

Frequent Visitor

@dhalpin I just saw your comment I think that this code will definitly works with your case.

 

Let me answer your questions in following order:

1. Yes, this code needs to be on the Submit button. This is where the magic (Collection creation) happens

2. My variables are all text, below you can see a big TextInput control with multiple lines. I paste the data right from Excel there.

3. The text pasted will be converted on the submit button before "patching" the data in your Sharepoint list.

 

Example Screen.JPG

4. This is how my submit button is configured. Note that I'm just set 9 cases because the data need on the Textinput should have always 9 columns, but you can set the number of columns that you need regardless of the number of rows to submit

 

Clear(TotalCollection2);;ForAll(Split(TextInput3.Text;Char(10));Collect(TotalCollection2;{C1:First(Split(Result;Char(9))).Result;C2:Last(FirstN(Split(Result;Char(9));2)).Result;C3:Last(FirstN(Split(Result;Char(9));3)).Result;C4:Last(FirstN(Split(Result;Char(9));4)).Result;C5:Last(FirstN(Split(Result;Char(9));5)).Result;C6:Last(FirstN(Split(Result;Char(9));6)).Result;C7:Last(FirstN(Split(Result;Char(9));7)).Result;C8:Last(Split(Result;Char(9))).Result}));;

 

ForAll(TotalCollection2;Patch(Table2;Defaults(Table2);{RQID:C1};{FECHA_REQUEST:Now()};{SOLICITADO:TextInput1_3.Text};{CUSTOMER:TextInput1.Text};{RQID:C1};{ESTILO:C2};{TIPO_TELA:C3};{COLOR:C4};{NOMBRE_ARTE:C5};{COMPONENTE:C6};{FORMA_TELA:C8};{CANTIDAD:C7};{DUE_DATE:DatePicker1.SelectedDate};{ESTATUS:"Pendiente"}))

 

I hope I make my self clear. I'm not english native but I would help you in whatever you need.

Frequent Visitor

Muchos gracias @CP0822

 

I will be working with this over the next few days and I will let you know how it goes.

 

Otra vez- gracias!

Regular Visitor

@dhalpin , @CP0822 

 

Do either of you have any further explanations on this? I have tried but can not get it to work. The above code continues to give me errors. 

 

I am looking to paste from and Excel file into a TextInput Box as you both mentioned. I can get a button to split the pasted cells into multiple rows in PowerApps from a collection, but I have not been able to create a Table from it with multiple columns.

Frequent Visitor

@dyoder 

 

Hi,

 

For that porpuse you should use the first code that a wrote here combining ForAll, Split inside a collection.

 

First, you have to split the TextInput info by row using char(10) separator. Then you create a New Collection and split each column inside this new combining FirstN, Split and Char(9) separator.

 

You must add many separators equal to the number of columns that you want to create, and these info has to be separated by tab or char(9)

 

ForAll(Split(TextInput3.Text;Char(10));Collect(TotalCollection2;C1:First(Split(Result;Char(9))).Result;C2:Last(FirstN(Split(Result;Char(9));2)).Result;C3:Last(FirstN(Split(Result;Char(9));3)).Result;C4:Last(FirstN(Split(Result;Char(9));4)).Result;C5:Last(FirstN(Split(Result;Char(9));5)).Result;C6:Last(FirstN(Split(Result;Char(9));6)).Result;C7:Last(FirstN(Split(Result;Char(9));7)).Result;C8:Last(Split(Result;Char(9))).Result}))

 

Let me know any further questions

Frequent Visitor

@CP0822  This is exactly what I've been trying to do to no luck. THANK YOU for sharing this. I'll try it out and hope for the best.

 

I've been advocating PowerApps and even the Power Platform in general (e.g. Flow/Automate and PowerBI) since I started using it last year. However, some of the limitations and missing basic functionalities makes it hard for others, even myself sometimes, to get into it. 

Regular Visitor

This was a core capability in Access web apps if it worked like it did in AWA that would be fine. Just need paste function for multi row in a view. 

Regular Visitor

Never did update this, but I went a different direction.

 

Used Power Automate to read the Excel file, then write it to our SQL database. The final report is then located in Power BI. Depending on the development of this work flow I might still use Power Apps to read the data from SQL, but will continue to use Automate to push the data from Excel. Worked better than a copy and paste.