Why is it not possible to increase the excel readtable range?

1 view (last 30 days)
Dursman Mchabe
Dursman Mchabe on 26 Sep 2021
Commented: dpb on 28 Sep 2021
Hi Everyone,
Why is it not possible to increase the excel read table range in my case?
I have an excel spreadsheet (attached) from which I am extracting data.
My challange is, I can only work with fixed range (A1: AG88),
When I try adding a column on the excel file, and try, A1:AH88, I get the following error message:
Error using MathsWorksQuestion (line 81)
Using '.Variables' with more than one level of subscripting is not supported. Use brace subscripting directly on the
table instead.
The code I am using is
%% Initialize and read data from Excel
clc
clear
clf
RawData = readtable('ExperimentalDatabase.xlsx','Range','A1:AH88');
%% Extract experiments data
% Create a structure called "Chips"
% ==> fields in the structure will be given "fields_names"
% ==> field names will corresponds to extraction pressure
% ==> Namely:
% Chips.Entrapped_0 corresponds to free liquor
% Chips.Entrapped_1 corresponds to an extraction pressure of 2.5 tons
% Chips.Entrapped_2 corresponds to an extraction pressure of 5 tons
% Chips.Entrapped_3 corresponds to an extraction pressure of 8 tons
field_names = {'FreeLiquor','Entrapped_1','Entrapped_2','Entrapped_3'};
extraction_pressure = [0, 2.5, 5 , 8];
% Create a cycle for chips results at different extraction pressures
for l = 1:length(extraction_pressure)
% index raw data for every particle size classified as chips
index = find(strcmp(RawData.ParticleSize,'Chips')... % for every extraction pressure
.*(RawData.EntrappedLiquorSqueezing_tons_ == extraction_pressure(l)));
% Create a "Temp" for all the chips' subset data of interest
Temp = RawData(index,:);
% Organize data the best way it will suite your needs, e.g.:
% Time : write "cooking time" into a vector
Chips.(field_names{l}).Time = Temp.CookingTime_min_;
% Constant data : Save data that stays constant in a "Description" structure
Chips.(field_names{1}).Description = struct('T', Temp.CookingTemperature__C_(1), ...
'P', Temp.CookingPressure_bar_(1), ...
'WoodMass', Temp.WoodMass_g_(1), ...
'SteamMass', Temp.SteamMass_g_(1), ...
'Na2SO3ODWBasis', Temp.Na2SO3ODWBasis___(1), ...
'Na2CO3ODWBasis', Temp.Na2CO3ODWBasis___(1),...
'LiquidToWoodRatio',Temp.LiquidToWoodRatio(1),...
'InitialNa2SO3_g_L_',Temp.InitialNa2SO3_g_L_(1));
% Redundent variables in Temp: Remove them
Temp = removevars(Temp,{'Date',...
'Run_',...
'ParticleSize',...
'WoodMass_g_',...
'SteamMass_g_',...
'Na2SO3ODWBasis___',...
'Na2CO3ODWBasis___', ...
'LiquidToWoodRatio',...
'CookingTime_min_', ...
'CookingTemperature__C_',...
'CookingPressure_bar_', ...
'EntrappedLiquorSqueezing_tons_',...
'ID_'});
% Field names of interest: save them in "fields"
fields = fieldnames(Temp);
% Less important field names: remove them
fields([1, 21, 22, 23]) = [];
% Find main experiments, i.e. when Repeat_ = 0
Rep0 = find(Temp.Repeat_ == 0);
% Experiments associated with specific times
for i = 1: length(Rep0)
% Find all repeats associated with specific time
k = find(Chips.(field_names{l}).Time == Chips.(field_names{l}).Time(Rep0(i)));
% Create fields
for j = 1:length(fields)
% Cell array containing individual values
Chips.(field_names{l}).Repeats.(fields{j}){i} = Temp.(fields{j})(k);
try
% Try a mean
Chips.(field_names{l}).Mean.(fields{j})(i) = mean(Temp.(fields{j})(k));
% Try a standard deviation
Chips.(field_names{l}).STD.(fields{j})(i) = std(Temp.(fields{j})(k));
catch
end
end
end
% Remove redunandant time points
Chips.(field_names{l}).Time = unique(Chips.(field_names{l}).Time);
end
,
  2 Comments
dpb
dpb on 26 Sep 2021
I can't reproduce the symptom here w/ R2020b --
>> tExp=readtable('ExperimentalDatabase.xlsx','Sheet','Main','Range','A1:AI88');
>> whos tExp
Name Size Bytes Class Attributes
tExp 87x35 66562 table
>> [head(tExp); tail(tExp)]
ans =
16×35 table
Date Run_ Repeat_ ParticleSize WoodMass_g_ SteamMass_g_ Na2SO3ODWBasis___ Na2CO3ODWBasis___ LiquidToWoodRatio CookingTime_min_ CookingTemperature__C_ CookingPressure_bar_ EntrappedLiquorSqueezing_tons_ ID_ TDS_mg_g_ LiquorPH LiquorAceticAcid_g_L_ LiquorGlucose_g_L_ LiquorXylose_g_L_ SampleMass_mg_ UV_Absorbance LiquorLignin_g_L_ InitialNa2SO3_g_L_ VolumeOfKIO3_mL__Titrated1 VolumeOfKIO3_mL__Titrated2 VolumeOfKIO3_mL__Titrated3 AverageVolumeOfKIO3_mL__Titrated StdDev LiquorResidualNa2SO3_g_L_ PulpAshContent_w_w__ PulpExtractives_w_w__ PulpLignin_w_w__ PulpCellulose_w_w__ PulpHemicellulose_w_w__ PulpHemicellulose2_w_w__
___________________ _____ _______ ____________ ___________ ____________ _________________ _________________ _________________ ________________ ______________________ ____________________ ______________________________ _____ _________ ________ _____________________ __________________ _________________ ______________ _____________ _________________ __________________ __________________________ __________________________ __________________________ ________________________________ __________ _________________________ ____________________ _____________________ ________________ ___________________ _______________________ ________________________
{'04/06/2021' } 6.00 0.00 {'Chips'} 50.00 50.00 16.00 1.60 3.00 0.00 172.00 9.00 0.00 1.00 0.86 7.27 0.56 0.14 0.21 184.50 0.15 4.06 53.33 10.60 7.20 9.50 9.10 {0×0 char} 114.70 NaN NaN NaN NaN NaN NaN
{'04/06/2021' } 6.00 0.00 {'Chips'} 50.00 50.00 16.00 1.60 3.00 0.00 172.00 9.00 2.50 2.00 0.95 7.16 0.89 0.20 0.33 117.30 0.14 5.97 53.33 7.10 7.30 5.50 6.63 {0×0 char} 83.61 NaN NaN NaN NaN NaN NaN
{'04/06/2021' } 6.00 0.00 {'Chips'} 50.00 50.00 16.00 1.60 3.00 0.00 172.00 9.00 5.00 3.00 0.80 7.02 0.86 0.16 0.28 127.30 0.85 33.38 53.33 5.50 6.70 6.10 6.10 {0×0 char} 76.89 NaN NaN NaN NaN NaN NaN
{'04/06/2021' } 6.00 0.00 {'Chips'} 50.00 50.00 16.00 1.60 3.00 0.00 172.00 9.00 8.00 4.00 1.05 NaN 0.95 NaN 0.21 74.30 0.09 6.06 53.33 NaN NaN NaN NaN {'NA' } NaN NaN NaN NaN NaN NaN NaN
{'07/06/2021' } 7.00 0.00 {'Chips'} 50.00 50.00 16.00 1.60 3.00 5.00 172.00 9.00 0.00 5.00 0.78 7.14 0.77 0.19 0.39 150.40 0.16 5.45 53.33 5.40 6.70 6.90 6.33 {0×0 char} 79.83 NaN NaN NaN NaN NaN NaN
{'07/06/2021' } 7.00 0.00 {'Chips'} 50.00 50.00 16.00 1.60 3.00 5.00 172.00 9.00 2.50 6.00 0.98 7.17 0.87 0.21 0.52 53.00 0.02 2.08 53.33 5.90 5.80 6.30 6.00 {0×0 char} 75.63 NaN NaN NaN NaN NaN NaN
{'07/06/2021' } 7.00 0.00 {'Chips'} 50.00 50.00 16.00 1.60 3.00 5.00 172.00 9.00 5.00 7.00 0.76 NaN 1.04 0.17 0.49 26.90 0.01 0.93 53.33 NaN NaN NaN NaN {'NA' } NaN NaN NaN NaN NaN NaN NaN
{'07/06/2021' } 7.00 0.00 {'Chips'} 50.00 50.00 16.00 1.60 3.00 5.00 172.00 9.00 8.00 8.00 0.85 6.97 1.02 0.11 0.43 82.50 0.06 3.39 53.33 NaN NaN NaN NaN {'NA' } NaN NaN NaN NaN NaN NaN NaN
{'Pottinger, 2020'} NaN 2.00 {'Fines'} 30.00 0.00 16.00 1.60 8.33 30.00 179.00 9.00 0.00 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN {0×0 char} NaN NaN NaN NaN NaN NaN NaN
{'22/06/2021' } 27.00 0.00 {'Fines'} 30.00 0.00 16.00 1.60 8.33 40.00 179.00 9.00 0.00 61.00 0.79 8.06 0.38 NaN 0.27 251.00 0.16 3.21 19.21 5.20 5.60 5.30 5.37 {0×0 char} 67.64 6.45 21.97 23.68 47.68 11.94 11.94
{'Pottinger, 2020'} NaN 0.00 {'Fines'} 30.00 0.00 16.00 1.60 8.33 60.00 179.00 9.00 0.00 NaN NaN NaN NaN NaN NaN NaN NaN NaN 19.21 NaN NaN NaN NaN {0×0 char} NaN NaN NaN NaN NaN NaN NaN
{'Pottinger, 2020'} NaN 1.00 {'Fines'} 30.00 0.00 16.00 1.60 8.33 60.00 179.00 9.00 0.00 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN {0×0 char} NaN NaN NaN NaN NaN NaN NaN
{'Pottinger, 2020'} NaN 2.00 {'Fines'} 30.00 0.00 16.00 1.60 8.33 60.00 179.00 9.00 0.00 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN {0×0 char} NaN NaN NaN NaN NaN NaN NaN
{'Pottinger, 2020'} NaN 0.00 {'Fines'} 30.00 0.00 16.00 1.60 8.33 100.00 179.00 9.00 0.00 NaN NaN NaN NaN NaN NaN NaN NaN NaN 19.21 NaN NaN NaN NaN {0×0 char} NaN NaN NaN NaN NaN NaN NaN
{'Pottinger, 2020'} NaN 1.00 {'Fines'} 30.00 0.00 16.00 1.60 8.33 100.00 179.00 9.00 0.00 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN {0×0 char} NaN NaN NaN NaN NaN NaN NaN
{'Pottinger, 2020'} NaN 2.00 {'Fines'} 30.00 0.00 16.00 1.60 8.33 100.00 179.00 9.00 0.00 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN {0×0 char} NaN NaN NaN NaN NaN NaN NaN
>>

Sign in to comment.

Sign in to answer this question.

Accepted Answer

dpb
dpb on 27 Sep 2021
The error message doesn't have anything to do with the Q? that was posed as written and looked at above, however...it's got to do with the line
Chips.(field_names{l}).Repeats.(fields{j}){i} = Temp.(fields{j})(k);
It fails for j=20 because the name 'Variables' which is the value of the last element of both the field_names and fields arrays doesn't exist in either the Chips struct nor the Temp table.
  2 Comments
Dursman Mchabe
Dursman Mchabe on 27 Sep 2021
Hi dpb,
Thanks a lot for the answer.
How can I increase j to be >20?
Regards
D
dpb
dpb on 28 Sep 2021
Well, the code is so convoluted I've not tried to figure out the logic in what it is it's trying to do, specifically, but the two arrays containing field names of the stuct and the the table will have to have element values that are actually in the two.
The problem isn't the value of the index, it's the content of the arrays. Reorder the two arrays as they currently are to put the string 'Variables' first and it'll fail on the first pass instead of the last with the same error message...so,you can have j be as large as you wish but IFF (if and only if) all the variables are extant in both.

Sign in to comment.

More Answers (0)

Categories

Find more on Tables in Help Center and File Exchange

Tags

Community Treasure Hunt

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

Start Hunting!