MERGE EXCEL SHEETS INTO ONE MATLAB DATA FILE
16 views (last 30 days)
Show older comments
SAPTORSHEE KANTO
on 27 Aug 2024
Commented: Shishir Reddy
on 28 Aug 2024
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
0 Comments
Accepted Answer
Piyush Kumar
on 27 Aug 2024
Yes, it is possible to combine your survey data from multiple Excel sheets into a single MATLAB .mat file. You can follow these steps -
3 Comments
Piyush Kumar
on 27 Aug 2024
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');
More Answers (1)
Shishir Reddy
on 27 Aug 2024
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
I hope this is helpful.
2 Comments
Shishir Reddy
on 28 Aug 2024
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.
- Read each sheet into a table.
- Concatenate the tables.
- 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');
See Also
Categories
Find more on Spreadsheets in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!