Reading a spreadsheet with amplxl generates some unexpected output:

MINOS 5.51:  amplxl: using alias: Foods
      amplxl: using alias: Nutrients
      amplxl: using alias: Amounts
optimal solution found.
13 iterations, objective 118.0594032

The "amplxl: using alias: . . ." messages should be suppressed, except maybe in verbose mode. In general there shouldn't be any output from "read table" if the spreadsheet is read successfully.

When write table is asked to write to a spreadsheet file that does not exist, it creates the file. Then when Excel opens the newly created file for the first time, the display looks like this:

It appears that nothing has been written, until the user realizes that he's looking at an empty Sheet1 tab, while the values have been written to the Results tab.

Preferably, the file should be created without the Sheet1 tab. If there is no way to get rid of the Sheet1 tab, then it would still be an improvement to have the spreadsheet file open with the Results tab showing.

When amplxl writes a file like Buy.xlsx it also creates a file Buy.xlsx.amplbak which appears to be an empty spreadsheet file.

If a spreadsheet is open in Excel when "read table" is invoked, amplxl cryptically reports:

Error reading table dietFoods with table handler amplxl:
Cannot extract workbook.

Nicolau observes: As far as I know this is an Excel issue. Unless you open the file in read only mode Excel will lock it and prevent access from other applications. In Libreoffice we are able to read or even write with the file opened (must reload the file after).

Ideally the table handler could tell that it's trying read from a file that's open in Excel; then it could report something specific like "Cannot read workbook while it is open for writing in Excel." Otherwise the new spreadsheet handler could work like the current one, by adding "Is the file open in another application?" to the message. But if we take that approach, we should consider whether there are any other common "Cannot extract workbook" cases that our message should mention.

The attached script test5.txt, based on a user's example, produces a 123MB file MOD45.xlsx. When I try to open the file, I get this message,

and when I click "yes" I eventually get a message about "unreadable content" in /xl/worksheets/sheet6.xml:

The user reports that "read table" aborted with a related message,

Error writing table Pedidos1 with table handler amplxl:
Could not extract sheet xl/worksheets/sheet5.xml

but I did not see that. Anyhow it appears that the size of the spreadsheet file may be the cause of the problem, because I did not encounter errors with similar but smaller examples.

Same setup as #3 except the specified file Buy.xlsx does exist and contains a 1x5 range named Results. The values are written properly to the spreadsheet, but when the file is double-clicked to open it in Excel, the following warning appears:

When Yes is clicked, the following repair report appears:

After Closed is clicked, the spreadsheet works normally. It can be saved and later opened without trouble, but Excel prompts to save it with a different name.

The following example looks for costSheet in the spreadsheet file notfind.xlsx:

set PROD;
param cost {PROD};

table costTab IN "amplxl" "notfind.xlsx" "costSheet": [PROD], cost;
read table costTab;

But if costSheet does not exist, the following general error message is displayed:

	Error reading table costTab with table handler amplxl:
	could not find table

This confuses users because "table" refers both to the AMPL table and to a table of data in the spreadsheet. Instead, amplxl should identify the problem more specifically; for example, the error message could be

	could not find a range or sheet named costSheet

The same comments apply to

table costTab IN "amplxl" "notfind.xlsx": [PROD], cost;

when no range or sheet named costTab can be found.

A user wanted to read spreadsheet change.xlsx using these statements:

set Family;
set Unit; 
param Change {Family, Family, Unit}; 

table Family IN "amplxl" "change.xlsx": [Family] IN;
read table Family; 

table Unit IN "amplxl" "change.xlsx": [Unit] IN;
read table Unit;

table Change IN "amplxl" "change.xlsx": [Fam1, Fam2, Unit], Change; 
read table Change; 

But read table Change; gave this error:

	Error reading table Change with table handler amplxl:
	Could not find column Change in spreadsheet table header

The error occurs because the column heading Change has a trailing space. There are several ways we could consider dealing with this hard-to-spot error:

• Remove trailing spaces from headers.
• Include the trailing space in the error message (so that there would be two spaces after column Change in the above example).
• Modify the error message so that the trailing space is more obvious, for example by putting quotes around the column name (so that it would be column "Change " in the above example).

There's a similar issue with trailing spaces in non-numeric data. Typically they do not prevent the spreadsheet data from being read, but lead to an error like invalid subscript Change[1,1,'A '] discarded at some later point.

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: 

The spreadsheet BlankMissing.xlsx has a blank in the key column SET, in the row corresponding to cost value 5. This causes the set member Missing to be created:

ampl: reset;
ampl: set S;
ampl: param cost {S};
ampl: table Cost IN "amplxl" "BlankMissing.xlsx": S <- [SET], cost;
ampl: read table Cost;
ampl: display cost;
cost [*] :=
      A  1
      B  2
      C  3
      D  4
Missing  5
;

Eventually the user sees an error of some sort in processing the model, but it is hard to trace that error back to the cause. The amplxl interface could instead report an error immediately when there is a blank in a key column for the table being read.

In some situations where there is more than one blank in the key column, an error message does appear immeidately, but it is not specific:

	Error reading table Cost with table handler amplxl:
	Could not parse data

The example transp3.mod + transp3.dat from the AMPL book has the following definitions:

set ORIG;
set DEST;

set LINKS within {ORIG,DEST};
var Trans {LINKS} >= 0;

The data for LINKS is a subset of the origin-destination pairs. A 2D spreadsheet table of the Trans variables can be specified equivalently as

table Transp3 OUT "amplxl" "2D" "Transp3.xlsx": {(i,j) in LINKS} -> [ORIG,DEST], Trans[i,j];

or

table Transp3 OUT "amplxl" "2D" "Transp3.xlsx": [ORIG,DEST], Trans;

But write table Transp3; fails with this error:

	Error writing table Transp3 with table handler amplxl:
	Could not write 2D data

When "2D" is removed from either table statement, the corresponding 1D table is written correctly.

With the files attached in Missing.zip, include test.run aborts with this message:

Error reading table DW with table handler amplxl:
AddRows: duplicate subscript ['Missing','TA5X6','CH']

Change input1.xlsx to input0.xlsx and there's no error, although the spreadsheets look almost identical.

Use AMPL .dat format files, it is possible to read a subset of pairs and an associated parameter from the same table. For example, with

set NUTR;
set FOOD;
set LINKS within {NUTR, FOOD};
param amt {LINKS} >= 0;

and data

set NUTR := A B1 B2 C ;
set FOOD := BEEF CHK FISH HAM MCH MTL SPG TUR ;

param :LINKS: amt (tr):
           A    C   B1   B2 :=
   BEEF   60   20    .   15
   CHK     .    .   20   20
   FISH    .    .   15    .
   HAM    40   40   35    .
   MCH    15   35   15   15
   MTL    70   30   15   15
   SPG    25   50   25   15
   TUR    60   20   15    . ;

neither the values marked . or the corresponding members of LINKS are included in the data that is read:

ampl: display LINKS, amt;
set LINKS :=
(A,BEEF)    (B2,CHK)    (B1,HAM)    (B2,MCH)    (B2,MTL)    (B2,SPG)
(C,BEEF)    (B1,FISH)   (A,MCH)     (A,MTL)     (A,SPG)     (A,TUR)
(B2,BEEF)   (A,HAM)     (C,MCH)     (C,MTL)     (C,SPG)     (C,TUR)
(B1,CHK)    (C,HAM)     (B1,MCH)    (B1,MTL)    (B1,SPG)    (B1,TUR);

amt [*,*] (tr)
:      A    B1   B2   C     :=
BEEF   60   .    15   20
CHK    .    20   20   .
FISH   .    15   .    .
HAM    40   35   .    40
MCH    15   15   15   35
MTL    70   15   15   30
SPG    25   25   15   50
TUR    60   15   .    20
;

Reading from spreadsheet data should work the same, with the role of . played by a blank cell. But currently the entire set {NUTR,FOOD} is read rather than just the pairs corresponding to cells that are non-blank. In this example, reading

with table definition

table dietAmts IN "amplxl" "dietIssue.xlsx" "Amounts" "2D":
   LINKS <- [NUTR, FOOD], amt;

gives the same values but the wrong LINKS set:

display LINKS, amt;
set LINKS :=
(A,BEEF)    (B2,CHK)    (A,HAM)     (B2,MCH)    (A,SPG)     (B2,TUR)
(B1,BEEF)   (C,CHK)     (B1,HAM)    (C,MCH)     (B1,SPG)    (C,TUR)
(B2,BEEF)   (A,FISH)    (B2,HAM)    (A,MTL)     (B2,SPG)
(C,BEEF)    (B1,FISH)   (C,HAM)     (B1,MTL)    (C,SPG)
(A,CHK)     (B2,FISH)   (A,MCH)     (B2,MTL)    (A,TUR)
(B1,CHK)    (C,FISH)    (B1,MCH)    (C,MTL)     (B1,TUR);

amt [*,*] (tr)
:      A    B1   B2   C     :=
BEEF   60   .    15   20
CHK    .    20   20   .
FISH   .    15   .    .
HAM    40   35   .    40
MCH    15   15   15   35
MTL    70   15   15   30
SPG    25   25   15   50
TUR    60   15   .    20
;

The attached example aborts with:

Error at _cmdno 152 executing "read_table" command
(file modex-hor.run, line 426, offset 11600):

	Error reading table TICenTer with table handler amplxl:
	Could not parse data

This is the table that amplxl is trying to read:

table TICenTer IN 'amplxl' (pinpxls) 'backup=false':
    sthegen <- [Unidad],
    pthetyp ~ TipoCen,
    pthebar ~ Barra,
    pthelaw ~ LeyERNC,
    pthemngro ~ PotMin,
    pthemxgro ~ PotMax,
    pthestup ~ CosPart,
    pthestdn ~ CosDete,
    ptheico ~ ConsInt,
    pthefor ~ TSFor,
    pthehea ~ ConsEsp,
    pthenfc ~ CVNC,
    pthemrk ~ Marcador,
    pthefac ~ Factor,
    pthemksm ~ SumMul;

The underlying error is that two columns of table TICenTer are labeled Unidad:

• The 1st column, which provides a list of unique thermal generating units that are supposed to populate the set sthegen.
• The 14th column, which specifies units of measurement.

What happens here is consistent with amplxl trying to populate sthegen from the 14th column, and -- since the entries in that column are not unique -- giving the mysterious Could not parse data error as reported in issue #12.

To use the example, put the mod and run files in the same directory as the AMPL executable, and put the two xlsx files in a subdirectory named test. As expected, the problem goes away when the 14th column is labeled something else, like Unidades.

A user wants to read a set from this sheet, in an xlsx file (also attached) created by Excel:

The amplxl driver is used like this:

set FAMILY;
table FAMILY "amplxl" "FAMILY.xlsx": [FAMILY] IN;
read table FAMILY;
display FAMILY;

But under Windows, the set displays like this in a command window,

and like this in the IDE:

The problem is that Excel uses UTF-8 encoding, while the command window and the IDE are displaying the set member names using different extended ASCII encodings. AMPL stores the correct UTF-8 bytes, so that results written out to a spreadsheet do show the correct accented characters.

It's not clear what to recommend to the user, other than to avoid accented (and other non-ASCII) characters in the spreadsheet, but I'm opening this issue in case anyone has an idea.

When writing this table,

model diet.mod;
data diet.dat;
solve;

table dietResults OUT "amplxl" "Buy.xlsx" "Results":
   [FOOD], cost, Buy, Buy.rc ~ BuyRC, {j in FOOD} Buy[j]/f_max[j] ~ BuyFrac;
write table dietResults;

if the specified spreadsheet file Buy.xlsx does not exist, the amplxl handler creates the file, but then crashes:

ampl: load amplxl.dll;
ampl: include dietOutput.run
MINOS 5.51: optimal solution found.
6 iterations, objective 88.2
cannot open file
sw: 

Buy.xlsx does get created, but it is empty. The AMPL files are dietOutput.run, diet.mod, diet.dat.

In this example, two rows of the range TranspMIdupl have identical key column values:

M      I         Cpmi
BARA   SUANBOD   32000
BARA   SUANBOD   29400
.......

This is flagged as an error, but the error message does not properly identify what is wrong:

Error at _cmdno 4 executing "read_table" command
(file ../Could_not_parse.run, line 15, offset 302):

	Error reading table TranspMIdupl with table handler amplxl:
	Could not parse data