Import CSV object with Semicolon delimiter (MS Excel VBO)

[KashishJain]

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

[GopalBhaire]

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

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

[sumire]

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

[KashishJain]

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

[KashishJain]

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

[GopalBhaire]

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

[sumire]

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

[KashishJain]

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

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

[NicholasZejdlik]

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

[NicholasZejdlik]

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