- Read the Excel File: Use 'readtable' or 'xlsread' to read the Excel file into MATLAB.
- Extract 'Mainvariables' and Variables: Identify and extract the 'Mainvariables' and their corresponding variables.
- Group Variables under 'Mainvariables': Create a structure or cell array to group the variables under their respective 'Mainvariables'.
- Create New Excel Files: Use 'writetable' to write each group of data into separate sheets in new Excel files.
Assign one or multiple variables to one category
2 Ansichten (letzte 30 Tage)
Ältere Kommentare anzeigen
Hey everyone,
I have the task to reorder data that looks as the attached excel files. My task is to write a new excel file for every table sheet. The new excel file shall contain several sheets which are named to our "Mainvariables", which can be found in the second line. Within the sheet all Variables of the given Mainvariable and the measured data have to be listed (the data can be found in line 3 and the measured data is listed below). My approach was to first read in the "Mainvariables" from line 2. Then, importing the Variables with the data and afterwards assigning the Variables and the data to their "Mainvariables". I also managed to do that for dataset 1 (see appendix), however I used the empty column as an argument to separate the groups from each other. Looking at dataset 2 I noticed that there are no more empty columns, meaning that I will need to find a way to group all Variables in line 3 to the "Mainvariable" that is above or "closest to the left".
Further, sometimes the datasets i received show the Mainvariables as "string" and sometimes as "double". Both shall be read by the readtable function, but only those considered that contain a value.
Thanks in advance!
0 Kommentare
Antworten (1)
Shubh
am 18 Jan. 2024
Hi Magnus,
To handle your task of reordering and exporting data from the provided Excel files into new Excel files with specific formatting, we can follow these steps:
Here's how you can implement this in MATLAB:
function process_and_save_excel(inputFilePath, outputFilePath)
% Read the entire dataset, skipping the first row
[num, txt, raw] = xlsread(inputFilePath, 'Sheet1', 'A2:Z1000'); % Adjust range as needed
raw = raw(1:size(num, 1), :); % Truncate to the size of numeric data
% Identify 'Mainvariables' (from the second row) and variables (from the third row)
mainVars = txt(1,:);
vars = txt(2,:);
% Initialize a structure for grouped data
groupedData = struct();
% Process and group the data
currentMainVar = '';
for i = 1:length(mainVars)
if ~isnan(mainVars{i})
currentMainVar = mainVars{i};
end
if ~isempty(currentMainVar) && ~isnan(vars{i})
groupedData.(currentMainVar).(vars{i}) = num(:,i);
end
end
% Create and write to a new Excel file
sheetNames = fieldnames(groupedData);
for i = 1:length(sheetNames)
mainVar = sheetNames{i};
T = struct2table(groupedData.(mainVar));
writetable(T, outputFilePath, 'Sheet', mainVar);
end
end
% Example usage
inputFile1 = 'path_to_your_dataset1.xlsx'; % Replace with your file path
outputFile1 = 'Processed_Dataset1.xlsx';
process_and_save_excel(inputFile1, outputFile1);
inputFile2 = 'path_to_your_dataset2.xlsx'; % Replace with your file path
outputFile2 = 'Processed_Dataset2.xlsx';
process_and_save_excel(inputFile2, outputFile2);
Replace 'path_to_your_dataset1.xlsx' and 'path_to_your_dataset2.xlsx' with the actual paths to your Excel files. This code will create new Excel files (Processed_Dataset1.xlsx and Processed_Dataset2.xlsx) with separate sheets for each "Mainvariable".
Make sure to adjust the range 'A2:Z1000' in xlsread according to the actual size of your data. The script assumes that the numeric data starts from the third row and the 'Mainvariables' and variables are in the second and third rows, respectively.
Hope this helps!
Siehe auch
Kategorien
Mehr zu Data Import from MATLAB finden Sie in Help Center und File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!