cancel
Showing results for 
Search instead for 
Did you mean: 
WarrenBelz

Pasting Excel data, displaying and patching to data source

Have you ever want to grab a block of Excel data, paste it and display in Power Apps, then patch it to your data source as new records? The process involves a bit of user discipline grabbing the correct columns, but it works.

The first bit requires two controls - a Text Input set to multi-line and expanded appropriately and a HTML Text box for displaying the result in a structured manner.

Below is an Excel sheet that has simply has the content copied and then pasted into the Text Input

HTMLPasteExcelData.jpg


The top section is the Text Input and  the bottom a HTML Text box with the HtmlText of

With(
   {
      wText: 
      Substitute(
         Substitute(
            txtXLPaste.Text,
            Char(10),
            "</td></tr><tr><td>"
         ),
         Char(9),
         "</td><td>"
      )
   },
   "<table><tr><td>" & wText & "</td></tr></table>"
)

 

PAHtml.jpg

This displays the data in a structured column manner for user review.

Now to Patch it into a matching list (as I mentioned, a bit of user discipline required here)

Clear(colXLCSV);
With(
   {
      wLines: 
      Split(
         txtXLPaste.Text,
         Char(10)
      )
   },
   ForAll(
      wLines,
      Collect(
         colXLCSV,
         {
            Value: 
            Split(
               Result,
               Char(9)
            )
         }
      )
   )
);
ForAll(
   colXLCSV As aPatch,
   Patch(
      DeviceTest,
      Defaults(DeviceTest),
      {
         ManufacturerName: First(aPatch.Value).Result,
         'Device Name': 
         Last(
            FirstN(
               aPatch.Value,
               2
            )
         ).Result,
         DeviceType: 
         Last(
            FirstN(
               aPatch.Value,
               3
            )
         ).Result,
         Price: 
         Value(
            Last(
               FirstN(
                  aPatch.Value,
                  4
               )
            ).Result
         ),
         Processor: 
         Last(
            FirstN(
               aPatch.Value,
               5
            )
         ).Result,
         Memory: 
         Last(
            FirstN(
               aPatch.Value,
               6
            )
         ).Result,
         Storage: 
         Last(
            FirstN(
               aPatch.Value,
               7
            )
         ).Result,
         ScreenSize: 
         Value(
            Last(aPatch.Value).Result
         )
      }
   )
)

I have included a couple of numeric values in the above as well.

As a brief explanation of what is happening here -
Firstly the collection at the top is split into two parts - the first using the With() statement turns the pasted text into a table with one field (named Value here) by Split() at Char(10) - a Line Break created by Excel when the rows are pasted.
These records are then collected, and the Value field is Split() into a table again at Char(9) - a Tab created by Excel denoting the field separation.

 

colXLCSVValue.jpg

As noted above, this table contains a field called Result (the output of the Split ) containing the row field values.

colXLCSVResult.jpg


Moving onto the Patch, it loops through the collection with ForAll() (I have also used an As statement for disambiguation, but this may not be necessary) and then "drills down" into the Result field inside the Value field table (the following two target fields as examples)

Storage: 
Last(
   FirstN(
      aPatch.Value,
      7
   )
).Result,
ScreenSize: 
Value(
   Last(aPatch.Value).Result
)

and here looks at the last item in the first 7 (essentially the 7th record) and the last record with the second example needing to be converted into a Value as the target field is Numeric.
Note this could be quite easily converted into an update record patch if the key field was contained in the Excel patch.