Re: [AMPL 6867] Error reading table from Excel

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

This means that the file Book1.xlsx either doesn't exist in the current directory or can't be opened for some reason, for example because of insufficient permissions. You might need to specify a path to Book1.xlsx if this file is not in the current directory.

HTH,

Victor

On Fri, Apr 5, 2013 at 9:58 PM, David <davidre...@gmail.com> wrote:

Hello I am having some trouble reading tables from Excel.

Every time I try to access some data I obtain the following error message from AMPL:

Error reading table TestTable with table handler odbc:

Cannot open file Book1.xlsx for table TestTable

where Book1.xlsx is an excel file with a unique column containing "N" in its first cell and containing integers in the cells bellow. My run file is:

reset;

model TestModel.mod;

table TestTable IN "ODBC" "Book1.xlsx": N <- [N];

read table TestTable;

display N;

solve;

I use AMPL 64-bit as well as Excel 2010 64-bit on a Windows 8 machine (I have ampltabl_64.dll in my ampl folder).

Thank your for your help,

-David.

--
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

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

Hi David,

ODBC tables correspond to named ranges in Excel, so you should have a named range called TestTable including your data defined in Book1.xlsx. There is some information about ranges on the RELATIONAL DATABASE ACCESS page. Also you can read about how to create a named range in Excel 2007 here.

HTH,
Victor

On Mon, Apr 8, 2013 at 11:08 PM, David <davidre...@gmail.com> wrote:

Hi Victor, 

Thanks for your answer, indeed the path was missing, however I still have a problem reading the file. After adding the correct path to Book1.xlsx in my run file 

table TestTable IN "ODBC" "C:\...\Book1.xlsx": N <- [N];

the error message has switch to:

Error reading table TestTable with table handler odbc:

Table `TestTable` does not appear in "C:\...\Book1.xlsx".

Thanks again,

-David

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

Hi David,

The syntax error is easy to fix - a closing angle bracket ('>') is missing just before the semicolon:

  table cost IN "ODBC" "C:\...\Book1.xlsx": [i~N],{j in N} <c[i,j]~(j)>;

However, this wouldn't work because N will be empty in the indexing expression {j in N}.

To fix this you can read the set N first and then the data for c:

table costN "ODBC" "C:\...\Book1.xlsx" "cost": [N] IN;

table cost IN "ODBC" "C:\...\Book1.xlsx": [i~N],{j in N} <c[i,j]~(j)>;

read table costN;

read table cost;

HTH,

Victor 

On Tue, Apr 9, 2013 at 6:24 PM, David <davidre...@gmail.com> wrote:

Hi Victor, 

Thank you for your prompt reply, indeed I was missing named ranges in Excel: I wrongly assumed that named ranges were columns names, instead of being a specific Excel feature.

I can now read most of my data in Excel, but I am having some difficulty reading matrices. I would like to read an asymmetric travel time matrix between pairs of nodes in a transportation network. If I have 4 nodes in my network, the matrix in my Excel file looks like:

N   1     2     3    4

1  t11  t12  t13  t14

2  t21  t22  t23  t24

3  t31  t32  t33  t34

4  t41  t42  t43  t44

I have tried to use the following command line in my run file -which I found on this forum- 

table cost IN "ODBC" "C:\...\Book1.xlsx": [i~N],{j in N} <c[i,j]~(j);

with the following lines in my model file

set N;

param c{N,N} >=0;

but it triggers a syntax error. I believe there should be a simpler syntax to read this matrix, but I couldn't find any example on the forum.

Could you help solve this last issue?

Thanks,

-David