topic RE: Find and Replace in Excel in Product Forum

Find and Replace in Excel

salman_shaik — Fri, 05 Aug 2022 19:06:00 GMT

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

RE: Find and Replace in Excel

PabloSarabia — Fri, 05 Aug 2022 21:25:00 GMT

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

RE: Find and Replace in Excel

salman_shaik — Sat, 06 Aug 2022 06:23:00 GMT

Hi @pablo sarabia

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

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

RE: Find and Replace in Excel

PabloSarabia — Sun, 07 Aug 2022 07:53:00 GMT

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

RE: Find and Replace in Excel

salman_shaik — Sun, 07 Aug 2022 08:12:00 GMT

Hi @PabloSarabia,

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

RE: Find and Replace in Excel

PabloSarabia — Sun, 07 Aug 2022 08:16:00 GMT

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

RE: Find and Replace in Excel

salman_shaik — Sun, 07 Aug 2022 08:21:00 GMT

Hi @PabloSarabia,
i have more than 70 fields in my excel.
is there any wild card functionality in query?

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

RE: Find and Replace in Excel

PabloSarabia — Sun, 07 Aug 2022 08:36:00 GMT

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

RE: Find and Replace in Excel

salman_shaik — Sun, 07 Aug 2022 08:43:00 GMT

Hi @PabloSarabia,
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
------------------------------

RE: Find and Replace in Excel

PabloSarabia — Sun, 07 Aug 2022 08:57:00 GMT

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

RE: Find and Replace in Excel

salman_shaik — Sun, 07 Aug 2022 09:17:00 GMT

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

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

RE: Find and Replace in Excel

PabloSarabia — Sun, 07 Aug 2022 09:26:00 GMT

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

RE: Find and Replace in Excel

salman_shaik — Sun, 07 Aug 2022 09:37:00 GMT

Hi @PabloSarabia,
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
------------------------------

RE: Find and Replace in Excel

PabloSarabia — Sun, 07 Aug 2022 10:14:00 GMT

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

RE: Find and Replace in Excel

salman_shaik — Mon, 08 Aug 2022 05:56:00 GMT

Hi @PabloSarabia,

I have tried as per your suggestion but the validation is failed in execute query stage.
pl find the below screenshot for your reference

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

RE: Find and Replace in Excel

PabloSarabia — Mon, 08 Aug 2022 08:36:00 GMT

Hi @Salman Shaik

Humm... is strange...

In your column names do you have this character ( ' )?

If you can share in private the excel headers I will try to analyzed the problem directly

See you in the Community, bye 🙂

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

RE: Find and Replace in Excel

salman_shaik — Mon, 08 Aug 2022 09:23:00 GMT

Hi @PabloSarabia,
I don't have any character(') in my column name. I just copy pasted the logic which you mentioned, while doing the validation its showing Missing " error. I feel the syntax of the query is wrong.
can you pl validate the query which you have posted from your end?

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

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

RE: Find and Replace in Excel

PabloSarabia — Mon, 08 Aug 2022 19:14:00 GMT

Hi @Salman Shaik

I don't know if maybe the problem is if you have any space in the Column name, or any other special character.

I try to reproduce this in my local but I didn't get the same error.

Try replacing the calculation stage inside the loop for this:

[SQL Statement Fields] & ", [" & [Collection Fields.Field Name] & "] = REPLACE([" & [Collection Fields.Field Name] & "],'*','')"

The only different is that I encapsulate in square brackets the field name.

If the error persists, give in private the SQL that is executing or the Excel header to debug it better.

Hope this helps you!

See you in the Community, bye 🙂

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

RE: Find and Replace in Excel

salman_shaik — Mon, 08 Aug 2022 19:27:00 GMT

Hi,
i am having issue with Execute stage not in calc stage. Can you please share the screenshot of execute stage logic. Its missing some " in the query so during validation its throwing error.

can you pl validate the execute stage and share me the logic.

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

RE: Find and Replace in Excel

PabloSarabia — Mon, 08 Aug 2022 22:10:00 GMT

Hi @Salman Shaik

Understood, I think that your problem was internally when you execute the SQL. Something like SQL syntax error or similar.

I don't understand why it's gives you this error

Maybe something go wrong when you copy and paste

This is the text:

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

I attach you the full process. Check it in your Blue Prism Solution

Hope this helps you!

Bye 🙂

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