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]

Hi @NiklasFranke,

Within the Code stage of the Import CSV action you should see the following line:
​

qt = ds.QueryTables.Add(Connection:="TEXT;" & Path, Destination:=dr)​

Try changing that to the following:

qt = ds.QueryTables.Add(Connection:="TEXT;" & Path & ";CharacterSet=UNICODE;", Destination:=dr)

Cheers,
Eric

[ewilson]

@NiklasFranke,

Ignore my last comment. Instead of changing the connection string you need to change the value of the .TextFilePlatform property in the Code stage. You should see this section of code:


Change the value of .TextFilePlatform from 437 to 65001 and you should be good to go.

-------------------------------------------------------------------------------------------------
UPDATE - FWIW we've updated the Excel VBO on the DX to include this change.

Cheers,
Eric​

[NiklasFranke]

Hey @ewilson

I changed it like you said
but now its even worse and i just dont know why,
At the moment a use the Replace in worksheet VBO and replace all the special chars like "Ö" with oe, etc. 

But that is not the perfect soloution.

[ewilson]

Hmm, that’s strange. I tested it with some data that included the ö and it worked fine for me. Can you post a test CSV so I can try it with data you’ve created?

Cheers,
Eric

[NiklasFranke]

Hello @ewilson in the attachment is a shortened Version of the csv.
I tryed this version too but its still the same.

Also i cant find an error in my process

Plus my collection has the same converting problem.

Greetings Niklas​​

[ewilson]

Hi @NiklasFranke,

There may be something wrong with your test data. I tried using the CSV you sent, but there were no special characters included in it that I saw whether I viewed it in Excel or in my UTF-8 text editor. So I went to this site​ and generated some test data that included German characters. Here's the data I end up with:


It didn't give me a lot of special German characters, but there are a few and I set it up to use the semi-colon as the delimiter. Here's what the data looks like after importing it into my Excel:


So it seems to be working.

Cheers,
Eric

[NiklasFranke]

​@ewilson thats strange.. so you dont have theese?
Can you show my you process? Maybe ours is different..​

Im not sure if Excel on our VMs is the reason but that can not be. The Excel VBOs are working perfect. Its just the CSV..​

[ewilson]

Hi @NiklasFranke,

Ok, this was a mistake on my part. You're test data was fine. I'm just blind I guess.

I opened your test data in my editor and checked the file properties. This is what UltraEdit detects as the encoding of the CSV file:


And this is what I see after importing your data into Excel:

Now, if I change the code page in the VBO from 65001 to 1252, as shown below:

This is what I see after importing the data into Excel:

So it seems 1252 is the magic code page for your CSV. I think what we'll do is change the Import CSV action such that you can provide the code page value as optional input.

Cheers,
Eric

[ewilson]

@NiklasFranke,

We've just posted version 10.0.2 of the Excel VBO. This includes a optional input parameter on the Import CSV action for specifying the encoding code page.

Cheers,
Eric​

[NiklasFranke]

@ewilson it worked! What a strage Encoding... i will make a note of that, and will check the Encoding for further CSV Datas, this one was just cursed.

I really really cant thank you enough. I would never have solven this problem by myself and really nobody could help me. ​

Thank you for all the help and the patience, you are my absolute hero.