Dear all,
I had a comparable problem where I wanted to write data to an excel file including the zero values. I made many tests based on the example in the gamswiki. However, there are some advantages and disadvantages attached to the different options. Especially with large data files it is not an option to use the variable export with squeeze=n. This will result in a gigantic excel file since excel will also save the zero’s.
Therefore, I used the parameter option writing the zero to only one row and colum of my large matrix. This option works for my own use, but as soon as I give the data to others they ask why there are zero’s in these columns. The sugestion to write text cells by epsout=’ will work, but one runs into problems when calculations are being made with these text cells. Therefore I was looking for a method to write completely empty cells to excel. The following example will exactly do this. Please not that the epsout=‘’ is not a double quote but two single quotes. Thus: epsout= =(single quote)(single quote).
This is the example code:
set i / 1*2/;
positive variable x(i), x2(i);
x.l(‘1’)=0;
x.l(‘2’)=1;
x2.l(‘1’)=0;
x2.l(‘2’)=1;
parameter y(i);
y(‘1’)=eps;
y(‘2’)=1;
- This will only export x(‘2’);
- Force the creation of x2(‘1’)
x2.scale(i) = 0.1;
execute_unload ‘x1’ x.l, x2.l, y;
Execute ‘gdxxrw x1.gdx o=x.xls Squeeze=N var=x.l rng=x!a1 dim=1’;
Execute ‘gdxxrw x1.gdx o=x.xls Squeeze=N var=x2.l rng=x!a5 dim=1’;
Execute 'gdxxrw x1.gdx o=x.xls epsout=0 par=y rng=x!a10 dim=1 ';
$onecho > task.txt
epsout= =‘’
par=y rng=x!a15 dim=1
$offecho
Execute ‘gdxxrw x1.gdx o=x.xls @task.txt’;
kind regards,
Mark Thissen
Op dinsdag 4 juni 2013 22:24:22 UTC+2 schreef Paul Van der Eijk:
EpsOut applies to what follows; so try:
execute ‘gdxxrw.exe Results.gdx EpsOut=0 var=x.l rng=range!a1 SQ=N’;
–Paul
On Tue, Jun 4, 2013 at 3:32 PM, FelDus wrote:
Hi all,
my question is the exact opposite. I am trying to have the gdx export all zeros to excel. I have tried different approaches but im just not getting there.
Is it only possible for parameters, or also for variables?
This is, among others, what ive tried:
x.l(i,t)$(NOT x.l(i,t)) = eps
execute ‘gdxxrw.exe Results.gdx var=x.l rng=range!a1 SQ=N EpsOut=0’
Thanks for any help…
Felix
Am Dienstag, 15. März 2011 00:51:40 UTC+1 schrieb AC:
We output some values from GAMS into Excel using the gdxxrw command.
We want to guarantee we get a ‘full’ set of outputs, even if the data
is zero or empty.
Normally in GAMS we set the zero values to eps, and use the option
EpsOut = 0 when exporting the data. This replaces each eps value with
0 in Excel.
We now however want to output a BLANK or empty cell rather than zero.
Having the value 0 in these particular cell is causing us some
headaches we want to avoid.
Is there a way to do this?
We tried EpsOut = “” but it complains that this is not valid.
Thanks
AndyC
\
To unsubscribe from this group and stop receiving emails from it, send an email to gamsworld+...@googlegroups.com.
Paul van der Eijk
GAMS Development Corporation
Tel : (202) 342-0180 Fax : (202) 342-0181
Email: pvand...@gams.com
Web : http://www.gams.com
–
To unsubscribe from this group and stop receiving emails from it, send an email to gamsworld+unsubscribe@googlegroups.com.
To post to this group, send email to gamsworld@googlegroups.com.
Visit this group at http://groups.google.com/group/gamsworld.
For more options, visit https://groups.google.com/d/optout.
202) 342-0180begin_of_the_skype_highlighting (202) 342-0180 end_of_the_skype_highlighting Fax : (202) 342-0181