Find and Replace in Excel
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
05-08-22 08:06 PM
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
------------------------------
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
05-08-22 10:25 PM
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
------------------------------
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
06-08-22 07:23 AM
by using the above query i am getting the below error. Pl suggest how to handle
------------------------------
Salman Shaik
------------------------------
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
07-08-22 08:53 AM
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:
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
"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
------------------------------
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
07-08-22 09:12 AM
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
------------------------------
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
07-08-22 09:16 AM
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
------------------------------
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
07-08-22 09:21 AM
i have more than 70 fields in my excel.
is there any wild card functionality in query?
------------------------------
Salman Shaik
------------------------------
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
07-08-22 09:36 AM
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
------------------------------
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
07-08-22 09:43 AM
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
------------------------------
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
07-08-22 09:57 AM
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
------------------------------
