Convert CSV to EXCEL in correct format

[NiklasFranke]

Hello, 

i really hope that this belongs here but i have a Probmlem with converting a CSV to an Excel file.
When Blue Prism opens the CSV file, the format is realy unstructured (Pic1 Attachment). The strang thing ist that when i open the CSV by myself, it looks correct.

My Process (Pic2) is working and saves the CSV as an XLSX BUT the format is still like in Pic1.

I know that theres a similar case at: Solved - How to handle csv file

RPA Forum - Powered by Robiquity		remove preview
Solved - How to handle csv file Hi, I am having csv file, kindly guide me how to handle the csv file. csv file have text with ";" . kindly guide me how to convert this into proper excel file so that I can import the file and use it in my test. View this on RPA Forum - Powered by Robiquity >

but that just didnt work for me .

I really hope that you can help me, cause i just dont know what to do.

[ewilson]

Hello @NiklasFranke,

It's hard to tell from your Pic2.jpg, but it looks like you have multiple characters defined in your Qualifier (delimited) data item. I assume that's what you're passing into the Import CSV action as the value of Delimiter, correct?

Cheers,
Eric

[NiklasFranke]

Hello @ewilson,

correct, the Qualifier ";" is the delimiter for the CSV. And ist just the one char ";" in the Qualifier (delimited), in the picture you see initial and current value, thats why it looks like two values.

Also tried it with Get CSV as Collection and imorted that in a new Workbook. But now i would have to loop through every cell and try to replace the ";" with " "

That also doesnt look right..​

[NiklasFranke]

@ewilson

In the other forums i just read that you have to make a copy of the VBO Import CSV and change the following:


That almost works but it destroys my special sings like you can see in coloumn G
But i dont think, that making copys of the standard VBOs is a good way to work..

I just dont know why BP opens the CSV so strange ​

[NiklasFranke]

Hello @ewilson It Finally works!!

Had to change the delimiter from ";" to "," ​and also had to change the code in the Excel VBO

Just the special signs are wrong now

[ewilson]

Hi @NiklasFranke,
​
What version of the Excel VBO are you using? Is it the latest available on the DX? I've run some tests locally with a test CSV using ";" (semicolon) as the delimiter and it imported into Excel fine for me.

Glad to hear you got it working with some changes, but hopefully we can figure out why it didn't work for you previously.

Cheers,
Eric

[NiklasFranke]

Hey @ewilson Dont know where to find the Version, but we are running BP V7.1.0​

[ewilson]

Ok, it sounds like you're using the older VBO that ships with the software. I always recommend that people go to the Digital Exchange and get the latest VBO from there as we (the DX Integrations and Enablement team) release updates to the connectors much faster than the core product team.

The current release of the MS Excel VBO is v10.0 and it can be found here. We actually went through and increased the version number of the core VBOs to v10 in order to make them stand-apart from those that are included in the installer. We also include the version prominently on the Initialise tab in a note stage. Eventually, these VBOs should be removed from the installer making the DX the sole source of truth for VBOs.


Cheers,
Eric

[NiklasFranke]

Hello @ewilson,

i made a copy of the process and used the new VBO from V7.1.0 with the delimiter ";" but that didnt work. 

BUT, your VBO works!
Only problem remaining is that the special german letters are convertet wrong, as you can see in row 7. Is there any way to change that? I think the VBO just cant handle Ä,Ö,Ü etc.

Greetings,
Niklas 

[NiklasFranke]

@ewilson looks, like i have to convert the Encoding in the Codestage (maybe the Import CSV VBO?) to UTF-8 cause i use non english chars. I am trying that for the last 3 hours but i just dont know how to do that.​