Excel connector can be used from SharePoint, OneDrive. In our example we are going to provide an app for users to upload the files in SharePoint or users can directly upload files in SharePoint.<\/p>\n\n\n\n
![\"\"](\"https:\/\/i0.wp.com\/365stack.in\/wp-content\/uploads\/2021\/09\/Screenshot-45-1024x401.png?resize=640%2C251&ssl=1\")
Attachment control can be added from a SharePoint generated form, To pass the excel content to workflow it should be converted to binary format using the Image control.<\/p>\n\n\n\n
![\"\"](\"https:\/\/i0.wp.com\/365stack.in\/wp-content\/uploads\/2021\/09\/Screenshot-47-1024x373.png?resize=640%2C233&ssl=1\")
On the Button click:<\/p>\n\n\n\n
Set(ExcelFile,JSON(FileImageHide.Image,JSONFormat.IncludeBinaryData));\nSet(FileBase64,Mid(ExcelFile,Find(\",\", ExcelFile)+1,Len(ExcelFile)-Find(\",\",ExcelFile)-1));\nSet(VarFileLink,UploadFile.Run(FileBase64,Concatenate(prefix,Last(attUpload.Attachments).Name)).sharepointlink)\n<\/code><\/pre>\n\n\n\nFile Upload Workflow:<\/p>\n\n\n\n
![\"\"](\"https:\/\/i0.wp.com\/365stack.in\/wp-content\/uploads\/2021\/09\/Screenshot-49.png?resize=640%2C397&ssl=1\")
Upload Workflow<\/figcaption><\/figure><\/div>\n\n\n\nWhen a file is created in SharePoint document library, the main workflow which will read excel rows and pushes it to dataverse will run.<\/p>\n\n\n\n
![\"\"](\"https:\/\/i0.wp.com\/365stack.in\/wp-content\/uploads\/2021\/09\/Screenshot-50.png?resize=640%2C505&ssl=1\")
<\/figure><\/li>![\"\"](\"https:\/\/i0.wp.com\/365stack.in\/wp-content\/uploads\/2021\/09\/Screenshot-51.png?resize=640%2C402&ssl=1\")
<\/figure><\/li><\/ul><\/a><\/a><\/a><\/div><\/div><\/div>\n\n\n\nInitializing the required variables<\/p>\n\n\n\n
![\"Excel\"](\"https:\/\/i0.wp.com\/365stack.in\/wp-content\/uploads\/2021\/09\/Screenshot-52.png?resize=640%2C429&ssl=1\")
Create Table Action<\/figcaption><\/figure><\/div>\n\n\n\nRemember the limitation, Only the tables from excel can be retrieved. Either the user should send the file with data encapsulated in a table or In our case, it is a report the number of rows will vary each month so we are creating a table for the whole excel sheet.<\/strong> <\/p>\n\n\n\nWe are providing a Table range to create a table for the sheet and Column names to be read from excel.<\/p>\n\n\n\n![\"\"](\"https:\/\/i0.wp.com\/365stack.in\/wp-content\/uploads\/2021\/09\/Screenshot-53.png?resize=640%2C481&ssl=1\")
Do Until<\/figcaption><\/figure>\n\n\n\nOnce all the records are created, the “boolContinue” variable will be set to true to stop the Do Until loop.<\/p>\n\n\n\n
List rows by default will return only 256 rows, We need to enable the pagination and threshold limit to read more rows in each iteration. In our case, we are setting to 50000 because the report will return more than 100000 rows each month.<\/p>\n\n\n\n
![\"\"](\"https:\/\/i0.wp.com\/365stack.in\/wp-content\/uploads\/2021\/09\/Threshold.png?resize=640%2C454&ssl=1\")
<\/figure><\/div>\n\n\n\n![\"\"](\"https:\/\/i0.wp.com\/365stack.in\/wp-content\/uploads\/2021\/09\/Screenshot-54.png?resize=640%2C487&ssl=1\")
List Rows Action<\/figcaption><\/figure><\/div>\n\n\n\n“List Rows” action will support filtering the tables but it was not working consistently when we tried so we used the “Filter array” action to filter the rows returned.<\/p>\n\n\n\n
Skip Count is used to skip the rows, Initially, it will be 0 and on each iteration, it will be incremented by 50000 to eliminate the already created rows. <\/p>\n\n\n\n![\"\"](\"https:\/\/i0.wp.com\/365stack.in\/wp-content\/uploads\/2021\/09\/Screenshot-55.png?resize=640%2C468&ssl=1\")
<\/figure>\n\n\n\nSince we are creating a table for the whole sheet, we need to eliminate the blank rows in the table, So we are filtering the 1st Column which is the S.No column is Not Equal to Blank()<\/strong>.<\/p>\n\n\n\n![\"\"](\"https:\/\/i0.wp.com\/365stack.in\/wp-content\/uploads\/2021\/09\/Screenshot-56-1024x409.png?resize=640%2C256&ssl=1\")
<\/figure>\n\n\n\nIf the total number of the returned rows is less than 50000, that is the last iteration. Because we set the pagination to 50000 if the returned rows are less than 50000 then it is the last iteration and the “boolContinue” variable will be set to true. <\/p>\n\n\n\n
![\"\"](\"https:\/\/i0.wp.com\/365stack.in\/wp-content\/uploads\/2021\/09\/Screenshot-57-1.png?resize=640%2C451&ssl=1\")
Create rows in table<\/figcaption><\/figure><\/div>\n\n\n\nOnce the rows are returned from the filter action it should be used in “Apply to each” action. For each row, the “Add a new row” action will create a record in the table.<\/p>\n\n\n\n
![\"\"](\"https:\/\/i0.wp.com\/365stack.in\/wp-content\/uploads\/2021\/09\/Screenshot-59.png?resize=640%2C296&ssl=1\")
Increment Count<\/figcaption><\/figure><\/div>\n\n\n\n![\"\"](\"https:\/\/i0.wp.com\/365stack.in\/wp-content\/uploads\/2021\/09\/Concurrency.png?resize=640%2C326&ssl=1\")
Concurrency Control<\/figcaption><\/figure>\n\n\n\nOnce the “Apply to Each” action is completed, the intCount variable should be incremented by 50000. Since it deals with more data, Concurrency Control should be turned on for “Apply to Each” to maximum. Otherwise, the workflow will run for the whole day and sometimes it will get timed out for the large set of data.<\/p>\n\n\n\n
User should be notified once the process is completed.<\/p>\n\n\n\n
Post out your questions in the comment section. Happy Building \ud83d\ude42<\/p>\n","protected":false},"excerpt":{"rendered":"
One of the most common requirements in any business use case is to read the excel from an external system via Email or direct upload by a user. In Power Platform it can be implemented in multiple ways. Data flows Dataflows are a great way to transform data and import it to excel, we can configure an excel file to […]<\/p>\n","protected":false},"author":2,"featured_media":1436,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_mi_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","footnotes":"","jetpack_publicize_message":"","jetpack_is_tweetstorm":false,"jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","enabled":false}}},"categories":[20,8,32],"tags":[49,46,33,9,10,30],"class_list":["post-1435","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-dataverse","category-power-automate","category-power-platform","tag-excelconnector","tag-microsoft-365","tag-power-platform","tag-powerapps","tag-powerautomate","tag-sharepoint"],"jetpack_publicize_connections":[],"aioseo_notices":[],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/365stack.in\/wp-content\/uploads\/2021\/09\/Screenshot-45.png?fit=1253%2C491&ssl=1?v=1631363241","jetpack_sharing_enabled":true,"jetpack_likes_enabled":true,"jetpack-related-posts":[{"id":1636,"url":"https:\/\/365stack.in\/index.php\/2021\/11\/29\/how-to-add-rows-in-excel-online-using-power-automate\/","url_meta":{"origin":1435,"position":0},"title":"How to add rows in Excel online using Power Automate","author":"Hemanthkumar Rajan","date":"November 29, 2021","format":false,"excerpt":"Adding rows to an excel file or exporting the data in \".xlsx\" format, which can be sent to users or other applications as data feed can be a common scenario. In this example, we used the \"Excel online business\" connector to add rows to the excel file, Previously we already\u2026","rel":"","context":"In "Power Automate"","block_context":{"text":"Power Automate","link":"https:\/\/365stack.in\/index.php\/category\/power-platform\/power-automate\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/365stack.in\/wp-content\/uploads\/2021\/11\/Screenshot-214.png?fit=997%2C551&ssl=1%3Fv%3D1637930822&resize=350%2C200","width":350,"height":200},"classes":[]},{"id":1678,"url":"https:\/\/365stack.in\/index.php\/2021\/11\/27\/how-to-add-microsoft-forms-responses-to-database\/","url_meta":{"origin":1435,"position":1},"title":"How to add Microsoft Forms responses to database","author":"Premkumar Perumal","date":"November 27, 2021","format":false,"excerpt":"Add Microsoft Forms responses to databases using power automate will help any person\/organization to do advanced data analytics on the responses. We can add responses to list or table using Power Automate in two different ways and create your survey using Microsoft Form as a first step. Method 1: After\u2026","rel":"","context":"In "Power Automate"","block_context":{"text":"Power Automate","link":"https:\/\/365stack.in\/index.php\/category\/power-platform\/power-automate\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/365stack.in\/wp-content\/uploads\/2021\/11\/Screenshot-2021-11-26-214110.png?fit=682%2C325&ssl=1%3Fv%3D1637943130&resize=350%2C200","width":350,"height":200},"classes":[]},{"id":1432,"url":"https:\/\/365stack.in\/index.php\/2021\/09\/13\/a-complete-guide-to-power-platform-pricing\/","url_meta":{"origin":1435,"position":2},"title":"A Complete Guide to Power Platform Pricing","author":"Hemanthkumar Rajan","date":"September 13, 2021","format":false,"excerpt":"Power Platform is a great platform to automate a business process within the organization, it offers a plethora of features that can be leveraged to minimize the development cycle. Power Platform software is extended from Azure features and services. For Eg: Dataverse manages to store data with the help of\u2026","rel":"","context":"In "Power Platform"","block_context":{"text":"Power Platform","link":"https:\/\/365stack.in\/index.php\/category\/power-platform\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/365stack.in\/wp-content\/uploads\/2021\/09\/Power-Apps-Pricing-1024x505-1.jpg?fit=1024%2C505&ssl=1%3Fv%3D1631557375&resize=350%2C200","width":350,"height":200},"classes":[]},{"id":1989,"url":"https:\/\/365stack.in\/index.php\/2022\/01\/03\/how-to-get-started-with-power-platform-solutions-canvas-apps\/","url_meta":{"origin":1435,"position":3},"title":"How to get started with Power Platform Solutions: Canvas Apps","author":"Hemanthkumar Rajan","date":"January 3, 2022","format":false,"excerpt":"Deploying app from one environment to another environment is the most problematic area in SDLC. If development is not taken place in a well-organized setup then the deployment is not going to be that easy. Especially working in the SaaS, we need to follow the product standards to get the\u2026","rel":"","context":"In "Canvas Apps"","block_context":{"text":"Canvas Apps","link":"https:\/\/365stack.in\/index.php\/category\/power-platform\/powerapps\/canvas-apps\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/365stack.in\/wp-content\/uploads\/2021\/12\/Screenshot-402-1.png?fit=1200%2C490&ssl=1%3Fv%3D1639549594&resize=350%2C200","width":350,"height":200},"classes":[]},{"id":1456,"url":"https:\/\/365stack.in\/index.php\/2021\/09\/17\/powerapps-print-form-to-pdf\/","url_meta":{"origin":1435,"position":4},"title":"How to Print Scrollable Screen to PDF in PowerApps","author":"Hemanthkumar Rajan","date":"September 17, 2021","format":false,"excerpt":"One of the common requirements in any application is to print the data either to save the form as PDF or print the form. PowerApps provides Print() function to print the screen in a printable format, but the limitation is the screen height cannot be greater than 2164px. If a\u2026","rel":"","context":"In "Canvas Apps"","block_context":{"text":"Canvas Apps","link":"https:\/\/365stack.in\/index.php\/category\/power-platform\/powerapps\/canvas-apps\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/365stack.in\/wp-content\/uploads\/2020\/10\/background1.jpg?fit=1200%2C675&ssl=1%3Fv%3D1623349406&resize=350%2C200","width":350,"height":200},"classes":[]},{"id":1640,"url":"https:\/\/365stack.in\/index.php\/2021\/12\/03\/how-to-create-csv-file-in-power-automate\/","url_meta":{"origin":1435,"position":5},"title":"How to create CSV file in Power Automate","author":"Hemanthkumar Rajan","date":"December 3, 2021","format":false,"excerpt":"Export to CSV is one of the common requirements in any application. But in this example, we are not going to show the procedure from the app that will be shared in a separate post. Here we are getting the data from Power Automate itself to support a large set\u2026","rel":"","context":"In "Power Automate"","block_context":{"text":"Power Automate","link":"https:\/\/365stack.in\/index.php\/category\/power-platform\/power-automate\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/365stack.in\/wp-content\/uploads\/2021\/11\/Screenshot-229.png?fit=934%2C596&ssl=1%3Fv%3D1637940499&resize=350%2C200","width":350,"height":200},"classes":[]}],"_links":{"self":[{"href":"https:\/\/365stack.in\/index.php\/wp-json\/wp\/v2\/posts\/1435","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/365stack.in\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/365stack.in\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/365stack.in\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/365stack.in\/index.php\/wp-json\/wp\/v2\/comments?post=1435"}],"version-history":[{"count":5,"href":"https:\/\/365stack.in\/index.php\/wp-json\/wp\/v2\/posts\/1435\/revisions"}],"predecessor-version":[{"id":1840,"href":"https:\/\/365stack.in\/index.php\/wp-json\/wp\/v2\/posts\/1435\/revisions\/1840"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/365stack.in\/index.php\/wp-json\/wp\/v2\/media\/1436"}],"wp:attachment":[{"href":"https:\/\/365stack.in\/index.php\/wp-json\/wp\/v2\/media?parent=1435"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/365stack.in\/index.php\/wp-json\/wp\/v2\/categories?post=1435"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/365stack.in\/index.php\/wp-json\/wp\/v2\/tags?post=1435"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}