Unexpected "Could not extract sheet"

[4er4er4er]

Unpack COLtest.zip and execute include COLtest.run which reads two spreadsheet files in a loop using amplxl. The first file is read successfully (though with some invalid subscripts discarded), but the second one is rejected with a Could not extract sheet message:

ampl: include COLtest.run
Error at _cmdno 5 executing "display" command
(file COLtest.run, line 15, offset 271):
error processing param COL:
	12 invalid subscripts discarded:
	COL[1,'TA',5]
	COL[2,'TA',5]
	COL[3,'TA',5]
	and 9 more.
COL [*,TA,*]
:       1         2         3         4       :=
1    2.14805   2.45592   3.23197   4.16625
2    2.39733   2.70252   3.4543    4.32747
3    2.64476   2.9441    3.66518   4.47475
4    2.88774   3.17831   3.8634    4.60845
5    3.12388   3.40311   4.04813   4.72913
6    3.35106   3.6168    4.21894   4.83749
7    3.5675    3.81807   4.37571   4.93433
8    3.7718    4.00602   4.51863   5.02052
9    3.96296   4.18012   4.64813   5.09695
10   4.14034   4.34018   4.76482   5.1645
11   4.30371   4.48632   4.86943   5.22402
12   4.45311   4.61892   4.9628    5.27634
13   4.58885    .         .         .
14   4.71148    .         .         .
15   4.82168    .         .         .
;

Error at _cmdno 7 executing "read_table" command
(file COLtest.run, line 14, offset 251):

	Error reading table COL[2] with table handler amplxl:
	Could not extract sheet
ampl: 

The two spreadsheet files have the same amount of data of the same kind, but strangely, they are not the same size. When the second one is read into Excel and then saved, the error no longer occurs. However there is now another strange behavior, as the invalid subscripts from the second file are read into param COL rather than being discarded:

ampl: include coltest.run
Error at _cmdno 5 executing "display" command
(file coltest.run, line 15, offset 271):
error processing param COL:
	12 invalid subscripts discarded:
	COL[1,'TA',5]
	COL[2,'TA',5]
	COL[3,'TA',5]
	and 9 more.
COL [*,TA,*]
:       1         2         3         4       :=
1    2.14805   2.45592   3.23197   4.16625
2    2.39733   2.70252   3.4543    4.32747
3    2.64476   2.9441    3.66518   4.47475
4    2.88774   3.17831   3.8634    4.60845
5    3.12388   3.40311   4.04813   4.72913
6    3.35106   3.6168    4.21894   4.83749
7    3.5675    3.81807   4.37571   4.93433
8    3.7718    4.00602   4.51863   5.02052
9    3.96296   4.18012   4.64813   5.09695
10   4.14034   4.34018   4.76482   5.1645
11   4.30371   4.48632   4.86943   5.22402
12   4.45311   4.61892   4.9628    5.27634
13   4.58885    .         .         .
14   4.71148    .         .         .
15   4.82168    .         .         .
;

COL [*,TA,*]
:       1         2         3         4         5       :=
1    2.07589   2.38393   3.16567   4.11694   5.0406
2    2.25301   2.56015   3.32684   4.23581   5.09418
3    2.42997   2.73457   3.48266   4.34759   5.14326
4    2.60581   2.90628   3.63261   4.45234   5.18816
5    2.7796    3.07445   3.77624   4.55021   5.22916
6    2.95047   3.2383    3.91321   4.64136   5.26657
7    3.11758   3.39715   4.0433    4.72602   5.30066
8    3.28019   3.55041   4.16636   4.80446   5.33169
9    3.43764   3.69758   4.28233   4.87694   5.35991
10   3.58936   3.83827   4.39123   4.94379   5.38555
11   3.73488   3.9722    4.49315   5.0053    5.40883
12   3.87383   4.09916   4.58825   5.0618    5.42996
13   4.00595    .         .         .         .
14   4.13108    .         .         .         .
15   4.24912    .         .         .         .
;

ampl: 

[nfbvs]

There seem to be some inconsistencies in this file, like incomplete xml nodes

<c r="D63">
	<f>-0.084*(42.9+(124-42.9)/(1+(((EXP(0.001283*A2:A64*(124-42.9)))*((124-(-11.8*IF(C2:C64=1,1.89,IF(C2:C64=2,2.2,C2:C64))+109.6))))/((-11.8*IF(C2:C64=1,1.89,IF(C2:C64=2,2.2,C2:C64))+109.6)-42.9))))+ 9.2326</f>
	<v/>
</c>

node the value node v is not opened, only closed.
The compression ratio also seems to be different from the one stated in the file but I'm still checking.

[mingodadampl]

The <v/> tag seems to be a valid empty tag "open/close" at once. The close tage is </v>.

[mingodadampl]

All values for column "D" are formulas (except the header) so they have an empty value <v/> and when reading a second time we are trying to write again values already set by the first read [DAYD,CROP,QUAL], COL .

[mingodadampl]

My bad on last comment about empty values, opening the files as zip files and inspecting the sheet1.xml I can see that the values are there too (besides the formula).

But the problem on the second loop pass seems corrected flagged as error, although the message can confuse the user.

[mingodadampl]

Although my logic seems correct about the possible failure on the second loop pass, when I added a reset data COL; inside the loop the error remain the same.

load amplxl.dll;

set DAYD = 1..18;
set CROP = {"TA"};
set QUAL = 1..4;
param COL {DAYD,CROP,QUAL};

param nCOL integer > 0;
table COL {c in 1..nCOL} IN "amplxl" ("COLtest" & c & ".xlsx") : [DAYD,CROP,QUAL], COL;

let nCOL := 2;
for {c in 1..nCOL}{
	reset data COL;
	read table COL[c];
	display COL;
}

[nfbvs]

Thank you for the notes!
I think there are 3 unrelated issues:

1 - Could not extract sheet: the main one, fixed in 002fdc0, amplxl was not prepared to handle targets defined by full paths in workbook.xml.rels.

2 - The fourth column in the COLtest2.xlsx file has formulas but the value of the cells is empty (except for the header). When we open the spreadsheet with a manager (like Excel) it updates the spreadsheet. However if we read with amplxl the values are not read because they are empty. This is also the reason why the file size increases after being saved with Excel.

3 - The indexed table is not discarding invalid subscripts on the second pass. This is independent from the Table Handler. I have a minimal example with .tab files but it does not seem to be possible to attach it here.

[mingodadampl]

Here is a more concise script that shows the problem in ampl table handling:

param UB := 4;

table test OUT: {i in 1..UB} -> [i ~ K], (i * 10) ~ V;
write table test;

set K := 1..(UB/2);
param V{K};

redeclare table test IN: [K], V;

for {K}
{
	read table test;
	display K;
	display V;
}
# note K is not changed, but V (that's indexed by K) is
display card(K);

Output:

set K := 1 2;

Error at _cmdno 5 executing "display" command
(file coltab2.ampl, line 15, offset 198):
error processing param V:
	2 invalid subscripts discarded:
	V[3]
	V[4]
V [*] :=
1  10
2  20
;

set K := 1 2;

V [*] :=
1  10
2  20
3  30
4  40
;

card(K) = 2

[mingodadampl]

Looking through ampl code in debug mode several times could not find where the data read from the table is assigned to the parameter.
Leaving it for later.

[mingodadampl]

Today I advanced a bit more on this issue, in table.c::assign_outargs function the logic that check when new elements are added to the current convar->val.rval, it can somehow fixed by adding this:

	do {
		if (index) {
			is_old = 1;
			k = index->in(out_arg->subscr,tva);
			if (k < 0) {
				is_old = 0;
                                ++nn; //DAD possible fix for logic error with is_old
				if (param) {
					if ((k = index->psize) > index->hlen
					 || k == 1)
						val_extend(convar, adj);
					k = index->psize;
					index->addmem(out_arg->subscr);
					}
				else {
					if (convar->declared_index->in(out_arg->subscr,tva) == -1)
						badsub(tva, convar, out_arg->subscr);
					k = addnow(out_arg->subscr,0,convar,valp,adj,tva);
					}
				}
			}

But that is not enough there is another problem in let.c::assign_outargs and it's call tree that I could not properly identify yet.