Power Automate: Read Excel (Dynamic Path)

 Hello Friends,

Welcome back with another post on Power Automate. In last post, we have discussed on how to upload files in SharePoint using PowerApps and Power Automate. Assume, that you have to upload Excel file using the same feature and then read that file and lastly insert all records of that excel in SharePoint. We have uploaded file successfully as we have seen in our last post.

1. PowerApps: Upload File To SharePoint
   

Now, we will use Power Automate to fetch this excel file from SharePoint and then read the file and lastly insert all records in SharePoint list. Before starting, please remember that as Power Automate reads data in the form of table from Excel file, therefore, there should be only one table in the Excel file you are going to upload. The reason, I am saying this is that we are making the Power Automate almost dynamic where only  library name will be static. The excel file name, table name will be picked dynamically. So, let's start-

1. Below is the sample file we will be using to upload. A similar kind of excel you may create where the first row will be headers and rest will be item rows. Then select all the rows including header and press "Ctrl + T". It will convert data range to table and assign it a unique table name.
2. 
3. We have created a list named "DataFromExcel" with columns as below-
4. 
5. Now we will create a Power Automate. Click on "App Launcher" (9 dots at top left corner) and select Power Automate from your SharePoint portal.
6. 
7. Create a Power Automate for "When a file is created in a folder (SharePoint)" and name it "ReadExcelAndInsertItemInSharePoint".
8. Select the Site Address and Folder Id (Folder name) where your excel will get uploaded.
9. 
   
   
10. Now we will try to fetch the tables present in this excel workbook. For this, add an action named "Get tables ( Excel Online Business )". It has three fields-
1. Location: the portal address
2. Document Library: the library in which file is uploaded
3. File: Id of file (use x-ms-file-id attribute of headers)
11. 
12. If you save the Power Automate and try to test it by uploading an excel file in document library, you will find the output of Get tables is a JSON having information of all the tables present in excel workbook.
13. Now we will fetch the rows of table. For this, use action "List rows present in a table ( Excel Online Business )".
1. Location: the portal address
2. Document library: the library in which file is uploaded
3. File: Id of file (use x-ms-file-id attribute of headers)
4. Table: Choose "Name" attribute from dynamic content window for Get tables.
14. 
15. The moment you select Name attribute, an "Apply to each" action will automatically get applied on this "List rows present in a table ( Excel Online Business )" action. This is the reason, I had mentioned earlier to have only one table in excel workbook.
16. 
17. Save the Power Automate and use the Test link provided at top right corner to test the same as well as the get the JSON of rows present in excel.
18. Once executed successfully, expand the Apply to each section then List rows present in a table and click on OUTPUTS >> Show raw outputs. It will show you JSON of the rows present in excel.
19. 
    
    
    
    
20. We need to parse the data in JSON, therefore, we will add action "Parse JSON".
21. 
    
    
22. For Schema, copy one block of row from the output of "List rows present in a table" and use "Generate from sample" to create schema.

23. {
        "type": "array",
        "items": {
            "type": "object",
            "properties": {
                "First Name": {
                    "type": "string"
                },
                "Last Name": {
                    "type": "string"
                },
                "Full Name": {
                    "type": "string"
                },
                "Employee Code": {
                    "type": "string"
                }
            }
        }
    }
    

24. The content has been parsed in JSON. Now, it's time to add each row in SharePoint list. for this, add another action named "Create item (SharePoint)".
25. Select the Site Address, List Name. Based on these values, the list columns will get populated. Bind each column with respective value from JSON.
26. The moment you link any of the field with JSON output, an "Apply to each" action/container will automatically get link with Create Item action. the reason is JSON may contain more than one record, therefore, each record has to be inserted in SharePoint list.
27. 
28. 
    
    
29. Complete "Apply to each" block.
30. 
    
    
31. It's all done. Now you may test it.
32. I just clicked on Test link and the flow executed successfully. The list used to store the items is now reflecting all these records which were present in Excel workbook.
33. 
34. This way, we can dynamically get the excel file name, read that file and save it's rows in SharePoint list.
35. Remember that the file template should remain same as the parsing of JSON and it's linking with SharePoint list is static. The dynamic part is to get the excel file path and read its content.
36. You can achieve the Upload File and Read Excel in Single Power Automate. Using my previous post (PowerApps: Upload File To SharePoint), when you are saving file to SharePoint, it will return you the ID of that file. Use that ID of file to get the file content and then implement this functionality.

With this, I am concluding this post.

Happy Coding !!!

Will see you again with some new topics.

Stay Safe !

Stay Healthy !