Read CSV file with both comma as column and decimal separator
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
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
Sort by:
1 - 2 of
21
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
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
Sort by:
1 - 1 of
11
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
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
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