130 views (last 30 days)
Show older comments
SAPTORSHEE KANTO on 27 Aug 2024 at 7:22
Commented: Shishir Reddy on 28 Aug 2024 at 4:45
Accepted Answer: Piyush Kumar
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 Show -2 older commentsHide -2 older comments
Show -2 older commentsHide -2 older comments
Sign in to comment.
Sign in to answer this question.
Accepted Answer
Piyush Kumar on 27 Aug 2024 at 8:52
@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
3 Comments Show 1 older commentHide 1 older comment
Show 1 older commentHide 1 older comment
Piyush Kumar on 27 Aug 2024 at 10:50
Direct link to this comment
https://in.mathworks.com/matlabcentral/answers/2148081-merge-excel-sheets-into-one-matlab-data-file#comment_3247264
Open in MATLAB Online
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 on 27 Aug 2024 at 10:57
Direct link to this comment
https://in.mathworks.com/matlabcentral/answers/2148081-merge-excel-sheets-into-one-matlab-data-file#comment_3247266
Open in MATLAB Online
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 on 27 Aug 2024 at 12:10
Direct link to this comment
https://in.mathworks.com/matlabcentral/answers/2148081-merge-excel-sheets-into-one-matlab-data-file#comment_3247311
THANK YOU VERY MUCH INDEED.. THIS WORKS PERFECTLY
Sign in to comment.
More Answers (1)
Shishir Reddy on 27 Aug 2024 at 9:40
Open in MATLAB Online
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.
2 Comments Show NoneHide None
Show NoneHide None
SAPTORSHEE KANTO on 27 Aug 2024 at 10:17
Direct link to this comment
https://in.mathworks.com/matlabcentral/answers/2148081-merge-excel-sheets-into-one-matlab-data-file#comment_3247246
Edited: SAPTORSHEE KANTO on 27 Aug 2024 at 10:21
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 on 28 Aug 2024 at 4:45
Direct link to this comment
https://in.mathworks.com/matlabcentral/answers/2148081-merge-excel-sheets-into-one-matlab-data-file#comment_3247819
Open in MATLAB Online
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');
Sign in to comment.
Sign in to answer this question.
See Also
Categories
MATLABData Import and AnalysisData Import and ExportStandard File FormatsSpreadsheets
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!
An Error Occurred
Unable to complete the action because of changes made to the page. Reload the page to see its updated state.
Select a Web Site
Choose a web site to get translated content where available and see local events and offers. Based on your location, we recommend that you select: .
You can also select a web site from the following list
Americas
- América Latina (Español)
- Canada (English)
- United States (English)
Europe
- Belgium (English)
- Denmark (English)
- Deutschland (Deutsch)
- España (Español)
- Finland (English)
- France (Français)
- Ireland (English)
- Italia (Italiano)
- Luxembourg (English)
- Netherlands (English)
- Norway (English)
- Österreich (Deutsch)
- Portugal (English)
- Sweden (English)
- Switzerland
- Deutsch
- English
- Français
- United Kingdom(English)
Asia Pacific
- Australia (English)
- India (English)
- New Zealand (English)
- 中国
- 日本Japanese (日本語)
- 한국Korean (한국어)
Contact your local office
