CSV field name not matching

[FrankieTEWV]

Hello,

Im attempting to build an automation where a csv file is downloaded and then its saved into a collection and then sent to a work queue to be worked.

This CSV file has lots of unwanted data in it and the only data i need for the automation is the employee numbers which all sit under the first column which has a header of 'Employee Number'.

My issue is when i run the process it populates the all the field names on csv but its saving the 'Employee Number' field as 'Employee Number' .. i have no idea why its putting those symbols before Employee Number and this is in turn not returning any of the employee number current values as its looking for Employee Number but has captured it as Employee Number

See below - CSV file - field name shows as Employee Number

Collection field name shows as

is it because the CSV has a cursor at the very start of that Employee Number heading? and if so how do you get it so it doesn't have the cursor or how do you programme it so it doesn't incorporate that '' at the start.

Thanks in advance to anyone who can help me!

Frankie

------------------------------
Frankie Legg
------------------------------

[devneetmohanty07]

Hi @Frankie Legg 

Can you also let us know what action you are using from which business object (VBO) exactly? The reason for such an error is not using the proper file encoding type as it seems to me form your screenshot. The cursor won't have anything to do with it as I particularly suppose you are using some sort of a VB .NET code in the business object that you are using rather than spying notepad.

I can suggest one way to try if that helps. 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 proper text can be identified. If this parameter does not work, feel free to experiment with some other parameters as each file differs from one another. Some of the encodings which can be used are: 'System.Text.Encoding.Default', 'System.Text.Encoding.UTF8', 'System.Text.Encoding.UTF16'

More information can also be found at the following website: How to use character encoding classes in .NET | Microsoft Learn

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

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

[sonsharm · Accepted solution]

Hi @Frankie Legg 

I have encountered a similar issue once. My csv file was in UTF-8 format. Below approach resolved my issue.

1.  Read data from a formatted csv file and store it in a text data item. Utility - File Management-->Read All Text From File
2. Save the text data item obtained from step1 to a new csv file. Utility - File Management-->Write Text to File
3. Now read the data from new csv file to a collection. Utility - File Management-->Get CSV Text As Collection

You can try implementing this logic and see if it is of any help.

------------------------------
Sonam Sharma
Manager, Blue Prism
SS&C
------------------------------

[FrankieTEWV]

thank you so much - we have followed the steps you suggested and this is now working ! 

------------------------------
Frankie Legg
------------------------------

[FrankieTEWV]

Thank you very much for your detailed response - i will keep this in mind for future builds as we have since resolved the issue .

------------------------------
Frankie Legg
------------------------------