[AMPL 24663] Error reading and writing table in AMPL

Hi, I have just begun to learn how to use Excel spreadsheets for data input and output in AMPL, but I have some problems with the odbc table handler. Maybe you can help me.

First my system:

-Windows 10 Edu 64-bit

-MS Office 2016 Edu 32-bit

What works:

-reading a .xls file

What does not work:

-reading a .xlsx file

  • writing a .xls file

  • writing a .xlsx file

I use the diet.xls.run script from the textbook:

table dietFoods “ODBC” “TABLES/diet.xls” “Foods”:

FOOD ← [FOOD], cost IN, f_min IN, f_max IN,

Buy OUT, Buy.rc ~ BuyRC OUT, {j in FOOD} Buy[j]/f_max[j] ~ BuyFrac;

table dietNutrs IN “ODBC” “TABLES/diet.xls” “Nutrients”: NUTR ← [NUTR], n_min, n_max;

table dietAmts IN “ODBC” “TABLES/diet.xls” “Amounts”: [NUTR, FOOD], amt;

read table dietFoods;

read table dietNutrs;

read table dietAmts;

solve;

write table dietFoods;

The following error occurs:

ampl: include TABLES/diet.xls.run;

MINOS 5.51: optimal solution found.

13 iterations, objective 118.0594032

Error at _cmdno 6 executing “write_table” command

(file ./TABLES/diet.xls.run, line 19, offset 492):

Error writing table dietFoods with table handler odbc:

DROP TABLE Foods failed.

Is another application using TABLES/diet.xls?

Then I reset and use the diet.xlsx.run script from the textbook:

table dietFoods “ODBC” “TABLES/diet.xlsx” “Foods”:

FOOD ← [FOOD], cost IN, f_min IN, f_max IN,

Buy OUT, Buy.rc ~ BuyRC OUT, {j in FOOD} Buy[j]/f_max[j] ~ BuyFrac;

table dietNutrs IN “ODBC” “TABLES/diet.xlsx” “Nutrients”: NUTR ← [NUTR], n_min, n_max;

table dietAmts IN “ODBC” “TABLES/diet.xlsx” “Amounts”: [NUTR, FOOD], amt;

read table dietFoods;

read table dietNutrs;

read table dietAmts;

solve;

write table dietFoods;

The following error occurs:

ampl: reset;

ampl: include TABLES/diet.xlsx.run;

Error at _cmdno 2 executing “read_table” command

(file ./TABLES/diet.xlsx.run, line 13, offset 397):

Error reading table dietFoods with table handler odbc:

“TABLES/diet.xlsx” does not end in any of

ampl:

Could you help me to address above issues? Thank you so much!

To avoid issues with the ODBC table handler, I recommend switching to our new direct spreadsheet interface. It requires only a few simple changes to your table statements, works with any .xlsx file, and has some useful new features.