Save variable from multiple excel spreadsheets

The excel-file is divided in different worksheets containing the same type of information. Each worksheet contains results from samples and has five columns containing different results from an anaylsis and 100.000 rows representing the number of cycles. I want to work with this data and I wanto to have one variable for each result instead of ten for each sample. The columns are named "maxStrain", "minStrain", "aveStrain", "maxVoid" and "minVoid". I want to create variables with these names that have the specific data from each spreadsheet. The import-function only allows me to import one sheet at a time and save it to one variable.

Is there a loop in the live editor I can implement, so that it goes from worksheet 12 to 21 importing the information from "B2:B100001"?

Example:

-variable maxStrain saves the data from the column "B2:B100001" from the worksheets 12 to 21.

-variable minStrain saves the data from the column "C2:C100001" from the worksheets 12 to 21 and so on with the other variables.

Thank you in advance!

Answer 1

[Karan Kannoujiya]

Hi,

You can use below code to save the data for diffrent sheet--->

%define other varibles also like this
maxStrain=[];
minStrain=[]
tempmaxStrain=[];
tempminStrain=[];

locationOfExcel="C:\Users\Karan\Downloads\Book1.xlsx"; %location of your excel sheet
[~,sheets,~] = xlsfinfo(locationOfExcel); %saving all sheet name in sheets variable 
for i = 1:length(sheets) %run from 12 to 21 in your case
    [~,data,~] = xlsread(locationOfExcel,sheets{i});
    data(1,:)=[]; %removing the column header
    tempmaxStrain=data(:,1);  %for saving first column maxStrain
    maxStrain=[maxStrain;tempmaxStrain];   %combining each sheet information for differnt column
    tempminStrain=data(:,2);%for saving second column minStrain
    minStrain=[ minStrain;tempminStrain]; %combining each sheet information for differnt column
end

you can do for others column variables( "aveStrain", "maxVoid" and "minVoid") also.

I hope it helps.

[vlernest]

Thank you for the code! It works, but it doesnt save the data I have in the different sheets. It's a dataset with 100.000 rows of data for each variable (in this case column). In total I want "aveStrain", "maxStrain" etc. to be a 100.000x10 matrix.

Thank you so much though!

[Karan Kannoujiya]

https://in.mathworks.com/help/matlab/import_export/read-spreadsheet-data-into-table.html

You can go through this link to read the excel sheet using readtable function instead of using xlsread function. I,m not sure why it is not saving the data.Can you please send the code you has written and also the screenshot of your excelfile data of any sheet ?