Import data using Patch or Collect delegation issues

[DAVIDPOWELL]

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

[GarethPrisk]

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.

[DAVIDPOWELL]

@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

[GarethPrisk]

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

[DAVIDPOWELL]

@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

[GarethPrisk · Accepted solution]

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

[DAVIDPOWELL]

@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

[GarethPrisk]

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.

1. Select the Data tab, search for Excel, and select Import from Excel
   • 
2. Select the Excel file
3. Click Open
   • 
4. Select the Tables you wish to import
5. Click Import
   • 
6. 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.

[DAVIDPOWELL]

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