Error reading excel files, no acceptable table handler found.

[Alessandro Oscar Gilardino Arias]

Hi, I have some problem when I tried to read the file diet.xlsx.run.

I am using a AMPL 64 bit, mi computer has an ODBC handler of 64 bit and I already changed the declaration to read the table from:

 this table dietFoods IN "ODBC" "TABLES/diet.xlsx" "Foods";

 into this table dietFoods "tableproxy" "odbc" "diet.xlsx" "Foods".

but I still have the following error message:

Error reading table dietFoods:

no acceptable table handler found.

Also, in my AMPL folder I have only the ampltabl.dll because I am using AMPL and the ODBC table handler with the same bit version (wich is 64 bit)

Thanks in advance.

regards

[Robert Fourer]

Are you using the 32-bit or the 64-bit version of Microsoft Excel? (Note that Microsoft's default for recent versions of Office has been to install the 32-bit version even under 64-bit Windows.)

If you are using the 64-bit version of Excel, then you only need ampltabl_64 in your AMPL folder.

If you are using the 32-bit version of Excel (or are running under something other than Windows), then see the detailed instructions at http://ampl.com/resources/database-and-spreadsheet-table-handlers/.

Bob Fourer
am...@googlegroups.com

=======

[Alessandro Oscar Gilardino Arias]

Thanks for your answer Bob, but I still have problem to read the excel files.

I attached a printscreen of my ODBC databas handler 

I folllowed the instructions you gave me in the last message, also I attached a printscreen of the tablehandlers in my AMPL file. As you can see in the file I have the ampltabl.dll and the tableproxy_64.

What can be wrong??

Thanks in advance.

[victor.z...@gmail.com]

It appears that ampltabl_64.dll is missing from your AMPL directory. You can download it from http://www.ampl.com/NEW/TABLEPROXY/ampltabl.mswin64.zip. BTW your installation looks more like a 32-bit distribution of AMPL. What does "ampl -v" say?

HTH,
Victor

--
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 http://groups.google.com/group/ampl.
For more options, visit https://groups.google.com/d/optout.

[Alessandro Oscar Gilardino Arias]

Hi Victor,

I donwload the file you told me but now I have the following message:

ampl: include diet.xlsx.run;

Error at _cmdno 4 executing "read_table" command

(file diet.xlsx.run, line 13, offset 415):

Error reading table dietFoods with table handler tableproxy:

Could not find "tableproxy32.exe".

Now I put the file tableproxy32.exe and I have the following message:

Error at _cmdno 2 executing "read_table" command

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

Error reading table dietFoods with table handler tableproxy:

Table handler odbc in C:\Users\alessandro\Desktop\AMPL\ampltabl.dll:

Refused: missing handler name or database details.

I am attaching a printscreen of the table handlers I have in my AMPL file.

FYI I have already change the declarations to read tables to

      table dietFoods "tableproxy" "odbc" "diet.xlsx" "Foods":

[victor.z...@gmail.com]

Hi Alessandro,

The "missing handler name or database details" error suggests that there is a problem with the table handler name, but the table declaration looks correct. Could you post the full AMPL code that reproduces the error?

Also what versions of tableproxy32 and ODBC handler do you use? These can be obtained with the following commands:

  tableproxy32 -v (run from command line)

  print _handler_desc['odbc']; (run from the AMPL prompt)

HTH,

Victor

[Alessandro Oscar Gilardino Arias]

Dear Victor, sorry for the late response, I type the commands you told me and this is what the AMPL shows:

ampl: option tableproxy32 -v;

ampl:  print _handler_desc['odbc'];

AMPL ODBC handler (20131212): expected 2-8 strings before ":[...]":

  'ODBC', connection_spec ['ext_name'] [option [option...]]

Connection_spec gives a connection to the external table.  If the table's

external name differs from the AMPL table name, the external name must be

given in place of 'ext_name'.  For IN tables, 'SQL=sqlstmt' can appear in

place of 'ext_name', where sqlstmt is a SQL statement, such as a SELECT

statement.  Possible options, explained below:

'maxlen=nnn'

'nsmix=...'

'time=...'

'verbose' or 'verbose=n' with 0 <= n <= 3

'write=append' or 'write=drop'

Use 'maxlen=nnn' to limit character strings to nnn characters (discarding

any excess characters).

With 'nsmix=*', columns of string data are treated as containing both

strings and numbers, with strings that look like decimal fixed- or floating-

point numbers treated as numbers (the default).  With 'nsmix=-', columns of

string data are kept as string data.  To give the effect of 'nsmix=*' to

some columns and of 'nsmix=-' to others, use syntax

index ~ 'Strcol:colname'

in the AMPL table declaration for columns that should be treated as with

the global 'nsmix=-' string.  For columns to be treated in the default way,

optionally you can use syntax "index ~ 'Mixed:colname'" in the table

declaration.  The "Strcol:" and "Mixed:" portion will be stripped from

the column name presented to the database.   Alternatively, use 'nsmix=!'

to use the old inconsistent practice of adding quotes to strings that look

like numbers when reading but not removing the quotes when writing.

The ... in 'time=...' is a comma-separated list of names of columns that

should be regard as time data when writing tables.  A better alternative

is to use syntax "index ~ 'Time:colname'" in the table declaration.

As with "Strcol:" and "Mixed:", the "Time:" portion will be stripped

from the column name shown to the database.

For OUT and INOUT tables, 'write=...' specifies how "write table" should work:

'write=drop' ==> drop and completely rewrite an existing table (default)

'write=append' ==> assume "write table" is only appending new rows

For 'verbose=n', n is the sum of

1 ==> show connection strings and

2 ==> show column types.

Plain 'verbose' is treated as 'verbose=1'.

Alternatives for connection_spec:

'filename.ext', where ext is a registered ODBC extension;

'filename.dsn' (written by the ODBC control panel's "File DSN");

an explicit connection string of the form 'DSN=...' or 'DRIVER=...';

or an ODBC data source name (see the ODBC control panel).

ampl: 

I am not really sure if that is what you need but I dont know exactly how to type in the command line.

Also I am attaching the file that is giving me the problems to read the Excel files

Thanks in advance

[victor.z...@gmail.com]

I meant running tableproxy32 from the command shell (cmd.exe on Windows) rather than AMPL. But you can do the same from AMPL:

  shell "tableproxy32 -v";

Could you post the output of the above command.

The AMPL script looks correct and I was able to run it without errors with the ODBC handler 20131212. Once you post the tableproxy version we can check that as well.

HTH,

Victor

[Alessandro Oscar Gilardino Arias]

Hi Victor, this is the full output when I run the diet.xlsx.run

ampl: include diet.xlsx.run;

Error at _cmdno 4 executing "read_table" command

(file diet.xlsx.run, line 13, offset 415):

Error reading table dietFoods with table handler tableproxy:

Table handler odbc in C:\Users\alessandro\Desktop\AMPL\ampltabl.dll:

Refused: missing handler name or database details.

ampl: 

[Alessandro Oscar Gilardino Arias]

Dear Victor, I hope everything is going well.

I am sorry for the insistance but I would like to know if you have any new information about the problem I had with the ODBC handler.

Thanks in advance and Best Regards 

[victor.z...@gmail.com]

Unfortunately I was not able to reproduce this error without changing the table declaration (replacing "odbc" with "Odbc"). Have you tried direct spreadsheet access without tableproxy? This can be done by using a 32-bit version of AMPL with 32-bit ODBC drivers or installing 64-bit ODBC drivers from http://www.microsoft.com/en-us/download/details.aspx?id=13255 and using a 64-bit version of AMPL. Do you still get an error in this configuration?

HTH,

Victor