Want to get data from an Excel sheet that isn't formatted as a table?
Use this Azure Function to detect where the relevant data is & extract it to a JSON array using a specified top header row as field names / JSON keys. Also includes options to specify which columns / fields to return, ways to conditionally filter rows, and options to specify which records to return through top & skip record counts.
*Also supports data extraction from macro-enabled excel (.XLSM) files & from older 5.0/95 excels (.XLS) files.
Examples
We can start with a simple example where the range / desired table is the only thing on Sheet2 and is flush against the top & left side of the sheet.
For this we can get the file content for the Excel workbook, & insert the full file content in the "file" input (no need to use an expression to extract the base64, the function will do that). Then we can specify "Sheet2" in the "sheet_name" input. The rest of the inputs technically don't even need to be included in the body for this one, but I like to keep them there incase I want to use them later.
Finally there is a Select action, primarily to show how to convert the integers returned from Excel into date-time format. For dates, the function's Python library returns the number of milliseconds since 1970-01-01, so we have to convert the integers to seconds & then add them to that date to get our correct date-time entries.
if(equals(null, item()?['DateColumnName']), '', formatDateTime(addSeconds('1970-01-01T00:00:00Z', div(item()?['DateColumnName'], 1000)), 'u'))
Here we can see the function returned the desired table data in a JSON array & the Select action converted the integers to date-time format.
Next let's show a more complicated example with a range / desired table placed randomly on the sheet with some irrelevant data placed all around it.
For this one we need to understand that the function will detect the minimum row & minimum column with data in it & start the range from those two indexes. So in this case it will start the range from column B, row 3 and go down to column I, row 21. The function takes the 1st range row & uses it for the column headers / field names / JSON keys. So we want to specify the function should ignore the first 2 rows of data in the "skip_top_rows" parameter. That way it will pick up the 3rd row with the Blank, Blank, Column1, Column2, Column3, Column4, Blank, Blank as the header row. Then we note the Blank column headers over the columns with irrelevant data pose an issue. We can either go to the Excel sheet & give those columns headers in the headers row or we can specify for the function to skip those columns in the range with the "skip_left_columns" & "skip_right_columns" parameters. There are 2 columns on the left to skip & 2 columns on the right to skip so we can input 2 for both of those parameters. Then we also see there are some irrelevant rows at the bottom of the range, so we can use the "skip_bottom_rows" parameter to skip those 2.
After the inputs are set to get the right range, we can then add our column selections, row filters, skip records, & top records. For this example we want to exclude Column4 so we put Column4 in the "remove_columns" parameter. Alternatively we could have entered "Column1,Column2,Column3" in the "select_columns" parameter to get the same result. Then for the rows "filter_query" we are using Python Pandas queries (
https://datagy.io/pandas-query/) to specify we only want records where Column2 is equal to String1 and where Column1 is greater than 2. Note that the Python Pandas queries offer many more options than odata queries for the usual Excel List rows action, we can use multiple And statements, multiple Or statements, contains/substrings like "Column2.str.contains('String1')", in operations like "Column2 in ['String1', 'String2']", column comparisons like "Column1 > Column2" or "Column1 == Column2", references to a built-in index column like "index < 5", and references to columns with spaces "`Column 2` == 'String1'". Then in this example we also specify after the range is selected & after the filter query is applied we should skip the 1st 2 records returned ("skip_records_count" of 2) and we should take only the top 3 records after that skip ("top_records_count" of 3).
So all of that ultimately returns us these 3 records. The irrelevant data in the range around the table was excluded. Column4 was excluded. The Column1 > 2 means the 1st 2 records were excluded, Column2 == 'String1' means we only got the String1 / odd records starting at record 3, skip records of 2 means records 3 & 5 were skipped so we start at record 7, and top records of 3 mean we only got records 7,9, & 11, cutting off record 13.
Import & Set-Up
Find & download the Solution import package at the bottom of this main post. Go to the Power Apps home page (https://make.powerapps.com/). Select Solutions on the left-side menu, select Import solution, Browse your files & select the GetExcelSheetData_1_0_0_xx.zip file you just downloaded. Then select Next & follow the menu prompts to apply or create the required connections for the solution flows. And finish importing the solution.
Once the solution is done importing, select the solution name in the list at the center of the screen. Once inside the solution click on the 3 vertical dots next to the flow name & select edit.
Now that the flow is imported & open, we need to set up the Azure Function used to extract the Excel data.
If you have already worked with and deployed Azure Functions before, then you can skip the extra installations.
If you haven't deployed Azure Functions, you can go to the Microsoft Store & make sure you have VS Code & Python installed.
Once VS Code is installed, open it. Go to the 4 blocks on the left side menu to open the list of extensions. Search for Azure in the extensions & select to install Azure Functions. Azure Account & Azure Resources will automatically be installed too.
Once all the extensions are installed, go to the Azure A on the left side menu & select to sign in to Azure.
Next set up a project folder on your machine for Azure Functions & a sub-folder for this Get Excel Sheet Data project.
Back in VS Code select the button to create a new Azure Function. Follow the Function set-up instructions selecting the Get Excel Sheet Data project folder you just created, Python language Model V2, and where in VS Code to open the new Azure Function project.
Once all the project files are loaded in VS Code, select the function_app.py file. Remove all the code in the file. Go back to the tab with the flow, open the "Azure Function Python Script" action, copy its contents & paste them into the function_app.py file in VS Code. Cntrl+S / Save the file.
Next go to the requirements.txt file. Go to the flow to the "Azure Function Requirements.txt" action & copy its contents. Paste the contents into the requirements.txt file in VS Code. Cntrl+S / Save the file.
Go back to the Azure A on the left-side menu. Select the Deploy function button. Select Create New in the list of function. Follow the menus/prompts to create a new function. (If Create New doesn't appear, you may have to log in to Azure, navigate to Azure Functions & go through the process to create a new function so the new function will appear in the list of function options to deploy to.)
Go to Azure & login. Go to Function App. Find & select the newly deployed function. Select the 1st function under Name. Select Get function URL & in the pop-up menu & copy the Function key url.
Paste the function URL into the URI input of the HTTP action.
The flow is now set for you to select an Excel workbook in your OneDrive the Get file content action, specify a sheet in "sheet_name" & extract your Excel data. Remember to adjust the follow-on Select action if you need to select specific columns from your output & reformat date columns.
Note you can also switch out the OneDrive Get file content action with the SharePoint Get file content action or any other Power Automate action that returns file content in a...
{
"$content-type": "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
"$content": "UEsDBBQABgAIAAAAIQDIkA4rZQEAABgFAAATAAgCW0NvbnRlbnRfVHlwZXNdLnhtbCCiBAIooAACAAAAA..."
}
...format. You just need to replace the OneDrive dynamic content in the HTTP "file" input with the "File content" dynamic content from that different action.
10/10/2024 Update: Added a few lines to the function Python script to help handle duplicate column names. It now adds a count to any duplicate column names, like ColumnA, ColumnB, ColumnA will now become { "ColumnA":"", "ColumnB":"", "ColumnA 1":"" }
10/13/2024 Update: Added support for XLS files (Note XLS dates must be handled differently). Confirmed support for XLSM files.
