Reading csv files with 18 first rows as headers and ";" as the separator


Need help right here. I have csv files with 18 headers. The values are separated by semicolon and it contains duplicated column names!!
First problem: Read CSv activity throws an error as there are duplicate names.
Second, I tried to exclude option “include Column Names”. What happened then? CSV throws an error as the columns format is mixed with and without quotes
What I am doing so far: as I only need the last row to be the header, I deleted the header using invoke VBA inside excel application scope. Problem is, after excel read this and perform action, somehow UiPath recognizes the values as comma separated!
And if I go further with it by using comma as the delimiter and use write range activity to export it to excel, it did not even separate the values :frowning:
As visual aid, below is how the header looked like.
thank you for your helps.

@Nazarius Can you send the Screenshot of the File after Invoke VBA is executed.

Hi @supermanPunch,

Thank you for your quick respond. As it contains sensitive data, I cannot send you the whole screen shot. however, As I mentioned before by invoking vba there is only one row left and that is the one I need as header:

Hope this is enough for you.

@Nazarius Are those values present only in the First Cell of the first row?

Hallo @supermanPunch,

Correct! the values exist only inthe first column.
I sense your power now haha.
Thanks for helping me further.

@Nazarius So is that a problem or is that how you need it? :sweat_smile:

that is not the problem, however if you read my last sentences of the first post you will know what the problem is. read write range does not result into the right excel format. simply to say: the values stay in one column

; @Nazarius Row
So it’s in this format , if im not wrong , :sweat_smile:

Yes correct. and that is after the data table is written into excel as the final result.
I dont want this. any suggestion?

@Nazarius You can take this Excel and Use Generate DataTable and split the first cell using ‘;’ as a column separator, and then use write range to some other file or the same file. In this way columns will be differentiated. If you don’t want this method i need to think of something else :sweat_smile:

1 Like