Get Excel Sheet Data

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. 

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 also note that JSON keys in objects can not repeat, so 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.

...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.