1 BEST ANSWER
Best Answers
Hi Augustina,
Thanks for clarifying the exact action name. I think the issue is because you are using the action 'Get CSV Text As A Collection' from 'Utility - File Management' business object. This business object uses OLEDB operation in the back end to connect to your CSV file and treat it as a database. The issue here lies with the formatting of your file. Since, it is SQL based query if you do not internally cast the column at the point where you are firing the query the format changes won't appear. In your case, I guess that the code stage used in this action is treating your column as a 'Number' and that is the reason when you put a character such as 'R' it is not treated properly.
Replicating The Issue:
I can show you a similar result I performed on a test CSV file and in my case also, 'R' won't be treated properly. The only difference is I am not getting 0 but null values instead.
As you can see, I have the below CSV file with me:
When I try to use a similar workflow as you have used this is the result I got:
Making changes to the underlying code in this object might be bit tricky as OLEDB queries in general have a very limited set of formatting available with them.
Solution:
So instead, you can use a separate set of actions which I have used to get the same result as you expect:
So here I am using first the 'Read All Text From File' action in the 'Utility - File Management' VBO itself which will return me a text data item with the exact text content as shown below:
Once, you get this text data item, then simply use the 'Get CSV As Collection' action from 'Utility - Strings' business object where you can pass the text data item and store the result in a collection:
Conclusion:
This approach won't ideally fail as when we use the 'Read All Text From File' action, we are using the StreamReader class from System.IO namespace that directly reads all your text content as bytes and by default applies the encoding of 'UTF-8' which will handle most of the character formats unlike the OLEDB approach where formatting can be a constraint. The only advantage that OLEDB approach which you are using right now has would be the speed of execution and handling of very large text files.
------------------------------
----------------------------------
Hope it helps you out and if my solution resolves your query, then please mark it as the 'Best Answer' so that the others members in the community having similar problem statement can track the answer easily in future
Regards,
Devneet Mohanty
Intelligent Process Automation Consultant | Sr. Consultant - Automation Developer,
WonderBotz India Pvt. Ltd.
Blue Prism Community MVP | Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com
----------------------------------
------------------------------
Thanks for clarifying the exact action name. I think the issue is because you are using the action 'Get CSV Text As A Collection' from 'Utility - File Management' business object. This business object uses OLEDB operation in the back end to connect to your CSV file and treat it as a database. The issue here lies with the formatting of your file. Since, it is SQL based query if you do not internally cast the column at the point where you are firing the query the format changes won't appear. In your case, I guess that the code stage used in this action is treating your column as a 'Number' and that is the reason when you put a character such as 'R' it is not treated properly.
Replicating The Issue:
I can show you a similar result I performed on a test CSV file and in my case also, 'R' won't be treated properly. The only difference is I am not getting 0 but null values instead.
As you can see, I have the below CSV file with me:
When I try to use a similar workflow as you have used this is the result I got:
Making changes to the underlying code in this object might be bit tricky as OLEDB queries in general have a very limited set of formatting available with them.
Solution:
So instead, you can use a separate set of actions which I have used to get the same result as you expect:
So here I am using first the 'Read All Text From File' action in the 'Utility - File Management' VBO itself which will return me a text data item with the exact text content as shown below:
Once, you get this text data item, then simply use the 'Get CSV As Collection' action from 'Utility - Strings' business object where you can pass the text data item and store the result in a collection:Conclusion:
This approach won't ideally fail as when we use the 'Read All Text From File' action, we are using the StreamReader class from System.IO namespace that directly reads all your text content as bytes and by default applies the encoding of 'UTF-8' which will handle most of the character formats unlike the OLEDB approach where formatting can be a constraint. The only advantage that OLEDB approach which you are using right now has would be the speed of execution and handling of very large text files.
------------------------------
----------------------------------
Hope it helps you out and if my solution resolves your query, then please mark it as the 'Best Answer' so that the others members in the community having similar problem statement can track the answer easily in future
Regards,
Devneet Mohanty
Intelligent Process Automation Consultant | Sr. Consultant - Automation Developer,
WonderBotz India Pvt. Ltd.
Blue Prism Community MVP | Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com
----------------------------------
------------------------------
Hi Augustina,
Thanks for clarifying the exact action name. I think the issue is because you are using the action 'Get CSV Text As A Collection' from 'Utility - File Management' business object. This business object uses OLEDB operation in the back end to connect to your CSV file and treat it as a database. The issue here lies with the formatting of your file. Since, it is SQL based query if you do not internally cast the column at the point where you are firing the query the format changes won't appear. In your case, I guess that the code stage used in this action is treating your column as a 'Number' and that is the reason when you put a character such as 'R' it is not treated properly.
Replicating The Issue:
I can show you a similar result I performed on a test CSV file and in my case also, 'R' won't be treated properly. The only difference is I am not getting 0 but null values instead.
As you can see, I have the below CSV file with me:
When I try to use a similar workflow as you have used this is the result I got:
Making changes to the underlying code in this object might be bit tricky as OLEDB queries in general have a very limited set of formatting available with them.
Solution:
So instead, you can use a separate set of actions which I have used to get the same result as you expect:
So here I am using first the 'Read All Text From File' action in the 'Utility - File Management' VBO itself which will return me a text data item with the exact text content as shown below:
Once, you get this text data item, then simply use the 'Get CSV As Collection' action from 'Utility - Strings' business object where you can pass the text data item and store the result in a collection:
Conclusion:
This approach won't ideally fail as when we use the 'Read All Text From File' action, we are using the StreamReader class from System.IO namespace that directly reads all your text content as bytes and by default applies the encoding of 'UTF-8' which will handle most of the character formats unlike the OLEDB approach where formatting can be a constraint. The only advantage that OLEDB approach which you are using right now has would be the speed of execution and handling of very large text files.
------------------------------
----------------------------------
Hope it helps you out and if my solution resolves your query, then please mark it as the 'Best Answer' so that the others members in the community having similar problem statement can track the answer easily in future
Regards,
Devneet Mohanty
Intelligent Process Automation Consultant | Sr. Consultant - Automation Developer,
WonderBotz India Pvt. Ltd.
Blue Prism Community MVP | Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com
----------------------------------
------------------------------
Thanks for clarifying the exact action name. I think the issue is because you are using the action 'Get CSV Text As A Collection' from 'Utility - File Management' business object. This business object uses OLEDB operation in the back end to connect to your CSV file and treat it as a database. The issue here lies with the formatting of your file. Since, it is SQL based query if you do not internally cast the column at the point where you are firing the query the format changes won't appear. In your case, I guess that the code stage used in this action is treating your column as a 'Number' and that is the reason when you put a character such as 'R' it is not treated properly.
Replicating The Issue:
I can show you a similar result I performed on a test CSV file and in my case also, 'R' won't be treated properly. The only difference is I am not getting 0 but null values instead.
As you can see, I have the below CSV file with me:
When I try to use a similar workflow as you have used this is the result I got:
Making changes to the underlying code in this object might be bit tricky as OLEDB queries in general have a very limited set of formatting available with them.
Solution:
So instead, you can use a separate set of actions which I have used to get the same result as you expect:
So here I am using first the 'Read All Text From File' action in the 'Utility - File Management' VBO itself which will return me a text data item with the exact text content as shown below:
Once, you get this text data item, then simply use the 'Get CSV As Collection' action from 'Utility - Strings' business object where you can pass the text data item and store the result in a collection:Conclusion:
This approach won't ideally fail as when we use the 'Read All Text From File' action, we are using the StreamReader class from System.IO namespace that directly reads all your text content as bytes and by default applies the encoding of 'UTF-8' which will handle most of the character formats unlike the OLEDB approach where formatting can be a constraint. The only advantage that OLEDB approach which you are using right now has would be the speed of execution and handling of very large text files.
------------------------------
----------------------------------
Hope it helps you out and if my solution resolves your query, then please mark it as the 'Best Answer' so that the others members in the community having similar problem statement can track the answer easily in future
Regards,
Devneet Mohanty
Intelligent Process Automation Consultant | Sr. Consultant - Automation Developer,
WonderBotz India Pvt. Ltd.
Blue Prism Community MVP | Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com
----------------------------------
------------------------------
Hi Augustina,
For the special character not being read properly issue, you can go inside the 'File Management' business object and go to the action 'Read All Text From File' and then in the code stage make the modification which I have highlighted once:
NOTE: I would recommend to create a duplicate copy of the File Management business object and make the changes there and use that so that you don't mess up the original business object if it used by some other bot in your environment.
Your updated code should look like below:
Here, I have just added an extra parameter called as '
For the other issue related to the loop part, no that most likely won't be the case because what we are getting right now is an undefined collection in contrast to what you had previously with you called as a defined collection. No matter what type of collection we have, looping should be possible as long as you have rows in that collection and if you are using the correct 'Collection Name' in your loop stage. Since the names of your collection are identical the old one and the new one it might be the case that why is it take an incorrect collection. I would first suggest to check the names and make them clearly distinct from each other.
Next, are you using a loop stage to iterate within the collection? If yes, check if you have supplied the correct collection name to it. If no, then are you using a decision stage to control the index of rows in your collection and using that to iterate?
------------------------------
----------------------------------
Hope it helps you out and if my solution resolves your query, then please mark it as the 'Best Answer' so that the others members in the community having similar problem statement can track the answer easily in future
Regards,
Devneet Mohanty
Intelligent Process Automation Consultant | Sr. Consultant - Automation Developer,
WonderBotz India Pvt. Ltd.
Blue Prism Community MVP | Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com
----------------------------------
------------------------------
For the special character not being read properly issue, you can go inside the 'File Management' business object and go to the action 'Read All Text From File' and then in the code stage make the modification which I have highlighted once:
NOTE: I would recommend to create a duplicate copy of the File Management business object and make the changes there and use that so that you don't mess up the original business object if it used by some other bot in your environment.
Your updated code should look like below:
Try
If File.Exists(File_Name) Then
Dim sr As New StreamReader(File_Name,System.Text.Encoding.ASCII)
Text = sr.ReadToEnd
sr.Close()
Success = True
Message = ""
Else
Throw New ApplicationException("The file at " & File_Name & " does not exist")
End If
Catch e As Exception
Success = False
Message = e.Message
End TryHere, I have just added an extra parameter called as '
System.Text.Encoding.ASCII' which will try to change the encoding format while reading your text file so that other special characters can be recognized.For the other issue related to the loop part, no that most likely won't be the case because what we are getting right now is an undefined collection in contrast to what you had previously with you called as a defined collection. No matter what type of collection we have, looping should be possible as long as you have rows in that collection and if you are using the correct 'Collection Name' in your loop stage. Since the names of your collection are identical the old one and the new one it might be the case that why is it take an incorrect collection. I would first suggest to check the names and make them clearly distinct from each other.
Next, are you using a loop stage to iterate within the collection? If yes, check if you have supplied the correct collection name to it. If no, then are you using a decision stage to control the index of rows in your collection and using that to iterate?
------------------------------
----------------------------------
Hope it helps you out and if my solution resolves your query, then please mark it as the 'Best Answer' so that the others members in the community having similar problem statement can track the answer easily in future
Regards,
Devneet Mohanty
Intelligent Process Automation Consultant | Sr. Consultant - Automation Developer,
WonderBotz India Pvt. Ltd.
Blue Prism Community MVP | Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com
----------------------------------
------------------------------
.png){kind=link}
.png){kind=link}
