Get the value of a cell that is in the same row of matching cell in excel to matlab
13 visualizaciones (últimos 30 días)
Mostrar comentarios más antiguos
Abdullah Azzam
el 6 de Feb. de 2020
Comentada: Abdullah Azzam
el 8 de Feb. de 2020
Hi guys and thanks in advance.
I have plenty of excel sheet that I am combining togather. However, the locations of the information in some sheet differ from those of others and I am trying to generalize my code. Consider the shown example
![Example.PNG](https://www.mathworks.com/matlabcentral/answers/uploaded_files/270195/Example.png)
Lets say that I want to import the value correspond to "E" which is "5" as in the example. However, I can't simply ask for reading cell B7. because in some sheets the table would shift up or down, left or right. Is there a way similar to Vlookup used in excel that can be used to find the location of "E" and take the correspond value "5" and import it to matlab?
0 comentarios
Respuesta aceptada
fred ssemwogerere
el 6 de Feb. de 2020
Hello, this can do nicely;
% Am assuming your file,"myfile.xlsx" (arbitrary name), is an excel file, and in the current (working) folder of Matlab
optns=detectImportOptions('myfile.xlsx');
% Assuming your variable names are: "Var1" and "Var2"
optns.SelectedVariableNames={'Var1','Var2'};
optns=setvartype(optns,{'Var1','Var2'},{'categorical','double'});
% Read file as table with the set options
T=readtable('myfile.xlsx',optns);
% Import value corresponding to only "E" (value along the same row as "E", but in different column)
valImport=T.Var2(T.Var1=="E");
12 comentarios
fred ssemwogerere
el 8 de Feb. de 2020
Hello, both files seem to have a similar format, so to extract your required information, this can do nicely for both files;
optns=detectImportOptions('3.xls','Sheet',1);
% Override the default "Data Range" set by "optns", to that covering the start of your data.
optns.DataRange='A16:P26'; % Range of your data based on both files supplied ("1.xls" and"3.xls")
% Remember Matlab will read each column from the spreadsheet, merged or unmerged, with different column headers such as; "Var1","Var2",......
% To extract data from the first column and the column of percentages (%), set their respective variable names as "Var1" and "Var3"
optns.SelectedVariableNames={'Var1','Var3'};
% Set read data-type of the columns
optns=setvartype(optns,{'Var1','Var3'},{'categorical','double'});
% Set the handling of missing or empty rows by filling with a scalar "0"
optns.MissingRule = 'fill';
optns = setvaropts(optns,{'Var1','Var3'},'FillValue',0);
% Read file as table with the set options
T=readtable('3.xls',optns);
% Import value corresponding to only "MILLINGS"
valImport=T.Var3(T.Var1=="MILLINGS");
Más respuestas (1)
Guillaume
el 6 de Feb. de 2020
Editada: Guillaume
el 6 de Feb. de 2020
readtable should be capable of finding the data range on its own, so use that to import your data.
Then for merging, simply use join possibly specifying the join variable. join will automatically match the row containing E in one table with the row containing E in the other, regardless of where it is. So the code would go something like this:
mergedtable = [];
for sheettomerge = ?:?
currenttable = readtable(??);
if isempty(mergedtable)
mergedtable = currenttable; %first file, no merge yet
else
mergedtable = join(mergedtable, currenttable, 'Keys', 'Var1');
end
end
0 comentarios
Ver también
Categorías
Más información sobre Spreadsheets en Help Center y File Exchange.
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!