Read CSV file with both comma as column and decimal separator

TLBTLB Member Posts: 2 Newbie
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

Tagged:

Best Answer

  • TLBTLB Member Posts: 2 Newbie
    Solution Accepted
    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

Answers

  • lionelderkrikorlionelderkrikor Moderator, RapidMiner Certified Analyst, Member Posts: 1,195 Unicorn
    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


Sign In or Register to comment.