Read CSV file with both comma as column and decimal separator

TLB

Good evening!
This is my first answer so i beg you pardon me if i violated some rule or etiquette code.

I have to read a CSV file like this:

Name,Country,Weight,Gender
John,Spain,"90,45",Male

I tried to read it with "read CSV" with " as quotes but RM does not read any line.
If I disable the quotes, RM separates me 90 and 45 in different columns.

How could I read it?
Maybe exist a regexp that could replace commas but only of they are (or aren't) surrounded by numbers? in that case I would do that.

Thank you!
Tommaso

TLB

Thank you for your reply!

I tried again and with that "dataset" It works.
So I studied better the complete dataset on Notepad++ and found that a correct example could be:
Name,Country,Weight,Gender
John,Spain,="90,45",Male

So I read the file without separating columns, then I replaced "=" with nothing, saved It as CSV and re-read It directory from the previous writer operator - now It works.

Is there a smarter way to do the job?

Tommaso

lionelderkrikor

Hi @TLB,

I don't know how your .csv file is formatted.
But I have saved a file (new1.txt in attached file) in Notepad ++ as .txt and the file is corrected imported for me (all is working as expected in RM)
Try to open your file with Notepad ++  and save it as .txt.
then reimport it with Read CSV operator by checking quote caracter = "
If it does not work , please share your file in order we can reproduce and thus understand what is going on ?

Regards,


Lionel

TLB

Thank you for your reply!

I tried again and with that "dataset" It works.
So I studied better the complete dataset on Notepad++ and found that a correct example could be:
Name,Country,Weight,Gender
John,Spain,="90,45",Male

So I read the file without separating columns, then I replaced "=" with nothing, saved It as CSV and re-read It directory from the previous writer operator - now It works.

Is there a smarter way to do the job?

Tommaso