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

1 次查看（过去 30 天）
Peter Alexander Mott
回答： Amit 2020-3-4
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 个评论
显示 1更早的评论

请先登录，再进行评论。

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

采纳的回答

Amit
Amit 2020-3-4
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...!

更多回答（0 个）

类别

在 Help Center 和 File Exchange 中查找有关 Large Files and Big Data 的更多信息

标签

Community Treasure Hunt

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

Start Hunting!

Translated by