Error reading table with tableproxy

[Giovanni Pavese]

Hi,

I'm trying to read the table following the chapter 10, pag.2 of the book.

I have created this simple myfood.xls table:

FOOD	cost	f_min	f_max
BEEF	3.19	2	10
CHK	2.59	2	10

In a .run file I have copied this:

reset;
set FOOD;
param cost {FOOD} > 0;
param f_min {FOOD} >= 0;
param f_max {j in FOOD} >= f_min[j];

table Foods IN "tableproxy" "odbc" "myfood.xls":
FOOD <- [FOOD], cost, f_min, f_max;
read table Foods;

I get the following error:

Error reading table Foods with table handler tableproxy:
    Table `Foods` does not appear in "myfood.xls".

What does it mean? How can I fix it?

Thank you!
Giovanni


ps. Here are some notes about my system:

Windows 10 with 64-bit system, processor x64.

ampl: option version;
option version 'AMPL Version 20160920 (Intel icl EMT64 10.1.029, 64-bit)\
Demo license with maintenance expiring 20170427.\
Using license file "C:\Program Files (x86)\amplide.mswin64\ampl.lic".\';

Microsoft Excel at 32 bit.

Following what is stated in http://www.ampl.com/NEW/TABLES/ , I have in my folder C:\Program Files (x86)\amplide.mswin64 the all 3 files requested.
 

[Robert Fourer]

Highlight the entire range of cells in myfood.xls where your table appears (including the first row with the column names), then click the Name Box at the left end of the formula bar -- where B7 appears in this screenshot:



Then type the table name -- Foods -- into the Name Box, and press Enter.  Finally, save the updated spredsheet file and try reading it from AMPL again.

Bob Fourer
am...@googlegroups.com

[Giovanni Pavese]

Thank you Bob, it works! :)
Giovanni

[Giovanni Pavese]

Hi! Following your suggestion I was able to read properly my Excel 2016 ".xls" file, but I have another problem now.

At the end of the script I want to write back my results, but I get an error about the impossibility to write on my file (DROP Table). So I have found this post https://groups.google.com/forum/#!searchin/ampl/drop$20table%7Csort:relevance/ampl/BEgskiLWBDI/V2UxsNzHvG4J where is recommended to use the ".xlsx" format to write in the Excel file.  But if I change the format I obtain this new error, in the reading process: " Error reading table myDEP with table handler tableproxy:No suitable driver found."

Therefore I have looked for another help, and found this post https://groups.google.com/forum/#!searchin/ampl/excel$20error$20driver%7Csort:relevance/ampl/sgDzFe01ZuI/4C4RkWX-BwAJ. Following what Victor said I have installed Microsoft Acces Database but I can't set properly the connection string. Infact, in the downlod page, I read this:
"If you are an application developer using ODBC to connect to Microsoft Office Excel data, set the Connection String to “Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};  DBQ=path to xls/xlsx/xlsm/xlsb file”  " .

Can you help me in the reading/writing of the table?

Thank you a lot!
Giovanni

[Robert Fourer]

Can you determine whether you are using 32-bit or 64-bit Excel 2016, and whether you are using 32-bit or 64-bit AMPL?  It would be best to fix the error on reading the xlsx file first.  Can you post AMPL and spreadsheet files that can be used to recreate the problem?

Bob Fourer
am...@googlegroups.com

From: am...@googlegroups.com [mailto:am...@googlegroups.com] On Behalf Of Giovanni Pavese

Sent: Wednesday, December 7, 2016 9:59 AM
To: AMPL Modeling Language

--
You received this message because you are subscribed to the Google Groups "AMPL Modeling Language" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ampl+uns...@googlegroups.com.
To post to this group, send email to am...@googlegroups.com.
Visit this group at https://groups.google.com/group/ampl.
For more options, visit https://groups.google.com/d/optout.

[Giovanni Pavese]

Dear Bob,

as said in my first post my Excel 2016 is 32 bit, while AMPL is 64 bit. I will send you my file by email because the spreadsheet contains private data :)

Giovanni

[AMPL Support]

The Excel files did not come through; can you attach them individually?

AMPL Support Services
sup...@ampl.com

From: am...@googlegroups.com [mailto:am...@googlegroups.com] On Behalf Of Giovanni Pavese
Sent: Wednesday, December 7, 2016 4:34 PM
To: am...@googlegroups.com
Subject: Re: [AMPL 13146] Re: Error reading table with tableproxy

 

Dear Bob,

you can find all my file attached to this email. These are confidential, so I ask you to not send to anyone.

I prefere to send you all files because, if you want, I will ask you another thing about my script.

However, the main file is linate.run, which loads the others.

- In the folder LIN you can find the Excel files.

- The file that imports what I need is Linate_import_ACDM.run: if you leave ".xls" the script works, but I can't write in it.

  If you change the format in ".xlsx" you will obtain the error that I explained you.

- the file 3_Steps_Problem is a command file that contains a 3 steps optimization problem, where the steps are nested: the output of the first goes in the second and the third, and the output of the second goes into the third too.

- The file Linate_export_ACDM.run tries to write in the Excel file the output of the third problem. Considering that I can't do that, I used the "display" command, after the "break all" command (that I comment when needed, obvioulsy).

- I have attached a screenshot of my installation folder, hope it is helpful.

 

I apologize for my confusing and not professional script, and for the disturb.

As always, I thank you a lot.

 

Giovanni

 

 

--
You received this message because you are subscribed to a topic in the Google Groups "AMPL Modeling Language" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/ampl/BtXnvst_8iE/unsubscribe.
To unsubscribe from this group and all its topics, send an email to ampl+uns...@googlegroups.com.

To post to this group, send email to am...@googlegroups.com.
Visit this group at https://groups.google.com/group/ampl.
For more options, visit https://groups.google.com/d/optout.

[Robert Fourer]

It seems you have 4 .run files, and we see that Linate_import_ACDM.run can be run first, but we are not sure which of the others is to be run and in which order. Can you give us instructions?

Actually the 32-bit/64-bit installation is not supported by Microsoft, and we had to implement the "tableproxy" program ourselves to get around this problem. If you are not generating truly huge problems with AMPL, then you may find it best to download the 32-bit version of AMPL instead; then the only database file you need in your computer is ampltabl.dll, and you can change "tableproxy" "odbc" back to "ODBC". (The 32-bit version runs just as fast as the 64-bit version.)

Bob Fourer
am...@googlegroups.com

=======

[Giovanni Pavese]

Hi Bob,

the main file is linate.run, that includes all the other files and runs the 3 step optimization.

I have tried to install the 32-bit version of AMPL but if I run the script I obtain the error
" Linate_import_ACDM.run, line 18 (offset 628):
    Error reading table myDEP:
    no acceptable table handler found. "

I have looked into the installation folder and I have both the ampltabl.dll and tableproxy64.exe files,

so I don't really undestand what I have to do.

However my problem can be huge: the files that I sent to you are not the last version.

Thanks for the help.

Giovanni

--
You received this message because you are subscribed to a topic in the Google Groups "AMPL Modeling Language" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/ampl/BtXnvst_8iE/unsubscribe.

To unsubscribe from this group and all its topics, send an email to ampl+unsubscribe@googlegroups.com.

[Robert Fourer]

(1) For using 32-bit AMPL: You need only ampltabl.dll from the 32-bit installation download, and not tableproxy64.exe or ampltabl_64.dll. Also when using 32-bit AMPL be sure that your table statements start like

table myDEP IN "ODBC" "LIN\DEPqueryOutputProc.xls": ...

so that the right handler is requested. If you check these things and continue to see "no acceptable table handler found" then you can use the AMPL command "display _HANDLERS;" to see what handlers are available. You should see that one of them is "odbc", but if not then we know that the problem is with the ampltabl.dll file. (The 32-bit AMPL executable can address almost 2 gigabytes of memory under Windows, so it can handle quite large problems.)

(2) For using 64-bit AMPL with 32-bit Excel and .xls file:

We are working on reproducing your problem with the mixed 32-64-bit setup. To help with this, can you go back to the situation where you are using 32-bit Excel with 64-bit AMPL, and use these "table" statements:

table myDEPout OUT "tableproxy" "odbc" "LIN\prova.xls" 'verbose=1':
[NON_DROPPED,PARKING,ROUTE]
{d in NON_DROPPED, (r,k) in PATHr[d]:r=entryp[d]} tr[d,r,k] ~ TSAT_opt;

table myDEPout2 INOUT "tableproxy" "odbc" "LIN\DEPqueryOutputProc.xls" 'verbose=1':
[NON_DROPPED,RWY]
{d in NON_DROPPED, i in PATHi[d]: i='RWY'} ti[d,i] ~ TTOT_opt;

(Note there are two changes to myDEPout2.) Let us know all of the error messages that appear.

To unsubscribe from this group and all its topics, send an email to ampl+uns...@googlegroups.com.

To post to this group, send email to am...@googlegroups.com.
Visit this group at https://groups.google.com/group/ampl.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "AMPL Modeling Language" group.

To unsubscribe from this group and stop receiving emails from it, send an email to ampl+uns...@googlegroups.com.

[Giovanni Pavese]

Dear Bob,

first of all thank for your patience, I really appreciate that :)

1) AMPL 32-bit with Excel 32-bit: I can read the .xls file correctly but I can't write on it (maybe I did a mistake somewhere?). I obtain the following error (I paste also the code lines):

table myDEPout OUT "ODBC" "LIN\prova.xls" # AMPL 32-bit, Excel 32-bit


[NON_DROPPED,PARKING,ROUTE]
{d in NON_DROPPED, (r,k) in PATHr[d]:r=entryp[d]} tr[d,r,k] ~ TSAT_opt;


write table myDEPout;

Error at _cmdno 2237 executing "write_table" command
(file Linate_export_ACDM.run, line 8, offset 369):

Linate_export_ACDM.run, line 8 (offset 381):
    Error writing table myDEPout with table handler odbc:
    DROP TABLE `myDEPout` failed.
    Is another application using "LIN\prova.xls"?
context:  write table  >>> myDEPout; <<<

2) AMPL 64-bit with Excel 32-bit: I can read and write the .xls file! :) I have obtained this not using what you said, but instead a combination of 32 and 64-bit settings (understood from an error in coping and pasting what you said XD)

ps. I notice that table myDEPout is written in the same page of myDEP, instead myDEPout2 is written in another page... why?

table myDEP IN "tableproxy" "odbc" "LIN\DEPqueryOutputProc.xls": # AMPL 64-bit, Excel 32-bit
allDEPARTURES <- [FLIGHT], PKB_dep ~ PKB, EOBT, TOBT;
read table myDEP;

table myARR IN "tableproxy" "odbc" "LIN\ARRqueryOutputProc.xls": # AMPL 64-bit, Excel 32-bit
allARRIVALS <- [FLIGHT], PKB_arr ~ PKB, ELDT, SIBT;
read table myARR;

table myDEPout OUT "ODBC" "LIN\DEPqueryOutputProc.xls" # AMPL 32-bit, Excel 32-bit but also OK PER 64!!!!!

[NON_DROPPED,PARKING,ROUTE]
{d in NON_DROPPED, (r,k) in PATHr[d]:r=entryp[d]} tr[d,r,k] ~ TSAT_opt;

write table myDEPout;

table myDEPout2 OUT "ODBC" "LIN\DEPqueryOutputProc.xls" # AMPL 32-bit, Excel 32-bit but also OK PER 64!!!!!

[NON_DROPPED,RWY]
{d in NON_DROPPED, i in PATHi[d]: i='RWY'} ti[d,i] ~ TTOT_opt;

write table myDEPout;

3) AMPL 64-bit with Excel 32-bit: If I leave the setting for the 32-bit AMPL (that is using "ODBC") I can read and write the .xls file (writing table myDEPout2 in another page)!

table myDEP IN "ODBC" "LIN\DEPqueryOutputProc.xls": # AMPL 32-bit, Excel 32-bit
allDEPARTURES <- [FLIGHT], PKB_dep ~ PKB, EOBT, TOBT;
read table myDEP;

table myARR IN "ODBC" "LIN\ARRqueryOutputProc.xls":
allARRIVALS <- [FLIGHT], PKB_arr ~ PKB, ELDT, SIBT;
read table myARR;

table myDEPout OUT "ODBC" "LIN\DEPqueryOutputProc.xls" # AMPL 32-bit, Excel 32-bit OK PER 64!!!!!

[NON_DROPPED,PARKING,ROUTE]
{d in NON_DROPPED, (r,k) in PATHr[d]:r=entryp[d]} tr[d,r,k] ~ TSAT_opt;

write table myDEPout;

table myDEPout2 OUT "ODBC" "LIN\DEPqueryOutputProc.xls" # AMPL 32-bit, Excel 32-bit OK PER 64!!!!!

[NON_DROPPED,RWY]
{d in NON_DROPPED, i in PATHi[d]: i='RWY'} ti[d,i] ~ TTOT_opt;

write table myDEPout2;

4) AMPL 64-bit with Excel 32-bit: If I copy what you wrote I obtain the following errors:

table myDEPout OUT "tableproxy" "odbc" "LIN\prova.xls" 'verbose=1':
        [NON_DROPPED,PARKING,ROUTE]
      {d in NON_DROPPED, (r,k) in PATHr[d]:r=entryp[d]} tr[d,r,k] ~ TSAT_opt;

write table myDEPout;

table myDEPout2 INOUT "tableproxy" "odbc" "LIN\DEPqueryOutputProc.xls" 'verbose=1':
     [NON_DROPPED,RWY]
      {d in NON_DROPPED, i in PATHi[d]: i='RWY'} ti[d,i] ~ TTOT_opt;

write table myDEPout2;


Error at _cmdno 2235 executing "write_table" command
(file Linate_export_ACDM.run, line 18, offset 802):

Linate_export_ACDM.run, line 18 (offset 814):
    Error writing table myDEPout with table handler tableproxy:
    DROP TABLE `myDEPout` failed.
    Is another application using "LIN\prova.xls"?
Verbose table-handler output:
AMPL ODBC driver, version 20151130.
Calling SQLDriverConnect("DRIVER=Driver do Microsoft Excel(*.xls);DBQ=C:\Users\Giovanni\Desktop\AMPL_GP\LIN\prova.xls")
Connection string: "DBQ=C:\Users\Giovanni\Desktop\AMPL_GP\LIN\prova.xls;Driver={Driver do Microsoft Excel(*.xls)};DriverId=790;MaxBufferSize=2048;PageTimeout=5;"
CREATE TABLE `myDEPout` (`NON_DROPPED` VARCHAR(9), `PARKING` VARCHAR(3), `ROUTE` VARCHAR(2), `TSAT_opt` NUMBER) returned -1
sqlstate = "42000"
errmsg = "[Microsoft][Driver ODBC Excel] Impossibile modificare la struttura della tabella "myDEPout". Il database è di sola lettura."
native_errno = -1809
DROP TABLE `myDEPout` returned -1
sqlstate = "S0002"
errmsg = "[Microsoft][Driver ODBC Excel] La tabella 'myDEPout' non esiste."
native_errno = -1305

context:  write table  >>> myDEPout; <<<

5) AMPL 64-bit with Excel 32-bit: If I try to read/write the .xlsx file I obtain this other error:

table myDEP IN "tableproxy" "odbc" "LIN\DEPqueryOutputProc.xlsx": # AMPL 64-bit, Excel 32-bit
allDEPARTURES <- [FLIGHT], PKB_dep ~ PKB, EOBT, TOBT;
read table myDEP;

Error at _cmdno 3 executing "read_table" command
(file Linate_import_ACDM.run, line 18, offset 704):

Linate_import_ACDM.run, line 18 (offset 715):

    Error reading table myDEP with table handler tableproxy:
    No suitable driver found.

context:  read table  >>> myDEP; <<<

I tried to be as much as possible clear, hope this can be useful!

Thanks again,
Giovanni.

[Giovanni Pavese]

Since that I have had many troubles with reading/writing Excel files, I have tried to use Acces for the first time and, following the instructions contained in chaper 10 of AMPL book, no problems arose. So now I have to learn to use Acces, but I would suggest to everyone to use this instead of Excel.

Bye,
Giovanni.

[Jorge Mario Bolivar Redondo]

Dear Bob, I have the very same problem. I already set the table name ( as you can see in the screenshot ), saved the file, and even typed the command "reset" in AMPL

However, I get the following error while trying to read the table in AMPL using tableproxy:

I tried to read the table in "amplide" environment. I'm using MS Excel 64-bit, and this is the version of AMPL I'm using:

"option version 'AMPL Version 20200501 (MS VC++ 10.0, 64-bit; expires midnight 20201215 GMT)\option version 'AMPL Version 20200501 (MS VC++ 10.0, 64-bit; expires midnight 20201215 GMT)\"

How can I fix this error? Thanks in advance.

Este correo no representa opinión o consentimiento oficial de la Universidad del Norte, por lo que esta no adquiere ninguna responsabilidad por su contenido, salvo en el caso de funcionarios en ejercicio de atribuciones reglamentarias. Puede provenir de una cuenta ofrecida a funcionarios o estudiantes, como parte del ejercicio educativo, evento en el cual tanto el mensaje como sus anexos son estrictamente confidenciales. Ha sido analizado con software antivirus; no obstante, no se garantiza que sea seguro o no contenga errores o virus, por lo que la Universidad del Norte no se hace responsable de su transmisión.

[AMPL Google Group]

You are creating a Excel "table" named TablaDem, but AMPL is looking for an Excel "range" having that name. To create a named range, see the instructions in my previous post in this thread.

--
Robert Fourer
am...@googlegroups.com

{#HS:1260022815-86147#}

On Thu, Aug 20, 2020 at 7:30 PM UTC, AMPL Modeling Language <am...@googlegroups.com> wrote:

Dear Bob, I have the very same problem. I already set the table name ( as you can see in the screenshot ), saved the file, and even typed the command "reset" in AMPL

However, I get the following error while trying to read the table in AMPL using tableproxy:

I tried to read the table in "amplide" environment. I'm using MS Excel 64-bit, and this is the version of AMPL I'm using:

"option version 'AMPL Version 20200501 (MS VC++ 10.0, 64-bit; expires midnight 20201215 GMT)\option version 'AMPL Version 20200501 (MS VC++ 10.0, 64-bit; expires midnight 20201215 GMT)\"

How can I fix this error? Thanks in advance.

El jueves, 1 de diciembre de 2016 a las 13:00:44 UTC-5, 4...@ampl.com escribió:

Highlight the entire range of cells in myfood.xls where your table appears (including the first row with the column names), then click the Name Box at the left end of the formula bar -- where B7 appears in this screenshot:

Este correo no representa opinión o consentimiento oficial de la Universidad del Norte, por lo que esta no adquiere ninguna responsabilidad por su contenido, salvo en el caso de funcionarios en ejercicio de atribuciones reglamentarias. Puede provenir de una cuenta ofrecida a funcionarios o estudiantes, como parte del ejercicio educativo, evento en el cual tanto el mensaje como sus anexos son estrictamente confidenciales. Ha sido analizado con software antivirus; no obstante, no se garantiza que sea seguro o no contenga errores o virus, por lo que la Universidad del Norte no se hace responsable de su transmisión. --

You received this message because you are subscribed to the Google Groups "AMPL Modeling Language" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ampl+uns...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/ampl/9aa22a6c-30b4-4267-b1ef-90822fe28743n%40googlegroups.com.