cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Meneghino
Level 10

How to copy-paste Excel data to table?

Hi all.  In a desktop application I want to be able to copy paste Excel data (screenshot below) into PowerApps.

 

It is easy to copy the Excel table data (Ctrl+C) and paste (Ctrl+V) into a text input (setting the text input Mode property to Multiline).

 

The problem is how to parse the tabs (ASCII 9) and returns (ASCII 10) to separate the data into table elements.

 

The only functions that help at the moment are Find and Char but I cannot see how to put them together to create a table, say using the Table function.

 

Can anyone see how?  Or do we need some more text functions to be added to PowerApps, like find the Nth occurrence of a character?

 

000.JPG

 

1 ACCEPTED SOLUTION

Accepted Solutions
Meneghino
Level 10

Re: How to copy-paste Excel data to table?

PS Here is the best solution I have found so far.  If anyone has a better idea please post here and I will change the chosen solution.

 

__.JPG

 

TextInput1.Mode = Multiline

ButtonClear.OnSelect = Clear(MyExcelTable)

ButtonLoadNextRow.OnSelect = Collect(MyExcelTable, {RowID: Last(MyExcelTable).RowID+1, StartOfRow: If(IsEmpty(MyExcelTable), 1, Find(Char(10), TextInput1.Text, Last(MyExcelTable).StartOfRow)+1) })

Gallery1.Items = MyExcelTable

Gallery1.TextBoxRowID.Text = ThisItem.RowID

Gallery1.TextBoxInstrumentName.Text = Mid(TextInput1.Text, ThisItem.StartOfRow, Find(Char(9), TextInput1.Text, ThisItem.StartOfRow)-ThisItem.StartOfRow)

Gallery1.TextBoxValue1.Text  = Mid(TextInput1.Text, ThisItem.StartOfRow+Len(TextBoxInstrumentName.Text)+1, Find(Char(9), TextInput1.Text, ThisItem.StartOfRow+Len(TextBoxInstrumentName.Text)+1)-ThisItem.StartOfRow-Len(TextBoxInstrumentName.Text)-1)

 

5 REPLIES 5
Meneghino
Level 10

Re: How to copy-paste Excel data to table?

PS Here is the best solution I have found so far.  If anyone has a better idea please post here and I will change the chosen solution.

 

__.JPG

 

TextInput1.Mode = Multiline

ButtonClear.OnSelect = Clear(MyExcelTable)

ButtonLoadNextRow.OnSelect = Collect(MyExcelTable, {RowID: Last(MyExcelTable).RowID+1, StartOfRow: If(IsEmpty(MyExcelTable), 1, Find(Char(10), TextInput1.Text, Last(MyExcelTable).StartOfRow)+1) })

Gallery1.Items = MyExcelTable

Gallery1.TextBoxRowID.Text = ThisItem.RowID

Gallery1.TextBoxInstrumentName.Text = Mid(TextInput1.Text, ThisItem.StartOfRow, Find(Char(9), TextInput1.Text, ThisItem.StartOfRow)-ThisItem.StartOfRow)

Gallery1.TextBoxValue1.Text  = Mid(TextInput1.Text, ThisItem.StartOfRow+Len(TextBoxInstrumentName.Text)+1, Find(Char(9), TextInput1.Text, ThisItem.StartOfRow+Len(TextBoxInstrumentName.Text)+1)-ThisItem.StartOfRow-Len(TextBoxInstrumentName.Text)-1)

 

taylor_lennar
Level: Power Up

Re: How to copy-paste Excel data to table?

Thanks for the post!  


I am having trouble replicating it, though.  I understand how to do the first four lines:

 

TextInput1.Mode = Multiline

ButtonClear.OnSelect = Clear(MyExcelTable)

ButtonLoadNextRow.OnSelect = Collect(MyExcelTable, {RowID: Last(MyExcelTable).RowID+1, StartOfRow: If(IsEmpty(MyExcelTable), 1, Find(Char(10), TextInput1.Text, Last(MyExcelTable).StartOfRow)+1) })

Gallery1.Items = MyExcelTable

It's the last four that I am struggling with.  Where would you define these characteristics?

 

Gallery1.TextBoxRowID.Text = ThisItem.RowID

Gallery1.TextBoxInstrumentName.Text = Mid(TextInput1.Text, ThisItem.StartOfRow, Find(Char(9), TextInput1.Text, ThisItem.StartOfRow)-ThisItem.StartOfRow)

Gallery1.TextBoxValue1.Text  = Mid(TextInput1.Text, ThisItem.StartOfRow+Len(TextBoxInstrumentName.Text)+1, Find(Char(9), TextInput1.Text, ThisItem.StartOfRow+Len(TextBoxInstrumentName.Text)+1)-ThisItem.StartOfRow-Len(TextBoxInstrumentName.Text)-1)

Thanks for any reply!!

Meneghino
Level 10

Re: How to copy-paste Excel data to table?

Hi @taylor_lennar

The last lines refer to text boxes (now called labels) created in the template of Gallery1

cklaaby
Level: Power Up

Re: How to copy-paste Excel data to table?

Hi Is it possible to load all lines at once?

JKat
Level: Powered On

Re: How to copy-paste Excel data to table?

Did you ever solve this? I'm looking for a solution for pasting cells from excel too.

 

There's new functionality that supports regex since the post - https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-ismatch