topic RE: Import CSV in Product Forum

Import CSV

AgustinaDiaz_Ca — Mon, 30 May 2022 18:36:00 GMT

I'm importing a CSV file using a comma delimiter, which works successfully. However, when i use "Get CSV text as a Collection" the data that contains a "R" value its transformed to "0" during import. How can I change this?

Cuando uso la acción "Get CSV text as a Collection" me escribe las R como 0. Este input se encuentra separado entre comas nada mas. Como hago para que escriba en la collection R y no 0?
------------------------------
Agustina Diaz Casas
------------------------------

RE: Import CSV

devneetmohanty07 — Mon, 30 May 2022 20:16:00 GMT

Hi Augustina,

I suspect the issue might not be at 'Get CSV Text As Collection' action but maybe even before that when you are actually reading the text from the CSV file itself.

Can you check couple of things at your end:

1) Which action are you using for reading the CSV text from the file? Is it 'Read All Text From File' action of 'Utility - File Management' Business Object ?

2) Is this same issue of 'R' getting replaced by '0' happening in the Text data item which is holding the entire CSV content when you read the CSV file. Just try to step in before 'Get CSV Text As Collection' action and check what is the current value of the input data item to this action. Let us know if there only you are getting this issue itself or not ?

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

----------------------------------
------------------------------

RE: Import CSV

AgustinaDiaz_Ca — Mon, 30 May 2022 20:50:00 GMT

Hi!
First I use Get Files action of Utility - File Management and then Get CSV Text as a Collection. In that collection is where i get the "0" instead of the "R". I´ll attach a picture of the data in excel as I think this is where the problem arises but my client cannot make changes to it so all i have left is to try fix it in BP.

------------------------------
Agustina Diaz Casas
------------------------------

RE: Import CSV

devneetmohanty07 — Mon, 30 May 2022 22:34:00 GMT

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

----------------------------------
------------------------------

RE: Import CSV

AgustinaDiaz_Ca — Tue, 31 May 2022 16:58:00 GMT

This worked great but i cant loop the collection later on in the process, is it because i dont have collection fields?

On the other hand, when i use "Read text from file" the Data doesnt recognize "ñ" or words with an accent mark, so if i try to use the old collection to store this data i cant, because it already has fields that doesnt match. Ill attach everything, hopefully you understand.

------------------------------
Agustina Diaz Casas
------------------------------

RE: Import CSV

devneetmohanty07 — Tue, 31 May 2022 18:27:00 GMT

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:

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 Try

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

----------------------------------
------------------------------

RE: Import CSV

PabloSarabia — Wed, 01 Jun 2022 06:20:00 GMT

Hi everyone!

I love this kind of spanish problem 🙂

Just add to the Devneet's explanation, when you "translate" the encoding from a file from one to other, this may cause some characters to be "lost". When I need to read text, usually we use the UTF-8 encoding. If you are not pretty sure of with encoding you have in the file, or problably have a lot of types, you can use the encoding default.

System.Text.Encoding.Default

Hope this helps you!!

See you in the Community 🙂

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

RE: Import CSV

AgustinaDiaz_Ca — Wed, 01 Jun 2022 18:04:00 GMT

Hola Pablo!
This is what ended up working for me, muchas gracias!!

------------------------------
Agustina Diaz Casas
------------------------------