MERGE EXCEL SHEETS INTO ONE MATLAB DATA FILE

7 次查看（过去 30 天）
SAPTORSHEE KANTO
SAPTORSHEE KANTO 2024-8-27
评论： Shishir Reddy 2024-8-28
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

请先登录，再回答此问题。

采纳的回答

Piyush Kumar
Piyush Kumar 2024-8-27
@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 个评论
显示 1更早的评论
Piyush Kumar
Piyush Kumar 2024-8-27
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');

请先登录，再进行评论。

更多回答（1 个）

Shishir Reddy
Shishir Reddy 2024-8-27
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 个评论
SAPTORSHEE KANTO
SAPTORSHEE KANTO 2024-8-27
编辑：SAPTORSHEE KANTO 2024-8-27
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
Shishir Reddy 2024-8-28
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');

请先登录，再进行评论。

类别

在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息

标签

产品


版本

R2024a

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by