You’re offline. This is a read only version of the page.
Hi Guys,
I have a requirement to retrieve rows from an excel file and manipulate them. For this I am using Excel Online (Business) connector
The connector itself has actions to do this but it is expecting Table as a mandatory field. The excel file I need to get data from does not has any tables in it and this is the requirement.
Is there any way to retrieve rows from an excel file which does not has any tables ?
That was my fear as well, thanks for the detailed explanation 😀! I'm glad I wasn't missing anything, here's to hoping this improves in the future, but will build a similar workaround for now.
Sadly this is the fatal, and infuriating flaw of Power Query, it requires opening the file to refresh (the connections do have a 'refresh every x minutes' option but it doesn't refresh unless the file is open.
The way I've gone round this is to use Windows Task Scheduler to run a VB script which opens the files, refreshes all data connections, waits 10 seconds (enough for my cases, might need more for bigger files), then closes the file. This is what the vb script looks like:
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Open("C:\Users\etc.xlsx")
objWorkbook.RefreshAll
WScript.Sleep 10000
objWorkbook.Close True
--
Feels like a hack but works like a treat, I run half a dozen of these around 5.30am every day so it doesn't bother me.
This is a great solve @leemager ! I really like this approach but how do you "force" a refresh of the Power Query connection every time you replace the weekly report file? Do you need to manually open up that file and click on refresh? I'm looking for an automated way to ingest many files on a daily basis without a table defined in them.
@hasnainhaider68 - that's a great tip but mainly for ad hoc one-off cases, and if you're going to manually amend an Excel file so Power Automate can use it as a one-off, it's just as easy to convert to a table (Ctrl-T) than to use a named range, and Tables bring so much more functionality. Named ranges are awesome, but I think the predominant use case people struggle with in Power Automate is when they get regular reports from someone outside their dept / org, which isn't in Table format. Any method which avoids you having to change it manually every time is ideal, so far the best approach I've got is a one-time Power Query, but what I'd really love is a Power Automate action like 'Create a table in Excel', using something like CurrentRegion in VBA (the full rectangle of data adjacent to any given cell)), then we'd be flying! 😃
Dear,
You should try this. you can retrieve rows from an Excel file that does not have any tables using Power Automate and the Excel Online (Business) connector. While the connector might expect a table name for certain actions, you can still achieve your goal by working with the data range directly. Here's how you can do it:
Use Named Ranges: Named ranges in Excel are named references to specific cell ranges. You can create named ranges in your Excel file to represent the data you want to manipulate. This allows you to reference the data range by name instead of using a table. Here's how:
a. In your Excel file, select the range of cells that you want to retrieve and manipulate. b. Go to the "Formulas" tab, and in the "Defined Names" group, click on "Name Manager." c. Click "New" to create a new named range. d. Give your named range a meaningful name and specify the cell range it refers to. e. Save the named range.
Retrieve Rows using Named Range: Now that you have a named range, you can use it in Power Automate to retrieve and manipulate data:
a. In your Power Automate flow, add an "Excel Online (Business)" connector action. b. Choose the appropriate action based on what you want to do with the data (e.g., "Get rows"). c. When the action asks for the table name, provide the name of the named range you created in Excel.
Process the Retrieved Data: After retrieving the rows using the named range, you'll have the data available for manipulation in Power Automate. You can add further actions to perform your desired manipulations.
After you start using Power Query I promise you'll wonder how you ever lived without it, it's a godsend even beyond this 'table conversion hack' for Power Automate purposes.
Thanks a lot for help! Will work on it today:)
@Marina51533 I have half a dozen of these 'when an email arrives > save attachment to SharePoint (with the same filename so it overwrites) > refresh the power query Excel file' setups because of having to deal with non-table Excel files sent by email from another Dept, it only takes a few mins to set up the first time and then it works forever (just have to refresh the power query data). The flow is really straightforward and the Power Query is even straightforwarder (assuming no transformations required, but even if you do, you only need to do that once and the transformations will be applied every time you refresh) 😊:
- When an email arrives as trigger: select the email those regular attachment emails come from, include a keyword that's guaranteed to be in the subject line and set only with attachments to yes.
- Get attachment (using the message id from the trigger)
- Create file in SharePoint - use a standard hard coded filename so that the file is always up to date (you can save the original filename version in an archive folder if you want as well, but I'm thinking about having the reliably refreshed data connected via Power Query), use attachments attachment id for the id and attachments content for the content.
The first time you do that, create a new Excel file, then go to Data > Get Data > from Excel Workbook, then select the worksheet and if you need to clean anything up, you can do it by clicking Transform and then do whatever (Power Query is AWESOME and worth learning, lots of YT vids on it) , but to just get the data in a table format, you can just click Load, and that's it! You'll see the original data in a nice table format, and from now on any time you refresh, it'll refresh based on the source data file and Power Automate can just use this table-format version.
I have very similar situation: i receive emails with Excel attachments, that dont have Table format. Im my case, i have to parse every Excel to json and work with API after that🙈
And never used Power Query before((
If i understand right, i have to send every attachment to SharePoint => Create a table from it - using Power Query? =>( parse to json)
Im new in PA, need some help...
#1
WarrenBelz
146,660
Most Valuable Professional
#2
RandyHayes
76,287
Super User 2024 Season 1
#3
Pstork1
66,004
Most Valuable Professional
Share
Report
All responses (
Answers (
