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.