CODEWITHSUNDEEP
excelmatlabio
Jason Thapa
Jason Thapa

Reputation: 123

How to extract excel column and import it to MATLAB?

Hello I have an excel file with multiple columns up to "CD". My code words perfectly for excel files with 26 columns but after that it doesn't work. 

[ia ib] = ismember(header, {item});
letter = find(ia)+'A'-1;
cell  = fprintf('%c:%c', letter, letter);
out = xlsread('filename', cell);

This code works until I get to Z:Z. When I get to AA, AB, AC,... it won't work. How do I extract the AA, CD, BG columns?

Upvotes: 0

Views: 829

Answers (1)

rayryeng
rayryeng

Reputation: 104484

It doesn't work because you are assuming that your letter for the header is only one character as indicated by:

letter = find(ia) + 'A' - 1;

What are you doing is essentially building the ASCII code for a capital letter between A to Z. This will obviously fail if you are trying to find a header with more than one letter. What you'll need to do is build a dictionary of all possible characters of AA to ZZ, then you can use the output of find(ia) on this dictionary if we exceed the column Z in your Excel sheet to extract out the right sequence of characters you need, then finally use this sequence of characters to index into your Excel sheet.

Referencing this question, I'm going to take Rody Oldenhuis's answer. Therefore, construct this dictionary of all possible two characters:

x = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
y = unique(nchoosek(repmat(x, 1,26), 2), 'rows');

y will be a N x 2 character matrix where each row is a unique permutation of two letters from A-Z (so AA, AB, etc.). The way the code is written, it should maintain the exact ordering like how Excel does it for columns that go beyond Z, so AA, AB, AC, ... AZ, BA, BB, BC, ... BZ, ..., ZX, ZY, ZZ. Next, we need to see whether or not the found index is between 1 and 26. If it is, you can use your previous code. If it isn't, then we'll do what we outlined above. Note that I will have to subtract this found index by 26 so I can index into this character array that we created. Assuming that header has all unique entries, we can do:

[ia ib] = ismember(header, {item});
index = find(ia, 1);
if index <= 26 %// Check if we are within columns A - Z
    letter = index + 'A' - 1;
else %// If not, we are at a column that is beyond Z.
    x = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
    y = unique(nchoosek(repmat(x, 1,26), 2), 'rows');
    index = index - 26; %// Subtract by 26 to reference into character array
    letter = y(index,:);
end
cell  = sprintf('%s:%s', letter, letter);
out = xlsread('filename', cell);

Note that I changed your fprintf call to sprintf as you desire to store the string representation of which cells you want to access. fprintf (in your case) will print to the screen, which is probably not what you want. Also, I've changed the variable cell to ce as cell is an actual function in MATLAB.

Also note that I've changed the %c formatting string to %s as the header may consist of more than one character.

Upvotes: 2

Related Questions