CSV text as collection giving special character in header

[Neel1]

I am reading a CSV as CSV text as collection action. i am getting special chacacter in first rows.

on checking found, encoding of CSV is UTF-8 BOM . how to fix this in blue prism so that it do not give special character.

------------------------------
Neeraj Kumar
------------------------------

[Athiban_Mahamathi]

Hi Neeraj,

I'd like to provide you with some suggestions on how to handle a UTF-8 BOM encoded CSV file:

• Read the CSV data using MS Excel-VBO (Get worksheet as Collection Action)
• Remove the UTF-8 encoding using custom code stages.

Between these alternatives, opting for the MS EXCEL - VBO approach appears to be the more straightforward solution.

------------------------------
Athiban Mahamathi - https://www.linkedin.com/in/athiban-mahamathi-544a008b/
Technical Consultant,
SimplifyNext,
Singapore
------------------------------

[_Amlansahoo]

Hi Neeraj,

If you can attach a sample csv file, then I can help you with custom codes for the same.

Also Neeraj in the meanwhile try to use the Import CSV function present in BP Default. You may have to change the code a bit according to your requirement then it will work. Just record a macro while importing the csv into Excel. You will get the below-attached code copy the same here. It should work. The highlighted one is the code for UTF -8 that you have to change in the existing code.

 

To get this option in Excel 365 you have to do few changes to get the above code by recording macro. Please see attached image for this,

------------------------------
Amlan Sahoo
Senior RPA Consultant
WonderBotz
------------------------------

[Neel1]

hello Amlan Sahoo & Athiban Mahamathi Mathialagan

actually want to avoid using EXCEL VBO route and macro. but that is good suggestion -will try out this option.

i found one utility on digital exchange. that one is working fine.

https://digitalexchange.blueprism.com/dx/entry/3439/solution/blue-prism---read-csv?_ga=2.164614207.1278002800.1691419152-1643662959.1690267307&_gl=1*xu4g8w*_ga*MTY0MzY2Mjk1OS4xNjkwMjY3MzA3*_ga_MFBQ2KF...

------------------------------
Neeraj Kumar
------------------------------

[stepher]

@Neeraj Kumar,

If you are getting the same special character consistently, then I would suggest the following:

• Read your CSV file into a text variable.
• Clean up your header in the text variable
  • I would use the Replace function
  • If the character is not consistent, then maybe a RegEx evaluation would be in order
• Using the same Strings object, use the 'Delimited Text to Collection' function on your clean(-er) text value

I know that I have also seen some of the actions have an input field for the formatting (UTF-8, UTF-8 BOM, etc), so it might be worth asking to have this added to the Strings object.

Good luck,

Red

------------------------------
Robert "Red" Stephens
Application Developer, RPA
Sutter Health
Sacramento, CA
------------------------------