Read multiple sheets from single Excel file and extract a specific set of columns from it.

Problem Statement:

I have a .xlsx file in the target folder with multiple sheets.

1) I want to read read sheet_1.

2) Extract data from columns 1 an 2 from sheet_1 and store it in an array for further processing.

read next two columns from sheet_1 and store it in an array for further processing.

repeat till I have read all the columns.

3) Analyze and plot the data.

4) Move to Sheet_2.

5) Repeat the process untill I have read and analyzed every single sheet of the .csv file.

Can some please help me figure out the solution code to this problem.

Thank You!

[Ameer Hamza]

By default, a CSV file cannot have multiple sheets. CSV file, by definition, is one big table created by separating columns using commas. Can you share your CSV file?

[Sagar Jagina]

Sorry, my mistake, it is an Excel file.

[Ameer Hamza]

Can you share an example excel file?

[Sagar Jagina]

Sorry Cannot do that!

[Ameer Hamza]

You can create an excel file with random data, but in the same format as the original file. Knowing the format of the data makes it easy to suggest a solution.

[Sagar Jagina]

Here it is. Thank you!

[Ameer Hamza]

There are 13 columns in your file. You asked to group the columns in 2. What to do with the 13th column?

[Sagar Jagina]

Leave the first column(time) and start from second column.

For example 2-3, 4-5,....12-13.

But I was planning if it can be more generic so that I can modify as I as need.

Thank you!

Answer 1

[Ameer Hamza]

Try the following code

num_sheets = 3;
data = cell(1,num_sheets);
for i=1:num_sheets
    data{i} = readmatrix('Sample.xlsx', 'Sheet', i);
end

part_data = cell(1,num_sheets); % partitioned data in 2 columns
for i=1:num_sheets
    M = data{i}(:,2:end);
    num_part = size(M,2)/2;
    part_data{i} = mat2cell(data{i}(:,2:end), size(M,1), 2*ones(1,num_part));
end

% plotting
for i=1:num_sheets
    figure;
    tiledlayout('flow');
    data = part_data{i}; % plotting data for i-th sheet
    for j=1:numel(data)
        nexttile
        plot(data{j}(:,1), data{j}(:,2));
    end
end

[Sagar Jagina]

Thank You!

[Ameer Hamza]

I am glad to be of help.

[Sagar Jagina]

Just one additon to this, I belive for this program to work, the program file and excel sfile should be in same folder, but what if both of them are in different folder and I still want to do the same task!

Can you please tell me how to read the excel file from different folder?

Thank You!

[Ameer Hamza]

You can specify the complete path of the Excel file

data{i} = readmatrix('C:\path_to_folder\Sample.xlsx', 'Sheet', i);

[Sagar Jagina]

Thank You So Much:)

[Amit]

@Ameer Hamza Thanks, this help me also. However one problem I face when I trying to plot all the figure on same axis. I try "hold on" before/after the last for loop but its not working. I am very much thankful for your help.