MATLAB: How to read only specific columns in from an Excel file, not consecutive columns

xlsread columns vertcat

I have a set of Excel files that are ~ 17,000 rows by 6,000 columns. Needless to say, that's kind of tough to work with. I would like to take (for example), columns 1, 2, 5, 77, 124,255, 334, 1000, etc- you get the idea. Seems like the xlsread function only accepts Excel cell notation ('A1:B5') as input. Is there any slick way to read in just some columns from my massive spreadsheets?
As a follow up, I have a loop to write data for each file, but what I really want is the data from each new file vertically concatenated onto the existing file. Can I just use vertcat somehow, or since it's a loop, do I need to use Data=[Data;newData] or something like that?

Best Answer

I enclose a first version of a function I just wrote. This will read a number of columns from a large Excel file. Since it opens the Excel file only once, it is significantly faster than repeated calls to xlsread, reading one column at a time.
No error checks so far. A crash may leave an Excel process running. End it using the Windows tast manager
 function data = read_excel_columns(filename,sheet,columns,firstrow,lastrow)
% Read selected columns from large Excel sheet using ActiveXServer
%  filename:  Seems that you have to use the full path to the Excel file
%  sheet   :  e.g. 'Sheet1'
%  columns :  array of column mumbers, e.g [17,341,784]
%  firstrow, lastrow:  The first and last rows to be read 
%  data:   : array of numerical values
%
% Are Mjaavatten, 2016-03-14
    nrows = lastrow-firstrow+1;
    ncols = length(columns);
    data = zeros(nrows,ncols);
    first = num2str(firstrow);
    last = num2str(lastrow);
    hExcel = actxserver('Excel.Application');
    hWorkbook = hExcel.Workbooks.Open(filename);
    hWorksheet = hWorkbook.Sheets.Item(sheet);
    for i = 1:ncols
        col = col2str(columns(i));
        Range = [col,first,':',col,last];
        RangeObj = hWorksheet.Range(Range);
        data(:,i) = cell2mat(RangeObj.value);
    end
    release(hWorksheet)
    release(hWorkbook)
    release(hExcel)
end
 function colname = col2str(n)
% Translate Excel column number to Column characters 
    s = '';
    while n > 0
        s = [s,char(mod(n-1,26)+65)];
        n = floor((n-1)/26);
    end
    colname = deblank(fliplr(s));
end
Related Question