Opening XLSX file and setting the variable type to double
9 views (last 30 days)
Show older comments
Konstantin Tkachuk
on 24 Sep 2019
Commented: Konstantin Tkachuk
on 27 Sep 2019
I am working with .xlsx file and trying to get the values from it and multiply by another value(for tests, I do +1 to every cell, but it is not working as well).
This is the code I am using
Input1 = readtable('OutputFile.xlsx','sheet',2);
WorkData = Input1(17:end,[7,23:end]);
i=[1,14,27,40,53,66,79];
ValueMatrix = table2array(Input1(18:end,23:end));
GWPValues = ValueMatrix(:,i);
GWPValuesMoney = cellfun(@(x) x+1, GWPValues, 'UniformOutput', false);
But when I am trying to add 1 to every cell, it treats the cells as string rather then numeric value.
In ValueMatrix I have only numeric values and NULL
![CaptureMatlab.PNG](https://www.mathworks.com/matlabcentral/answers/uploaded_files/239481/CaptureMatlab.png)
Maybe due to NULL I cannot do mathematical operations with ValueMatrix. If this is the reason how can I substitute all NULLs to 0?
0 Comments
Accepted Answer
Guillaume
on 24 Sep 2019
Edited: Guillaume
on 24 Sep 2019
You never need to use table2array to operate on a table. You can work directly on the table, it's often simpler.
If you look at the table once it's loaded, you'll see that it loads all the header data as table data. Since all that header is text, matlab automatically sets the variable type to text. Since you don't want that header data, you either need to tell matlab to ignore it by giving a 'Range' to readtable, or you need to use a function that's better at detecting the data format.
detectImportOptions, at least on R2019b, does a very good job of detecting and skipping the header.
opt = detectImportOptions('OutputFile.xlsx', 'Sheet', 2); %check that opt.VariableNamesRange is A18. if it is, it worked
workData = readtable('OutputFile.xlsx', opt);
However, it still imports some columns as text because some of them contain the text NULL. The best thing would be to fix your spreadhseet so you don't have mixed text and numbers in numeric columns. Note that even excel formulas would fail on such columns, so you can't blame matlab. Nonetheless, with detectImporOptions we can tell matlab to treat these variables as numbers, so the code becomes:
opt = detectImportOptions('OutputFile.xlsx', 'Sheet', 2);
opt = opt.setvartype(23:numel(opt.VariableNames), 'double'); %override data type for columns 23 to end
workData = readtable('OutputFile.xlsx', opt);
Now the file has been imported properly.
It appears you want to add one to all GWP* variables, in which case:
toadd = startsWith(workData.Properties.VariableNames, 'GWP');
workData{:, toadd} = workData{:, toadd} + 1;
More Answers (1)
Ankit
on 24 Sep 2019
Hello Konstantin,
I have a question why don't you change the excel sheet value from NULL to 0?
Could you please give a try to below code and let me know if its work for you.
Input1 = readtable('OutputFile.xlsx','sheet',2);
WorkData = Input1(17:end,[7,23:end]);
i=[1,14,27,40,53,66,79];
ValueMatrix = table2array(Input1(18:end,23:end));
GWPValues = ValueMatrix(:,i);
tf = strcmp(GWPValues,'NULL');
GWPValues(tf) = {0} ;
GWPValuesMoney = cellfun(@(x) x+1, GWPValues, 'UniformOutput', false);
Thank you
Ankit
See Also
Categories
Find more on Cell Arrays in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!