GDX reads of Excel data where the number is stored as text

[Archiver]

Hi

We have just upgraded a model from using v22.3 to v22.6.

Unfortunately the Excel data which is read into the model has many
instances of numbers stored as text [probably a copy and paste once
long ago], and this is causing problems in v22.6 (and I tried a couple
of later versions also). This did not used to cause issues with 22.3

We will clean up the data as we go, however there are many many
different datasets (hundreds) and its very likely someone will push
one of these unfixed versions of Excel data through the model at some
stage. Is there a setting or similar for dealing with this?

Thanks
Andy
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---

[Archiver]

Andy,

I checked with our developer of gdxxrw on this issue:

"We asked Excel to give us the value of a cell; depending on the
locale setting, the value returned can be different depending on the
current decimal separator. In one locale 1.8 returns 1.8 while in an
other it returned 1.0. To avoid this, we started [in a GAMS release >
22.3] to return NA for cells that should contain a numerical value but
contain text instead (Except we look for Inf Eps etc)."

So I think there is no way of getting the numbers out of cells that
are formatted as text and start with a quote. Inside GAMS you can find
out if data read in by gdxxrw had such cells: Look for NA. You can
abort the program and tell the user to fix this in the spreadsheet.

For example,

alias (*,u);
$call gdxxrw x.xlsx o=x.gdx par=x rng=a1 rdim=1 cdim=0
$if errorlevel 1 $abort problems with gdxxrw

parameter x(u);

$gdxin x.gdx
$load x

set err_x(u) cells with text instead of numbers. Reform Excel cells to
number;
err_x(u) = x(u) = NA;

abort$card(err_x) err_x;

Hope this helps,
Michael Bussieck - GAMSWorld Coordinator

On Feb 11, 8:27 pm, AC wrote:
> > Hi
> >
> > We have just upgraded a model from using v22.3 to v22.6.
> >
> > Unfortunately the Excel data which is read into the model has many
> > instances of numbers stored as text [probably a copy and paste once
> > long ago], and this is causing problems in v22.6 (and I tried a couple
> > of later versions also). This did not used to cause issues with 22.3
> >
> > We will clean up the data as we go, however there are many many
> > different datasets (hundreds) and its very likely someone will push
> > one of these unfixed versions of Excel data through the model at some
> > stage. Is there a setting or similar for dealing with this?
> >
> > Thanks
> > Andy
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---