read special cell in excel file

Question

MOzhdeh Salimi 2021-9-8

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1449049-read-special-cell-in-excel-file

评论： Mathieu NOE 2021-9-8

Hi all

This is my code. The first column in my file data is txt. Now I want when raw=2 equal raw=3 to write my station name that put in raw=1.

please help me.

a=('sabet.xlsx');
[num,txt,raw]=xlsread('a');
c1=raw(:,1);
c2=raw(:,2);
c3=raw(:,3);
for b=1:1:numel(c2)
    for c=1:1:numel(c3)
        for d=1:1:numel(c1)
   
if b == c
    
   
end
    end
end
    
end

2 个评论
显示无隐藏无

Mathieu NOE 2021-9-8

hello

it would help if you could share the excel file as well

tx

MOzhdeh Salimi 2021-9-8

sabet.txt

Hi Dear

I attached my file as txt file

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Mathieu NOE 2021-9-8

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1449049-read-special-cell-in-excel-file#answer_783369

在 MATLAB Online 中打开

hello again

so here a small code to extract the lines where the 2 numbers in row 2 and row 3 are below a given threshold. FYI I could not find any line where the two numbers are strictly equal but there are a few ones very close. So you can decide about the threshold (limit).

here we have 3 selected names with threshold = 10 ;

% 'Anar' 40839 40830

% 'Arak' 40769 40761

% 'Baneh' 99280 99281

code is as follows : (you can expand it to save the result in an excel sheet for example or in a text file)

clc
clearvars
T = readtable('sabet.txt');
C = table2cell(T);
[m,n] = size(C);
% init
C2 = cell2num(C(:,2));
C3 = cell2num(C(:,3));
index = [];
delta = [];
threshold = 10;
% main loop
for ci = 1:m
   if  ~isnan(C2(ci)) && ~isnan(C3(ci))
        tmp = abs(C2(ci) - C3(ci)); % compute abs delta between two values (raw 2 and raw3 )
        if tmp <= threshold % keep line index of raws with delta below threshold
            index = [index; ci]; % agregate index
            delta = [delta; tmp];% agregate delta value
        end
    end
end
% plot(index,delta);
celldisp(C(index,1));
% 'Anar'	40839	40830
% 'Arak'	40769	40761
% 'Baneh'	99280	99281

8 个评论
显示 6更早的评论隐藏 6更早的评论

Mathieu NOE 2021-9-8

在 MATLAB Online 中打开

ok so this is now fixed using the FEX submission that does the same thing as cell2num

cell2num - File Exchange - MATLAB Central (mathworks.com)

I simply renamed it differently so it will not conflict with the native cell2num I have;

% updated code is : 
clc
clearvars
T = readtable('sabet.txt');
C = table2cell(T);
[m,n] = size(C);
% init
% C2 = cell2num(C(:,2)); % release above R2018 ? 
% C3 = cell2num(C(:,3)); % release above R2018 ? 
C2 = another_cell2num(C(:,2)); % release below R2018
C3 = another_cell2num(C(:,3)); % release below R2018 
index = [];
delta = [];
threshold = 10;
% main loop
for ci = 1:m
   if  ~isnan(C2(ci)) && ~isnan(C3(ci))
        tmp = abs(C2(ci) - C3(ci)); % compute abs delta between two values (raw 2 and raw3 )
        if tmp <= threshold % keep line index of raws with delta below threshold
            index = [index; ci]; % agregate index
            delta = [delta; tmp];% agregate delta value
        end
    end
end
% plot(index,delta);
celldisp(C(index,1));
% 'Anar'	40839	40830
% 'Arak'	40769	40761
% 'Baneh'	99280	99281
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
function [outputmat]=another_cell2num(inputcell)
% Function to convert an all numeric cell array to a double precision array
% ********************************************
% Usage: outputmatrix=cell2num(inputcellarray)
% ********************************************
% Output matrix will have the same dimensions as the input cell array
% Non-numeric cell contest will become NaN outputs in outputmat
% This function only works for 1-2 dimensional cell arrays
if ~iscell(inputcell), error('Input cell array is not.'); end
	outputmat=zeros(size(inputcell));
	
	for c=1:size(inputcell,2)
      for r=1:size(inputcell,1)
          
	% original code      
	%     if isnumeric(inputcell{r,c})
	%       outputmat(r,c)=inputcell{r,c};
	%     else
	%       outputmat(r,c)=NaN;
	%     end
	
	%Works great if you use addition by C Schwalm to the if statement. If statement within the code should now look like :
		if isnumeric(inputcell{r,c})
		    outputmat(r,c)=inputcell{r,c};
		elseif isnumeric(str2num(char(inputcell{r,c}))) %addition
		    outputmat(r,c)=str2num(char(inputcell{r,c})); %addition
		else
		    outputmat(r,c)=NaN;
		end
	
      end  
	end
end

MOzhdeh Salimi 2021-9-8

Dear Mathieu Let me explain my question again. I have excell file that includes 3 columns. First column is station name, second column is station code and third column is some station code that choose from second column. Now I want to find station name from first column that the codes equal to third column.

Mathieu NOE 2021-9-8

在 MATLAB Online 中打开

sorry , I misunderstood the question before. now my brain has re-started to work !

check this one !

clc
clearvars
T = readtable('sabet.txt');
C = table2cell(T);
[m,n] = size(C);
% init
% C2 = cell2num(C(:,2)); % release above R2018 ? 
% C3 = cell2num(C(:,3)); % release above R2018 ? 
C2 = another_cell2num(C(:,2)); % release below R2018
C3 = another_cell2num(C(:,3)); % release below R2018 
index1 = [];
index2 = [];
delta = [];
threshold = 10;
% main loop
for ci = 1:m
   if  ~isnan(C2(ci)) && ~isnan(C3(ci))
        %tmp = abs(C2(ci) - C3(ci)); % compute abs delta between two values (raw 2 and raw3 )
        [val , ind] = min(abs(C2(ci) - C3));
        if val < 1 % keep line index of raws with delta below threshold
            index1 = [index1; ci]; % agregate index
            index2 = [index2; ind]; % agregate index
            delta = [delta; val];% agregate delta value
        end
    end
end
% plot(index,delta);
% display in command window : 
% first station code / corresponding (second) col value / second station code / corresponding (third) col value / 
[C(index1,1) C(index1,2) C(index2,1) C(index2,3)]
% ans =
% 
%   18×4 cell array
% 
%     {''Abali''             }    {[40755]}    {''Bukan''            }    {[40755]}
%     {''Abarkuh''           }    {[99539]}    {''Arak (Airport)''   }    {[99539]}
%     {''Ahar''              }    {[40704]}    {''Darrehshahr''      }    {[40704]}
%     {''Arak''              }    {[40769]}    {''Bushehr (Airport)''}    {[40769]}
%     {''Arsanjan''          }    {[99579]}    {''Dorud''            }    {[99579]}
%     {''Bam''               }    {[40854]}    {''Dehagh''           }    {[40854]}
%     {''Bandarabbas''       }    {[40875]}    {''Bushehr (Coastal)''}    {[40875]}
%     {''Bandar-E- Mahshahr''}    {[40832]}    {''Bandar-E-Deylam''  }    {[40832]}
%     {''Bandar-E-Khamir''   }    {[99674]}    {''Ahvaz''            }    {[99674]}
%     {''Bandar-E-Lengeh''   }    {[40883]}    {''Bandar-E-Dayyer''  }    {[40883]}
%     {''Biyarjomand''       }    {[40742]}    {''Eqlid''            }    {[40742]}
%     {''Damghan''           }    {[40761]}    {''Arak''             }    {[40761]}
%     {''Daregaz''           }    {[40807]}    {''Ajabshir''         }    {[40807]}
%     {''Dehloran''          }    {[40796]}    {''Abumusa Island''   }    {[40796]}
%     {''Dorud''             }    {[99444]}    {''Chahbahar''        }    {[99444]}
%     {''Dowshan Tappeh''    }    {[40753]}    {''Daran''            }    {[40753]}
%     {''Emamzadeh Jafar''   }    {[99565]}    {''Arsanjan''         }    {[99565]}
%     {''Eyvan''             }    {[99433]}    {''Bandar-E-Lengeh''  }    {[99433]}
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
function [outputmat]=another_cell2num(inputcell)
% Function to convert an all numeric cell array to a double precision array
% ********************************************
% Usage: outputmatrix=cell2num(inputcellarray)
% ********************************************
% Output matrix will have the same dimensions as the input cell array
% Non-numeric cell contest will become NaN outputs in outputmat
% This function only works for 1-2 dimensional cell arrays
if ~iscell(inputcell), error('Input cell array is not.'); end
	outputmat=zeros(size(inputcell));
	
	for c=1:size(inputcell,2)
      for r=1:size(inputcell,1)
          
	% original code      
	%     if isnumeric(inputcell{r,c})
	%       outputmat(r,c)=inputcell{r,c};
	%     else
	%       outputmat(r,c)=NaN;
	%     end
	
	%Works great if you use addition by C Schwalm to the if statement. If statement within the code should now look like :
		if isnumeric(inputcell{r,c})
		    outputmat(r,c)=inputcell{r,c};
		elseif isnumeric(str2num(char(inputcell{r,c}))) %addition
		    outputmat(r,c)=str2num(char(inputcell{r,c})); %addition
		else
		    outputmat(r,c)=NaN;
		end
	
      end  
	end
end

请先登录，再进行评论。

read special cell in excel file

2 个评论
显示无隐藏无

采纳的回答

8 个评论
显示 6更早的评论隐藏 6更早的评论

更多回答（0 个）

另请参阅

类别

标签

Community Treasure Hunt

read special cell in excel file

2 个评论 显示 无隐藏 无

采纳的回答

8 个评论 显示 6更早的评论隐藏 6更早的评论

更多回答（0 个）

另请参阅

类别

标签

Community Treasure Hunt

2 个评论
显示无隐藏无

8 个评论
显示 6更早的评论隐藏 6更早的评论