cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Oskarkuus
Post Prodigy
Post Prodigy

Let user import large data amounts to a powerapp

I have an app i want to build.

I got everything working but one thing....

 

How to let user input data.

The app do calculations based on the data in the app.

I could let user manually input with a form, but since i want a user to put hundreds of rows that is not viable.

 

So i want a user to import it from excel or from some other source. My users have the data that should go into the app in excel already. But spread out in several files and they just need to put it into my app, do some work and than it is done.

 

But how can i import data either let a user upload a excelfile

Or just let them copy paste a column into the app.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @Oskarkuus ,

 

If you will have more columns to add into the collection, the whole solution would be:

1\ OnSelect of the Button associated with Text box1:

 

ClearCollect(coltemp1, RenameColumns(AddColumns(Split(TextInput1.Text,Char(10)),"Num1",0),"Result","Column1"));
ClearCollect(Col1,ForAll(Sequence(CountRows(coltemp1)) As A,Patch(coltemp1,Last( FirstN(coltemp1,A.Value)) ,{Num1:A.Value})))

 

 

2\ OnSelect of the Button associated with Text box2:

 

ClearCollect(coltemp2, AddColumns( Split(TextInput2.Text,Char(10)),"Num2",0));
ForAll(Sequence(CountRows(coltemp2)) As A,Patch(coltemp2,Last( FirstN(coltemp2,A.Value)) ,{Num2:A.Value}));
ClearCollect(Col2,AddColumns(Col1,"Column2",LookUp(coltemp2, Num1 = ThisRecord.Num2,Result)))

 

 

3\ OnSelect of the Button associated with Text box3:

 

ClearCollect(coltemp3, AddColumns( Split(TextInput3.Text,Char(10)),"Num3",0));
ForAll(Sequence(CountRows(coltemp3)) As A,Patch(coltemp3,Last( FirstN(coltemp3,A.Value)) ,{Num3:A.Value}));
ClearCollect(Col3,AddColumns(Col2,"Column3",LookUp(coltemp3, Num1 = ThisRecord.Num3,Result)))

 

 

And so on to add more column only need modify collection names.

 

Hope this helps.

 

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

7 REPLIES 7
v-jefferni
Community Support
Community Support

Hi @Oskarkuus ,

 

Since I have no idea of how would users input data in the app, assuming there are hundreds of TextInput boxes in the App, and the number of rows of data in the Excel table is always exactly the same as the number of TextInput boxes, you will be able to achieve the goal by below steps:

 

1\ Add a TextInput box, set the Mode to Multiline. Users could copy the column data from Excel table and paste in it.

 

2\ Add a button to create a collection, split the texts copy and paste from the Excel column.

ClearCollect(coltemp, AddColumns(Split(SourceData.Text,Char(10)),"Num",0));
ClearCollect(Col,ForAll(Sequence(CountRows(coltemp)) As A,Patch(coltemp,Last( FirstN(coltemp,A.Value)) ,{Num:A.Value})))

 

3\ Use LookUp function in Default of the hundreds of TextInput boxes to mapping to the specific row in the collection.

For example, the first TextInput which need to be populated of the data from first row:

LookUp(Col,Num=1).Result

 

I made a test for your reference:

5.png

6.png

 

Hope this helps.

 

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

Oskarkuus
Post Prodigy
Post Prodigy

Very nice. I got that to work.

 

But tbh i dont really get the code with the patch and clearcollect functions.

 

If i would want to ad another column to the same collection.

For example.

I paste 100 rows into the textpox. Press button and populate collection with those rows.

Now i want to add a second column and add another 100 rows of data into a column called PNR.

 

How do i do this?

Hi @Oskarkuus ,

 

I think you could add another TextInput for that column. OnSelect of another button, apply below formulas:

ClearCollect(coltemp1, AddColumns( Split(SourceData2.Text,Char(10)),"Num1",0));
ForAll(Sequence(CountRows(coltemp1)) As A,Patch(coltemp1,Last( FirstN(coltemp1,A.Value)) ,{Num1:A.Value}));
ClearCollect(coltemp2,AddColumns(coltemp,"PNR",LookUp(coltemp1, Num = ThisRecord.Num1,Result)))

8.png

With above formulas, a new collection will be created and then use the last formula to add the PNR column values based on the number columns.

 

I reviewed the code and have dumped the second ClearCollect function to make it clear. The Patch function is to number for the collection, the Num column would be like an ID column.

 

Hope this helps.

 

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

Your picture show exactly what i look for. but i cant copy your code.

ClearCollect(coltemp1; AddColumns( Split(TextInput1.Text;Char(10));"Num1";0));;

ForAll(Sequence(CountRows(coltemp1)) As A;Patch(coltemp1;Last( FirstN(coltemp1;A.Value)) ;{Num1:A.Value}));;

ClearCollect(coltemp2;AddColumns(coltemp1;"PNR";LookUp(coltemp1; Num1 = ThisRecord.Num1;Result)))

 

Oskarkuus_0-1619176230359.pngOskarkuus_1-1619176292721.png

 

Oskarkuus
Post Prodigy
Post Prodigy

I think i figured it out. Combined your posts instead of reading them as two solutions 🙂

//TextInput 1
ClearCollect(coltemp; AddColumns(Split(TextInput1.Text;Char(10));"Num";0));;
ClearCollect(Col;ForAll(Sequence(CountRows(coltemp)) As A;Patch(coltemp;Last( FirstN(coltemp;A.Value)) ;{Num:A.Value})));;

//TextInput 2
ClearCollect(coltemp1; AddColumns( Split(TextInput1_1.Text;Char(10));"Num1";0));;
ForAll(Sequence(CountRows(coltemp1)) As A;Patch(coltemp1;Last( FirstN(coltemp1;A.Value)) ;{Num1:A.Value}));;
ClearCollect(coltemp2;AddColumns(coltemp;"PNR";LookUp(coltemp1; Num = ThisRecord.Num1;Result)));;

Oskarkuus
Post Prodigy
Post Prodigy

Gaah, im sorry i just cant get it to work.

I thought i had it... but now i am stuck on adding a third textbox.

 

Hi @Oskarkuus ,

 

If you will have more columns to add into the collection, the whole solution would be:

1\ OnSelect of the Button associated with Text box1:

 

ClearCollect(coltemp1, RenameColumns(AddColumns(Split(TextInput1.Text,Char(10)),"Num1",0),"Result","Column1"));
ClearCollect(Col1,ForAll(Sequence(CountRows(coltemp1)) As A,Patch(coltemp1,Last( FirstN(coltemp1,A.Value)) ,{Num1:A.Value})))

 

 

2\ OnSelect of the Button associated with Text box2:

 

ClearCollect(coltemp2, AddColumns( Split(TextInput2.Text,Char(10)),"Num2",0));
ForAll(Sequence(CountRows(coltemp2)) As A,Patch(coltemp2,Last( FirstN(coltemp2,A.Value)) ,{Num2:A.Value}));
ClearCollect(Col2,AddColumns(Col1,"Column2",LookUp(coltemp2, Num1 = ThisRecord.Num2,Result)))

 

 

3\ OnSelect of the Button associated with Text box3:

 

ClearCollect(coltemp3, AddColumns( Split(TextInput3.Text,Char(10)),"Num3",0));
ForAll(Sequence(CountRows(coltemp3)) As A,Patch(coltemp3,Last( FirstN(coltemp3,A.Value)) ,{Num3:A.Value}));
ClearCollect(Col3,AddColumns(Col2,"Column3",LookUp(coltemp3, Num1 = ThisRecord.Num3,Result)))

 

 

And so on to add more column only need modify collection names.

 

Hope this helps.

 

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

Top Solution Authors
Top Kudoed Authors
Users online (51,676)