Excel Data – Paste and Patch

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

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>"
)

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(
               Value,
               Char(9)
            )
         }
      )
   )
);
ForAll(
   colXLCSV As aPatch,
   Patch(
      DeviceTest,
      Defaults(DeviceTest),
      {
         ManufacturerName: First(aPatch.Value).Result,
         'Device Name': 
         Last(
            FirstN(
               aPatch.Value,
               2
            )
         ).Value,
         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).Value
         )
      }
   )
)

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 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 in the paste from Excel denoting the field separation.

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

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
   )
).Value,
ScreenSize: 
Value(
   Last(aPatch.Value).Value
)

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.