Writing commands linking Excel tables

[JuanD S]

Hey there

I would like to know if there are any commands or instructions available in AMPL-writing tables in Excel for: 

1 After the write table XYZ is executed, in the excel file, it appears a new tab named as: C__Users_AMPL_Results.xls and a new table named as:C__Users_AMPL_XYZ.xls How can I make the names shorten without the route file name "C__Users_AMPL" ?, for instance the tab created by AMPL is named: Results or has an specific name and the table is named XYZ.

2. Can I address the results into a specific tab that the spreadsheet already has?, for instance in my excel file I have a tab: Summary and I want the results of the table to be written in that tab in an specific range of cells.

3. I had to create a dsn file in order to modify the ReadOnly=0 in Excel ODBS so that AMPL could be able to modify the results in the Excel file as follows:

[ODBC]

Driver=Microsoft Excel Driver (*.xls)

ReadOnly=0

DBQ=C:\Users\AMPL\Results.xls

 However this procedure can be cumbersome if we have to run many scenarios at day since we have to change the name of the output file,  so: Is there a faster way to bypass these creation of dns file?

Thank you! 

[Robert Fourer]

1. If you write the table statement in the form

table XYZ IN "ODBC" "C:\Users\AMPL\XYZ.xls" "Results": ...

then the tab and table names should be "Results" as you want.

2. To write in a specific range of cells, name the range and refer to that range in the table statement. For example if you had a range of blank cells named "Results" then the above example would write to those cells. (If the range is not big enough to hold all the result values, it will be expanded.)

3. This dsn file is not normally needed to write to an xls file from AMPL. We are investigating to see if we can give some more advice here.

Bob Fourer
am...@googlegroups.com

=======

[JuanD S]

Hello Robert

Unfortunately, I didn't succeed writing the commands you suggested me and the write instruction still kept on creating a new tab and a new tablename like: C__Users_AMPL_Results.xls

I wrote:

table Results_table OUT "ODBC" 'C:\Users\AMPL\readoff.dsn'  "C:\Users\AMPL\Results.xls" "OUTPUT" "Results_table" 'verbose=1' : [PROD,WEEKS], Make, Inv, Sell;

write table Results_table;

where OUTPUT is the tab created in advance in the file "Results".

            Results_table is the name of the range created in advance in the tap OUTPUT in the file "Results"

Moreover, when I run the model for a second time it appears that the table already exists and can't overwrite:

AMPL ODBC driver, version 20151130.

Connection string: "DBQ=C:\Users\AMPL\Results.xls;Driver={Microsoft Excel Driver (*.xls)};DriverId=790;MaxBufferSize=2048;PageTimeout=5;ReadOnly=0;"

CREATE TABLE `C:\Users\AMPL\Results.xls` (`PROD` VARCHAR(6), `WEEKS` VARCHAR(6), `Make` NUMBER, `Inv` NUMBER, `Sell` NUMBER) returned -1

sqlstate = "S0001"

errmsg = "[Microsoft][Controlador ODBC Excel] Table 'C:\Users\AMPL\Results.xls' already exists."

native_errno = -1303

"DROP TABLE `C:\Users\AMPL\Results.xls`" succeeded.

ampl: 

Thank you again

[Robert Fourer]

3. In our tests, "file.dsn" can be replaced in the table statement by an equivalent string:

"DRIVER=Microsoft Excel Driver (*.xls);ReadOnly=0;DBQ=C:\Users\AMPL\Results.xls"

Then suppose you want to change the filename each time by appending the value of a param or index "t" to "Results" in the filename. Instead of a fixed string you can give a character string expression in the table statement:

("DRIVER=Microsoft Excel Driver (*.xls);ReadOnly=0;DBQ=C:\Users\AMPL\Results" & t & ".xls")

String expressions inside AMPL commands are written inside (...) and use & as the string concatenation operator. If t has a numerical value in this context it is automatically converted to a character string.

Bob Fourer
am...@googlegroups.com

=======

From: am...@googlegroups.com [mailto:am...@googlegroups.com] On Behalf Of JuanD S
Sent: Tuesday, August 8, 2017 9:48 PM
To: AMPL Modeling Language
Subject: [AMPL 14584] Writing commands linking Excel tables

[Robert Fourer]

The file C:\Users\AMPL\Results.xls is already specified in C:\Users\AMPL\readoff.dsn. Then the string "C:\Users\AMPL\Results.xls" in your table statement is interpreted as the range name in the spreadsheet, and it is converted to C__Users_AMPL_Results.xls because special characters are not allowed in range names.

Also since range names are unique in all sheets of the entire workbook, only the range name should be given in the table statement.

Thus you should drop the filename and tab name from your statement:

table Results_table OUT "ODBC" 'C:\Users\AMPL\readoff.dsn'

"Results_table" 'verbose=1' : [PROD,WEEKS], Make, Inv, Sell;

If you run "write table Results_table;" a second time, the values written the first time will be replaced in the spreadsheet by the new values. To write to a different range every time, you need define an indexed collection of tables, as in this example:

table Results_table {i in S} OUT "ODBC" 'C:\Users\AMPL\readoff.dsn'
("Results_table" & i) 'verbose=1' : [PROD,WEEKS], Make, Inv, Sell;

Then you can use a loop like

for {i in S} {
...
write table Results_table[i];

[JuanD S]

Hello Robert

Everything worked perfectly, once again THANK YOU for you sharing your ideas!

Juan D. Silva