Power Automate Exchange

Please login or click SIGN UP FOR FREE to create your FlowUG account to join this user group.
Expand all | Collapse all

Sequentially open all Word documents in library, then find and replace text from a list

  • 1.  Sequentially open all Word documents in library, then find and replace text from a list

    Posted 22 days ago

    Hello,

    First time poster, long time reader and student. 

    My company is going through a org structure review and all job titles will be changing. Our core business documents (in the thousands) all contain job titles that will need to change.

    I need to know if it's possible to use Automate to execute this work based on the following process:
    (1) Open document
    (2) Search document for <old job string 1>
    (3) Replace <old job string 1> with <new job string 1>
    (4) Repeat (2) (3) for strings as per <List>
    (5) Upon completion, close document
    (6) Proceed to next document

    Any advice how to do this without much manual input would be much appreciated. Thanks!



    ------------------------------
    Tyler Hall
    Integrated Management System Specialist
    ------------------------------
    Digital Acceleration Binge Day.  January 28th 2021 | 11:00 AM - 2:00 PM EST


  • 2.  RE: Sequentially open all Word documents in library, then find and replace text from a list

    Posted 21 days ago
    Hi Tyler,

    The idea you have is quite good to have something that automatically update your documents, however I think Power Automate won't be the right tool for your scenario.

    First things first, you have not mentioned anything about where the documents are located. Are they centrally placed, like in SharePoint? The solution where the documents are stored, is it on premise like a shared drive or you can query it over APIs. If you can query it over APIs maybe using the HTTP Request Action you can build queries to retrieve the files and place them in SharePoint or OneDrive for file processing. The later is required if you are working with Word or Excel files in Power Automate.

    You have also not mentioned what format the files are. For Word and Excel there are some actions where you can retrieve the file contents and work on it. Also both formats are ZIP files with XML files in them, so you can always convert them zip files, extract the contents in OneDrive and parse the XML file called, content.xml in the root folder. On the other hand if the files are PDF then you will need some online paid service to do the changes.

    An issue I see is the number of documents that you need to update. Power Automate performance decreases drastically when you have a lot of iterations. Furthermore, the For Each and Until loops are limited to 5000 loops maximum. You can overcome the issue by batching the documents or splitting the runs by location.

    ​Nonetheless, I think it is a good idea and hope the pointers on Word and Excel help you out figure out something.

    ------------------------------
    Karlston D'Emanuele
    ------------------------------

    Digital Acceleration Binge Day.  January 28th 2021 | 11:00 AM - 2:00 PM EST


  • 3.  RE: Sequentially open all Word documents in library, then find and replace text from a list

    Top Contributor
    Posted 20 days ago
    @Karlston D'Emanuele has nailed the situation pretty much spot on. Automate could do it, even if the files are on-prem, you'll just need a gateway. It will probably take forever and a day to complete if you have thousands of documents you'll have to do them in batches. You'll have to get file contents, save the contents to a string variable, find and replace on the variable, then either create the file with a new name/location or delete the original and create the file again

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

    Digital Acceleration Binge Day.  January 28th 2021 | 11:00 AM - 2:00 PM EST