topic RE: Import CSV object with Semicolon delimiter (MS Excel VBO) in Product Forum

Import CSV object with Semicolon delimiter (MS Excel VBO)

KashishJain — Wed, 29 Apr 2020 09:52:00 GMT

Hi,

I am using MS Excel VBO - Import CSV Action to get csv into excel. The CSV has semicolon separated values so i changed the the code to this :

There are no errors in the object but when I run the code it says : The given key was not present in the dictionary and fails.

Can anybody help me with this ? Or have any other solution for this ?

------------------------------
Kashish Jain
RPA Developer
Schneider Electric
------------------------------

RE: Import CSV object with Semicolon delimiter (MS Excel VBO)

GopalBhaire — Thu, 30 Apr 2020 04:22:00 GMT

It mostly means it couldn't find the handle if no instance is created, where are you creating instance.

------------------------------
Gopal Bhaire
Analyst
Accenture
------------------------------

RE: Import CSV object with Semicolon delimiter (MS Excel VBO)

sumire — Thu, 30 Apr 2020 05:41:00 GMT

Hi Kashish,

I don't think you have to change code.
Just read your csv file to data item(text) using "read all text from file", replace semicolon to comma, and use "get csv as collection" action

------------------------------
Mitsuko
Asia/Tokyo
------------------------------

RE: Import CSV object with Semicolon delimiter (MS Excel VBO)

KashishJain — Thu, 30 Apr 2020 06:24:00 GMT

Hi Gopal,

The Instance is being created in the process using create instance from MS Excel VBO and then passed onto this action as a parameter.

------------------------------
Kashish Jain
Application Engineer
Schneider Electric
------------------------------

RE: Import CSV object with Semicolon delimiter (MS Excel VBO)

KashishJain — Thu, 30 Apr 2020 06:27:00 GMT

Hey,

Could you explain where will I find these "read all text from file'' and "get csv as collection" objects and what will the flow be like?

------------------------------
Kashish Jain
Application Engineer
Schneider Electric
------------------------------

RE: Import CSV object with Semicolon delimiter (MS Excel VBO)

GopalBhaire — Thu, 30 Apr 2020 06:50:00 GMT

From screenshot I can see you have created a different object and the instance is not present in dictionary of this object. Copy this page to the MS Excel VBO and then run.

------------------------------
Gopal Bhaire
Analyst
Accenture
------------------------------

RE: Import CSV object with Semicolon delimiter (MS Excel VBO)

sumire — Thu, 30 Apr 2020 07:09:00 GMT

"read all text from file" is in utility-file management
"get csv as collection" is in utility-strings

flow is:

and calculate stage is Replace([Text],";",",")

------------------------------
Mitsuko
Asia/Tokyo
------------------------------

RE: Import CSV object with Semicolon delimiter (MS Excel VBO)

KashishJain — Thu, 30 Apr 2020 09:05:00 GMT

So after changing the text from ";" to "," do we not have to save it or something ?

------------------------------
Kashish Jain
Application Engineer
Schneider Electric
------------------------------

RE: Import CSV object with Semicolon delimiter (MS Excel VBO)

NicholasZejdlik — Thu, 30 Apr 2020 15:37:00 GMT

Using Replace will not work in all cases. Depending on the text, it could either create extra fields, or change the value of a field which contains a quoted semi-colon. In the Utility - Strings object, there is an action for Get Delimited Text As Collection, which allows you to specify the delimiter to use. So you'd keep the Read All File From Text, but replace the next two stages with Get Delimited Text As Collection.

------------------------------
Nicholas Zejdlik
RPA Developer
------------------------------

RE: Import CSV object with Semicolon delimiter (MS Excel VBO)

NicholasZejdlik — Thu, 30 Apr 2020 15:41:00 GMT

At that point, you have the semi-colon delimited CSV file loaded as a collection. If you wanted to save the file with the updated delimiter, you could use Get Collection As CSV to convert the collection into a comma delimited CSV, then write that to the file. Alternatively, you could launch Excel and write the collection into a worksheet directly.

------------------------------
Nicholas Zejdlik
RPA Developer
------------------------------

RE: Import CSV object with Semicolon delimiter (MS Excel VBO)

KashishJain — Tue, 05 May 2020 06:58:00 GMT

Hey Mitsuko,

This worked for me. Thanks for the support 🙂

------------------------------
Kashish Jain
Application Engineer
Schneider Electric
------------------------------

RE: Import CSV object with Semicolon delimiter (MS Excel VBO)

RageLiberato — Fri, 11 Sep 2020 05:57:00 GMT

Hi @KashishJain,

How did you make it work? did you save or target the text replaced? I tried both and its not working

------------------------------
Rage Liberato
------------------------------