Opening XLSX file and setting the variable type to double

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

Maybe due to NULL I cannot do mathematical operations with ValueMatrix. If this is the reason how can I substitute all NULLs to 0?

Answer 1

[Guillaume]

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;

[Konstantin Tkachuk]

Thank you for detailed responce!

Answer 2

[Ankit]

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

[Konstantin Tkachuk]

Thank you, that solution worked.