Removing unrelevant spaces from a csv file with Matlab

1
votes

I am trying to import a csv file with Matlab, containing headerlines and several columns. It starts like : 

Essai : Taux 1;"1,00000";mm/min
Déformation : Déformation de traction (Déplacement) longueur initiale;"100,00000";mm
Généralités : Date de début;"09/07/2015 17:12"
Généralités : Date de fin;"09/07/2015 17:15"

Temps;Charge;Déplacement traverse
(s);(N);(mm)
"0,00000";"-0,22448";"0,00000"
"0,10000";"2,56269";"0,00093"
"0,20000";"3,84100";"0,00328"
"0,30000";"3,84073";"0,00524"
"0,40000";"4,06938";"0,00648"
...

I can import it with xlsread removing the headerlines by hand :

A = xlsread('filename.csv');
B = A(n_headerlines+1:end,:);

and it works fine. However, when I reach thousands, there are unrelevant spaces in my data : 

...
"147,70000";"984,81097";"2,46197"
"147,80000";"998,60400";"2,46318"
"147,89999";"1 020,65094";"2,46489"
"148,00000";"1 043,27832";"2,46694"
...

which gives NaN's in A or B after importing the data.

How can I remove these spaces while keeping the csv format ? I was thinking of using strrep but I cannot figure out how without destroying the formatting of my csv file.

Thank you for you help !

2
matlabcsvdata-import
Are you sure that the spaces come from the xlsread and not from the original csv file ? Furthermore, I don't understand why you're afraid of using to use a strrep to remove blank spaces, since every blank space you will encounter seems to be an issue. - Peut22
@zounnouf, It seems you have 2 options. Either you should remove the spaces in your csv file before using xlsread or read your csv file line by line as string using delimiter ";" and remove spaces from each string and fill a matrix - Lati
if the NaNs in A are bothering you, you can just get rid of them with A(isnan(A))=[]; after your call to xlsread. - Hoki

2 Answers

0
votes

Try using [~,~,A]=xlsread(...) instead of A=xlsread(...).

The latter call returns numerical values only, with NaN for anything that doesn't convert properly. Since you have spaces in all values over a thousand, you're seeing NaN in the num-only output.

The former call will contain numerical data and text data in a cell. You can operate on the text strings to remove spaces and then convert to numbers (using cellfun). Afterward you can convert the entire cell into a numerical array (using cell2mat).

As best practice, I recommend using delimread instead of xlsread on csv files. You will still need to remove the spaces.

0
votes

Thanks for your help

Based on Kirby Fears tip, I could import the data into a cell, with my wrong-formatted values into strings instead of NaN's, then use strrep.

Somehow, I could not replace right away the spaces in the strings (probably I acted on a 1x1 cell and not on a string). Finally, the following works :

[a,b,c]=xlsread('filename.csv');
idx_NaN = find(isnan(a)); % A way to track all indexes corresponding to wrong-formatted data. Watch out : linear indexes !
a_without_NaN = a;
for i = 1:size(idx_NaN,1)
    [i_NaN, j_NaN] = ind2sub(size(a),idx_NaN(i));
    char_NaN = c{i_NaN, j_NaN};
    if ~isnan(char_NaN)
        char_NaN = strrep(char_NaN,',','.');
        char_NaN = char_NaN(~isspace(char_NaN));
        a_without_NaN(i_NaN,j_NaN) = str2double(char_NaN);
    end
end

a_without_NaN contains the imported data, with satisfying format.