Import a CSV file with no header separating numbers from symbols in a table

Thank you in advance for your help.

I'm trying to import a CSV file coming from an outsource company, structured this way:

A,"7,40 €"
B,"20,60 €"
C,"23,99 €"
[...]

Where the first comma is the separator for the two different columns, while the second comma is the decimal of the price.

I tried to import it using reatable('filename.csv').

what i get is similar to the following result:

ans =

  1×3 table

                    A                                                                                                               x7_40_  
    ________________________________________________________________    ___________

    {'B'}                                                                                                                                {'20,60 €'}
   {'C'}                                                                                                                                {'23,99 €'}

What i would like to get instaed is a cell 1x3 like this

A    7.40
B    20.60
C    23.99

I can't substitute commas with dots previously otherwise it wont' recognise the two columns anymore. So i definitely need to manage the file after importing.

Is anyone so kind to please assist me in this?

Thank you.

Answer 1

[Ive J]

Readtable ReadVariableNames allows you this:

tab = readtable('tmp.csv', 'ReadVariableNames', false); % don't read variable names
tab.Var2 = cellfun(@(x)sscanf(x, '%f'), replace(tab.Var2, ',', '.'));

[Michele Rizzato]

Works great! Thanks!

Answer 2

[dpb]

Gotta' give it a little help...but boy! that's ugleee! :( At least they did use quoted strings.

opt=detectImportOptions('Michele.csv');
opt.VariableNamesLine=0;
opt.DataLines=[1 inf];
opt.VariableNames={'Var1','Var2'};
tMich=readtable('Michele.csv',opt);
tMich.Var1=categorical(tMich.Var1);
tMich.Var2=str2double(strrep(extractBefore(tMich.Var2,' '),',','.'));

resulted in

>> tMich
tMich =
  3×2 table
    Var1    Var2 
    ____    _____
     A        7.4
     B       20.6
     C      23.99
>>     

No matter what, DetectImportOptions and readtable want to make the first row into variable names -- this is a bug or at least a quality of implementation fault in my opinion. Shouldn't have to tell it not to do that if set the VariableNamesLine to 0. That's a nit, but an annoyance if don't know about it. In your case it ate the first data line as the variable names as well by default.

[Michele Rizzato]

Thank you, man!

i know but this is beyond my control. Gotta work with what i receive unfortunately!