Load select query into excel file using power automate

Question

I have created a power automate flow to execute a sql query and create a sharepoint file in excel format everyday and load the retrieved select query to the excel file. Can anyone suggest what to provide in file content in the below screenshot and also how to load data into excel file in sharepoint?

I wondered if you’d run into this issue hence why on the answer I gave to your other question, I used a text file. I have a solution for you but can’t answer right now, will do so as soon as I can though. — Skin
– Skin, Commented Apr 24, 2022 at 23:34
These are the steps to be followed: create sharepoint file(excel), create excel table, inside apply each(add row into table). In this method, I get issue while creating excel table saying badgateway with 8 retires. — John11
– John11, Commented Apr 25, 2022 at 1:27
Oh ok, the workbook is able to be created and updated with a table but that’s it? So you can confirm the table exists in the workbook by opening it and checking? — Skin
– Skin, Commented Apr 25, 2022 at 3:37
I am able to create excel file but getting error while creating excel table in the excel — John11
– John11, Commented Apr 25, 2022 at 3:39
You can't just create an Excel file by using the create file action, it's just a blank nothing file, it has no XML structure to it which indicates that it's an Excel spreadsheet. I have a solution for you though. — Skin
– Skin, Commented Apr 25, 2022 at 6:37

Skin · Accepted Answer · 2022-04-25 07:10:47Z

0

First of all, create a template Excel file in the SharePoint folder your flow will have access to (called Template.xlsx below) ...

Now in your flow, copy that workbook to another workbook in another folder, I've done that to the Completed folder. Technically, you could put it anywhere though ...

... now in the next step, rename your file by calling one of the API's on the SharePoint site in question ...

You need to make sure you enter the following headers and then body (be sure to parameterise your filename though) ...

Uri: _api/web/lists/getbytitle('Documents')/items(@{outputs('Copy_file')?['body/ItemId']})

Key	Value
X-HTTP-Method	MERGE
If-Match	*
Content-Type	application/json;odata=verbose

Body

{
  "__metadata": {
    "type": "SP.Data.Shared_x0020_DocumentsItem"
  },
  "FileLeafRef": "New File Name.xlsx"
}

Now you have a true Excel workbook to work with.

edited Apr 25, 2022 at 7:10

answered Apr 25, 2022 at 6:54

Skin

11.7k3 gold badges16 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

John11 Over a year ago

Great. This helps. In my flow, the steps are Execute the query (SQL), Create sharepoint excel file with todays date as filename. Is there a way to load the sql result into excel file using http request? And also in this send HTTP request where are we inserting the query results into excel? This is in sequence with this question: stackoverflow.com/questions/71962419/…

Skin Over a year ago

Yeah, but it’s kinda secondary to this question so technically, you should flag this question as answered and then open another question. Having said that, you should have a crack with the Create table action that you were previously using. I think you were on the right track, you just needed to overcome this question.

Collectives™ on Stack Overflow

Load select query into excel file using power automate

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related