How to import Excel sheets into AMPL to do optimization

[qiuc...@umich.edu]

If I want to import excel data to Ampl, I have already installed ampltabl.macosx64.tgz, I cannot still run it, it mentioned I had to install an ODBC database connector, what did that mean, could anybody give me a specific example? Here is the error message I got without the connector?

ampl: model diet.mod;
ampl: table Foods IN "ODBC" "diet.xls":
ampl? FOOD <- [FOOD], cost, f_min, f_max;
ampl: read table Foods;
Error executing "read_table" command:

        Error reading table Foods with table handler odbc:
        ODBC is not available.

I believe the answer is not https://support.office.com/en-us/article/odbc-drivers-that-are-compatible-with-excel-for-mac-9fa6bc7f-d19e-4f7f-9be4-92e85c77d712. for I don't import data into Excel, but instead out of Excel and I don't expect to spend money on that service? Anybody has the same situation and get it solved? My computer is Mac and has the latestest verison of Excel which is 64 bits together installed with 64 bits AMPL.

[AMPL Google Group]

There are a few threads about ODBC on Mac OS here. For instance:
- https://groups.google.com/d/topic/ampl/4VCOFGRN1pY/discussion
- https://groups.google.com/forum/#!msg/ampl/8sne3XHNUlw/qKN-CfxP_z8J

We do not currently have support for reading Excel spreadsheets under macOS. Under Windows we use the ODBC (Open Database Connectivity) driver supplied by Microsoft with the Excel application (hence the "ODBC" in the table statements), but unfortunately a comparable driver is not available free for Mac computers as far as we know. Our long-term plan is to get away from using ODBC for Excel, but for now there is not a really good free alternative to going through text files.

Best regards,
Filipe

--
Filipe Brandão
am...@googlegroups.com

{#HS:515811168-232#}

--
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.

[qiuc...@umich.edu]

Hi, I have changed to a Windows computer however it still doesn't work. I have two versions of AMPL powered by cloudpaging service in the campus computers. I couldn't use "ODBC" to import data in terms of both versions and the error messages are the same as before "ODBC is not available". However, fortunately, when I was using AMPL-Student 2016, the only choice is "tableproxy" "odbc" and it worked. However, when I turned to AMPL 2016, the above syntax, unfortunately, didn't work and the error message said "could not find tableproxy32.exe". This is a cloud service powered by the university and I have no idea about what was happening and I couldn't change the setting of the service. Could you please give me other suggestions? It said the Excel version of the computer is 64-bit but I believe it must be 32-bit for "tableproxy" "odbc" can only work for 64-bit AMPL and 32-bit Excel. And the AMPL 2016 must be 32-bit for it doesn't have tableproxy32.exe. But if the above two statements are true, 32-bit AMPL 2016 must compatible with 32-bit Excel with "ODBC". I get really confused and I don't have access to the true versions of the above three softwares, sombody could help? 

[AMPL Google Group]

Could you please run odbcad32.exe and send us prints of the tabs "User DSN" and "Drivers" so that we can see all the ODBC drivers that you have installed? You may have both 32 and 64 bit drivers on the same machine and it may be causing trouble. After finding the drivers available you may specify the one that you want to use in the table statement (see, e.g., https://groups.google.com/d/msg/ampl/GqR-UenDHlE/Ns8HUWoHBAAJ). I would also recommend you to add the option "verbose" to the table statement so that you can obtain more details about the error.

Best regards,
Filipe

--
Filipe Brandão
am...@googlegroups.com

{#HS:515811168-232#}

On Thu, Feb 1, 2018 at 10:56 PM UTC, <am...@googlegroups.com> wrote:

Hi, I have changed to a Windows computer however it still doesn't work. I have two versions of AMPL powered by cloudpaging service in the campus computers. I couldn't use "ODBC" to import data in terms of both versions and the error messages are the same as before "ODBC is not available". However, fortunately, when I was using AMPL-Student 2016, the only choice is "tableproxy" "odbc" and it worked. However, when I turned to AMPL 2016, the above syntax, unfortunately, didn't work and the error message said "could not find tableproxy32.exe". This is a cloud service powered by the university and I have no idea about what was happening and I couldn't change the setting of the service. Could you please give me other suggestions? It said the Excel version of the computer is 64-bit but I believe it must be 32-bit for "tableproxy" "odbc" can only work for 64-bit AMPL and 32-bit Excel. And the AMPL 2016 must be 32-bit for it doesn't have tableproxy32.exe. But if the above two statements are true, 32-bit AMPL 2016 must compatible with 32-bit Excel with "ODBC". I get really confused and I don't have access to the true versions of the above three softwares, sombody could help?

On Thu, Feb 1, 2018 at 1:19 PM UTC, AMPL Google Group <am...@googlegroups.com> wrote:

There are a few threads about ODBC on Mac OS here. For instance:
- https://groups.google.com/d/topic/ampl/4VCOFGRN1pY/discussion
- https://groups.google.com/forum/#!msg/ampl/8sne3XHNUlw/qKN-CfxP_z8J

We do not currently have support for reading Excel spreadsheets under macOS. Under Windows we use the ODBC (Open Database Connectivity) driver supplied by Microsoft with the Excel application (hence the "ODBC" in the table statements), but unfortunately a comparable driver is not available free for Mac computers as far as we know. Our long-term plan is to get away from using ODBC for Excel, but for now there is not a really good free alternative to going through text files.

Best regards,
Filipe

--
Filipe Brandão
am...@googlegroups.com

[qiuc...@umich.edu]

Here are the shots:

[AMPL Google Group]

You have both 32 and 64 bit drivers installed; ACEODBC.DLL drivers are 64 bit drivers and ODBCJT32.DLL are 32 bit drivers. The following table statement should work with 64 bit AMPL:

table Foods "odbc" "Driver=Microsoft Excel Driver (*.xls, *.xlsx,
*.xlsm, *.xlsb);ReadOnly=0;DBQ=diet.xls" "verbose":

In case this table statement does not work please send us the output of "ampl -v" for each AMPL installation you have on that machine.

Best regards,
Filipe

--
Filipe Brandão
am...@googlegroups.com

{#HS:515811168-232#}

On Fri, Feb 2, 2018 at 11:56 PM UTC, <am...@googlegroups.com> wrote:

<https://lh3.googleusercontent.com/--rw-ZBX-GGI/WnT00sQaTyI/AAAAAAAAAO0/89__43rnWoYd06hZCABZPGfSPGTsdufqgCLcBGAs/s1600/Capture4.PNG&gt;

<https://lh3.googleusercontent.com/-q-5eMC3YepQ/WnT0ymrStsI/AAAAAAAAAOw/CNrfYt1mtkEiERdaJKBlMTgXiKf11z2aQCLcBGAs/s1600/Capture3.PNG&gt;

<https://lh3.googleusercontent.com/--rw-ZBX-GGI/WnT00sQaTyI/AAAAAAAAAO0/89__43rnWoYd06hZCABZPGfSPGTsdufqgCLcBGAs/s1600/Capture4.PNG&gt;

<https://lh3.googleusercontent.com/-Z_ablKMAl00/WnT0woPj19I/AAAAAAAAAOs/-avBu2SQUpwx-PgO7tRLDmKNWbNBvlzPgCLcBGAs/s1600/Capture2.PNG&gt;

<https://lh3.googleusercontent.com/-q-5eMC3YepQ/WnT0ymrStsI/AAAAAAAAAOw/CNrfYt1mtkEiERdaJKBlMTgXiKf11z2aQCLcBGAs/s1600/Capture3.PNG&gt;

<https://lh3.googleusercontent.com/-Z_ablKMAl00/WnT0woPj19I/AAAAAAAAAOs/-avBu2SQUpwx-PgO7tRLDmKNWbNBvlzPgCLcBGAs/s1600/Capture2.PNG&gt;

<https://lh3.googleusercontent.com/-3SEbO8gySGY/WnT0uUEuoxI/AAAAAAAAAOo/DILlXAPl7-geZWlL0KDuSBYFhgOY3NxXgCLcBGAs/s1600/Capture.PNG&gt;
Here are the shots:

<https://lh3.googleusercontent.com/-3SEbO8gySGY/WnT0uUEuoxI/AAAAAAAAAOo/DILlXAPl7-geZWlL0KDuSBYFhgOY3NxXgCLcBGAs/s1600/Capture.PNG

On Fri, Feb 2, 2018 at 6:13 PM UTC, AMPL Google Group <am...@googlegroups.com> wrote:

Could you please run odbcad32.exe and send us prints of the tabs "User DSN" and "Drivers" so that we can see all the ODBC drivers that you have installed? You may have both 32 and 64 bit drivers on the same machine and it may be causing trouble. After finding the drivers available you may specify the one that you want to use in the table statement (see, e.g., https://groups.google.com/d/msg/ampl/GqR-UenDHlE/Ns8HUWoHBAAJ). I would also recommend you to add the option "verbose" to the table statement so that you can obtain more details about the error.

Best regards,
Filipe

--
Filipe Brandão
am...@googlegroups.com

On Thu, Feb 1, 2018 at 10:56 PM UTC, <am...@googlegroups.com> wrote:

Hi, I have changed to a Windows computer however it still doesn't work. I have two versions of AMPL powered by cloudpaging service in the campus computers. I couldn't use "ODBC" to import data in terms of both versions and the error messages are the same as before "ODBC is not available". However, fortunately, when I was using AMPL-Student 2016, the only choice is "tableproxy" "odbc" and it worked. However, when I turned to AMPL 2016, the above syntax, unfortunately, didn't work and the error message said "could not find tableproxy32.exe". This is a cloud service powered by the university and I have no idea about what was happening and I couldn't change the setting of the service. Could you please give me other suggestions? It said the Excel version of the computer is 64-bit but I believe it must be 32-bit for "tableproxy" "odbc" can only work for 64-bit AMPL and 32-bit Excel. And the AMPL 2016 must be 32-bit for it doesn't have tableproxy32.exe. But if the above two statements are true, 32-bit AMPL 2016 must compatible with 32-bit Excel with "ODBC". I get really confused and I don't have access to the true versions of the above three softwares, sombody could help?

On Thu, Feb 1, 2018 at 1:19 PM UTC, AMPL Google Group <am...@googlegroups.com> wrote:

There are a few threads about ODBC on Mac OS here. For instance:
- https://groups.google.com/d/topic/ampl/4VCOFGRN1pY/discussion
- https://groups.google.com/forum/#!msg/ampl/8sne3XHNUlw/qKN-CfxP_z8J

We do not currently have support for reading Excel spreadsheets under macOS. Under Windows we use the ODBC (Open Database Connectivity) driver supplied by Microsoft with the Excel application (hence the "ODBC" in the table statements), but unfortunately a comparable driver is not available free for Mac computers as far as we know. Our long-term plan is to get away from using ODBC for Excel, but for now there is not a really good free alternative to going through text files.

Best regards,
Filipe

--
Filipe Brandão
am...@googlegroups.com

On Wed, Jan 31, 2018 at 7:10 PM UTC, <am...@googlegroups.com> wrote:

If I want to import excel data to Ampl, I have already installed ampltabl.macosx64.tgz, I cannot still run it, it mentioned I had to install an ODBC database connector, what did that mean, could anybody give me a specific example? Here is the error message I got without the connector?

ampl: model diet.mod;
ampl: table Foods IN "ODBC" "diet.xls":
ampl? FOOD <- [FOOD], cost, f_min, f_max;
ampl: read table Foods;
Error executing "read_table" command:

Error reading table Foods with table handler odbc:
ODBC is not available.

I believe the answer is not https://support.office.com/en-us/article/odbc-drivers-that-are-compatible-with-excel-for-mac-9fa6bc7f-d19e-4f7f-9be4-92e85c77d712. for I don't import data into Excel, but instead out of Excel and I don't expect to spend money on that service? Anybody has the same situation and get it solved? My computer is Mac and has the latestest verison of Excel which is 64 bits together installed with 64 bits AMPL.

[Qiucheng Wang]

Thanks a lot!

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