Find and Replace in Excel

[salman_shaik]

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

[PabloSarabia]

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

[salman_shaik]

Hi @pablo sarabia

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

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

[PabloSarabia]

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

[salman_shaik]

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

[PabloSarabia]

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

[salman_shaik]

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

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

[PabloSarabia]

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

[salman_shaik]

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

[PabloSarabia]

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