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!