Blue Prism Product

 View Only
last person joined: 22 hours ago 

This community covers the core Blue Prism product.

Back to discussions
Expand all | Collapse all

Find and Replace in Excel

  • 1.  Find and Replace in Excel

    Posted 5 days ago
    Hi,
    I want to replace some special characters like *,"" with Null in excel before doing further validations. Can you pl suggest me if there is any action available to do in Blueprism?

    ------------------------------
    Salman Shaik
    ------------------------------


  • 2.  RE: Find and Replace in Excel

    Posted 5 days ago
    Hi @Salman Shaik

    I think, the best way for this is to use Data - OLEDB ​and replace your special characters by using UPDATE SQL statement.

    Something similar to this:

    UPDATE [Sheet1$]
    SET valueField1 = REPLACE(valueField1,'*','')


    This is faster than opening the full Excel and read it.


    Hope this helps you! And if this solves the problem, remember mark as the best answer!

    See you in the Community, bye :)

    ------------------------------
    Pablo Sarabia
    Solution Manager & Architect
    Altamira Assets Management
    Madrid
    ------------------------------

    Original Message


  • 3.  RE: Find and Replace in Excel

    Posted 4 days ago
      |   view attached
    Hi @pablo sarabia

    by using the above query ​ i am getting the below error. Pl suggest how to handle

    ------------------------------
    Salman Shaik
    ------------------------------

    Original Message


  • 4.  RE: Find and Replace in Excel

    Posted 3 days ago
    Hi @Salman Shaik

    You need to put the SQL sentence as a text, with (")

    I give you a full example step by step to achive this:

    1.- You need to add this VBO Data - OLEDB

    2.-  To use this you need to follow the next structure:
    In the Set Connection action you need to put your connection string.

    Try to use something similar to this:

    "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="&[ExcelFile]&";Extended Properties='Excel 12.0 Xml;HDR=YES';"

    Where the data item ExcelFile is where I have my Excel file.

    3.- If the Open action gives you the error "'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine", no worries, just install this: https://www.microsoft.com/en-us/download/confirmation.aspx?id=13255.

    4.- In the "Execute" action you need to put the SQL Statement. For this example I have created
    And I will replace the * from the Field2 with a "YYYY". (When we define the connection string with HDR=YES, we indicate that the first row contains columnnames)

    "UPDATE [Hoja1$] SET Field2 = Replace(Field2, '*','YYYY')"

    After executing this step your excel will be updated.

    5.- And finally, Close the connection by using the Close Action.


    Hope this is clearer and it solves the problem. If this helps you, remember mark as the best answer!

    See you in the Community, bye :)

    ------------------------------
    Pablo Sarabia
    Solution Manager & Architect
    Altamira Assets Management
    Madrid
    ------------------------------

    Original Message


  • 5.  RE: Find and Replace in Excel

    Posted 3 days ago
    Hi @Pablo Sarabia,

    can you please share the single query to replace all the * in the particular sheet Hoja1​ instead of executing for each field seperately.

    so that it can be helpful…

    ------------------------------
    Salman Shaik
    ------------------------------

    Original Message


  • 6.  RE: Find and Replace in Excel

    Posted 3 days ago
    Hi @Salman Shaik

    Sure, but in SQL you need to specify each field separately :(

    But you can put any fields that you need in a single SQL sentence

    For example, this is for my three fields:

    "UPDATE [Hoja1$] SET
    Field1 = Replace(Field1, '*','')
    , Field2 = Replace(Field2, '*','')
    , Field3 = Replace(Field3, '*','')"

    Do you have a lot of fields in your sheet?


    See you in the Community, bye :)​

    ------------------------------
    Pablo Sarabia
    Solution Manager & Architect
    Altamira Assets Management
    Madrid
    ------------------------------

    Original Message


  • 7.  RE: Find and Replace in Excel

    Posted 3 days ago
    Hi @Pablo Sarabia,
    i have more than 70 fields in my excel.
    is there any wild card functionality in query?

    ------------------------------
    Salman Shaik
    ------------------------------

    Original Message


  • 8.  RE: Find and Replace in Excel

    Posted 3 days ago
    Hi @Salman Shaik

    Unfortunately, that cannot be done. SQL only supports field-by-field :(

    If your excel is not going to grow in fields, you can try building your SQL sentence with CONCAT in Excel. Easy and fast.

    But we can look for another way to do this. Your Excel only have one sheet? or want to do this in several sheets?


    Bye :)

    ------------------------------
    Pablo Sarabia
    Solution Manager & Architect
    Altamira Assets Management
    Madrid
    ------------------------------

    Original Message


  • 9.  RE: Find and Replace in Excel

    Posted 3 days ago
    Hi @Pablo Sarabia,
    My excel is having 4 sheets but i have to do replacements in only one sheet.
    1.can we use nested replace function to replace characters for all columns?
    2. Can we give the sheet name as dynamic in query instead of hardcoding it?

    ------------------------------
    Salman Shaik
    ------------------------------

    Original Message


  • 10.  RE: Find and Replace in Excel

    Posted 3 days ago
    Hi @Salman Shaik

    I think i found a good solution for this. And with this solution you don't need to take care with the column numbers, or if in the future you want to add more.

    Here is the schema of the solution.


    The changes are:

    1.- After opening the connection to your Excel, get as collection the first line in the sheet. Use "Get Collection" action with this SQL Statement

    "SELECT TOP 1 * FROM [Hoja1$]"
    2.- Then use the VBO "Utility - Collection Manipulation" and the action "Get Collection Fields" to get a collection with all the fields that you have in your sheet.

    3.- Create a loop through your Collection Fields. Inside the loop, put a Calculation Stage with this formula:
    [SQL Statement Fields] & ", " & [Collection Fields.Field Name] & " = REPLACE(" & [Collection Fields.Field Name] & ",'*','')"

    4.- Finally, use the "Execute" Action with this SQL formula inside:

    "UPDATE [Hoja1$] SET " & Replace(","&[SQL Statement Fields];",,";"")

    I think this is a very good approach for your problem, cause if you add one more field, this automatically replace the character


    Hope this helps you!

    See you in the Community, bye :)

    ------------------------------
    Pablo Sarabia
    Solution Manager & Architect
    Altamira Assets Management
    Madrid
    ------------------------------

    Original Message


  • 11.  RE: Find and Replace in Excel

    Posted 3 days ago
    I feel the Execute Query action should be inside the loop  to replace the characters field by field.

    ------------------------------
    Salman Shaik
    ------------------------------

    Original Message


  • 12.  RE: Find and Replace in Excel

    Posted 3 days ago
    Hi @Salman Shaik

    Yeah! It's another posibility for this.

    But I think that is slow to make +70 updates instance only 1. If is more clear for you, yes, you can put the Execute inside.

    Here is the statement

    ​"UPDATE [Sheet1$] SET " & [Collection Fields.Field Name] & " = REPLACE(" & [Collection Fields.Field Name] & ",'*','')"

    Does this solve your problem completely?


    See you in the Community, bye :)

    ------------------------------
    Pablo Sarabia
    Solution Manager & Architect
    Altamira Assets Management
    Madrid
    ------------------------------

    Original Message


  • 13.  RE: Find and Replace in Excel

    Posted 3 days ago
    Hi @Pablo Sarabia,
    Is your previous logic(Placing execute outside the loop) also does the same replacement * with null with much speed than the logic suggested by me?
    because i am not able to understand your precious logic thats why i am asking you several times.

    can you pl elaborate more on your previous logic in calculation stage, execute stage
    pl dont mind….

    ------------------------------
    Salman Shaik
    ------------------------------

    Original Message


  • 14.  RE: Find and Replace in Excel

    Posted 3 days ago
    Hi @Salman Shaik

    Both solutions make the same correctly.

    The only different ​between these two solutions is the time that its takes to complete.

    As I said in my previos post, is not the same to execute one update with all the fields than execute 70 updates with only one field. Of course, in both solutions you replace the *, but the second one takes several minutes to complete.

    To ilustrate this, I make bigger my example Excel with 80 fields and 70.000 lines.

    I duplicate my example to make as you suggest, the update inside the loop, and I take the time with two variables and the function Now()

    Running the first one, one update outside the loop its take 30 seconds to be completed:

    Then I run your suggestion, the update inside, and it takes me more than 10 minutes.


    Why is this happening? Because if you go field by field you waste a lot of time opening and closing the command execution.Of course, my solution is not perfect, if you have too many fields in an update you may get the opposite effect and it may take longer.

    Now is more clear?


    Bye :)

    ------------------------------
    Pablo Sarabia
    Solution Manager & Architect
    Altamira Assets Management
    Madrid
    ------------------------------

    Original Message


  • 15.  RE: Find and Replace in Excel