Reading, sorting and writing from one excel worksheet to another

Problem: I have a home data file where the information I need is located in two different tabs in different columns, not sequential, and need to reorganize it on another worksheet. The problem is that I'm using xlsread and xlswite multiple times for each individual set of columns I want written on the destination. The information is populating as I want it, but when I look at the workspace, it only shows the result of the last iteration, not the entire table I put together using the xlsread and xlswrite.

Goal: I need to find a way, maybe a for loop or something, to have the information recorded and updated as a table and not individual variables so when I run the script it updates the entire table and not have the risk of just updating one variable.

HELP!!!! :'(

[dpb]

Not much specific anybody here can do with just a general description and especially without showing us what the input/outputs are...

BUT, the biggest suggestion I would have would be:

1. Dump xlsread, xlswrite and use readtable, writetable instead -- much more flexible.
2. Read everything you need and create the output table in memory in a new table variable from the input pieces, then write the whole thing out...don't call the input/output routines (whether old-style or new) more than absolutely need to; the all open/close the file every time...

[Omar O'Farrill]

@dpb for example: I have 2 excel files, one of them contains the information I need and the other one is where I want to write that information to. The information needed is split between two worksheets within excel file 1. So it reads something like this:

[~,~,data1x] = xlsread('filename','sheet','range')
xlswrite('filename',data1x,'sheet',range')

[~,~,data1y] = xlsread('filename','sheet','range')
xlswrite('filename',data1y,'sheet','range')

[~,~,data2x] = xlsread('filename','sheet','range')
xlswrite('filename',data2x,'sheet',range')

[~,~,data2y] = xlsread('filename','sheet','range')
xlswrite('filename',data2y,'sheet',range')

And goes all the way up to 8. The information I need is under the same column on each worksheet, so pretty much half of it on one sheet, half in the other. Data1x is the first half and data1y is the second half. The columns are not sequential, for example: columns go from A-Z and I only need columns T, P, Q, N (in that order). What would be the best way to approach this? I think it would be best with a for loop, but I don't know how to build it. I'm sort of a newbie.

Answer 1

[dpb]

You're not listening...yes, you can undoubtedly make use of a loop here, but there's no evidence of having tried...

columns=reshape('TPQN'-'@',2,[]);                   % lookup columns wanted from sheet by column
d=dir('yourExcelFileWildcardExpression*.xls*');     % match the wanted input file names
for i=1:numel(d)                                    % iterate over those files
  y=[];                                             % accumlator for each output
  for s=1:2                                         % iterate over the two sheets (or use names in array)  
    x=readmatrix(fullfile(d(i).name),'Sheet',s);    % read each sheet in turn
    y=[y x(:,columns(:,s))];                        % build the output from two sheets
  end
  tOut=table(t,'VariableNames',{'A','B'});
  writetable(tOut,'yourOutputFileName.xlsx')
end

Define the variable names for the output files from an external array or read them from the input sheet by using <readtable> and returning the variable names as suggested earlier...in that case, then you catenate the specific variables from the input table instead of pasting two arrays together...

Again, without something specific to look at, the above is just "air code", salt to suit...

[dpb]

Oh, to demo...it generally takes a while for newcomers to realize that character variables are just numbers in disguise...illustrate the column manipulations above.

x=[1:6].*ones(4,6)                  % illustrate indexing with small example can tell what get...

x = 4×6

1 2 3 4 5 6 1 2 3 4 5 6 1 2 3 4 5 6 1 2 3 4 5 6

columns=reshape('BDCA'-'@',2,[]);   % use indices inside the array size, not in numeric order
x(:,columns(:,1))                   % see which columns we return for first column of indices

ans = 4×2

2 4 2 4 2 4 2 4

which illustrates we got the second ('B') and fourth ('D') columns from the array that we expected/wanted...