MATLAB: Problem reading excel data, unknown format

importing excel datastring conversionxlsread

I have a sample of data from a bigger one in excel (attached), I tried with this lines of code to read the data (I need to work with the numeric values), I know they are special characters since they have a blank space before the numeric str, I tried the next but in doesn't work, any ideas?

[num,txt,raw] = xlsread('data.xls', '1', 'A1:N8'); x=(strtrim(char(txt(1,7))))% it should give '14' but still gives ' 14'

thanks

Best Answer

It’s not a ‘normal’ space (char(32)). It’s a ‘nonbreaking’ space, (char(160)).

Use strrep to replace it with a ‘normal’ space:

txt = strrep(txt, char(160), char(32));
x=(strtrim((txt(1,6))));

Then, it works.

Related Solutions

MATLAB: File-loop, for loop,importing excel data

%Read each CSV file, change the text data to numeric and work with it
clear;
clc;
dirroot='C:\Documents\Excel\';
for L=1:2
    
    dirname     = [dirroot, 'L', num2str(L)];
    files       = dir([dirname,'*.csv']);             % Listing all files in the given 
                                                            % directory with the extension of csv
    filenames   = {files.name};
    
    for i=1:size(filenames, 2)                              % load all the csv files separately
                                                  
        t           = readtable(string(filenames{i}));      % Give me the csv file in a table
        t.Var1      = str2double(string(t.Var1));           % convert the first 2 variables from characters in cells to doubles
        t.Var2      = str2double(string(t.Var2));           % text is replaced by NaN (not a number)
        data        = table2array (t);                      % convert to double array
        
        [rows, columns] = size(data);                       % The size of the matrix (the number of
                                                            % elements is the same for all columns
                                                            % in a matrix, also nr 16)
        is_max_rows      = islocalmax(data(:,16))           % logical vector of all local max in
                                                            % column 16
        local_max_col_16 = data(is_max_rows, 16)            % value of local maxima in column 16
        
        [rowsWith50, colsWith50] = find(data == 50);        % Find out all the rows/cols that have a 50
                                                            % in them.
        theMax = max(data(:,unique(colsWith50)))            % get the max of the columns with  a 50
                                                            %  without repetition of the columns with
                                                            % 2x or more 50
                                                            
        save([dirname, 'results_L_', num2str(L), '_dataset_', num2str(i)]); % save data
    end
end

It might not be the most elegant way how I did it, either. But it should work if the structure of all the files is the same (text in first 2 columns).

But lmk how you get along!

MATLAB: Isn’t strtrim working on the string

Your whitespace might be "significant", as mentioned in the documentation for strtrim.

For example, if you do

s = [char(160) 'abcd']
strtrim(s)

then the strtrim command will have no effect.

What do you get for these whitespace characters if you type

double(s)

where s is your string?

You could use regexprep to get rid of significant whitespace:

s = regexprep(s,char(160),'')

You'll need to take care of not removing those special characters from other places.

Best Answer

Related Solutions

MATLAB: File-loop, for loop,importing excel data

MATLAB: Isn’t strtrim working on the string

Related Question