Answered

Import data using Patch or Collect delegation issues

(0) Share

Report

Posted on by Community Power Pla...

I have a problem:
"Automate/Flow" has not yet ben approved in our company.
So i'am having to figure out how to get data from one source to another. With No delegation issues.
Basically: Excel to SharePoint to Sql server.

Current state: Using sql server as my datasource. Everything is working fine.
Problem:
They want to import data that is currently stored in Excel (above 500 lines) to Sharepoint.
Then eventually move the data from Sharepoint to Sql server.

So my question runs: the Excel files(above 500 lines) Can Patch/Collect or some other function handle an Import to Sharepoint.?
Even if i have to some how jerryrig the function to take the data from Excel to Sharepoint in 500 chunks that would be ok ?

Thanks
David

Categories:

Building Power Apps

I have the same question (0)

All responses (8)

Answers (1)

GarethPrisk 2,828 on at

Like (0)

Report
You are correct, if you are using Collections that you will have incremental 'collects' based on the app setting. This is 500 by default, but can be incremented to 2000.
This can be accomplished (albeit jerry-rigged, but you indicated it's okay), by importing the Excel file into the PowerApp as static data. Simply determine a mechanism to 'page' the records in appropriate increments. Then you can create a Collection that indicates the groups, and do a ForAll function against that Collection. For example:
Collection indicates you have 4 'groups' of 500 records
Each collection record has the start and end row for the group
A ForAll function loops for the 4 collection records
It filters the dataset for the 'group'
It patches that dataset to SharePoint

How many rows in Excel total?
Are you able to add a column to help page/group the rows?

Also, if you import the Excel file. You may be able to simply ForAll against that table and Patch. It may not be subject to delegation limits, but I am not sure.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

@GarethPrisk
How many rows in Excel total?
4320 (SO FAR BUT CAN GROW)
Are you able to add a column to help page/group the rows?
the id column will be in the excel file.

So , as I suspected
Import the Excel file into a Collection then Either Patch/ForAll/Collect(??)
the data to SharePoint ?
If so: exanples of how to get data from excel to a Collection ?

Thanks
Dave

Was this reply helpful? Yes No
GarethPrisk 2,828 on at

Like (0)

Report
MSFT Docs on Importing Static Excel Data

Prior to import
Your data must in an Excel table
Make a note of what the table is named (in case there are multiple in the workbook)
During import
Select the table noted above, and import into app
NOTE: If you need to 'refresh' the static data, delete the Excel data source, and import again, otherwise it will increment the table name with a _1 suffix, and mess up references
After import
You can only read the static data
Either
Collect(colExcelData, {TableName})
ForAll({TableName}, Patch({SharePoint},Defaults({SharePoint}),{sharePointColumn1: excelColumn1, sharePointColumn2: excelColumn2}))

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

@GarethPrisk
You replied "During import"

That's the problem: I cant import via Flow or Import.
I will have to code it out..

We do not have Flow working.

So I think I will have to make a connection to Excel.
Create a Collection and place the data in the Collection from Excel.

I was just curious, on how t do that. I have yet to find any examples.

Dave

Was this reply helpful? Yes No
Verified answer

GarethPrisk 2,828 on at

Like (0)

Report
Here is how you add static data, from an Excel file, to a PowerApp.
Here is how you add Excel as a data source, to your app.
Here is how you create an app from an Excel source.

Depending on how frequently this data changes:
Rarely, or this is a one-time migration, go the static route
Frequently, go with the Data connection
Note: PowerApps will add an ID column to the table
Note: PowerApps may potentially lock the Excel file from editing

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

@GarethPrisk
"Here is how you add static data, from an Excel file, to a PowerApp."
This just gives an example on loading and creating a app.
no import coding.

The data that goes into the Collection will just be temporary.
and then moved into sql server or sharepoint.

Dave

Was this reply helpful? Yes No
GarethPrisk 2,828 on at

Like (1)

Report
Sorry, I think we may be having a misunderstanding.
You can add the data from your Excel file's tables, as static tables into the PowerApp. There is not special 'import' or 'transformation' or anything involved - it's simply copying those tables verbatim as static tables in the app.
Select the Data tab, search for Excel, and select Import from Excel
Select the Excel file
Click Open
Select the Tables you wish to import
Click Import
Interact with the static data directly, or move it into a collection if you need to manipulate the data

Then you can either try a ForAll function against the static data itself, or a collection based on it, to patch the rows into SharePoint.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

very good. for the additional explanation.
Now I will need to find examples on how to move data to a collection then to sharepoint or sql server
Thanks
Dave

Was this reply helpful? Yes No