Power Automate Exchange

Has anyone come up with a more efficient means of creating excel files? I have a flow that takes data from SQL and writes it to an excel table, via the add rows, then copies the file contents and resaves as a new file. It then loops through to the next item in the list, erases the table rows it previously wrote and starts adding new rows again.

We've had issues lately with communication between sharepoint online (where the files live) and flow not being very responsive or extremely slow. I've tried saving off the file contents of my "template" file to a flow variable, and using that to reset the file each time, but i keep getting blocked by erroneous file lock errors and can't  update the template file in that method, so I have to look up all rows and delete one by one.

Trying to find a solution to squeeze every once of efficiency out of this as possible.



Thanks,

------------------------------
Sam Duval
Data Quality Analyst
Indianapolis
------------------------------

Hello Sam,

What I should do is open the template, save it immediatly as a copy with the new name. Put your data is, save it and then start over again.

Ron

------------------------------
Ron Kats

d[ o_0 ]b
------------------------------

Original Message:
Sent: Mar 06, 2020 02:49 PM
From: Sam Duval
Subject: Adding Data To Excel Files

Has anyone come up with a more efficient means of creating excel files? I have a flow that takes data from SQL and writes it to an excel table, via the add rows, then copies the file contents and resaves as a new file. It then loops through to the next item in the list, erases the table rows it previously wrote and starts adding new rows again.

We've had issues lately with communication between sharepoint online (where the files live) and flow not being very responsive or extremely slow. I've tried saving off the file contents of my "template" file to a flow variable, and using that to reset the file each time, but i keep getting blocked by erroneous file lock errors and can't  update the template file in that method, so I have to look up all rows and delete one by one.

Trying to find a solution to squeeze every once of efficiency out of this as possible.



Thanks,

------------------------------
Sam Duval
Data Quality Analyst
Indianapolis
------------------------------

Thanks for the response @Ron Kats I tried that approach before, and flow can't use dynamic content for table names in the ads row to table function 😟 Microsoft is really struggling with Excel abilities in flow

------------------------------
Sam Duval
Data Quality Analyst
Indianapolis
------------------------------

Original Message:
Sent: Mar 07, 2020 09:50 PM
From: Ron Kats
Subject: Adding Data To Excel Files

Hello Sam,

What I should do is open the template, save it immediatly as a copy with the new name. Put your data is, save it and then start over again.

Ron

------------------------------
Ron Kats

d[ o_0 ]b

Original Message:
Sent: Mar 06, 2020 02:49 PM
From: Sam Duval
Subject: Adding Data To Excel Files

Has anyone come up with a more efficient means of creating excel files? I have a flow that takes data from SQL and writes it to an excel table, via the add rows, then copies the file contents and resaves as a new file. It then loops through to the next item in the list, erases the table rows it previously wrote and starts adding new rows again.

We've had issues lately with communication between sharepoint online (where the files live) and flow not being very responsive or extremely slow. I've tried saving off the file contents of my "template" file to a flow variable, and using that to reset the file each time, but i keep getting blocked by erroneous file lock errors and can't  update the template file in that method, so I have to look up all rows and delete one by one.

Trying to find a solution to squeeze every once of efficiency out of this as possible.



Thanks,

------------------------------
Sam Duval
Data Quality Analyst
Indianapolis
------------------------------

Not sure if Microsoft updated this functionality. It may now be possible to use dynamic table values in some actions.

But if they didn't, there is still this work-around to make them dynamic: http://johnliu.net/blog/2019/5/workarounds-needed-to-use-the-excel-connector-in-microsoft-flow

And if that doesn't work for you, then there are ways to batch create, update, & delete Excel rows much faster...

Upsert

https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Excel-Batch-Create-Update-and-Upsert/td-p/1624706

Delete
https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Excel-Batch-Delete/m-p/1634375#M735

------------------------------
Tyler Kolota
Power Platform Developer
------------------------------

Original Message:
Sent: Mar 06, 2020 02:49 PM
From: Sam Duval
Subject: Adding Data To Excel Files

Has anyone come up with a more efficient means of creating excel files? I have a flow that takes data from SQL and writes it to an excel table, via the add rows, then copies the file contents and resaves as a new file. It then loops through to the next item in the list, erases the table rows it previously wrote and starts adding new rows again.

We've had issues lately with communication between sharepoint online (where the files live) and flow not being very responsive or extremely slow. I've tried saving off the file contents of my "template" file to a flow variable, and using that to reset the file each time, but i keep getting blocked by erroneous file lock errors and can't  update the template file in that method, so I have to look up all rows and delete one by one.

Trying to find a solution to squeeze every once of efficiency out of this as possible.



Thanks,

------------------------------
Sam Duval
Data Quality Analyst
Indianapolis
------------------------------

That's a pretty slick feature (the upsert), yes dynamic tables work now which is great to see. I haven't played with it much as we've since added paginated reports to our repertoire and I mostly use that now for what I used to have to use flow to generate excel files for.

------------------------------
Sam Duval
Data Quality Analyst
Element Financial
Indianapolis IN
------------------------------

Original Message:
Sent: Oct 24, 2022 04:29 PM
From: Tyler Kolota
Subject: Adding Data To Excel Files

Not sure if Microsoft updated this functionality. It may now be possible to use dynamic table values in some actions.

But if they didn't, there is still this work-around to make them dynamic: http://johnliu.net/blog/2019/5/workarounds-needed-to-use-the-excel-connector-in-microsoft-flow

And if that doesn't work for you, then there are ways to batch create, update, & delete Excel rows much faster...

Upsert

https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Excel-Batch-Create-Update-and-Upsert/td-p/1624706

Delete
https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Excel-Batch-Delete/m-p/1634375#M735

------------------------------
Tyler Kolota
Power Platform Developer

Original Message:
Sent: Mar 06, 2020 02:49 PM
From: Sam Duval
Subject: Adding Data To Excel Files

Has anyone come up with a more efficient means of creating excel files? I have a flow that takes data from SQL and writes it to an excel table, via the add rows, then copies the file contents and resaves as a new file. It then loops through to the next item in the list, erases the table rows it previously wrote and starts adding new rows again.

We've had issues lately with communication between sharepoint online (where the files live) and flow not being very responsive or extremely slow. I've tried saving off the file contents of my "template" file to a flow variable, and using that to reset the file each time, but i keep getting blocked by erroneous file lock errors and can't  update the template file in that method, so I have to look up all rows and delete one by one.

Trying to find a solution to squeeze every once of efficiency out of this as possible.



Thanks,

------------------------------
Sam Duval
Data Quality Analyst
Indianapolis
------------------------------

Use Power Automate to pass the data to Excel and run a script in Excel to add the data to the table.  It runs about 100 times faster than adding the data to the table using PowerAutomate

HAve a look at Convert CSV Files to Excel (xslx format) in Power Automate

YouTube		remove preview
Convert CSV Files to Excel (xslx format) in Power Automate Learn how to leverage Power Automate's out of the box actions & Office Scripts to convert CSV documents into Excel (xlsx). Check out this video (https://www.youtube.com/watch?v=A-ttKpNH5Vw) if your source file is in XLSX format instead of CSV. View this on YouTube >

------------------------------
Guy Boswell
Care for the Family
Newport
02920810800
------------------------------

Original Message:
Sent: Mar 06, 2020 02:49 PM
From: Sam Duval
Subject: Adding Data To Excel Files

Has anyone come up with a more efficient means of creating excel files? I have a flow that takes data from SQL and writes it to an excel table, via the add rows, then copies the file contents and resaves as a new file. It then loops through to the next item in the list, erases the table rows it previously wrote and starts adding new rows again.

We've had issues lately with communication between sharepoint online (where the files live) and flow not being very responsive or extremely slow. I've tried saving off the file contents of my "template" file to a flow variable, and using that to reset the file each time, but i keep getting blocked by erroneous file lock errors and can't  update the template file in that method, so I have to look up all rows and delete one by one.

Trying to find a solution to squeeze every once of efficiency out of this as possible.



Thanks,

------------------------------
Sam Duval
Data Quality Analyst
Indianapolis
------------------------------