Data from excel with operators

I have a excel sheet with two coloumns. One has a name other has the corresponding mass to it. I have used the correspèonding line to read it and find the position of the name. But when i am trying to fing the mass to the corresponding name as shown below it is not able to store it in the memory. In the excel i have the mass values as 1.989*10^30. this seems to affect the code as the same code works fine when the cells in the excel has just numeric values.

majbod = 'Sun';
minbod = 'Earth';
majbodin = readtable("Major_and_Minor_Bodies.xlsx","Sheet",1);
minbodin = readtable("Major_and_Minor_Bodies.xlsx","Sheet",2);
MAJORBODY = table2array(majbodin(:,"Major_Body"));
MINORBODY = table2array(minbodin(:,"Minor_Body"));
mmaj = table2array(majbodin(:,"Mass"));
mmin = table2array(minbodin(:,"Mass"));
selected_majbody = find(strcmp(MAJORBODY,majbod));
selected_minbody = find(strcmp(MINORBODY,minbod));
M = mmaj(selected_majbody);
m = mmin(selected_minbody);
disp([M ;m])

I have attached the excel file with the code. Please help me with this.

Is there a better way to write the code compared to the way which I wrote?

Thanks.

Answer 1

[Cris LaPierre]

The main issue you are having is that your numbers are not numbers, but text. If possible, update your numbers in Excel to use this notation instead: 1.989E+30

That way, the mass can be read in as a number. Otherwise, convert the text representation of the number to a number.

Is there an easier way? Yes! Use the table to your advantage. Make the object the row name, and then reference it by name.

opts = spreadsheetImportOptions("NumVariables", 2);

% Specify sheet and range
opts.Sheet = "Sheet2";
opts.DataRange = "A2";

% Specify column names and types
opts.VariableNames = ["Minor_Body", "Mass"];
opts.VariableTypes = ["categorical", "string"];

minorBodies = readtable("Major_and_Minor_Bodies.xlsx", opts,"ReadRowNames",true);
%This next line replaces the " *10^" with "e"
minorBodies.Mass=str2double(regexprep(minorBodies.Mass,['(\s*)+\*10\^'],['e']));

Now when you want to reference a mass, use the rowname.

minorBodies.Mass("Saturn")

ans = 5.6830e+26

[Lepakshi Ramkiran]

Got it thanks a lot!!!!

[Lepakshi Ramkiran]

What should I use if I have multiple coloumns like A2 and A3 to use then what should be changed in the code??

opts.DataRange = "A2";

I tried using an array but it should "When specifying multiple ranges, 'DataRange' must only contain row ranges." this error. so what should be modified?

opts.DataRange = ["A2" ;"A3"];

Thanks.

[Cris LaPierre]

This is just telling it which row the data begins on. Without this input, it tries to read the headers in as data as well. the data you currently have already has 2 columns of data, and it is working as intended.

[Lepakshi Ramkiran]

ohh. I have added a third column to the excel, and i have changet the code as follows. But its gives out thr value of the third variable as 0. what is that i have to correct in here? i have attached the new spreadsheet throughg which i have to do that.

minor_bodies = spreadsheetImportOptions("NumVariables", 3);
major_bodies = spreadsheetImportOptions("NumVariables", 2);
major_bodies.Sheet = "Sheet1";
major_bodies.DataRange = "A2";
minor_bodies.Sheet = "Sheet2";
minor_bodies.DataRange = "A2";
major_bodies.VariableNames = ["Major_Body", "Mass", "Radius"];
major_bodies.VariableTypes = ["categorical", "string", "double"];
minor_bodies.VariableNames = ["Minor_Body", "Mass", "Radius"];
minor_bodies.VariableTypes = ["categorical", "string", "double"];

minorBodies = readtable("Major_and_Minor_Bodies.xlsx", minor_bodies,"ReadRowNames",true);
majorBodies = readtable("Major_and_Minor_Bodies.xlsx", major_bodies,"ReadRowNames",true);
minorBodies.Mass=str2double(regexprep(minorBodies.Mass,['(\s*)+\*10\^'],['e']));
majorBodies.Mass=str2double(regexprep(majorBodies.Mass,['(\s*)+\*10\^'],['e']));

M = minorBodies.Mass("Saturn");
m = majorBodies.Mass("Saturn");
rma = majorBodies.Radius("Saturn");
disp([M ;m ;rma])

[Cris LaPierre]

It's not 0, it's 0.000e+26

   1.0e+26 *

    5.6830
    5.6830
    0.0000

The value is being hidden because the magnitude of the other values are so high. Remove the semicolon from the end of the line that assigns a value to rma to see it.

[Lepakshi Ramkiran]

yup that was it. thanks a lot.