User Tools

Site Tools


How reads gdxxrw Excel data, which is stored as text?

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 any many different data sets (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?

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
err_x(u) = x(u) = NA;

abort$card(err_x) err_x;
interfaces/how_reads_gdxxrw_excel_data_which_is_stored_as_text.txt · Last modified: 2009/02/20 10:23 by admin