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

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.

[dpb]

Simplest is just read the sheet and do the lookup in memory. If the data are structured regularly readtable would be good choice as then you've got the facilities of table for selecting data.

Answer 1

[Bob Thompson]

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.

[dpb]

The above has a potential snag in that the size of the numbers and text cell arrays aren't necessarily the same as alldata so the locations returned in the latter don't correlate to the same cell location in either of the former. The column will tend to be much more reliable than the row.

[Bob Thompson]

That is true, they may not match up in size, but that's why I ended up drawing the data out of the alldata array itself, and completely ignoring the numbers and text arrays. To my limited knowledge, these two arrays must be created in order to get the alldata array, which is the only reason I have them.

[dpb]

That's what the tilde is for...

[~,~,alldata] = xlsread(excelfile);

But if the data in the spreadsheet are regular, readtable takes care of it all for you much more easily by returning the actual variable type and the column heading as the table variable name.

I strongly recommend you explore its capabilities in lieu of the above workaround -- until it that was the only choice but for most(*) things there's now a better way.

(*) For complex spreadsheets may well be forced to still parse the spreadsheet data itself; for one such example see another recent thread along similar lines <Rearrange cell content by groups> that while it started out with a different question related to munging on the raw cell data as returned for a given case evolved into a rather interesting discussion of reading complex spreadsheets.

[Bob Thompson]

"That's what the tilde is for... [~,~,alldata] = xlsread(excelfile);

But if the data in the spreadsheet are regular, readtable takes care of it all for you much more easily by returning the actual variable type and the column heading as the table variable name.

I strongly recommend you explore its capabilities in lieu of the above workaround -- until it that was the only choice but for most(*) things there's now a better way. "

Tips like this are why I keep coming back to these forums. Thanks.

[dpb]

No problem...we're here for that purpose besides just enjoying ML... :)