Import and Read data with several sheets

Hello Everyone,

I am trying to use the line of code below to read my matrix data from an excel file. However, I get stuck from the the 2nd line of code, which takes forever to run and not complete. I am wondering if I am doing something wrong. Can some help with advice?

[~,sheet_name]=xlsfinfo('Gas.xlsx');

for k=1:numel(sheet_name)

data{k}=readmatrix('Gas.xlsx','Sheet',sheet_name{k},'Range','A2:IP176');

end

[Stephen23]

Avoid deprecated XLSINFO. Use SHEETNAMES:

https://www.mathworks.com/help/matlab/ref/sheetnames.html

Answer 1

[dpb]

Hard to diagnose w/o the file, but try

sheets=sheetnames('Gas.xlsx');
for k=1:numel(sheets)
  data{k}=readmatrix('Gas.xlsx','Sheet',sheets(k),'NumHeaderLines',1);
end

Answer 2

[Mathy]

Hi Oluwasogo,

The following code snippet will assist you in reading data from multiple Excel sheets:

Assumptions:

• The desired data is stacked vertically.
• The number of columns is consistent across all sheets.

% Importing Data from excel across multiple sheets and filenames.

filename = 'file.xlsx';

[~,sheet_name]=xlsfinfo(filename)

data_all = [];

for k=1:numel(sheet_name)

[data,TXT,RAW]=xlsread(filename,sheet_name{k})

% stack data vertically

data_all = [data_all ; data];

end

For more details, you can refer to this MATLAB Answer: https://www.mathworks.com/matlabcentral/answers/707188-import-data-with-several-sheets

Hope this helps!

[Stephen23]

"Using 'readtable' can be more efficient for reading data from Excel files."

Really? Do you have a reference for this claim?

Answer 3

[Ashutosh Thakur]

Hello Oluwasogo,

I understand that you are trying to read an Excel sheet and the performance issue might be due to the xlsinfo and readmatrix functions. Additionally, to get the names of the sheets in an Excel file, the sheetnames function is recommended instead of xlsinfo.

You can refer to the following documentation link for more information on sheetnames:

https://www.mathworks.com/help/matlab/ref/sheetnames.html.

Please note that the sheetnames function will return a string array instead of a cell array. Additionally, you can preallocate the cell array such as data for better performance. The following lines of code can be used as a reference:

sheet_names = sheetnames('Filename.xlsx');
numSheets = numel(sheet_names)
data = cell(1, numSheets); % Preallocate cell array
for k = 1:numSheets
data{k} = readmatrix('Filename.xlsx', 'Sheet', sheet_names(k), 'Range', 'A2:IP176');
end

I hope this helps you!