MATLAB: How to search an Excel file for a certain column, then save that entire column

columnsexcelseperate

I am working on a project where columns could be out of order, so I cannot depend on saving data from these columns as they will change. The first row of each column is the name of the data that the column contains. I want to search for the name of the column then save the data from the column.

Best Answer

The table option may work, I personally don't have much experience reading tables. I tend to read excel files using the more common xlsread() command. For finding a specific column based on column title I have used.

[numbers,text,alldata] = xlsread(excelfile);
[headerRow,headerCol] = find(cellfun(@(x)~isempty(strfind(x,'header')),alldata));

By creating the xlsread array results you allow xlsread to input all of the data, instead of just the number results, this allows it to maintain any cells with text.

The array of the second command allows you to find the specific row and column for the matching string, 'header'. The find command is what actually brings up the row and column, while the cellfun command allows all cells within the specified array (datain) to be checked for the condition. If I remember correctly, strfind only works for an array of strings, so the isempty check and cellfun command are necessary to make this work with the mixed data in the datain array.

Related Solutions

MATLAB: How Find last row in excel

If you're comfortable with using ActiveX, here is function:

%-----------------------------------------------------------------------------
% Returns the next empty cell in column after row 1.  Basically it puts the active cell in row 1
% and types control-(down arrow) to put you in the last row.  Then it add 1 to get to the next available row.
function nextRow = GoToNextRowInColumn(Excel, column)
  try
    % Make a reference to the very last cell in this column.
    cellReference = sprintf('%s1048576', column);
    Excel.Range(cellReference).Select;
    currentCell = Excel.Selection;
    bottomCell = currentCell.End(3); % Control-up arrow.  We should be in row 1 now.
    % Well we're kind of in that row but not really until we select it.
    bottomRow = bottomCell.Row;
    cellReference = sprintf('%s%d', column, bottomRow);
    Excel.Range(cellReference).Select;
    bottomCell = Excel.Selection;
    bottomRow = bottomCell.Row;  % This should be 1
    % If this cell is empty, then it's the next row.
    % If this cell has something in it, then the next row is one row below it.
    cellContents = Excel.ActiveCell.Value;  % Get cell contents - the value (number of string that's in it).
    % If the cell is empty, cellContents will be a NaN.
    if isnan(cellContents)
  % Row 1 is empty.  Next row should be 1.
  nextRow = bottomRow;  % Don't add 1 since it was empty (the top row already).
    else
  % Row 1 is not empty.  Next row should be row 2.
  nextRow = bottomRow + 1;  % Will add 1 to get row 1 as the next row.
    end
  catch ME
    errorMessage = sprintf('Error in function GoToNextRowInColumn.\n\nError Message:\n%s', ME.message);
    fprintf('%s\n', errorMessage);
    WarnUser(errorMessage);
  end
  return; % from LeftAlignSheet
end % of GoToNextRowInColumn

MATLAB: Write vectors in cell array to excel

Is this what you're trying to do?

%Making a sample cell array with variable-length vectors
Data = cell(50, 2);
for j = 1:50
    L = randi(10);
    Data(j, :) = {rand(1, L) rand(1, L)};
end
%Saving to excel format
MaxLen = max(cellfun('length', Data(:, 1)));
AllData = cell(MaxLen, numel(Data));
for j = 1:size(Data, 1)
    L = numel(Data{j, 1});
    AllData(1:L, 2*j-1) = num2cell(Data{j, 1});
    AllData(1:L, 2*j) = num2cell(Data{j, 2});
end
xlswrite('test.xlsx', AllData)

Best Answer

Related Solutions

MATLAB: How Find last row in excel

MATLAB: Write vectors in cell array to excel

Related Question