Reading multiple sheets from an excel file

I have an excel spreadsheet and the data is seperated into a different sheet for each year but I want to have all of the years worth of data in one table so I was wondering what the best way to approach this is? Right now my code only gets the data from 2007 and thats it.

opt=detectImportOptions('BC1 2007-2020.xlsx');
opt.VariableTypes(3)={'double'};
BC1=readtable('BC1 2007-2020.xlsx',opt);
BC1.Date=datetime(BC1{:,1},'InputFormat','dd/MM/yy HH:mm:ss');
BC1=table2timetable(BC1);

plot(BC1.Date,BC1.WaterLevel_Meters);

Answer 1

[dpb]

You'll just have to loop over the sheets; use sheetnames to return the list of sheets in the given file and iterate over it--presuming the sheets are in the desired order in the workbook, they'll be returned in chronological order; if not you can always sort() it first.

Presuming the sheets are also in the same format, you can use the same import object for each, changing only the sheet name/number although naming the sheet explicitly in the readtable call overrides the import options value.

I'll note that you could also incorporate the datetime variable type and input format into the options object thus saving the explicit conversion code.

Since it's not known a priori (I presume) how many records are in each sheet, the simplest coding is to simply dynamically catentate the new table onto the existing one...

fn='BC1 2007-2020.xlsx';        % using a fully-qualified filename here would be good practice
tBC=[];
opt=detectImportOptions(fn);
% wanted/need options code/fixup here...
shts=sheetnames(fn);
for i=1:numel(shts)
  tBC=[tBC;readtable(fn,opt,'Sheet',shts(i))];
end
...

Do the conversion to timetable from all only once at the end as well as the time conversion if choose to not use the import options route for import for it...

While dynamic allocation is not the most elegant solution, it is by far the simplest and as long as the sizes and numbers of the sheets to be imported isn't too large, the overhead will be not too painful -- particularly if only doing the reading once or a few times and mostly using the resulting data. Of course, once read, one can save the table as a .mat file and retrieve the whole thing very quickly; only when new data are added would it be necessary to reread the raw file again.

[JMG]

Thank you for your help! Its giving me an error when I test out your method:

Error using matlab.io.ImportOptions/readtable (line 661)
Unexpected order of inputs using import options. Use the syntax: readtable(filename, options, ...)

Error in Practice (line 15)
  tBC=[tBC;readtable('BC1 2007-2020.xlsx','Sheet',shts(i),opt)];
  
  

So I moved the opt before the 'Sheet' and it works except the third sheet has a different variable name so it wont concate. Any suggestions to work around this? This is the error:

  Error using Practice (line 14)
All tables being vertically concatenated must have the same variable names.

[Walter Roberson]

tBC=[tBC;readtable('BC1 2007-2020.xlsx','sheet',shts{i},opt)];

[dpb]

I always want to do that...think I'd eventually remember named argument parameters come last. Sorry.

tBC=[tBC;readtable(fn,opt,'Sheet',shts(i))];

As the other error says, you can't concatenate tables which have different variable names -- and that also will break the assumption of being able to use the same opt object on all sheets without modification.

How to deal with this depends upon whether they're really different variables on different sheets or just a naming convention mismatch?

By far the simplest solution on the MATLAB importing side would be to fix the spreadsheet so that all sheets do have the same data in the same columns including columns for missing data if there are differences from year to year.

Also NB: that names must be unique in each sheet and number of columns the same for all sheets...the catenation assumes that they belong together in the same order--if that's not true, then have to solve that as well.

[JMG]

Thank you I actually just found the document with the data all formatted together so I no longer need to do any of this! I appreciate your help though and now I know for future reference.

[dpb]

I downloaded and looked at the file --

IMO, you should fix the broken file -- the date,time are sometimes in column A, sometimes A+B and there's other meta-data in the water level header line in some not in others but either way, it'll break if read the names.

That you can work around by not reading the variable names at all and just using the column numbers/generic Var1...VarN names then set the final variable names in the table properties struct.

You can write code to read either date, time or datetime as one string, but I still think it would be better to fix these data and then ensure create future in the same format going forward instead.

But, the other way would be to use detectImportOptions in turn on each file inside the loop and see how many variables it returns and then handle the different cases.

That also means have to convert to datetime for each file in turn using the appropriate code to convert the datetime and add the duration for those with both (or join the two strings) first. I didn't notice if the time/date formats were the same across sheets or not -- that could be another sticky wicket if somebody did those differently, too.

Answer 2

[Image Analyst]

Try this:

baseFileName = 'BC1 2007-2020.xlsx';        % using a fully-qualified filename here would be good practice
tblAllSheetData = [];
% opt=detectImportOptions(baseFileName);
% wanted/need options code/fixup here...
sheetNames = sheetnames(baseFileName)
for k = 1 : numel(sheetNames)
	thisTable = readtable(baseFileName,'Sheet',sheetNames{k});
	fprintf('Read a table of %d rows and %d columns from a sheet names %s.\n',...
		height(thisTable), width(thisTable), sheetNames{k});
	% Stitch onto the end
	tblAllSheetData = vertcat(tblAllSheetData, thisTable);
end

but it will fail when it gets to sheet 2008, which has 5 columns instead of only 3. How do you want to handle the situation where some sheets don't have the same number of columns as other sheets? Getting to the root of the problem . . . why DON'T all sheets have the same format?

[Image Analyst]

@JMG, are you going to answer these questions?

[dpb]

https://www.mathworks.com/matlabcentral/answers/885144-reading-multiple-sheets-from-an-excel-file#comment_1654434 comment above to my response of essentially same content that he located an existing file containing all the data so the Q? became moot...