MERGE EXCEL SHEETS INTO ONE MATLAB DATA FILE

Dear All,

I have Survey data for six years each year containing 26 variables and more than 400 thousand entries for each variable. Is it possible to join the data year by year into a single MATLAB mat file from the EXCEL file. The data for each year in the Excel file is on a different sheet.

Any help will be appreciated.

Regards

Answer 1

[Piyush Kumar]

@SAPTORSHEE KANTO,

Yes, it is possible to combine your survey data from multiple Excel sheets into a single MATLAB .mat file. You can follow these steps -

• Read data from each sheet one by one using readtable function.
• Combine the data from each year to get a sigle table or array.
• Save the final table to a ".mat" file using save function

[Piyush Kumar]

You can refer the following code -

excelFileName = '<excel_file_name>.xlsx';
combinedData = table();

for sheet = 1:6
    % Construct the sheet name
    sheetName = ['<sheet_name>', num2str(sheet)]; 
    % If the sheet name is Year1, Year2, Year3..., replace <sheet_name> with "Year"
    
    % Pass the sheetname as argument to the readtable function
    yearlyData = readtable(excelFileName, 'Sheet', sheetName);
    
    % Append the yearly data to the combined data table
    combinedData = [combinedData; yearlyData];
end

save('combined_survey_data.mat', 'combinedData');

Replace the "<excel_file_name>" and "<sheet_name>" with the actual values. Let me know if you face any issue with the approach.

[Piyush Kumar]

You can use sheetnames function too.

excelFileName = '<excel_file_name>.xlsx';
% Get the names of all sheets in the Excel file
sheets = sheetnames(excelFileName);
combinedData = table();

for i = 1:length(sheets)
    % Get the current sheet name
    sheetName = sheets{i};
    
    % Read data from the current sheet
    yearlyData = readtable(excelFileName, 'Sheet', sheetName);
    
    % Append the yearly data to the combined data table
    combinedData = [combinedData; yearlyData];
end

save('combined_survey_data.mat', 'combinedData');

[SAPTORSHEE KANTO]

THANK YOU VERY MUCH INDEED.. THIS WORKS PERFECTLY

Answer 2

[Shishir Reddy]

Hi Saptorshee

It is possible to combine data from multiple Excel sheets into a single .mat file. This can be achieved by using MATLAB’s built-in functions to read data from each sheet and then save it in a .mat file.

Kindly refer to the following script to understand how it is implemented in MATLAB.

% Define the Excel file name
excelFileName = 'your_survey_data.xlsx';

% Define the sheet names (or numbers) corresponding to each year
sheetNames = {'Year1', 'Year2', 'Year3', 'Year4', 'Year5', 'Year6'};

% Initialize a structure to store the data
surveyData = struct();

% Loop over each sheet to read and store the data
for i = 1:length(sheetNames) 
    % Read data from the current sheet
    sheetData = readtable(excelFileName, 'Sheet', i);
    % Store the data in the structure with a field name for each year
    fieldName = ['Year', num2str(i)];
    surveyData.(fieldName) = sheetData;
end

% Save the combined data into a .mat file
save('combinedSurveyData.mat', 'surveyData');

For more information regarding ‘readtable’ and ‘save’ functions, kindly refer the following documentation links

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

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

I hope this is helpful.

[SAPTORSHEE KANTO]

Thank you very much... but somehow the mat file being created is not a single file but merge of six files i.e., 1x1 struct file with six fields. Each field is a year file. Is it possible to combine them into one. My variable names are same for each datasheet in excel.

Regards.

[Shishir Reddy]

If the variable names are the same across all sheets and you want to combine them into a single data structure (e.g., a table), you can concatenate the tables vertically. Here's how you can modify the script to achieve that.

1. Read each sheet into a table.
2. Concatenate the tables.
3. Save the combined table to a .mat file.

Here's an updated script:

% Define the Excel file name
excelFileName = 'survey_data.xlsx';

% Define the sheet names or indices
sheetNames = {'Year1', 'Year2', 'Year3', 'Year4', 'Year5', 'Year6'};

% Initialize an empty table to store combined data
combinedData = [];

% Loop through each sheet and read the data
for i = 1:length(sheetNames)
    % Read the data from each sheet
    data = readtable(excelFileName, 'Sheet', sheetNames{i});
    
    % Concatenate the data vertically
    combinedData = [combinedData; data];
end

% Save the combined data to a .mat file
save('combined_survey_data.mat', 'combinedData');