Connecting to Excel Tables via Excel 2011 for Mac using 64-bit AMPL on Mac OS X 10.9 (64-Bit)

[djph...@gmail.com]

Hello Folks,

I'm trying to interface with Excel tables and cannot seem to do it.  I have done the following:

1. Visited http://www.ampl.com/NEW/TABLES/

2. Downloaded ampltabl-macosx.zip, unzipped, and placed ampltabl.dll where my 64-bit binaries are stored.

3. Recoded my AMPL statements from:

    table MyTableName IN "ODBC" "MyExcelFile.xls": .... etc etc

    to:  table MyTable Name IN 'tableproxy' 'odbc' ('MyExcelFile.xls'): .... etc.

Here's the specific lines:

FROM:

table Corn IN "ODBC" "FarmerDataDet.xlsb": C <- [C], PlantingCost, SalePrice, MaxAtGoodPrice, LowSalePrice, PurchasePrice, MinReq;

read table Corn;

TO:

table Corn IN 'tableproxy' 'odbc' ('FarmerDataDet.xls'): C <- [C], PlantingCost, SalePrice, MaxAtGoodPrice, LowSalePrice, PurchasePrice, MinReq;

read table Corn;

The error message is:

Error at _cmdno 6 executing "read_table" command

(file ./FarmerExcelDet.run, line 14, offset 673):

Error reading table Corn:

no acceptable table handler found.

I've tried other syntactical modifications with no luck.  Any advice?

Thanks.

-Wen

My System:

Mac OS X 10.9: 64-bit Operating System

2.8 GHz Intel Core i7: 64-bit Processor

AMPL: 64-Bit for Mac OS X

Excel 2011 for Mac (Latest Available Version): 32-Bit

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

Hi Wen,

As far as I know, Microsoft doesn't provide the Excel ODBC driver for Mac OSX, which is required to import data from Excel in AMPL. You can see the list of installed ODBC drivers using ODBC Manager for Mac which is available here.

However, ODBC drivers compatible with Excel for Mac are available from the following third-party vendors:

* OpenLink Software

* Actual Technologies

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/groups/opt_out.

[djph...@gmail.com]

Thank you for your response, Victor!

[djph...@gmail.com]

Hi Victor,

Thanks again for your reply.  I investigated the options you provided to me, but it doesn't look like they will work.  However, before I jump to conclusions, I'd like to share my understanding with you in the event that I am missing something.

First, I must connect to an Excel spreadsheet and not another data source.

Secondly, I am no computer expert, but my understanding of an ODBC connector is that it is specific to not only platform (e.g. Mac OS X), but also application (e.g. Excel).  Therefore, I need to find an ODBC connector for Mac OS X for Excel (on a Mac).

Visiting OpenLink Software's site, I could not find any suitable ODBC drivers for Excel on Mac OS X: http://uda.openlinksw.com/

Visting the Actual Technologies site, I DID find what appears to be support for ODBC drivers for Excel on Mac OS X, but I must purchase a license: http://www.actualtech.com/download.php

So, the questions I would like confirmation with are:

1. I need an ODBC that is specific to Mac and Excel for Mac? (Yes/No)

2. As far as you know, there are no freely available ODBC connectors for Mac and Excel for Mac? (Yes/No).

Again, thanks for your response and for your leads.

Best,

-Wen

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

Hi Wen,

Yes, you need an ODBC driver that is specific to Mac and Excel for Mac.

And yes, I'm not aware of a free version of an ODBC driver for Excel.

Best regards,

Victor

[djph...@gmail.com]

Victor,

Thank you very much for your confirmation.  In addition, thank you for the excellent work you do related to the development and support of AMPL.

Best,

-Wen

[tanyas...@gmail.com]

Hi,

I am having trouble connecting excel to odbc database in mac. I have installed odbc manager from Actual technologies. When I try to open a new query from data tab in excel, it tells me that no ODBC drivers are installed. What should i do? please help me.

Thanks

Tanya

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

Hi Tanya,

I'm not sure I completely understand what you are trying to do. What kind of database are you trying to connect to, what is the role of Excel here if you are not connecting to it and, most importantly, how is it related to AMPL?

Anyway, you can see the list of installed ODBC drivers in the Drivers tab of the ODBC Administrator.

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.

[Tanya Singh]

Hi Victor,

I just download iODBC driver manager. But when I test the DSN, it fails. Could you help me with it?

Thanks

Tanya

--
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/8sne3XHNUlw/unsubscribe.
To unsubscribe from this group and all its topics, send an email to ampl+uns...@googlegroups.com.

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

First, make sure that you have not only the driver manager, but also the ODBC driver for the database system, you are trying to use, installed.

If the ODBC driver is installed and listed in the ODBC Administrator, but you get an error when trying to connect to it from AMPL, please add the "verbose" option to the table declaration (as described here) and send us the complete output of running read table or write table command that fails.

You might also find the AMPL database and spreadsheet connection guide useful.

HTH,

Victor

[Tanya Singh]

Hi,

I am new at all this. Could you help me out with it?

I basically want to use 2-3 excel tables in mysql. So that I could give a query and get desired result. I am not sure where to start from. I am using mac OS X10.9 and i have Microsoft excel 2011 for mac installed. What do you think should be my next step?

FYI, I have already downloaded and installed mysql-5.6.16-osx10.7-x86_64 in my mac.

Thanks

Tanya

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

Hi Tanya,

Using Excel tables in MySQL doesn't look like something related to AMPL, so I'd recommend referring to the documentation and/or discussion groups for the software you are using. This discussion group is primarily for AMPL-related questions.

HTH,

Victor