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
Level: Powered On

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

Level: Power Up

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.

Level: Powered On

@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

 

 

Level: Powered On

@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.

Level: Powered On

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!

Level: Powered On

@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.

Level: Powered On

@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