Skip to main content
Nintex Community Menu Bar
Solved

Writing to the same Excel file from multiple machines - read only issue

  • May 16, 2024
  • 4 replies
  • 101 views
  • Translate
K
Forum|alt.badge.img+3

I am trying to find a way for several bots to work from the same shared Excel file.  Assume there is a large list of data (Account Numbers) in one column and another column beside it called “RPA Status”.  When one bot starts to work an account, it looks at the Excel file to find the first Account Number with an empty RPA Status field.  It then copies this Account Number and writes “In Process” to the RPA Status field so that another bot will not pick up this Account Number.  It writes back “Complete” once it has completed whatever process it is doing.  Ideally, I’d like to have multiple machines pulling in and writing back to this Excel file.

 

The issue is that if I try running another bot pulling from the same file, the Excel is in “Read Only” status until the other workflow is completely finished.  Is there a way to write to an Excel file and immediately close out the connection so that another bot can write to it?  I know SQL is an option, but that raises several other issues.

Best answer by Danny Toh

@kwstroud 2 ways you can achieve the results you want.

  1. Use Excel Online - Put your Excel in a SharePoint Online site and have the bots interact with this file. Excel files on SharePoint Online are configured to allow multiple access at the same time. 
  2. Use a loop condition to check until the file is closed before continuing - In this scenario, you will need to perform a small exception handling at the point where you are writing to the file. When the file is in use, the Paste Excel command will return an error “FILE_READ_ONLY”. All you need is to catch the error and loop it until the error is gone to proceed. Do however take note that this may also lead to an infinite loop condition so you may want to add additional controls to prevent that from happening.

     

View original
    Did this topic help you find an answer to your question?

    4 replies

    D
    Nintex Employee
    Forum|alt.badge.img+3
    • Danny TohNintex Employee
    • Nintex Employee
    • 5 replies
    • Answer
    • May 17, 2024

    @kwstroud 2 ways you can achieve the results you want.

    1. Use Excel Online - Put your Excel in a SharePoint Online site and have the bots interact with this file. Excel files on SharePoint Online are configured to allow multiple access at the same time. 
    2. Use a loop condition to check until the file is closed before continuing - In this scenario, you will need to perform a small exception handling at the point where you are writing to the file. When the file is in use, the Paste Excel command will return an error “FILE_READ_ONLY”. All you need is to catch the error and loop it until the error is gone to proceed. Do however take note that this may also lead to an infinite loop condition so you may want to add additional controls to prevent that from happening.

       

    Translate
    K
    MillaZ
    2 people like this
    • K
      kwstroud
    • MillaZ
      MillaZ
    K
    Forum|alt.badge.img+3
    • kwstroud
    • Author
    • Rookie
    • 7 replies
    • May 17, 2024

    Thanks @Danny Toh - I’m creating new files with the workflow each day (based on the date) - Example:  FileName05172024.xlsx.  Can you directly create a new Excel file in SharePoint Online with Nintex RPA?  

     

    The reason the 2nd option won’t work is that each bot will be continuously pulling from the Excel file - It seems to be locked until the flow completely ends.  It seems like this option would have the first bot that accesses the file complete all of the work and end before the Read Only error goes away, and by then it’s too late.

     

    Thanks so much for the reply!!

    Translate
    D
    Nintex Employee
    Forum|alt.badge.img+3
    • Danny TohNintex Employee
    • Nintex Employee
    • 5 replies
    • May 20, 2024

     @kwstroud you can’t create an Excel directly on SharePoint Online. You can refer to the list of commands that interact with SharePoint Online here.

    Translate
    D
    Forum|alt.badge.img

    Hi, not sure if this issue is still relevant, but I solved a similar issue by doing the write to excel in Python using openpyxl Library. This allowed me to get more precise control on when the workbook is supposed to be closed.

    Another option I’ve used is using a CSV (or txt file) as it doesn’t get locked when multiple robots are writing to it. Then when you need it in an excel format you can convert the latest version of the CSV.

    Translate

    Reply


    Sidebar Buttons
    Nintex Community Home Contact Nintex Support Email Nintex Community Check Nintex Status
    Terms & ConditionsCookie Settings

    Cookie policy

    We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

     
    Cookie Settings