Power Automate Exchange

Please login or click SIGN UP FOR FREE to create your FlowUG account to join this user group.
 View Only
  • 1.  Adding Data To Excel Files

    Top Contributor
    Posted Mar 06, 2020 02:50 PM
    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
    ------------------------------


  • 2.  RE: Adding Data To Excel Files

    Bronze Contributor
    Posted Mar 07, 2020 09:50 PM
    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
    ------------------------------



  • 3.  RE: Adding Data To Excel Files

    Top Contributor
    Posted Mar 08, 2020 09:16 AM
    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
    ------------------------------



  • 4.  RE: Adding Data To Excel Files

    Posted Oct 24, 2022 04:29 PM

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



  • 5.  RE: Adding Data To Excel Files

    Top Contributor
    Posted 16 days ago
    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
    ------------------------------



  • 6.  RE: Adding Data To Excel Files

    Posted 17 days ago
    Edited by Guy Boswell 17 days ago
    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
    ------------------------------