Thanks all for the great suggestions.
Marshal, it doesn’t read the column that way. It returns all zero if I just specify the column in rng.
Michael, It was a very interesting idea. the approach works fine, it is much more complicated than I thought.
Edson, it was a very nice idea to use ODBC. Thanks for the great insight !
~Lorena
On Thu, Dec 16, 2010 at 7:30 AM, Edson Valle wrote:
Lorena
In your case I would try the reading using a ODBC (see the file Excel.gms in the data utilities model).
It would be something like this:
suppose your sets:
Set i
/1*5/;
Set j /
51
62
42
40
50/
;
onecho > excelcmd.txt
c=DRIVER=Microsoft Excel Driver (*.xls);dbq=MyExcelFile.xls;
q=SELECT i, amount FROM [MySheet]
o=excel.inc
$offecho
$call =sql2gms @excelcmd.txt
parameter amount(i) /
$include excel.inc
/;
display amount;
That should help. Don’t forget to see Excel.gms.
Regards
Edson
2010/12/16 Michael Bussieck
Lorena,
The gdxxrw tool cannot have the index sets and the data in
disconnected parts of a sheet. The “solution” is to read the
contiguous block with gdxxrw and then filter out in GAMS what you
don’t need. Here is a GAMS model for your example. This all works as
long as the data in the contiguous block can be read by gdxxrw (so
first indices/strings and then numerical data). The GAMS model does
the processing in two steps. First we read with datain.gms the entire
block and assemble the parameters and sets we need. In the main
program we just read the prepared sets and parameters. The reason for
the split is that in this way we can keep domain checking for the main
model while the datain model works without domain checking (almost
everything is indexed with ‘’ or aliases to '’). The model also
makes use of the projection operator: option id1 datain.gms
$call gdxxrw Book1.xlsx o=data.gdx par=data rng=Sheet1!a1 rdim=2
cdim=1
$if errorlevel 1 $abort ‘Problems with gdxxrw’
alias (*,i,j);
set h / amount, price /;
Parameter data(i,j,h);
$gdxin data
$load data
Parameter amount(i), price(i);
set ii(i), jj(j), ijmap(i,j); option ii wrote:
Hello GAMS users,
I’m trying to read a set ’ i ’ from an excel worksheet and then read
parameter amount(i) from the same worksheet. However the data related
to parameter ‘amount’ is on the 3rd column and I should skip the
second column. (This is just an example from a real model).
i j amount price
1 51 0.897238452 0.894801995
2 62 0.010895643 0.808889436
3 42 0.962863496 0.051417506
4 40 0.552773924 0.261134264
5 50 0.033257683 0.326668677
In order to do this I wrote the following GDXXRW line, however it
keeps reading the data from the second column not the one that I want:
read the set data
$call 'gdxxrw i=test.xlsx o=test.gdx set=i rng=a2 rdim=1 cdim=0 ’
$gdxin ‘test.gdx’
set i();
$load i
$gdxin
*read parameter data
$call ‘gdxxrw i=test.xlsx o=test.gdx par=res rng=a2:a11,c2:c11 rdim=1
cdim=0’
$gdxin ‘test.gdx’
parameter amount(i);
$load amount
$gdxin
display amount;
the result is 51 62 42 40 50 which is not the data for the 3rd or ‘C’
column.
I appreciate if you take a look at this code and let me know which
part I’m doing wrong that I’m not able to skip the second column.
I know one option is just removing the second column; but it’s not
desired for my case because I have lots of data and I have so many
parameters which are on different columns.
Regards,
Lorena
Edson Valle
edsoncv@gmail.com
To post to this group, send email to gamsworld@googlegroups.com.
To unsubscribe from this group, send email to gamsworld+unsubscribe@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/gamsworld?hl=en.