extracting data from excel

[Shmuph]

I receive a weekly email with an excel (xlsx) file attachment that contains statistical data.  The attachment always has the same name.

I want to take the data from that file (every line apart from the headers) and then update a sharepoint list which contains the cumulative data.  The sharepoint list can then be used in an excel power query to extract and present the data.

The data is not contained in a table, but I have seen the new Run Script function which I have been trying to explore, and I have a script created.

So far my thought process is:  On New Email->Create file (Attachment saved to sharepoint library)->Run Script (to data turn to table)->Get a Row ->Create Item (to add the copied row into a sharepoint list)->Delete Item (to remove the attachment)

I get stuck on the Run Script which tells me:  status: 400,  message: The parameter 'file' has an invalid value
If i take the Run Script function to run on it's own I get a different error that says 'Body is required'

Any help would be appreciated, or alternative ideas as to how to complete the challenge!  Thanks in advance

[v-siky-msft]

Hi @Shmuph ,

Run script File can only be selected from File browse, it's not supported to reference the excel file by any dynamics content(Verified on my side too).Hence, I'm afraid 'Run Script' action can't be effective to format a excel that just created as a table in this scenario. See: Run script (Preview) 

Actually, your needs can be implemented by UI flow with Win Automation. In this blog, @yashag2255 showed us in detail how to format Excel to a table through UI flow and Win Automation, and how to retrieve the Excel table data for further operation. See: Format excel data as a table in Power Automate using Win Automation 

Hope this helps.
Sik

[Shmuph]

thanks for your quick response @v-siky-msft, I was hoping that since the excel file always had the same name I could utilise the run script function.  If that isn't the case I'll try the solution you've suggested, thanks again.

[v-siky-msft]

Hi @Shmuph ,

I also tried that and faced an error that excel sheet can't be opened any more, so hope yashag's solution works for you.

Sik