Importing Microsoft Excel data to MATLAB as a single variable from multiple spreadsheets that contains various data ranges

1 visualizzazione (ultimi 30 giorni)
I have 6 spreadsheets containing station locations from which I want to generate a single variable in a cell array called "Station" (it will be a 251x1 cell array). The names of the 6 sheets are seen below in 'opts.Sheet' and the ranges that I want from each of the spreadsheets are specified below in 'ranges'. I'm wondering how I can loop through each of the spreadsheets and extract the data for the ranges I have specified? The below code was generated from the MATLAB import tool and I'm simply trying to modify it. If there is a more functional way of solving this problem any suggestions would be appreciated.
%% Setup the Import Options
opts = spreadsheetImportOptions("NumVariables", 1);
% Specify sheet
opts.Sheet = "CTD 2014"; %"CTD 2015"; "CTD 2016"; "CTD 2017"; "CTD 2018"; "CTD 2019";
% Specify column names and types
opts.VariableNames = "Station";
opts.VariableTypes = "char";
opts = setvaropts(opts, 1, "WhitespaceRule", "preserve");
opts = setvaropts(opts, 1, "EmptyFieldRule", "auto");
% Import the data
tbl = table;
% CTD 2014 CTD 2015 CTD 2016 CTD 2017 ..... etc
ranges = ["D5:D7" "D9:D25" "D31:D39"]; %["D5:D31" "D33:D51" "D53:D53" "D55:D58"]; ["D6:D25" "D27:D36" "D38:D46"]; ["D5:D11" "D13:D14" "D16:D25" "D27:D71"]; ["D5:D12" "D14:D19" "D21:D43" "D46:D47"]; ["D5:D8" "D10:D15" "D29:D47"];
for idx = 1:length(ranges)
opts.DataRange = ranges(idx);
tb = readtable("SBE 19plus CTD Profiling Database.xlsx", opts, "UseExcel", false);
tbl = [tbl; tb]; %#ok<AGROW>
end
%% Convert to output type
Station = tbl.Station;
%% Clear temporary variables
clear idx opts ranges tb tbl
  3 Commenti

Accedi per commentare.

Risposta accettata

Amit
Amit il 4 Mar 2020
Hello Peter, Try the below solution :
station = [];
SheetName = {'CTD 2014'; 'CTD 2015'; 'CTD 2016'; 'CTD 2017'; 'CTD 2018'; 'CTD 2019'};
FileName = 'SBE 19plus CTD Profiling Database.xlsx';
for sheet_idx = 1:length(SheetName)
opts = detectImportOptions(FileName,'Sheet',SheetName{sheet_idx});
[station_raw] = readtable(FileName,opts);
station_raw = station_raw.Station;
station_raw(strcmp(station_raw,'-')) = [];
station_raw(strcmp(station_raw,'?')) = [];
station_raw = station_raw(~cellfun(@isempty, station_raw));
station = [station; station_raw];
end
I hope this helps...!

Più risposte (0)

Community Treasure Hunt

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

Start Hunting!

Translated by