write model results into excel-sheet

[Eric E Sättar]

Hi,

I am about to export the results of AMPL into an excel-sheet:

I used the following command to write one of the variable called (flow(a,b,c,h)), but I face this error,

table Oflow OUT  "tableproxy" "odbc"  "C:\Users\ampl02\Test_AMPL2017\smallLP\Results2.xls" ("Oflow"):

{a in Forest, b in SawM, c in Region, h in Assort} -> [Forest, SawM,Region, Assort], flow[a,b,c,h] ~ flow;

write table Oflow;

after running this code, the "Results2.xls" is generated, but when I open this file it says "the file is corrupt and cannot be open " (see attachment please).

I would appreciate if someone letting me know about this Error.

Sattar

[Robert Fourer]

Try this: Delete the file Results2.xls that AMPL generated. Create a new, blank spreadsheet file named Results2.xls in the folder C:\Users\ampl02\Test_AMPL2017\smallLP. Then run your test again. Afterwards double-click Results2.xls to open it, and look in the spreadsheet tab named Oflow.

Bob Fourer
am...@googlegroups.com

=======

[Eric E Sättar]

Hi Bob,

Thanks for your support,

I did the same processes you advised me (I got following error (see attach please)), the question now is, where should I put the "Results2.xls" within the code? 

table Oflow OUT  "tableproxy" "odbc"  "C:\Users\ampl02\Test_AMPL2017\smallLP" ("Oflow"):

{a in Forest, b in SawM, c in Region, h in Assort} -> [Forest, SawM,Region, Assort], flow[a,b,c,h] ~ flow;

write table Oflow;

Thanks for your support.

P.S. I apologize if I post this topic twice, seems my reply doesn't appear here,  

[Robert Fourer]

In the "table Oflow ..." statement, instead of "C:\Users\ampl02\Test_AMPL2017\smallLP" you should put the name of your spreadsheet file. For example, if Results2.xls is inside the smallLP folder, then you should specify "C:\Users\ampl02\Test_AMPL2017\smallLP\Results2.xls".

Bob Fourer
am...@googlegroups.com

=======

From: am...@googlegroups.com [mailto:am...@googlegroups.com] On Behalf Of Eric E Sättar
Sent: Tuesday, November 14, 2017 10:26 AM
To: AMPL Modeling Language

[Eric E Sättar]

Hi Bob,

Thanks for continued supports, I did your suggestions (create an excel file Results2.xls into my directory and write the printing code like this,

 table Rflow OUT  'tableproxy' 'odbc'  "C:\Users\ampl02\Test_AMPL2017\BELT\smallLP\Results2.xls" ("Rflow"):

 {a in Forest, b in SawM, c in Region, h in Assort} -> [Forest, SawM,Region, Assort], flow[a,b,c,h] ~ flow;

write table Rflow;

and have got this error (see attachment please). the message is in french  and I put English version as well, says " ODBC  excel driver login failed>>Unexpected error from external database drive (1) " 

"

Thank you so much for your time and assistance!

In the "table Oflow ..." statement, instead of "C:\Users\ampl02\Test_AMPL2017\smallLP" you should put the name of your spreadsheet file.  For example, if Results2.xls is inside the smallLP folder, then you should specify "C:\Users\ampl02\Test_AMPL2017\smallLP\Results2.xls". 

Bob Fourer 
am...@googlegroups.com 

======= 

From: am...@googlegroups.com [mailto:am...@googlegroups.com] On Behalf Of Eric E Sättar 

Sent: Tuesday, November 14, 2017 10:26 AM 
To: AMPL Modeling Language 

Subject: [AMPL 15106] Re: write model results into excel-sheet  

Hi Bob,

Thanks for your support,

I did the same processes you advised me (I got following error (see attach please)), the question now is, where should I put the "Results2.xls" within the code? 

table Oflow OUT  "tableproxy" "odbc"  "C:\Users\ampl02\Test_AMPL2017\smallLP" ("Oflow"):

{a in Forest, b in SawM, c in Region, h in Assort} -> [Forest, SawM,Region, Assort], flow[a,b,c,h] ~ flow;

write table Oflow;

[Robert Fourer]

Here are some further troubleshooting steps:

(1) Give the AMPL command "option version;" and send us the message that results;

(2) Look under File / Help in Excel and tell us whether you see "32-bit" or "64-bit" under the heading About Microsoft Excel at the right.

(3a) If Excel is 32-bit, then in the Windows Control Panel, go to Administrative Tools / ODBC Data Sources (32-bit). This will open a window headed "ODBC Data Source Administrator (32-bit)". Take a screen shot of that window and paste it into your reply.

(3b) If Excel is 64-bit, then in the Windows Control Panel, go to Administrative Tools / ODBC Data Sources (64-bit). This will open a window headed "ODBC Data Source Administrator (64-bit)". Take a screen shot of that window and paste it into your reply.

[Eric E Sättar]

Hi Bob,

Thanks for your support, I enclosed all items upon your request. Please see the attachments.

Meanwhile, I used the following command to write the variable called 'flow' data into the Results2.xls, which I already created an excel file with this name under my directory.

Thanks again.

table Rflow OUT  'tableproxy' 'odbc'  "C:\Users\ampl02\Test_AMPL2017\smallLP\Results2.xls" ("Rflow"):

 {a in Forest, b in SawM, c in Region, h in Assort} -> [Forest, SawM,Region, Assort], flow[a,b,c,h] ~ flow;

write table Rflow;

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

[Robert Fourer]

Hi Eric,

You can try the following variation to explicitly request the driver that is listed (in ODBC.JPG):

table Rflow OUT 'tableproxy' 'odbc'

"Driver=Microsoft Excel Driver (*.xls);
ReadOnly=0;DBQ=C:\Users\ampl02\Test_AMPL2017\smallLP\Results2.xls": ...

Be sure that the specification in quotes beginning Driver= is all on one line. (You don't need to specify "Rflow" as the spreadsheet range since it's also the name of the AMPL table.) Before running, check that ampltabl_64.dll, ampltabl.dll, and tableproxy32.exe are all in the same folder as ampl.exe.

If you continue to have trouble, try downloading the latest AMPL distribution -- you are running a version from 2013 (according to option.JPG) and there have been numerous fixes and updates since then. According to our records, the person at your institution who has access to our download site is Line Simoneau <Line.S...@forac.ulaval.ca>.

Finally if you are still seeing a problem with the latest version, run again with the "verbose" option like this:

table Rflow OUT 'tableproxy' 'odbc'

"Driver=Microsoft Excel Driver (*.xls);
ReadOnly=0;DBQ=C:\Users\ampl02\Test_AMPL2017\smallLP\Results2.xls" "verbose=1": ...

This will cause "write table" to produce a lot more output; send us the whole listing and we will have a look at it.

Bob Fourer
am...@googlegroups.com

=======

From: am...@googlegroups.com [mailto:am...@googlegroups.com] On Behalf Of Eric E Sättar
Sent: Sunday, November 19, 2017 10:19 PM
To: AMPL Modeling Language
Subject: Re: [AMPL 15164] Re: write model results into excel-sheet

hanks for your support, I enclosed all items upon your request. Please see the attachments.

Meanwhile, I used the following command to write the variable called 'flow' data into the Results2.xls, which I already created an excel file with this name under my directory.

[Eric E Sättar]

Hi Bob,

Thanks a lot for your kindly support. I'll try to stick with these prescriptions to see what would happen (I'll share the outcome). Since I had to write the results into txt which was more convenient. 

Bests