readcell is filling cell locations with "1x1 missing"

104 views (last 30 days)
I am using R2109a and it is recommended to use readcell instead of xlsread. Readcell is slower than xlsread and it is returning "1x1 missing" instead of an empty cell when reading an .xlsx file. I don't know what logical I can use to remove these cell locations without brute forcing the problem. Does anyone else know what I can do to fix this?
Capture.PNG
  2 Comments
Josef Shaoul
Josef Shaoul on 11 Jan 2020
The other answers did not work for me. readtable was very slow, and the cellfun(missing...) solution gave empty columns.
The simple, stupid solution is use readcell (faster than xlsread) and just a double loop over the raw cell data to convert <missing> to NaN. That yielded the same result as xlsread.
Would be ven better if the next version would restore the result from xlsread to readcell, because now the cell array that is imported cannot be saved again in an Excel file!
Stephan Piotrowski
Stephan Piotrowski on 21 Jan 2022
@Josef Shaoul I experienced extremely slow readtable, but found that calling it as readtable(input,'basic',true) greatly improved the performance.

Sign in to comment.

Accepted Answer

Guillaume
Guillaume on 4 Jun 2019
If your data (excluding header) is purely numeric, you'd be better off using readmatrix instead of readcell. If your date is heterogeneous you'd be better off using readtable. The matrix/table will use less memory than a cell array and more crucially make it very easy to remove rows with <missing> entries with rmmissing.
t = rmmissing(readtable(youfile))
With a cell array, rmmissing will only work if the cell array only contain char vectors.

More Answers (1)

Jon Martinez
Jon Martinez on 4 Jun 2019
I've had the same problem reading a CSV file and managed to substitute all missing cells with this command:
your_file = readcell(your_path);
your_file(cellfun(@(x) any(ismissing(x)), your_file)) = {''};
The cellfun command will give you a logical array that mantains the structure of the original cell array. Hope it helps.
  2 Comments
David Teitge
David Teitge on 6 Jun 2019
Thanks! I did not try your answer because the other answer suggested using another function which works better than readcell. I still appreciate it!

Sign in to comment.

Categories

Find more on Creating and Concatenating Matrices in Help Center and File Exchange

Tags

Products


Release

R2019a

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!