How to read rows from an excel file which are not contained in a table?

[Anonymous]

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 ? 

[jbishop]

If you have Premium Connectors, I recommend the tutorial from Sharepains which uses HTTP Requests and Microsoft Graph: https://sharepains.com/2018/10/17/microsoft-flow-read-large-excel-files-within-seconds-without-creat... 

If you do not have Premium Connectors, but do have 365, I highly recommend DamoBird's tutorial using Office Scripts (underrated channel, great stuff on there):

https://www.youtube.com/watch?v=UspiIlj6hSQ 

If you have neither, then I'm not exactly sure of how to achieve that since a lot of the connectors capable of that kind of operation are either Premium or require 365 Accounts.

Hope this helps!

Cheers,

jbishop

[trice602]

HI @Anonymous ,

Yes, you can also do this with Power Automate Desktop and even run it through a cloud flow.  In this example, you would Run a Desktop Flow Action and call your RPA that can work with rows, columns and ranges of data with ease - even when you don't have actual table data.  Please mark as a solution if this helps!  Always glad to help! Tom 👍🏽

[leemager · Accepted solution]

@SriHarishARJ This comes up a lot in my workplace and by far the easiest workaround I've found (at least for reading from Excel - for writing to an Excel sheet without it being in Table format, Office Scripts is the way forward - the Power Automate desktop suggestion ITT requires a premium license to call it from a cloud flow) is just to use Power Query to pull in data form the source file and have the flow use that version instead. Takes a couple of minutes max to Get data > from workbook and you have a nice neat Table Format just as Power Automate likes it 😊

A common related scenario is we get the same non-table spreadsheet coming in by email regularly with updated data, so we have one flow to auto-save the attachment as it arrives in to the same SharePoint location, and then an Excel file which uses Power Query to pull in the data from the attachment save location. Each new flow with the email trigger overwrites the previous file, so our Power Query version which automatically creates it in Table Format updates upon each refresh.

[Marina51533]

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

[leemager]

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

[Marina51533]

Thanks a lot for help! Will work on it today:)

[leemager]

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. 

[hasnainhaider68]

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:

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

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

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

[leemager]

@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! 😃

[Ray_T]

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.

[leemager]

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.

[Ray_T]

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.